Stored Programs

Section 0: Module Objectives or Competencies

Section 1: Overview

A stored program is a routine (like a subprogram in most any computing language) that is stored in the database.

These notes address three types of stored programs supported by MySQL.

• Procedures – invoked as needed with the CALL statement.
• Functions – used within other SQL statements. Stored functions work just like pre-installed MySQL functions such as SUM().
• Triggers – automatically invoked when a particular event occurs.

Section 2: Justification

The effective use of stored programs provides many benefits, several of which are listed below:

While stored programs do provide significant performance benefits in some scenarios, they are not inherently faster than standard SQL statements (stored programs are not precompiled).

In other instances, the use of stored programs may decrease performance because the Query Cache in older versions of MySQL does not store the results of queries that use input parameters/variables/stored functions or queries executed within stored functions/triggers.

Section 3: Basic MySQL Syntax

Delimiter

A MySQL client program such as phpMyAdmin uses the delimiter (;) to separate statements and executes each statement separately.

However, a stored program consists of multiple statements separated by a semicolon (;).

If you use a MySQL client program to define a stored program that contains semicolon characters, the MySQL client program will not treat the whole stored program as a single statement, but many statements.

Therefore, you must redefine the delimiter temporarily so that you can pass the whole stored program to the server as a single statement.

To redefine the default delimiter, you use the DELIMITER command:

The delimiter_character may consist of a single character or multiple characters e.g., // or $$, but you should avoid using the backslash (\) because it is the escape character in MySQL.

DELIMITER //

CREATE PROCEDURE sp_name()
BEGIN
-- statements
END //

DELIMITER ;

In this code:

• First, change the default delimiter to //.
• Second, use (;) in the body of the stored program and // after the END keyword to end the stored procedure.
• Third, change the default delimiter back to a semicolon (;)

Assignment Statements

Once a variable is declared, it is ready to use. To assign a variable a value, you use the SET to assign values to variables.

SET variable_name = value;

For example:

DECLARE total INT DEFAULT 0;
SET total = 10;

The value of the total variable is 10  after the assignment.

IF-THEN Statements

The IF-THEN statement allows you to execute a set of SQL statements based on a specified condition.

The following illustrates the syntax of the IF-THEN statement:

IF condition THEN
statements;
END IF;

In this syntax:

• First, specify a condition to execute the code between the IF-THEN and END IF.
  • If the condition evaluates to TRUE, the statements between IF-THEN and END IF will execute.
  • Otherwise, the control is passed to the next statement following the END IF.
• Second, specify the code that will execute if the condition evaluates to TRUE.

For example:

IF credit > 50000 THEN
SET pCustomerLevel = 'PLATINUM';
END IF;

This IF-ELSE structure sets the value for the variable pCustomerLevel to PLATINUM if the credit limit of the customer is greater than 50,000.

IF-THEN-ELSE Statements

If you want to execute other statements when the condition in the IF branch does not evaluate to TRUE, you can use the IF-THEN-ELSE statement as follows:

IF condition THEN
statements;
ELSE
else-statements;
END IF;

In this syntax, if the condition evaluates to TRUE, the statements between IF-THEN and ELSE execute.

• Otherwise, the else-statements between the ELSE and END IF execute.

For example:

IF credit > 50000 THEN
SET pCustomerLevel = 'PLATINUM';
ELSE
SET pCustomerLevel = 'NOT PLATINUM';
END IF;

This IF-ELSE structure sets the value for the variable pCustomerLevel to PLATINUM if the credit limit of the customer is greater than 50,000.

If the credit is not greater than 50,000, we set the customer level to NOT PLATINUM in the block between ELSE and END IF.

WHILE Loop

The WHILE loop is a loop statement that executes a block of code repeatedly as long as a condition is true.

Here is the basic syntax of the WHILE statement:

WHILE search_condition DO
statement_list
END WHILE

In this syntax:

First, specify a search condition after the WHILE keyword.

The WHILE checks the search_condition at the beginning of each iteration.

If the search_condition evaluates to TRUE, the WHILE executes the statement_list as long as the search_condition is TRUE.

The WHILE loop is called a pretest loop because it checks the search_condition before the statement_list executes.

Second, specify one or more statements that will execute between the DO and END WHILE keywords.

DECLARE counter INT DEFAULT 1;
DECLARE dt DATE DEFAULT startDate;

WHILE counter <= day DO
CALL InsertCalendar(dt);
SET counter = counter + 1;
SET dt = DATE_ADD(dt,INTERVAL 1 day);
END WHILE;

The WHILE loop repeatedly inserts dates into the calendars table until the counter is equal to day.

Comments

Comments can be used to document the purpose of an SQL statement or the logic of a code block in a stored program.

When parsing SQL code, MySQL ignores the comments part and only executes the SQL part.

MySQL supports three comment styles:

1. From a '-- ' to the end of the line. The double dash-comment style requires at least whitespace or control character (space, tab, newline, etc) after the second dash

   SELECT * FROM users; -- This is a comment

2. From a '#' to the end of the line.

   SELECT
   lastName, firstName
   FROM
   employees
   WHERE
   reportsTo = 1002; # get subordinates of Diane

3. C-style comment /**/ can span multiple lines and can be used to document or even comment out a block of SQL code.

   /*
   Get sales rep employees
   that report to Anthony
   */
   
   SELECT
   lastName, firstName
   FROM
   employees
   WHERE
   reportsTo = 1143
   AND jobTitle = 'Sales Rep';

Parameters

The parameter list is required; an empty list of () should be used when creating stored programs that do not require any parameters.

Stored rocedures accept three types of parameters.

• An IN parameter passes a value into a program. This is the default parameter type.
• An OUT parameter passes a value from the procedure back to the caller. Its initial value is NULL within the procedure, and its value is visible to the caller when the procedure returns.
• An INOUT parameter is initialized by the caller, may be modified by the procedure, and any change made by the procedure is visible to the caller when the procedure returns.

Stored functions only accept one type of parameter (IN).

• However, the IN is implied and explicit use of the IN keyword in the parameter list will result in an error.

Section 4: Nested Transactions

MySQL transactions cannot be nested.

• This is a consequence of the implicit commit performed for any current transaction when you issue a START TRANSACTION statement or one of its synonyms.
• This may cause problems with stored programs that make use of transactions.

Given the following AddPubs procedure:

delimiter //
CREATE PROCEDURE AddPubs ()
BEGIN
   START TRANSACTION;
   INSERT INTO publishers (pub_id, pub_name) VALUES (234, 'New Publisher');
   INSERT INTO publishers (pub_id, pub_name) VALUES (736, 'Duplicate');
   COMMIT;
END//
delimiter ;

Now consider the following call to AddPubs procedure, nested in another transaction:

START TRANSACTION;
INSERT INTO publishers (pub_id, pub_name) VALUES (555, 'Five!');
CALL AddPubs();
ROLLBACK;

The START TRANSACTION within the AddPubs procedure performs an implicit COMMIT on the previous transaction, causing the new publisher (pub_id = 555) to be added with no possibility for rolling this change back.

SELECT * FROM publishers; displays the table contents, confirming that the table was changed in spite of the final ROLLBACK.

+--------+-----------------------+------------+-------+---------+
| pub_id | pub_name              | city       | state | country |
+--------+-----------------------+------------+-------+---------+
|    555 | Five!                 | NULL       | NULL  | USA     |
|    736 | New Moon Books        | Boston     | MA    | USA     |
|    877 | Binnet & Hardley      | Washington | DC    | USA     |
|   1389 | Algodata Infosystems  | Berkeley   | CA    | USA     |
|   1622 | Five Lakes Publishing | Chicago    | IL    | USA     |
|   1756 | Ramona Publishers     | Dallas     | TX    | USA     |
|   9901 | GGG&G                 | Mnchen     | NULL  | Germany |
|   9952 | Scootney Books        | New York   | NY    | USA     |
|   9999 | Lucerne Publishing    | Paris      | NULL  | France  |
+--------+-----------------------+------------+-------+---------+
9 rows in set (0.00 sec)

To avoid this issue, consider keeping all transactions within independent stored programs that do not call one another.

Another option is to start the transaction before invoking the procedure, create a SAVEPOINT at the beginning of the procedure, and leverage ROLLBACK TO SAVEPOINT in the error handler.