Stored Procedures



index
Disabled back button Next Section
printable version

Section 0: Module Objectives or Competencies
Course Objective or Competency Module Objectives or Competency
The student will be able to list and explain the fundamental concepts of stored programs and triggers in SQL, will gain experience developing them, and will learn how to determine when it is appropriate to use them. The student will be able to list and explain the fundamental concepts of stored procedures in SQL, will gain experience developing them, and will learn how to determine when it is appropriate to use them.
Section 1: Definition

Learning MySQL - Stored Procedures






A stored procedure is a group of one or more database statements stored in the database’s data dictionary and called from either a remote program, another stored procedure, or the command line.

Procedures can do the following:

Outputs:

Section 2: Creation

Declaration

Stored procedures are created with CREATE PROCEDURE:

CREATE PROCEDURE routine name ([[ IN | OUT | INOUT ] parameter_name type [,...]])
routine_body

Procedures accept three types of parameters. The parameter list is required; an empty list of () should be used when creating procedures that do not require any parameters.

  • An IN parameter passes a value into a procedure. 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.

CREATE PROCEDURE Stats (OUT CountAuthors INT, OUT CountTitles INT)

Invocation

Stored procedures can be invoked from a stored procedure, stored function, or trigger:

A call like CALL Stats(@authorCount, @titleCount); will invoke the procedure.

The complete sProc can be seen in the Examples section.

Body

Stored procedures are generally formed of regular SQL statements, although there are some differences.

  • Flow control constructs – specifically, IF, CASE, ITERATE, LEAVE, LOOP, WHILE, and REPEAT – may appear within stored procedures to control the order of statement execution.
  • There are restrictions on which SQL statements may appear in sProcs.
  • Stored procedures may consist of multiple statements by using a BEGIN ... END compound statement block.

Variables

Stored procedures may also declare and use their own set of local variables. DECLARE creates local variables within a stored procedure.

  • SET assigns values to variables, and SELECT ... INTO assigns the results of a query that returns a single row to variables.
  • All local variables must be declared at the beginning of the stored procedure.

Don't confuse local variables with user-defined variables. .

  • Local variables are only available within the particular stored procedure or BEGIN ... END block where they are defined.
  • User-defined variables begin with an @ symbol. They are available anywhere within your connection to the MySQL server, and allow you to store a value in one statement and then refer to it later in another statement.
  • User-defined variables cannot be declared; they can ony be initialized with a SET statement.
  • Stored procedures may make use of both local variables and user-defined variables.

BEGIN ... END Compound Statement

BEGIN ... END syntax is used for writing compound statements.

  • A compound statement can contain multiple statements, enclosed by the BEGIN and END keywords.
  • Each of the statements in a compound statement is terminated by a semicolon
  • See this link for details.

The names of stored programs, parameters, and variables are not case sensitive.

Section 3: Managing Stored Procedures

To list existing stored procedures, use SHOW PROCEDURE STATUS.

Recall the code of a particular stored procedure with SHOW CREATE PROCEDURE.

To remove stored procedures from the database, use DROP PROCEDURE.

  • To drop a stored procedure if it exists (before creating a new version) use the IF EXISTS clause, as in DROP PROCEDURE IF EXISTS <stored_procedure_name>;

To make changes to any type of stored procedure, drop and re-create the routine.

Section 4: Examples

Example: Search Procedure

To test this example, you can use the pubs database that first appeared in the Sample Databases notes.

This example uses an IN parameter and lists books by authors whose last name begins with the specified search query.

delimiter //
CREATE PROCEDURE AuthorSearch (IN varQuery VARCHAR(255))
BEGIN
SET varQuery = CONCAT(varQuery,'%');
SELECT au_id, au_lname, au_fname, title, notes
FROM titles
JOIN titleauthor USING(title_id)
JOIN authors USING(au_id)
WHERE au_lname LIKE varQuery
ORDER BY au_id, title;
END//
delimiter ;

Defining stored routines from the mysql client (command line interface) requires the use of the delimiter command.

  • Here, the statement delimiter is temporarily changed to // so the ; used in the procedure body is passed through to the server rather than being interpreted by the mysql client.

Use CALL AuthorSearch('Gr'); to invoke the procedure and display the results:

            +-------------+------------+----------+---------------------------------+-------------------------------------------------------------------------------------------------------------------------+
            | au_id       | au_lname   | au_fname | title                           | notes                                                                                                                   |
            +-------------+------------+----------+---------------------------------+-------------------------------------------------------------------------------------------------------------------------+
            | 213-46-8915 | Green      | Marjorie | You Can Combat Computer Stress! | The latest medical and psychological techniques for living with the electronic office. Easy-to-understand explanations. |
            | 472-27-2349 | Gringlesby | Burt     | Sushi, Anyone?                  | Detailed instructions on how to make authentic Japanese sushi in your spare time.                                       |
            +-------------+------------+----------+---------------------------------+-------------------------------------------------------------------------------------------------------------------------+
            2 rows in set (0.00 sec)
            

To test these stored programs, simply copy and paste the code (including the delimiter lines) into the SQL window in phpMyAdmin and click Go to create the procedure. Then you can copy and paste the CALL statement into the SQL window and click Go to test it.



Example: Prime Number Procedure

This stored procedure counts the number of prime numbers less than the input number.

delimiter //
CREATE PROCEDURE FindPrimes(IN maxNum INT)
BEGIN
DECLARE lcvOuter INT DEFAULT 2;
DECLARE lcvInner INT;
DECLARE countprimes INT DEFAULT 0;
DECLARE isprime BOOLEAN;

WHILE (lcvOuter < maxNum) DO
SET lcvInner = 2;
SET isprime = TRUE;
WHILE (lcvInner < lcvOuter) AND isprime DO
IF (MOD(lcvOuter, lcvInner) = 0) THEN
SET isprime = FALSE;
END IF;
SET lcvInner = lcvInner + 1;
END WHILE;

IF (isprime) THEN
SET countprimes = countprimes + 1;
END IF;
SET lcvOuter = lcvOuter + 1;
END WHILE;
SELECT CONCAT(countprimes, ' prime numbers less than ', maxNum) AS result;
END//
delimiter ;

Use CALL FindPrimes(1000); to invoke the procedure and display the results:

            +----------------------------------+
            | result                           |
            +----------------------------------+
            | 168 prime numbers less than 1000 |
            +----------------------------------+
            1 row in set (0.24 sec)
            

Avoid using stored programs to perform number crunching.

  • This example is only meant to illustrate the use of flow control within stored programs.
  • SQL is not optimized for performing arithmetic calculations; counting the 1,229 primes less than 10,000 with the above FindPrimes procedure can take upwards of 20 seconds on modern hardware.


Example: Statistics Procedure

Stored procedures can use OUT parameters to return individual pieces of information to the caller from the pubs database. This example returns summary statistics using parameters.

delimiter //
CREATE PROCEDURE Stats (OUT CountAuthors INT, OUT CountTitles INT)
BEGIN
SELECT COUNT(*) INTO CountAuthors FROM authors;
SELECT COUNT(*) INTO CountTitles FROM titles;
END//
delimiter ;

Use a call like CALL Stats(@a, @t); to invoke the procedure.

SET @a = 0;
SET @t = 0;
CALL Stats(@a, @t);
SELECT @a, @t;

  • As this procedure does not return any result set, the MySQLsql client only displays MySQL returned an empty result set (i.e. zero rows)..
  • With the above call, the OUT parameters returned by the procedure will be stored in the @a and @t user-defined variables.
  • Following the call, a simple SELECT @a, @t; can be used to reveal the two values, but these user-defined variables could also be used as part of a more complicated query or as input to another stored procedure.
            +------+------+
            | @a   | @t   |
            +------+------+
            |   23 |   16 |
            +------+------+
            1 row in set (0.00 sec)
            
Section 5: Resources

MariaDB Stored Procedures

MySQL Stored Procedure Tutorial






MySQL Stored Procedure 7 - Intro to Stored Procedures






MySQL Stored Procedure 8 - Procedure Syntax

Prime Numbers

Definition: Prime numbers are numbers that have only 2 factors: 1 and themselves. For example, the first 5 prime numbers are 2, 3, 5, 7, and 11. There are 1,229 primes less than 10,000.