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.
- Stored procedures are commonly called SPROCS.
- Stored procedure features and command syntax are specific to the database engine.
Procedures can do the following:
- Procedures may start, commit, or rollback transactions.
- Procedures may return multiple values using
OUT
orINOUT
parameters and/or result sets (i.e., the results of aSELECT
statement will be returned to the caller). - Procedures may be called recursively.
- Procedures may use prepared statements through the normal
PREPARE
,EXECUTE
, andDEALLOCATE PREPARE
mechanisms.
Outputs:
-
A stored procedure can return a single value such as a number or text value or a result set (set of rows).
- More on result sets if you are interested.
- Depending on how the inputs are defined, changed values to inputs can be propagated back to the calling procedure.
- MySQL stored procedures: all ways to produce an output
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 isNULL
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
, andREPEAT
– 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, andSELECT ... 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
andEND
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 inDROP 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
MySQL Stored Procedure Tutorial
MySQL Stored Procedure 7 - Intro to Stored Procedures