Advanced Stored Procedures In MySQL

In our previous article, we learned the basics of stored procedures and how they can help you manage your MySQL database efficiently. In this guide, we’ll dive deeper and discuss more advanced stored procedures concepts and techniques. By the end of this article, you’d have learned some skills that will make you more proficient in working with stored procedures in MySQL.

Stored procedures share many similarities with functions in procedural languages such as JavaScript, Python, etc. They are used to group and execute many tasks in one call. They can be called and executed repeatedly using loops. They can also be nested in one another and executed in parent-stored procedures. You can also nest MySQL functions in stored procedures and more!

Nested stored procedures

Nested stored procedures help to break up large amounts of SQL statements into smaller reusable pieces. Moving reusable logic pieces into smaller composable pieces can make your procedures more readable and easy to debug.

Whenever you write a statement that calls a procedure X in the body of another stored procedure Y, you’re nesting X in Y. The syntax looks like this:

CREATE PROCEDURE parent_procedure

BEGIN

SELECT column FROM table;

// other statements here

// nested stored procedure

CALL nested_procedure; 

ENDCode language: SQL (Structured Query Language) (sql)

Let’s demonstrate how nested stored procedures work. For this example, we’d be refactoring the film_in_stock stored procedure we created in our previous article. I’ll type it in here for reference:

DELIMITER //

CREATE PROCEDURE film_in_stock(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
BEGIN
    SELECT inventory_id
    FROM inventory
    WHERE film_id = p_film_id
    AND store_id = p_store_id
    AND inventory_in_stock(inventory_id);

    SELECT COUNT(*)
    FROM inventory
    WHERE film_id = p_film_id
    AND store_id = p_store_id
    AND inventory_in_stock(inventory_id)
    INTO p_film_count;
END //

DELIMITER;Code language: SQL (Structured Query Language) (sql)

We can take the second SELECT statement and place it into its own stored procedure named count_inventory so that we can reuse the logic in other places.

DELIMITER //

CREATE PROCEDURE count_inventory(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
BEGIN
    SELECT COUNT(*)
    FROM inventory
    WHERE film_id = p_film_id
    AND store_id = p_store_id
    AND inventory_in_stock(inventory_id)
    INTO p_film_count;
END //

DELIMITER;Code language: SQL (Structured Query Language) (sql)

The count_inventory stored procedure will accept three arguments just like it’s parent procedure:

1. p_film_id defines the variable for the film’s id
2. p_store_id defines the variable for the store’s id
3. p_film_count will hold the total number of films that were found in the inventory using the filters passed.

The parent procedure film_in_stock will now look like this:

DELIMITER //

CREATE PROCEDURE film_in_stock(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
BEGIN
    SELECT inventory_id
    FROM inventory
    WHERE film_id = p_film_id
    AND store_id = p_store_id
    AND inventory_in_stock(inventory_id);

    CALL count_inventory(p_film_id, p_store_id, p_film_count);
END //

DELIMITER;Code language: SQL (Structured Query Language) (sql)

When we call the film_in_stock stored procedure, it will make a call to the count_inventory stored procedure to count the number of films based on the condition values provided in its IN parameters:

mysql> CALL film_in_stock(1, 1, @p_film_count);
+--------------+
| inventory_id |
+--------------+
|            1 |
|            2 |
|            3 |
|            4 |
+--------------+
4 rows in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

The nested procedure count_inventory also let’s us retrieve the inventory count using the user-defined variable we pass into the OUT parameter @p_film_count:

mysql> SELECT @p_film_count;
+---------------+
| @p_film_count |
+---------------+
|             4 |
+---------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

From our example above, you would notice we called a function named inventory_in_stock. We won’t go deep into MySQL functions just yet, but the body of the function looks like this:

CREATE FUNCTION inventory_in_stock (p_inventory_id INT) RETURNS tinyint(1)
    READS SQL DATA
BEGIN
    DECLARE v_rentals INT;
    DECLARE v_out     INT;

    SELECT COUNT(*) INTO v_rentals
    FROM rental
    WHERE inventory_id = p_inventory_id;

    IF v_rentals = 0 THEN
      RETURN TRUE;
    END IF;

    SELECT COUNT(rental_id) INTO v_out
    FROM inventory LEFT JOIN rental USING(inventory_id)
    WHERE inventory.inventory_id = p_inventory_id
    AND rental.return_date IS NULL;

    IF v_out > 0 THEN
      RETURN FALSE;
    ELSE
      RETURN TRUE;
    END IF;
ENDCode language: SQL (Structured Query Language) (sql)

The point I’m trying to make is that you can also nest MySQL functions into stored procedures.

Variables in stored procedures

Variables are very useful when writing stored procedures. They hold immediate results and can be referenced anywhere in the body of a stored procedure. The value of a variable can range from primitive data types and objects to results from calculations, or data returned from functions and nested stored procedures. This value can change during the execution of the stored procedure. 

A variable is always assigned a name and you can reference that name to retrieve the current value the variable holds.

There are three types of variables in MySQL, all of which can be used in a stored procedure based on scope. These are local variables, user-defined variables and system variables. Scope in MySQL actually refers to the current context of code that determines how variables can be accessed.

Declaring local variables in stored procedures

Local variables are usually scoped within the BEGIN and END clause of a stored procedure. 

To make use of a local variable in a stored procedure, you have to first declare it using the DECLARE statement in the following syntax:

DECLARE variable_name data_type(size) [DEFAULT default_value];Code language: SQL (Structured Query Language) (sql)

The variable name follows the DECLARE statement and must obey the rules of naming columns in MySQL. Next, we specify the datatype and length of the variable. A variable’s value is NULL by default, but we can specify another value using the DEFAULT clause.

Assigning variables to new values

You can change the existing value of a variable by re-assigning it to a new value. This is done using the SET statement:

SET variable_name = value;Code language: SQL (Structured Query Language) (sql)

For example:

DECLARE total INT unsigned DEFAULT 0;
SET total = 1800;Code language: SQL (Structured Query Language) (sql)

You can also pass the result of a query into a variable using the SELECT INTO statement:

DECLARE inventory_count INT DEFAULT 0;
SELECT COUNT(*) INTO inventory_count FROM inventory;Code language: SQL (Structured Query Language) (sql)

The following stored procedure uses local variables to fetch all users that have surnames that begin with the letter A through D:

DELIMITER //

CREATE PROCEDURE fetch_users()
BEGIN
    DECLARE start CHAR(1) DEFAULT 'A';
    DECLARE end CHAR(1) DEFAULT 'D';

    SELECT lastname FROM actor WHERE lastname BETWEEN start AND end;
END //

DELIMITER;Code language: SQL (Structured Query Language) (sql)

User-defined variables

In our past example, we created a user-defined variable @p_film_count to hold the value of the inventory count of the films. User-defined variables are session specific, meaning the user variables defined in a client cannot be seen by other clients except the user can access the Performance Schema user_variables_by_thread table.

User-defined variables are always written by prefixing the letter @ with the name of the variable, e.g. @total_orders. The name of a user-defined variable consists of alphanumeric characters, ., _, and $.  To use other characters, you must quote it as a string or identifier, e.g. @'my_var' or @"my_var".

Unlike local variables, you can access a user-defined variable without declaring it. If you use a user-defined variable that has not been declared, its value will be NULL by default and it will be a type of string. User variables can be assigned a value from a limited set of data types: integer, decimal, floating-point, binary or nonbinary string, or NULL value.

SELECT @var_name;Code language: SQL (Structured Query Language) (sql)

You can initialize a user-defined variable using the SET or SELECT statement:

SET @start = 'A', @end = 'D';
SELECT lastname FROM actor WHERE lastname BETWEEN start AND end;Code language: SQL (Structured Query Language) (sql)

SELECT @start := 'A', @end := 'D';
SELECT lastname FROM actor WHERE lastname BETWEEN start AND end;Code language: SQL (Structured Query Language) (sql)

System variables

System variables are variables that are readily available for use in the MySQL server. These variables are maintained by the MySQL server and they are used to configure its operation. The system variables are usually classified into GLOBAL, SESSION or both.

 All system variables have default values and they can be set at server startup using the command line or with an option file. Some can even be changed at runtime using the SET statement. You can use system variables in expressions and even in the body of a stored procedure.

You can see the current value of a system variable used by the MySQL server with the SHOW VARIABLES statement or the SELECT statement:

SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern' | WHERE expr]
SELECT @@var_name;Code language: SQL (Structured Query Language) (sql)

When using the SHOW VARIABLES statement without the GLOBAL or SESSION modifier the default modifier is SESSION. When using the SELECT statement, you must prefix the system variable name with @@ so that the MySQL server knows you’re referencing a system variable and not a user-defined or local variable.

Conditionals (IF statement)

The MySQL IF statement allows you to execute one or more SQL statements based on a condition. The IF statement can have THEN, ELSE, and ELSEIF clauses, and is terminated using END IF;. 

The basic syntax of the IF statement is as follows:

IF condition THEN statement_list
    [ELSEIF condition THEN statement_list] ...
    [ELSE statement_list]
END IF;Code language: SQL (Structured Query Language) (sql)

When the condition evaluates to true , the statement_list (one or more SQL statements) will execute. But if no condition matches, the ELSE clause statement_list will execute.

The following stored procedure greeting checks returns a greeting based on the current time of day:

DELIMITER //

CREATE PROCEDURE greeting()
BEGIN
DECLARE hour INT DEFAULT HOUR(NOW());
DECLARE message VARCHAR(20);

IF hour < 18 THEN
SET message = "Good day!";
ELSE
SET message =  "Good evening!";
END IF;

SELECT message;
END //

DELIMITER ;Code language: SQL (Structured Query Language) (sql)

Loops in stored procedures

Loops can be used in repeating the execution of one or more SQL statements until a condition is met. To terminate a loop if a condition has been met you can use the LEAVE statement. The basic syntax of a loop is as follows:

[begin_label:] LOOP
    statement_list
END LOOP [end_label]Code language: SQL (Structured Query Language) (sql)

LOOP statements just like REPEAT and WHILE statements are permitted to use labels if need be under the following rules:

• begin_label must be followed by a colon
• begin_label can be given without end_label. If end_label is present, it must be the same as begin_label.
• end_label cannot be given without begin_label.
• Labels at the same nesting level must be distinct.
• Labels can be up to 16 characters long.

The general syntax when using a LEAVE statement in a LOOP looks like the following:

[label1:] LOOP
    ...
    /* Terminate the loop */
    IF condition THEN
        LEAVE [label1];
    END IF;
    ...
END LOOP [label1]Code language: SQL (Structured Query Language) (sql)

While the LEAVE statement is used to quit or terminate the loop, the opposite ITERATE is used to start the loop again. The syntax looks like this:

label1: LOOP    ...    /* Start the loop again */
    IF condition THEN ITERATE label1; END IF;
    ...
END LOOP label1;Code language: JavaScript (javascript)

The following example uses both statements to continue iterating or terminate the loop based on a condition:

CREATE PROCEDURE doiterate(p1 INT)
BEGIN
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN ITERATE label1; END IF;
    LEAVE label1;
  END LOOP label1;
END;Code language: SQL (Structured Query Language) (sql)

Cursors

When working with large rows of data, you may want to process the data on a row-by-row basis. That’s where cursors come in. Cursors are used to iterate through the results of a query allowing you to perform certain operations on each row of data individually.

In MySQL, Cursors are supported in stored programs such as stored procedures, stored functions, and triggers, and they have the following properties:

• Asensitive: The server may or may not make a copy of its result table
• Read only: Cursors cannot be used to modify the data of the resulting table.
• Nonscrollable: Can be traversed only in one direction and cannot skip rows

Cursors are to be declared after you must have declared variables and conditions and also before you declare any handlers:

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE a CHAR(16);
  DECLARE b, c INT;

  /* Declare cursors: cur1 and cur2 */
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;  /* Declare handler to handle the condition when cursor reaches end of result set */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  /* Open the cursors to initialize result set*/
  OPEN cur1;
  OPEN cur2;

  read_loop: LOOP

    /* FETCH retrieves the rows the cursors currently point to, then updates cursors to the next row */
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF done THEN
      LEAVE read_loop;
    END IF;
    IF b < c THEN
      INSERT INTO test.t3 VALUES (a,b);
    ELSE
      INSERT INTO test.t3 VALUES (a,c);
    END IF;
  END LOOP;

  /* Close cursors when no longer used to release memory used */
  CLOSE cur1;
  CLOSE cur2;
END;Code language: SQL (Structured Query Language) (sql)

The example above shows how cursors work in the following steps:

• First, we declare the cursors cur1 and cur2 using the DECLARE...CURSOR FOR statement. Notice how it appears after the variable declarations and before the handlers.
• Next, we open the cursors, basically, initializing them for use using the OPEN statement.
• Then we use the FETCH statement to get the next rows of data a cursor currently points to, the FETCH statement also updates the cursor to point to the following row.
• Lastly, when done with the cursors, we use the CLOSE statement to deactivate them and release the memory they use.

Notice that we also declared a handler to handle the condition when a cursor reaches the end of the result set using the following statement:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;Code language: SQL (Structured Query Language) (sql)

Error handling in stored procedures

MySQL doesn’t make use of traditional TRY…CATCH statements for error handling in stored procedures, instead it uses Condition Handling to handle errors encountered.

The DECLARE...HANDLER statement is used to specify a handler that deals with one or more conditions. It has the following syntax:

DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement



handler_action: {
    CONTINUE
  | EXIT
}

condition_value: {
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
}Code language: SQL (Structured Query Language) (sql)

From the syntax above, if one of the conditions (condition_value) are met, statement will execute. statement can be a simple statement such as SET var_name = value, or a compound statement written using BEGIN and END.

The handler_action represents the action the handler takes after the execution of the handler statement. It can be either of the following values:

• CONTINUE: Execution of the current program continues.
• EXIT: Execution terminates for the BEGIN … END compound statement in which the handler is declared. This is true even if the condition occurs in an inner block.

The condition_value indicates the specific condition or class of conditions that activates the handler. It can be one of the following types:

• mysql_error_code: This is an integer literal that indicates a MySQL error code. For example 1051 which means Unknown table '%s'. For example:

DECLARE CONTINUE HANDLER FOR 1051
  BEGIN
    -- body of handler
  END;Code language: SQL (Structured Query Language) (sql)

• SQLSTATE [VALUE] sqlstate_value: This is a 5-character string literal that indicates an SQLSTATE value, such as ‘42S01’ to specify “unknown table”. For example:

DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
  BEGIN
    -- body of handler
  END;Code language: SQL (Structured Query Language) (sql)

• SQLWARNING: This is the shorthand for the class of SQLSTATE values that begin with '01':

DECLARE CONTINUE HANDLER FOR SQLWARNING
  BEGIN
    -- body of handler
  END;Code language: SQL (Structured Query Language) (sql)

• NOT FOUND:  This is the shorthand for the class of SQLSTATE values that begin with '02'. This is usually used together with cursors to control what happens when a cursor reaches the end of a data set. When no more rows are available, MySQL raises a No Data condition with a SQLSTATE value '02000'. Setting up a handler for a NOT FOUND condition allows you to detect the condition. We illustrated how to set up such a handler when we treated cursors earlier.
• SQLEXCEPTION: This is the shorthand for the class of SQLSTATE values that begin with '00', '01', or '02'.
• condition_name: You can define your own condition name that is associated with a MySQL error code or SQLSTATE value. To declare a named error condition, you make use of the DECLARE…CONDITION statement as follows:

DECLARE condition_name CONDITION FOR condition_valueCode language: SQL (Structured Query Language) (sql)

Here condition_name represents the name for the condition and condition_value represents the MySQL error code or SQLSTATE value the condition is associated with.

The following example uses a handler for SQLSTATE ‘23000’, which occurs for a duplicate-key error:

mysql> CREATE TABLE test.t (s1 INT, PRIMARY KEY (s1));
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //

mysql> CREATE PROCEDURE handlerdemo ()
      BEGIN
        DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
        SET @x = 1;
        INSERT INTO test.t VALUES (1);
        SET @x = 2;
        INSERT INTO test.t VALUES (1);
        SET @x = 3;
      END;
      //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;

mysql> CALL handlerdemo();
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x;
+------+
| @x   |
+------+
| 3    |
+------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Scope rules for handlers

MySQL handlers are invoked based on their location within the stored program’s definition and on the conditions they handle:

• A handler declared between a BEGIN...END block is in scope only for the SQL statements following the handler’s declaration within the block. In the following example, handlers H1 and H2 are in scope for conditions raised by statements stmt1 and stmt2. But neither H1 nor H2 are in scope for conditions raised in the body of H1 or H2:

BEGIN -- outer block
  DECLARE EXIT HANDLER FOR ...;  -- handler H1
  DECLARE EXIT HANDLER FOR ...;  -- handler H2
  stmt1;
  stmt2;
END;Code language: SQL (Structured Query Language) (sql)

• A handler is in scope only for the block in which it is declared. It cannot be activated for conditions occurring outside that block. In the following example, handler H1 is in scope for stmt1 in the inner block, but not for stmt2 in the outer block:

BEGIN -- outer block
  BEGIN -- inner block
    DECLARE EXIT HANDLER FOR ...;  -- handler H1
    stmt1;
  END;
  stmt2;
END;Code language: SQL (Structured Query Language) (sql)

• A handler can be specific or general. Specific handlers are for MySQL error codes, SQLSTATE values, or condition names whereas general handlers are for conditions in the SQLWARNING, SQLEXCEPTION, or NOT FOUND class. 

Condition specificity is related to condition precedence. 

When a condition occurs in a stored program, MySQL server searches for applicable handlers in the current scope. If there are no applicable handlers, it checks outside the scope and continues outwards for any applicable handlers. When the server finds one or more applicable handlers at a given scope, it chooses among them based on condition precedence:

• A MySQL error code handler takes precedence over an SQLSTATE value handler.
• An SQLSTATE value handler takes precedence over general SQLWARNING, SQLEXCEPTION, or NOT FOUND handlers.
• An SQLEXCEPTION handler takes precedence over an SQLWARNING handler.
• For the case where several applicable handlers have the same precedence, the choice of which handler the server activates is nondeterministic and may change depending on the circumstances under which the condition occurs.

Conclusion

In this tutorial you have learned the more advanced concepts for working with stored procedures. You learned how to improve your stored procedures definitions using variables, nested procedures, loops, and conditionals. We also covered cursors and condition handling in stored procedures. Stored procedures are not the only way to bundle and execute one or more SQL statements in MySQL. In our next MySQL tutorial, we will learn how stored functions can be used in performing operations on the database, we will also explore the differences with Stored procedures. Until then, happy coding!

Also, feel free to explore the CoderPad blog for more database-oriented posts, such as these:

• SQL Functions and Techniques Every Data Person Should Know
• Optimize MySQL Database Schema
• An Introduction To Stored Procedures In MySQL

I’m Elvis Duru. I create helpful content for web developers with a focus on React, Node.js, SQL/NoSQL Databases, and more! Let’s connect on Twitter.