Stored Procedures and Cursors
Stored procedures, functions, and cursors are the latest in MySQL, initially available in MySQL 5.0.0-alpha, released January 12, 2004. Stored procedures and functions are made up of several MySQL statements that are compiled and stored in the server. And because they’re in the server, they’re available to every client and thus provides a consistent behavior system wide. You execute a procedure with aCALLcommand or, in the case of a function, embed it in your SQL statement. You don’t need to re-issue each individual SQL statement that makes up the procedure.
Stored procedures are similar to procedures in other programming languages. They’re made up of programming statements, can accept input values and outputs resulting values. The basic syntax for creating a stored procedure is
CREATE PROCEDURE procedure-name (parameter list) . . . procedure body
To rename a procedure use
ALTER PROCEDURE procedure-name NAME new-name
To drop or remove a procedure use
DROP PROCEDURE [IF EXISTS] procedure-name
where the optionalIF EXISTSis a MySQL extension.
To see the SQL statements that make up the procedure, use
SHOW CREATE PROCEDURE procedure-name
To see characteristics of a procedure such as its name, creator, database it’s in, etc., use
SHOW PROCEDURE STATUS [LIKE pattern]
If you don’t specify aLIKEpattern, all procedures will be listed.
Before you create a procedure, keep in mind the following:
- You cannot create a procedure if it already exists. So make sure that you don’t use the name of an existing procedure or that you drop that procedure before you issue aCREATE PROCEDURE.
- A procedure, unlike functions, does not have aRETURNstatement. Thus, strictly speaking, a procedure cannot return a value. However, procedure parameters may be designated as input (IN, the default), output (OUT), or input/output (INOUT). The procedure manipulates the input to produce the output which becomes available to the caller. In effect, this is how parameter passing is done in a procedure. The caller passes the input argument and the procedure returns the output.
- The body of the procedure must be bounded by aBEGINandENDblock if it contains more than one statement.
- Local variables, unlike user variables, are named without the @ character and can exist only within theBEGIN andENDblock. When the procedure block is exited, local variables cease to exist.
- You use bothSETandSELECT … INTO …commands to assign a value to a local variable, you useSELECT … INTO …to assign a value from a table. Consequently, when youSELECT … INTOan output parameterFROMa table, you must make sure it returns only one record. UseLIMIT 1if in doubt.
- With the release of MySQL 5.0.1-alpha (July 2005), each procedure or function is now associated with a database. An implicit USEstatement is made when the procedure or function is invoked. Consequenty, a USEdatabasestatement is not allowed inside the procedure. Secondly, dropping a database also drops all the procedures and functions in it. (This, by the way, is also how some database servers implement it. See, for example, Microsoft SQL Server 2000.)
- Three types of comment tags are allowed in MySQL: the # and — characters tell MySQL to ignore anything to the end of line; the /* */ pair tells MySQL to ignore anything between the /* and the */.
Note: The combination /* and— could cause a problem! For example, the following (which I’m used to doing when coding C), doesn’t work because the closing */ has been commented out by the—.
/*-------------------- comment line 1 comment line 2 ---------------------*/
Also, if you use the—comment, leave a space after the second dash. For example, this causes an error
--This is a comment
But this is okay.
----------------------------------------------------- -- This is a comment -----------------------------------------------------
- The end of query delimiter must be changed from the default semicolon (;) to another character such as the | character. This way, you can send semicolon delimited statements to the server without them getting executed until the terminating | character is encountered.
Shown below is an SQL script that creates a stored procedure called proc1. First, the number of customers matching the given ID number is selected and placed into the output parameter ocount. If only one customer is found to have the given ID, then the customer’s name is placed into the output parameter oname. The USE statement ensures that we’re using the right database and thus, need not qualify our table name with the database name, for example, Jolux.Customers to refer to the table Customers in the Jolux database.
-- File: create_proc1.sql ---------------------------------------------------------------------- -- This procedure accepts three parameters: -- incode - input parameter, the customer Id number -- oname - output parameter, the customer name -- ocount - output parameter, number of records with matching Id ---------------------------------------------------------------------- USE Jolux; DROP PROCEDURE IF EXISTS proc1; DELIMITER | CREATE PROCEDURE proc1 (INOUT incode INT, OUT oname VARCHAR(24), OUT ocount INT) BEGIN SELECT COUNT(*) INTO ocount FROM Customers WHERE id = incode; IF ocount > 1 THEN SELECT 'Eeek! Query returns more than 1 record.' INTO oname; ELSEIF ocount = 1 THEN SELECT name INTO oname FROM Customers WHERE id = incode; ELSE SET oname = 'ID number entered is invalid'; END IF; END |
To create the stored procedure execute the script from the Bash shell, as in the following.
[joel@localhost joel]$ mysql -u joel -p < create_proc1.sql Enter password:
To execute the stored procedure, login and connect to the Joluxdatabase.
[joel@localhost joel]$ mysql -p Jolux Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 47 to server version: 5.0.0-alpha-standard Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
Now, execute the stored procedure as in the following. Note that there must not be a space between the function name and the opening parenthesis. This is how MySQL distinguishes a function or procedure call from a reference to a column name.
mysql> SET @custid = 35; Query OK, 0 rows affected (0.00 sec) mysql> CALL proc1(@custid, @custname, @count); Query OK, 0 rows affected (0.00 sec) mysql> SELECT @custid, @custname, @count; +---------+-----------+--------+ | @custid | @custname | @count | +---------+-----------+--------+ | 35 | Magno | 1 | +---------+-----------+--------+ 1 row in set (0.00 sec)
A stored function is just like a procedure, except that it must specify a return type and must have aRETURNstatement. Creating a stored function takes the following basic form:
CREATE FUNCTION function-name (parameter list) RETURNS data-type . . . RETURN statement, etc . . .
Renaming and dropping a function use the same syntax used in renaming and dropping a procedure.
ALTER FUNCTION function-name NAME new-name DROP FUNCTION [IF EXISTS] function-name
To see the SQL statements that make up the function, use
SHOW CREATE FUNCTION function-name
And to see characteristics of a function, use
SHOW FUNCTION STATUS [LIKE pattern]
If you don’t specify aLIKEpattern, all functions will be listed.
Currently, you cannot use SQL query statements with stored functions ot you get an error like this
ERROR 1314 (0A000): Statements like SELECT, INSERT, UPDATE (and others) are not allowed in a FUNCTION.
However, the documentation at MySQL.com states that this restriction should soon be lifted.
Here’s a script that creates a function that accepts no argument and returns a string.
USE Jolux; DROP FUNCTION IF EXISTS f1; DELIMITER | CREATE FUNCTION f1() RETURNS VARCHAR(32) RETURN 'Eeeeek!'; |
Here’s how it’s invoked.
mysql> SELECT f1(); +---------+ | f1() | +---------+ | Eeeeek! | +---------+ 1 row in set (0.00 sec)
Here’s a function that accepts an integer and returns a string.
USE Jolux; DROP FUNCTION IF EXISTS f2; DELIMITER | CREATE FUNCTION f2(x int) RETURNS VARCHAR(32) BEGIN IF x > 0 THEN RETURN CONCAT('You gave me ', x, '.'); END IF; RETURN 'You gave me nothing!'; END |
Here’s how it’s invoked.
mysql> SELECT f2(100); +------------------+ | f2(100) | +------------------+ | You gave me 100. | +------------------+ 1 row in set (0.00 sec)
DECLARE and SET Statements
TheDECLAREstatement is used to define various items local to a procedure or function, for example, local variables, handlers and cursors. ADECLAREmust only appear inside aBEGIN … ENDblock before any other SQL statements. In MySQL 5.0.2-alpha, variables must be declared first at the beginning of the statement block and cursors must be declared before any handlers.
CREATE PROCEDURE procedure_name(parameters list . . .) BEGIN DECLARE variable_name type; DECLARE cursor-name CURSOR FOR sql-statement DECLARE handler_type FOR SQLSTATE '02000' OPEN cursor-name sql statements . . .
SQL query statements return rows in a set and you operate all the rows in that set at the same time. Cursors provide a way to manipulate result sets one row at a time, in a way similar to how an API such as PHP manipulates them.
Cursors like stored procedures are new to MySQL, appearing only since MySQL 5.0.0-alpha. Why this late? Well, there really has been no clear and present danger from not implementing cursors since MySQL normally goes with PHP, Perl or Python — and Linux and Apache, if you’re thinking LAMP — where the result set can be manipulated one row at a time.
MySQL cursors are read only and one directional, forward only cursor. That is, when you fetch a row, you get the next row that hasn’t been fetch or anSQLSTATE ‘02000’when all the rows have been fetched. You cannot go backward or forward at will. A cursor must be created and opened before it can be used, and it must be closed after it’s used. You can open the cursor again, in which case, you’ll get back the beginning of the result set.
ADECLAREstatement creates a cursor and anOPENmakes it ready for use.
DECLARE cursor-name CURSOR FOR sql-statement OPEN cursor-name
AFETCH…INTOstatement reads a row from the result set.
FETCH cursor-name INTO variable-name1, ...
And when the cursor is no longer required, aCLOSEstatement makes the cursor unavailable. (Unlike other systems, there is noDEALLOCATEcommand in MySQL to release the data structure comprising the cursor and other resources associated with it.)
Following is a procedure file, tcursor1.sql, that builds a simple cursor named cur to contain records from theBadCustomers table. Customers that owe more than 1,000.00 is marked “A thief!.” Though simple, it contains all the necesary steps for creating a more elaborate cursor. These steps are:
- Create all required variables: the bit to indicate the end of the result set and the variables to hold data fetched from the cursor, in this case, the customer name and the amount owed.
DECLARE at_end BIT DEFAULT 0; DECLARE custname VARCHAR(32); DECLARE amount decimal(7,2);
- Create the cursor. (Simply think of the cursor as a file containing rows of data, in this case, name and amount_owed.)
DECLARE cur CURSOR FOR SELECT name, amount_owed FROM BadCustomers;
- Set up the handler for SQLSTATE ‘02000’, a condition reached when there’s no more row to be fetched. Here, the handler simply turns the bit at_endon to tell us that we’ve reached the end of the result set.
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET at_end = 1;
- Open the cursor to make it ready for use.
- Fetch and process each record unless, of course, we’ve reached the end of cursor.
FETCH cur INTO custname, amount; WHILE (NOT at_end) DO IF amount > 1000.00 THEN SELECT concat("A thief! ", custname, " ", amount) as ''; ELSE SELECT concat(custname, " ", amount) as ''; END IF; FETCH cur INTO custname, amount; END WHILE;
If you want to useREPEATinstead ofWHILE, you can do this.
REPEAT FETCH cur INTO custname, amount; IF NOT at_end THEN IF amount > 1000.00 THEN SELECT concat("A thief! ", custname, " ", amount) as ''; ELSE SELECT concat(custname, " ", amount) as ''; END IF; END IF; UNTIL (at_end) END REPEAT;
- Finally, close the cursor.
Here’s the complete script.
-- File name: tcursor1.sql USE Jolux; DROP PROCEDURE IF EXISTS tcursor1; DELIMITER | CREATE PROCEDURE tcursor1() BEGIN DECLARE at_end BIT DEFAULT 0; DECLARE custname VARCHAR(32); DECLARE amount decimal(7,2); DECLARE cur CURSOR FOR SELECT name, amount_owed FROM BadCustomers; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET at_end = 1; OPEN cur; FETCH cur INTO custname, amount; WHILE (NOT at_end) DO IF amount > 1000.00 THEN SELECT concat("A thief! ", custname, " ", amount) as ''; ELSE SELECT concat(custname, " ", amount) as ''; END IF; FETCH cur INTO custname, amount; END WHILE; CLOSE cur; END; | DELIMITER ; CALL tcursor1();
The last two statements switches the delimiter back to the default semicolon and then executes the procedure. In my system, this produces the following output.
[joel@localhost joel]$ mysql -p < tcursor2.sql Enter password: A thief! Imelda Marcos 66666.66 Ferdie Marcos 999.66 A thief! Joseph Estrada 12995.99
Here’s another example using cursor. This one returns (asOUTPUT) the customer (name and amount owed) that owes the least and the greatest amount.
-- File name: testcursor.sql USE Jolux; DROP PROCEDURE IF EXISTS testcursor; DELIMITER | CREATE PROCEDURE testcursor(OUT max_name varchar(24), OUT max_amt decimal(7,2), OUT min_name varchar(24), OUT min_amt decimal(7,2)) BEGIN DECLARE at_end BIT DEFAULT 0; DECLARE custname VARCHAR(32); DECLARE amount decimal(7,2); DECLARE cur CURSOR FOR SELECT name, amount_owed FROM BadCustomers; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET at_end = 1; SET max_name = ''; Set max_amt = 0.0; SET min_name = ''; Set min_amt = 9999999.99; OPEN cur; FETCH cur INTO custname, amount; WHILE (NOT at_end) DO IF amount < min_amt THEN SET min_amt = amount; SET min_name = custname; END IF; IF amount > max_amt THEN SET max_amt = amount; SET max_name = custname; END IF; FETCH cur INTO custname, amount; END WHILE; CLOSE cur; END; | DELIMITER ; call testcursor(@hiname, @hiamt, @loname, @loamt); select CONCAT(@loname, ' - ', @loamt) 'Lowest'; select CONCAT(@hiname, ' - ', @hiamt) 'Highest';
Here’s the result.
[joel@localhost joel]$ mysql -p < testcursor.sql Enter password: Lowest Ferdie Marcos - 999.66 Highest Imelda Marcos - 66666.66
Sometimes it’s necessary that we handle aparticular error condition ourselves, specify what we want done, instead of letting MySQL handle it by default. For example, MySQL would quickly abort when there’s a primary or unique key violation. But what we could do, if we wanted to prevent this, is to create a stored procedure that would, for example, let MySQL continue and just ignore duplicate key violations. The need to personally handle a particular condition, is even more apparent with cursors. You can never tell that you’ve reached the end of the cursor and have fetched all the rows unless you examine a condition identified asSQLSTATE ‘02000’.
Error conditions or states are given as an alphanuneric code known as SQLSTATE. While there are only two types of handlers available, CONTINUEand EXIT, there are plenty of SQLSTATE. (See the MySQL documentation for a complete list of Error codes and SQLSTATE codes.)
Shown below is a stored procedure to illustrate the use of a handler. The very first thing the procedure does is to declare a handler for each of the two types of error conditions that we want to handle in this example:
- when there’s a primary or duplicate key violation (SQLSTATE ‘23000’) and
- when there’s a duplicate column name violation, i.e., when you use the same column name in the same table (SQLSTATE ‘42S21’).
The handler declaration for anSQLSTATEhas the following simplified syntax.
DECLARE [CONTINUE | EXIT] HANDLER FOR SQLSTATE code SQL-statement
Without the’42S21’handler, the MySQL engine would abort the entire script as soon as theCREATE TABLEstatement is encountered since it contains duplicate column names. Likewise, without the’23000’handler, it would abort when it encounters theINSERTstatement since that statement would cause a duplicate key violation.
-- File name: testhandler.sql USE Jolux; DROP PROCEDURE IF EXISTS testhandler; DELIMITER | CREATE PROCEDURE testhandler() BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' set @x = 'DUP KEY handler works'; DECLARE CONTINUE HANDLER FOR SQLSTATE '42S21' set @y = 'DUP COLUMN handler works'; CREATE TABLE testt(a int, a int, a int); -- Dup column SELECT CONCAT('The ', @y, ' if you see this') AS ''; INSERT INTO BadCustomers (id) VALUES (20); -- Dup key INSERT INTO BadCustomers (id) VALUES (20); SELECT CONCAT('The ', @x, ' if you see this') AS ''; END; | DELIMITER ; call testhandler();