MySQL Stored Procedure Tutorial

1. Introduction

In an enterprise level application, there is always a need to perform certain specific set of tasks on the database on a regular basis like database cleanup, processing payroll, creating a new entity with several default entries and lots more. Such a task could involve execution of multiple queries for each task. This process could be eased if there was a way to group these tasks into a single task and execute it. MySQL Stored Procedure is precisely for this purpose. MySQL stored procedure is a pieced of pre-compiled SQL code which can be executed to run multiple tasks along with performing certain logical operations. A MySQL procedure is analogous to functions in a general programming language. This article covers the creation of MySQL procedures step-by-step using a practical use-case scenario.

2. Getting started with MySQL procedures

To begin with, let us create a simple zero action procedure. A zero action procedure is a procedure that will not really perform any database task but provide us a feedback that the procedure executed successfully. Before we get started with the code, here are the pre-requisites to proceed with the tutorial.

• MySQL server up and running
• MySQL shell running

Once you have the MySQL shell started, select the database for the tutorial. If you do not have a database, use the below commands to create and select database.

mysql> create schema procedure_training;
mysql> use procedure_training;

These commands will create the schema and select it. Now, let us understand the syntax of a simple procedure creation without variables before we create our first procedure.

mysql> DELIMITER //
mysql> CREATE PROCEDURE PROCEDURE_NAME()
    -> begin
    -> PROCEDURE_BODY
    -> end
    -> //

A procedure contains new line characters as well as semicolons in its statement. Hence, the first step before creating a procedure is to change the delimiter that decides when a statement actually ends. Hence the code DELIMITER // has been written with the goal to indicate that the statement ends only when the MySQL prompt encounters the // symbol.
To create a procedure, start with the statement CREATE PROCEDURE PROCEDURE_NAME() where the PROCEDURE_NAME can be replaced with the name of your choice. The next statement contains the keyword BEGIN. It indicates that the body of the procedure begins from that point. The next part can be a single statement or multiple statement replacing the PROCEDURE_BODY in the above code. Finally, once the procedure body is completed, put the keyword END to indicate that the procedure body ends there.

Now that we have a basic understanding of the syntax, let us create our first procedure using the code below.

mysql> DELIMITER //
mysql> CREATE PROCEDURE myfirstprocedure()
    -> begin
    -> select 'This is my first procedure';
    -> end
    -> //
Query OK, 0 rows affected (0.06 sec)

In the above code, we create a procedure named myfirstprocedure. To keep the procedure simple, all we put in the body is a single statement to display a comment. As long as the entire code is written as expected, you will see the output as shown above.

Once the query is executed successfully, you can call the procedure using a simple command shown below.

mysql> delimiter ;
mysql> call myfirstprocedure();
+----------------------------+
| This is my first procedure |
+----------------------------+
| This is my first procedure |
+----------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

As it can be seen, I first changed the delimiter back to semicolon to avoid the trouble of writing // to end the statement. Once changed, execute the call statement to execute the procedure. Since, the body just contains a comment, it is displayed as shown above. Let us now create a slightly complex procedure.

3. Creating a procedure to insert records

An insert procedure is used to insert a bunch of predefined entries on creation of a specific record. A perfect use case of it is to assign few default access rights to a user when the user is created. In order to do such an activity, we need a table to work upon. Create a table using the create statement as provided below:

CREATE TABLE `procedure_training`.`user_roles` (
  `id` INT NOT NULL,
  `rolename` VARCHAR(45) NULL,
  `userid` INT NULL,
  PRIMARY KEY (`id`));

The next step is to create a procedure that insert a bunch of roles for userid value 1. This can be done using below procedure.

DELIMITER //
 CREATE PROCEDURE insertdefaultroles()
   BEGIN
   insert into user_roles values (1, 'admin',1), (2, 'employee',1),(3,'learner',1);
   END //
 DELIMITER ;

The above procedure inserts 3 records in the table when executed. The output of the same has been shown below.

mysql> call insertdefaultroles();

Query OK, 3 rows affected (0.14 sec)

mysql> select * from user_roles;
+----+----------+--------+
| id | rolename | userid |
+----+----------+--------+
|  1 | admin    |      1 |
|  2 | employee |      1 |
|  3 | learner  |      1 |
+----+----------+--------+
3 rows in set (0.01 sec)

4. Dropping a procedure

When you need to re-create the procedure, just like tables, you need to drop the procedure before creating it again. In an alternate database like Oracle DB, there is support to create or replace a procedure by using CREATE OR REPLACE instead of the plain CREATE. However, in MySQL, it is mandatory to delete the procedure before you could re-create it. As we proceed in the tutorial, we would require to drop the procedure frequently. The query to drop a procedure is similar to the DROP query for MySQL tables. A query to drop the procedure insertdefaultroles has been shown below.

mysql> DROP PROCEDURE insertdefaultroles;

5. Variables in procedure

As you must have noticed in the above code, we supplied static user id and primary key value. This renders the procedure unusable after it has been used once. Hence, we need a way out to automate these values. Let us begin with the value of primary key first.

5.1 Adding variables inside procedure

The first step to use a variable is to declare it. Once it has been declared, we need to get the maximum value of primary key variable currently in the table. The code below does precisely the same.

DROP PROCEDURE insertdefaultroles; 
DELIMITER //
 CREATE PROCEDURE insertdefaultroles()
   BEGIN
   DECLARE pkvalue int;
   select max(id) into pkvalue from user_roles;
   insert into user_roles values (pkvalue+1, 'admin',1), (pkvalue+2, 'employee',1),(pkvalue+3,'learner',1);
   END //
 DELIMITER;

The code contains a drop procedure statement to drop the procedure before recreating it. If you do not do so, you would encounter an error mentioning that the procedure already exists. Once you are done with the execution of the code, call the procedure once again to view the results. The results would should that new entries have been added to the table with increasing value of primary keys.

mysql> call insertdefaultroles();
Query OK, 3 rows affected (0.07 sec)
mysql> select * from user_roles;
+----+----------+--------+
| id | rolename | userid |
+----+----------+--------+
|  1 | admin    |      1 |
|  2 | employee |      1 |
|  3 | learner  |      1 |
|  4 | admin    |      1 |
|  5 | employee |      1 |
|  6 | learner  |      1 |
+----+----------+--------+
6 rows in set (0.00 sec)

5.2 Adding procedure parameters

Although the trouble of dynamic primary key has been resolved, the user id is still static. The user id cannot be retrieved from the database like the primary key. Hence, the user id needs to be passed to the procedure in some way. Procedure allows function parameters for this.

DROP PROCEDURE insertdefaultroles; 
DELIMITER //
 CREATE PROCEDURE insertdefaultroles(uid int)
   BEGIN
   DECLARE pkvalue int ;
   select max(id) into pkvalue from user_roles;
   insert into user_roles values (pkvalue+1, 'admin',uid), (pkvalue+2, 'employee',uid),(pkvalue+3,'learner',uid);
   END //
DELIMITER ;

The above code declares one such procedure. This procedure takes the user id in the variable uid. This variable can be used to replace the value of user id in the query. Execute the procedure and ensure that the execution is successful.

To run the procedure, pass the user id just like you pass it in a normal function. The code below shows the execution and results of the execution too.

mysql> call insertdefaultroles(2);
Query OK, 3 rows affected (0.10 sec)

mysql> select * from user_roles;
+----+----------+--------+
| id | rolename | userid |
+----+----------+--------+
|  1 | admin    |      1 |
|  2 | employee |      1 |
|  3 | learner  |      1 |
|  4 | admin    |      1 |
|  5 | employee |      1 |
|  6 | learner  |      1 |
|  7 | admin    |      2 |
|  8 | employee |      2 |
|  9 | learner  |      2 |
+----+----------+--------+
9 rows in set (0.01 sec)

As it can be seen, the new user id 2 was passed in the procedure parameter and the same has been replaced in the query. The procedure call now inserts the default roles for the user id 2.

6. Returning a specific value from a procedure

Until now, we either returned the results of procedure execution or returned a comment by executing a query. Now the need comes to perform a certain calculation and return the evaluated value. For instance, let us count the number of user having a specific role. In order to count the users, you would need to search the users by a specific role and get a count of records. In order to do so, execute the below query.

$ mysql> select count(*) from user_roles where rolename = 'admin';
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

The query outputs the number of users as it can be seen above. This count of users can be returned as a value from a procedure. In order to do so, you need to select the value in a similar way as the comment. The below code shows such an implementation for you.

DROP PROCEDURE countusers; 
DELIMITER //
 CREATE PROCEDURE countusers(role varchar(45))
   BEGIN
   DECLARE usercount int ;
   select count(*) into usercount from user_roles where rolename = role;
   select usercount;
   END //
 DELIMITER ;

The above code creates a procedure that takes the value of count into a variable usercount and selects it to return the related value. As it can be noticed, the output of the first query is not displayed as it is overshadowed by the query that follows. In a procedure, the output is always dependent on the query that is executed at the end unless a deliberate select of a variable is executed in between.

7. Conditional flows in a procedure

So far, we have seen simple use cases of procedure. These use cases involve queries that are not really complicated. In this section, we would proceed with branching the flow of procedure conditionally. This is where the real application of procedure comes into picture. Conditional flows allow you to take decision on what operation needs to be done based on a query or a variable that is passed. Before we get into write a procedure containing a case, let us understand its syntax. The below code snippet shows the procedure syntax for a procedure containing cases.

CREATE PROCEDURE procedure_name
BEGIN
CASE case_value
    WHEN expression THEN queries_to_be_executed
    [WHEN expression THEN queries_to_be_executed] ...
    [WHEN expression THEN queries_to_be_executed] ...
    [ELSE queries_to_be_executed]
END CASE
END

In the above syntax, the items mentioned in square braces are optional. In the above procedure, WHEN indicates the starting of an if condition. The keyword CASE signifies the beginning of a conditional branching. The expression statement could be any variable or an expression being evaluated. THEN is used to indicate the beginning of body if the expression evaluates to true.

The rest of the statements are optional. They are used to add more if conditions and an else condition in case none of the provided expressions are evaluated to true. Now, proceeding towards writing an actual procedure. We would use simple body statements to understand the conditional branching well. In general, you could use any SQL query statements in the body.

CREATE PROCEDURE firstcaseprocedure(variable varchar(10))
BEGIN
CASE variable
    WHEN 'first' THEN select 'First condition executed';
    WHEN 'second'  THEN select 'Second condition executed';
    WHEN 'third'  THEN select 'Third condition executed';
    ELSE select 'Else condition executed';
END CASE;
END;

In the above code, carefully check the semicolons and single quotes placed. They are the major cause of errors if at all you encounter one. Once the procedure is created, test the procedure execution for each test case. The testing of the procedure has been shown below.

mysql> call firstcaseprocedure('first');
+--------------------------+
| First condition executed |
+--------------------------+
| First condition executed |
+--------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call firstcaseprocedure('second');
+---------------------------+
| Second condition executed |
+---------------------------+
| Second condition executed |
+---------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call firstcaseprocedure('third');
+--------------------------+
| Third condition executed |
+--------------------------+
| Third condition executed |
+--------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call firstcaseprocedure('xyz');
+-------------------------+
| Else condition executed |
+-------------------------+
| Else condition executed |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

As it can be seen in the above execution results, the output obtained is conditional based on the supplied argument. Although we supplied the argument, it is also possible to define the argument within the procedure. For instance, consider a procedure that updates whether a student passed or failed. In such a procedure, the procedure dynamically retrieves the marks and check whether the student passed or fail and updates the record accordingly.

A sample procedure with a similar implementation is shown below. The table has been assumed to be existing as the example is just to clarify a use case.

CREATE PROCEDURE passorfail(sid int)
BEGIN
DECLARE marks int;
select student_marks into marks from student_result where student_id = sid;
CASE 
    WHEN marks<35 THEN select 'Fail';
    WHEN marks<50 THEN select 'C Grade';
    WHEN marks<70 THEN select 'B Grade';
    ELSE select 'A Grade';
END CASE;
END;

Here, notice the difference in the case carefully. The case variable is no longer common between the statements. Whenever we need to use comparison or logical operators, we need to use it the way it is shown above. The condition marks<35 can be replaced by any relevant condition involving a different variable too.

In the above code, the flow control takes place using CASE statement. However, often we are more comfortable using IF…. ELSE for its simplicity in using the expressions. Unlike CASE, where you are allowed to evaluate only one specific variable in the condition, IF…ELSE allows you to check for multiple variables with different combinations. Let us understand the syntax of an IF…ELSE statement in procedure.

IF search_condition THEN query_statements
    [ELSEIF search_expression THEN query_statements] ...
    [ELSE statement_list]
END IF

The above syntax can be updated and used in any procedure body. The statements in the square braces are optional and can be avoided if not required. The query_statements part can contains single or multiple SQL conditions to be executed if the search_condition evaluates to true. The above CASE…WHEN statement is converted into IF…ELSE in the code below.

CREATE PROCEDURE passorfail(sid int)
BEGIN
DECLARE marks int;
select student_marks into marks from student_result where student_id = sid;
IF marks<35 THEN select 'Fail';
ELSEIF marks<50 THEN select 'C Grade';
ELSEIF marks<70 THEN select 'B Grade';
ELSE select 'A Grade';
END IF
END;

Observe the amount of simplicity it introduces. It just gets easier to understand conditions for even a layman.

8. Looping through records

Notice that in the above use case of student marks, all we did is print the pass grade of a student based on the supplied id. A procedure as discussed before is used to do large pre-defined tasks. Hence, it would be more useful if one could update the grade of each student by just calling the procedure. In order to do so, let us create a table and add few details to it. Execute the below queries in order to do the same.

mysql> create table student_marks (student_id int, marks int, grade varchar(5));
Query OK, 0 rows affected (0.12 sec)

mysql> insert into student_marks values (1, 50,null),(2,60,null),(3,34,null),(4,70,null),(5,43,null);
Query OK, 5 rows affected (0.07 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from student_marks;
+------------+-------+-------+
| student_id | marks | grade |
+------------+-------+-------+
|          1 |    50 | NULL  |
|          2 |    60 | NULL  |
|          3 |    34 | NULL  |
|          4 |    70 | NULL  |
|          5 |    43 | NULL  |
+------------+-------+-------+
5 rows in set (0.01 sec)

Now, we would create a procedure that checks the marks of each student and assigns a grade to each student. Before proceeding, let us understand the syntax and terminologies. The code snippet shows the syntax of the procedure containing a loop.

CREATE PROCEDURE curdemo()
BEGIN
  //Declaration of variables
  //Open cursors

  read_loop: LOOP
    //FETCH Cursors
    //Loop through cursors
    IF done THEN
      LEAVE read_loop;
    END IF;
    //BODY
    END IF;
  END LOOP;
  //Close cursors
END;

In the above syntax, the term to be noted is Cursor. A cursor is similar to a pointer to a row in the database. You could assign a result set to a cursor. Once assigned, the cursor can help you iterate through the records one by one. The read_loop label is used to continue an iteration from a specific label. Thus, when you write read_loop:LOOP, the code keep rotating until it encounters the statement LEAVE read_loop. Now that we have understood the syntax, let us write our first procedure with an iteration in it.

CREATE PROCEDURE iterdemo()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE sid int;
  DECLARE mks int;
  DECLARE cur1 CURSOR FOR select student_id,marks from student_mks;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN cur1;
  
  read_loop: LOOP
    FETCH cur1 INTO sid, mks;
    IF  done THEN LEAVE read_loop; END IF;
    IF mks<35 THEN update student_marks set  grade ='Fail' where student_id = sid;
	ELSE update student_marks set  grade ='Pass' where student_id = sid; 
	END IF;
  END LOOP;
  CLOSE cur1;
END;

The above procedure assigns the result status of a student – whether the student has passed the exam or failed based on the scored marks. The procedure executes a loop around the records of table student_marks. The cursor basically points to the records that are selected. As it can be seen in the above code, the cursor cur1 points to all the records of student_marks table. On executing the loop read_loop, the cur1 cursor is used to fetch data row by row. The statement FETCH cur1 into sid,mks puts the data of current row into the variables and moves the cursor to point to the next row. Thus, each row is being processed for the if condition provided in the body. The if condition checks for the marks and decides whether the student passed or failed the exam. Accordingly the rows of the table will be updated. On calling the procedure, you can check the results to be similar to the one shown below:

mysql> delimiter ;
mysql> call iterdemo();
Query OK, 0 rows affected (0.01 sec)

mysql> select * from student_marks;
+------------+-------+-------+
| student_id | marks | grade |
+------------+-------+-------+
|          1 |    50 | Pass  |
|          2 |    60 | Pass  |
|          3 |    34 | Fail  |
|          4 |    70 | Pass  |
|          5 |    43 | Pass  |
+------------+-------+-------+
5 rows in set (0.00 sec)

As it can be seen, only the grade for student with marks below 35 is updated to fail. We can also use an IF ELSE ladder to update the exact grade of the student. The below code does the same.

mysql> drop procedure iterdemo;
Query OK, 0 rows affected (0.12 sec)
mysql> DELIMITER //
mysql> 
mysql> CREATE PROCEDURE iterdemo()
    -> BEGIN
    ->   DECLARE done INT DEFAULT FALSE;
    ->   DECLARE sid int;
    ->   DECLARE mks int;
    ->   DECLARE cur1 CURSOR FOR select student_id,marks from student_marks;
    ->   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    ->   OPEN cur1;
    ->   read_loop: LOOP
    ->     FETCH cur1 INTO sid, mks;
    ->     IF  done THEN LEAVE read_loop; END IF;
    ->     IF mks ELSEIF mks ELSEIF mks ELSE update student_marks set  grade ='A' where student_id = sid; 
    -> END IF;
    ->   END LOOP;
    ->   CLOSE cur1;
    -> END;//
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter ;
mysql> call iterdemo;
Query OK, 0 rows affected (0.05 sec)
mysql> select * from student_marks;
+------------+-------+-------+
| student_id | marks | grade |
+------------+-------+-------+
|          1 |    50 | B     |
|          2 |    60 | B     |
|          3 |    34 | Fail  |
|          4 |    70 | A     |
|          5 |    43 | C     |
+------------+-------+-------+
5 rows in set (0.00 sec)

The above code processes the marks value for multiple IF condition and updates the grade accordingly. This is precisely how a procedure can help in doing major tasks with simplicity.

9. Conclusion

To conclude, procedure is a set of rules and queries to be executed in order to perform database intensive tasks with simplicity. With the procedures, you can execute normal SQL queries, branch the query flow conditionally and iterate through a list of records and do mass update when necessary.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.