What are Stored Procedures
MySQL 5.0 finally introduces functionality for Stored Procedures. So what exactly are stored procedures? That is the kind of question that gets database professionals who use other DBMS's raising their eyebrows. Stored procedures have been integral to Oracle, PostgreSQL, DB-2, MS-SQL server and others for years, and it has long been a sore point that MySQL has not had them. But there is no snobbery here - if you are a MySQL newbie, or have been using MySQL for years and want to find out what all the fuss is about, read on. If it is your eyebrows that are raised, and you just want to know how MySQL implements them, you will be relieved to know MySQL stored procedures are very similar to the DB2 implementation, as both are based on the SQL:2003 standard.
A stored procedure is simply a procedure that is stored on the database server. MySQL developers have to date unthinkingly written and stored their procedures on the application (or web) server, mainly because there hasn't been an option. That has been limiting. Some have claimed that there are two schools of thought - one claiming that logic should be in the application, the other saying it should reside in the database. However, most professionals would not bind themselves to one or other viewpoint at all times. As always, there are times when doing either makes sense. Unfortunately, some of the staunchest adherents of the in the application school are only there because until now they have had no choice, and it is what they are used to doing. So why would we want to place logic on the database server?
Why use stored procedures?
· They will run in all environments, and there is no need to recreate the logic. Since they are on the database server, it makes no difference what application environment is used - the stored procedure remains consistent. If your setup involves different clients, different programming languages - the logic remains in one place. Web developers typically make less use of this feature, since the web server and database server are usually closely linked. However, in complex client-server setups, this is a big advantage. The clients are automatically always in sync with the procedure logic as soon as its been updated.
· They can reduce network traffic. Complex, repetitive tasks may require getting results, applying some logic to them, and using this to get more results. If this only has to be done on the database server, there is no need to send result sets and new queries back and forth from application server to database server. Network traffic is a common bottleneck causing performance issues, and stored procedures can help reduce this. More often though, it is the database server itself that is the bottleneck, so this may not be much of an advantage.
A simple example
A stored procedure is simply some SQL statements. Almost any valid SQL can go inside a stored procedure, with a few exceptions, which we will look at, at a later date. Let's set up a basic stored procedure first. This one will simply say 'Hello' in the Xhosa language - Molo.
mysql> CREATE PROCEDURE molo() SELECT 'Molo';
Query OK, 0 rows affected (0.00 sec)
It is as simple as that. And to call it:
mysql> CALL molo()\G
*************************** 1. row ***************************
Molo: Molo
1 row in set (0.00 sec)
Hardly useful, but the basics are there. CREATE PROCEDURE sp_name() will define the procedure, and CALL sp_name() will call the procedure.
Parameters
The real benefit of a stored procedure is of course when you can pass values to it, as well as receive values back. The concept of parameters should be familiar to anyone who has had experience with any procedural programming experience.
There are three types of parameter:
· IN: The default. This parameter is passed to the procedure, and can change inside the procedure, but remains unchanged outside.
· OUT: No value is supplied to the procedure (it is assumed to be NULL), but it can be modified inside the procedure, and is available outside the procedure.
· INOUT: The characteristics of both IN and OUT parameters. A value can be passed to the procedure, modified there as well as passed back again.
Mastery of stored procedures does require knowledge of session variables. Most of you probably know how to use session variables already, but if not, the concept is simple. You can assign a value to a variable, and retrieve it later. Here is an example, setting the variable x to the Xhosa word for hello to a group of people.
mysql> SET @x='Molweni';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x\G
*************************** 1. row ***************************
@x: Molweni
1 row in set (0.00 sec)
An IN example
Here is an example of a stored procedure demonstrating the use of an IN parameter. Since IN is the default, there is no need to specify the parameter as such.
mysql> CREATE PROCEDURE sp_in(p VARCHAR(10)) SET @x = P;
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp_in('Molo');
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x\G
*************************** 1. row ***************************
@x: Molo
1 row in set (0.00 sec)
The session variable @x is set inside of the procedure, based upon the parameter P, which is passed to the procedure, and remains unchanged.
An OUT example
mysql> SET @x='Molweni';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE PROCEDURE sp_out(OUT p VARCHAR(10)) SET P='molo';
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp_out(@x);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x\G
*************************** 1. row ***************************
@x: molo
1 row in set (0.00 sec)
We reset @x just to make sure the final result is not a legacy of the previous procedure. This time, the parameter P is changed inside of the procedure, while the session variable is passed to the procedure, ready to receive the result.
An INOUT example
mysql> CREATE PROCEDURE sp_inout(INOUT P INT) SET @x=P*2;
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp_inout(2);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x\G
*************************** 1. row ***************************
@x: 4
1 row in set (0.00 sec)
Here, a parameter is passed to the procedure, used in the calculation, and the results are made available to the session variable @x.
Getting information about existing stored procedures
It is clearly necessary to be able to get more information about any stored procedures later, such as a list of procedures available, and the definitions. There are MySQL-specific ways to do this, and the syntax should be familiar to experienced MySQL users. SHOW PROCEDURE STATUS returns a list of stored procedures, and some metadata about them, while SHOW CREATE PROCEDURE returns the definition of a particular procedure.
mysql> SHOW PROCEDURE STATUS\G
************* 1. row ************
Db: test
Name: molo
Type: PROCEDURE
Definer: ian@localhost
Modified: 2005-07-29 19:20:27
Created: 2005-07-29 19:20:27
Security_type: DEFINER
Comment:
************* 2. row ************
Db: test
Name: sp_in
Type: PROCEDURE
Definer: ian@localhost
Modified: 2005-08-02 11:58:34
Created: 2005-08-02 11:58:34
Security_type: DEFINER
Comment:
************* 3. row ************
Db: test
Name: sp_inout
Type: PROCEDURE
Definer: ian@localhost
Modified: 2005-08-02 12:16:18
Created: 2005-08-02 12:16:18
Security_type: DEFINER
Comment:
************* 4. row ************
Db: test
Name: sp_out
Type: PROCEDURE
Definer: ian@localhost
Modified: 2005-08-02 12:01:56
Created: 2005-08-02 12:01:56
Security_type: DEFINER
Comment:
4 rows in set (0.00 sec)
It will become clear what all these fields mean as we progress through the rest of this tutorial series.
mysql> SHOW CREATE PROCEDURE molo\G
*************************** 1. row ***************************
Procedure: molo
sql_mode:
Create Procedure: CREATE PROCEDURE 'test'.'molo'()
SELECT 'Molo'
1 row in set (0.00 sec)
There is also an ANSI-standard way of doing it, which will be more familiar to other users.
mysql> SELECT * FROM INFORMATION_SCHEMA.ROUTINES\G
*************************** 1. row ***************************
SPECIFIC_NAME: molo
ROUTINE_CATALOG: NULL
ROUTINE_SCHEMA: test
ROUTINE_NAME: molo
ROUTINE_TYPE: PROCEDURE
DTD_IDENTIFIER:
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: SELECT 'Molo'
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: NULL
PARAMETER_STYLE:
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS: CONTAINS_SQL
SQL_PATH: NULL
SECURITY_TYPE: DEFINER
CREATED: 2005-07-29 19:20:27
LAST_ALTERED: 2005-07-29 19:20:27
SQL_MODE:
ROUTINE_COMMENT:
DEFINER: ian@localhost
*************************** 2. row ***************************
SPECIFIC_NAME: sp_in
ROUTINE_CATALOG: NULL
ROUTINE_SCHEMA: test
ROUTINE_NAME: sp_in
ROUTINE_TYPE: PROCEDURE
DTD_IDENTIFIER:
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: SET @x = P
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: NULL
PARAMETER_STYLE:
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS: CONTAINS_SQL
SQL_PATH: NULL
SECURITY_TYPE: DEFINER
CREATED: 2005-08-02 11:58:34
LAST_ALTERED: 2005-08-02 11:58:34
SQL_MODE:
ROUTINE_COMMENT:
DEFINER: ian@localhost
*************************** 3. row ***************************
SPECIFIC_NAME: sp_inout
ROUTINE_CATALOG: NULL
ROUTINE_SCHEMA: test
ROUTINE_NAME: sp_inout
ROUTINE_TYPE: PROCEDURE
DTD_IDENTIFIER:
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: SET @x=P*2
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: NULL
PARAMETER_STYLE:
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS: CONTAINS_SQL
SQL_PATH: NULL
SECURITY_TYPE: DEFINER
CREATED: 2005-08-02 12:16:18
LAST_ALTERED: 2005-08-02 12:16:18
SQL_MODE:
ROUTINE_COMMENT:
DEFINER: ian@localhost
*************************** 4. row ***************************
SPECIFIC_NAME: sp_out
ROUTINE_CATALOG: NULL
ROUTINE_SCHEMA: test
ROUTINE_NAME: sp_out
ROUTINE_TYPE: PROCEDURE
DTD_IDENTIFIER:
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: SET P='molo'
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: NULL
PARAMETER_STYLE:
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS: CONTAINS_SQL
SQL_PATH: NULL
SECURITY_TYPE: DEFINER
CREATED: 2005-08-02 12:01:56
LAST_ALTERED: 2005-08-02 12:01:56
SQL_MODE:
ROUTINE_COMMENT:
DEFINER: ian@localhost
4 rows in set (0.01 sec)
Let's introduce some more complex examples. First, we will create a sample table.
mysql> CREATE table sp1 (id INT, txt VARCHAR(10), PRIMARY KEY(id));
Query OK, 0 rows affected (0.11 sec)
Delimiters, and multi-statement procedures
Stored procedures of course are not that useful if they are just one statement. The effects of all the procedures we have looked at so far could have had been duplicated much more easily with a single SQL statement. Useful procedures are much longer than that. Those of you who are on the ball may be thinking of a complication. How can we differentiate between multiple statements inside the procedure, and the end of the procedure? We have to create a different delimiter to end the CREATE PROCEDURE statement. Here is how:
mysql> DELIMITER |
Note that there is no semicolon after the '|' symbol, which we will use as the delimiter for our purposes. You have to choose a delimiter that does not appear in your procedure, and it can be more than one character.
mysql> CREATE PROCEDURE sp_ins (P VARCHAR(10))
-> BEGIN
-> SET @x=CHAR_LENGTH(P);
-> SET @y = HEX(P);
-> INSERT INTO sp1(id,txt) VALUES(@x,@y);
-> END|
Query OK, 0 rows affected (0.05 sec)
mysql> CALL sp_ins('ABC');
-> |
Query OK, 1 row affected (0.00 sec)
mysql> DELIMITER ;
mysql> SELECT * FROM sp1\G
*************************** 1. row ***************************
id: 3
txt: 414243
1 row in set (0.00 sec)
Note what happened when we tried to call the procedure. Because MySQL was still using the | symbol as a delimiter, and not the semicolon, the statement did not run after the semicolon. We first needed to close it with the piping symbol. Afterwards, we reset the delimiter back to normal, and test that the records were correctly added to the sp1 table.
Procedure variables
Stored procedures do not only make use of the standard SQL statements. You can also DECLARE variables that exist only inside the procedure, as well as assign values to them with the SET statement without using the '@' symbol, required for session variables. Here is an example.
mysql> DELIMITER |
mysql> CREATE PROCEDURE sp_declare (P INT)
-> BEGIN
-> DECLARE x INT;
-> DECLARE y INT DEFAULT 10;
-> SET x = P*y;
-> INSERT INTO sp1(id,txt) VALUES(x,HEX('DEF'));
-> END|
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> CALL sp_declare(4);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM sp1\G
*************************** 1. row ***************************
id: 3
txt: 414243
*************************** 2. row ***************************
id: 40
txt: 444546
2 rows in set (0.00 sec)
Variables that are declared without a default, such as x above, are set to NULL.
Populating variables from a pre-existing table
Now that you have seen how to INSERT records into a table from within a procedure, you may be wondering how we get values out of an existing table. Here is a simple example.
mysql> DELIMITER |
mysql> CREATE PROCEDURE sp_select ()
-> BEGIN
-> DECLARE x INT;
-> DECLARE y VARCHAR(10);
-> SELECT id,txt INTO x,y FROM sp1 LIMIT 1;
-> SELECT x,y;
-> END|
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> CALL sp_select()\G
*************************** 1. row ***************************
x: 3
y: 414243
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
For now, we just returned the first record, to introduce the syntax. In next month's tutorial, we will look at iterations, which allow us to loop through multiple records.
Conclusion
I hope that you will be feeling comfortable with the syntax, and will be able to create your own stored procedures. None of the examples this month have been particularly useful, but next month's continuation will introduce some more advanced topics, and show you some of the real power of stored procedures.
Introduction
Last month I introduced MySQL Stored Procedures. If you have not yet read that article, you should do so first, as I continue to build upon the examples from then. This month I look at some more advanced concepts, including conditions and loops.
Characteristics
Last month we created skeleton stored procedures, to introduce the concepts. Let's start off this month by looking at all of the characteristics that you can define when creating a stored procedure. Here is a sample CREATE PROCEDURE statement making use of all of the clauses.
CREATE PROCEDURE sp_full()
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
COMMENT 'Returns a random number'
SELECT RAND()
LANGUAGE SQL simply means that the language used to write the procedure is SQL. MySQL cannot yet use procedures in any other languages, but it probably will in the future. It is, unsurprisingly, the default.
NOT DETERMINISTIC implies that the procedure may produce different results, given the same inputs. The alternative is DETERMINISTIC, if the procedure always gives the same results given the same input. The default is NOT DETERMINISTIC. Procedures that are NOT DETERMINISTIC have implications for binary logging and replication (if it performs updates, it means that slave data could be different to master data, and for this reason you can't define a NOT DETERMINISTIC procedure if binary logging is enabled). Besides binary logging, MySQL as yet makes no use of this information (potentially the optimizer could use it to improve performance).
MODIFIES SQL DATA indicates that data could be modified by the procedure. Alternatives are CONTAINS SQL (which would be SQL that does not read or write data), NO SQL or READS SQL DATA. The default is CONTAINS SQL.
SQL SECURITY DEFINER indicates that MySQL should check the privileges of the user that defined the procedure when executing the procedure. The alternative is SQL SECURITY INVOKER, which causes MySQL to use the privileges of the user calling the procedure. The default is SQL SECURITY DEFINER.
COMMENT is self-explanatory, and can be used to describe the procedure. It is a non-standard MySQL extension.
SQL statements unique to Stored Procedures
A procedure may need to use conditions or loops, and standard SQL is not sufficient for these purposes. For that reason, the SQL standard includes a number of statements that only occur inside stored procedures. We already looked at the DECLARE statement last month. Let's take a look now at conditions and loops. The concepts should all be familiar to anyone with programming experience.
Conditions: IF THEN ELSE
MySQL implements conditions in the stock standard, tried and tested way.
IF condition
THEN statement/s
ELSE statement/s
END IF
The logic is simple. If the condition is met, then a set of statements are carried out. Else, another set of statements are carried out.
Here is an example:
mysql> CREATE PROCEDURE sp_condition(IN var1 INT)
BEGIN
IF (var1 > 10)
THEN SELECT 'greater';
ELSE SELECT 'less than or equal';
END IF;
END|
Query OK, 0 rows affected (0.06 sec)
mysql> CALL sp_condition(5)\G
*************************** 1. row ***************************
less than or equal: less than or equal
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp_condition(15)\G
*************************** 1. row ***************************
greater: greater
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
I presume you can follow the logic easily enough. NULL values throw a curveball into the mix - an argument against their usage is that they undermine standard boolean logic. Nevertheless, you may come across instances where they are used. Let's see what happens if we pass a NULL to the procedure we have just created:
mysql> CALL sp_condition(NULL)\G
*************************** 1. row ***************************
less than or equal: less than or equal
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
As you probably expected, NULL > 10 evaluates to false, so the ELSE statement is run. Let's switch things around:
mysql> CREATE PROCEDURE sp_condition2(IN var1 INT)
BEGIN
IF (var1 <= 10)
THEN SELECT 'less than or equal';
ELSE SELECT 'greater';
END IF;
END|
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp_condition2(NULL)\G
*************************** 1. row ***************************
greater: greater
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Using what should in boolean logic be an identical test, we get a differing result. NULL <= 10 also evaluates to false. NULL's are beyond such dualistic thinking. This should be enough of a warning to be cautious of NULL's!
Conditions: CASE
The other construct used for condition is CASE.
CASE variable
WHEN condition1 statement/s
WHEN condition2 statement/s
ELSE statement/s
END CASE
This construct is used when the same variable is being tested against multiple conditions. Instead of long nested IF statements, using a CASE statement allows the procedure code to be more compact and easily readable. Here is an example:
mysql> CREATE PROCEDURE sp_case(IN var1 INT)
BEGIN
CASE var1
WHEN 1 THEN SELECT 'One';
WHEN 2 THEN SELECT 'Two';
ELSE SELECT 'Something else';
END CASE;
END|
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp_case(1)\G
*************************** 1. row ***************************
One: One
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp_case(2)\G
*************************** 1. row ***************************
Two: Two
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp_case(3)\G
*************************** 1. row ***************************
Something else: Something else
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Loops: WHILE
Loops are a vital component of procedures - they allow the same portion of code to be repeated a number of time. WHILE loops, the first type of loop we are going to look at, continuously repeat a block while a particular condition is true.
WHILE condition DO
statement/s
END WHILE
Here is an example. Be careful when entering it though, and with all loops! Always create your procedures on a test server first. All novice (and some not so novice) programmers at one time or another create an infinite loop. If you had forgotten to increment var1 in the procedure, it would always stay as whatever you had passed to the procedure. If this were anything less than 20, the loop would continue indefinitely.
mysql> CREATE PROCEDURE sp_while(IN var1 INT)
BEGIN
WHILE (var1 <>
SELECT var1;
SET var1=var1+1;
END WHILE;
END|
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp_while(18)\G
*************************** 1. row ***************************
var1: 18
1 row in set (0.00 sec)
*************************** 1. row ***************************
var1: 19
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp_while(22)\G
Query OK, 0 rows affected (0.00 sec)
Note that when we called the procedure, passing 22, no statements were executed, as the condition failed immediately.
REPEAT UNTIL
The other commonly used loop is the REPEAT UNTIL construct.
REPEAT
statement/s
UNTIL condition
END REPEAT
The statements are repeatedly performed until the condition is met. A difference to the WHILE loop is that the condition is only tested after the statements have been performed, so there is always at least one instance of the statements being run. Here is an example:
mysql> CREATE PROCEDURE sp_repeat(IN VAR1 INT)
BEGIN
REPEAT
SELECT var1;
SET var1=var1+1;
UNTIL var1>5
END REPEAT;
END|
Query OK, 0 rows affected (0.09 sec)
mysql> CALL sp_repeat(3)\G
*************************** 1. row ***************************
var1: 3
1 row in set (0.00 sec)
*************************** 1. row ***************************
var1: 4
1 row in set (0.00 sec)
*************************** 1. row ***************************
var1: 5
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp_repeat(8)\G
*************************** 1. row ***************************
var1: 8
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Note, that even when we call the procedure with 8, which means that the condition evaluates to true (and exits the loop) the first time it is encountered, the SELECT statement is still executed once, as the condition is only tested at the end.
LABELS, LEAVES and LOOPS
Labels are simply text strings that are used to mark part of the procedure. They can simply play the role of comments, or be part of the logic, as we will see below in the LOOP construct. Here is an example of LABELS used to comment the start of a procedure. The label is begin1. This may not seem useful here, but complex statements can be deeply nested, and in that case, clear labels will make the logic easier to follow. They also play a vital logical role, as we will see later.
mysql> CREATE PROCEDURE sp_label()
begin1: BEGIN
END|
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE PROCEDURE sp_label2()
begin1: BEGIN
END begin1|
Query OK, 0 rows affected (0.00 sec)
In the second example, the end is also labeled. If an end label exists, it must match a start label of the same name. You can use LABELS before BEGIN, WHILE, REPEAT and LOOP (introduced below) statements, as well as the accompanying END statements, and also as targets for ITERATE statements (also introduced below).
A third kind of loop is the LOOP construct. This one does not test against a condition at either the start or the end of the loop. It continues looping until explicitly exited with a LEAVE statement, making it easy to get stuck in an infinite loop (as I did when creating this example). The LEAVE statement exits a block (which can include the procedure itself). Since there can be many nested constructs, it must also be accompanied by a label name to determine which block to leave.
label LOOP
statement/s
LEAVE label
statement/s
END LOOP
Here is an example:
mysql> CREATE PROCEDURE sp_loop(IN var1 INT)
BEGIN
loop1: LOOP
IF (var1 > 5) THEN
LEAVE loop1;
END IF;
SET var1=var1+1;
SELECT var1;
END LOOP;
END |
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp_loop(2)\G
*************************** 1. row ***************************
var1: 3
1 row in set (0.00 sec)
*************************** 1. row ***************************
var1: 4
1 row in set (0.00 sec)
*************************** 1. row ***************************
var1: 5
1 row in set (0.00 sec)
*************************** 1. row ***************************
var1: 6
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
The LABEL name we used above is loop1, and the LEAVE statement explicitly left loop1. We did not make use of an end label.
ITERATE statements
Iterates can only appear within LOOP, REPEAT, and WHILE statements. They are followed by a LABEL name, and effectively direct control back to that LABEL. So, if they appear half-way through a loop, and they direct control back to the top of that loop, the rest of the loop will not be carried out (bear this logic in mind when testing, as it again opens the possibilities for infinite loops).
In this next example, we use a WHILE loop, and ITERATE back to the beginning of the loop if var1 is still less than 3.
mysql> CREATE PROCEDURE sp_while_iterate(IN var1 INT)
-> BEGIN
-> while_label: WHILE (var1 <>
-> SELECT CONCAT('starting the loop: var1 is: ',var1);
-> SET var1=var1+1;
-> IF (var1<3)>
-> ITERATE while_label;
-> END IF;
-> SELECT CONCAT('ending the loop: var1 is: ',var1);
-> END WHILE;
-> END|
Query OK, 0 rows affected (0.00 sec)
mysql> CALL sp_while_iterate(1)\G
*************************** 1. row ***************************
CONCAT('starting the loop: var1 is: ',var1): starting the loop: var1 is: 1
1 row in set (0.00 sec)
*************************** 1. row ***************************
CONCAT('starting the loop: var1 is: ',var1): starting the loop: var1 is: 2
1 row in set (0.00 sec)
*************************** 1. row ***************************
CONCAT('ending the loop: var1 is: ',var1): ending the loop: var1 is: 3
1 row in set (0.00 sec)
*************************** 1. row ***************************
CONCAT('starting the loop: var1 is: ',var1): starting the loop: var1 is: 3
1 row in set (0.00 sec)
*************************** 1. row ***************************
CONCAT('ending the loop: var1 is: ',var1): ending the loop: var1 is: 4
1 row in set (0.00 sec)
*************************** 1. row ***************************
CONCAT('starting the loop: var1 is: ',var1): starting the loop: var1 is: 4
1 row in set (0.00 sec)
*************************** 1. row ***************************
CONCAT('ending the loop: var1 is: ',var1): ending the loop: var1 is: 5
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
The first time through the loop, only starting the loop is displayed, as var1 is at 2 when the time comes to evaluate the IF condition, and the condition evaluates to true. The ITERATE sends control back to the start of the loop again. The second time through, var1 starts at 2, but is then incremented to 3, and the IF condition is false. The full loop is executed.
Conclusion
This week we introduced conditions and loops, two of the basic fundamental structures used in stored procedures. There is still more to come, so check back next month as we continue our series.
| | Statement | Description | |
| CREATE PROCEDURE | Creates a stored procedure, which is stored in the proc table in the MySQL database. |
| CREATE FUNCTION | Creates a user-defined function, essentially a stored procedure that returns data. |
| ALTER PROCEDURE | Alters a previously defined stored procedure that was created using the CREATE PROCEDURE statement.
It does not affect related stored procedures or stored functions. |
| ALTER FUNCTION | Alters a previously defined stored function that was created using the CREATE FUNCTION statement. It does not affect related stored procedures or stored functions. |
| DROP PROCEDURE | Removes one or more stored procedures from MySQL's proc table. |
| DROP FUNCTION | Removes one or more stored functions from MySQL's proc table. |
| SHOW CREATE PROCEDURE | Returns the text of a previously defined stored procedure that was created using the CREATE PROCEDURE statement. This statement is a MySQL extension to the SQL:2003 specification. |
| SHOW CREATE FUNCTION | Returns the text of a previously defined stored function that was created using the CREATE FUNCTION statement. This statement is a MySQL extension to the SQL:2003 specification. |
| SHOW PROCEDURE STATUS | Returns the characteristics of a previously defined stored procedure; including name, type, creator, creation date, and modification date. This statement is a MySQL extension to the SQL:2003 specification. |
| SHOW FUNCTION STATUS | Returns the characteristics of a previously defined stored function; including name, type, creator, creation date, and modification date. This statement is a MySQL extension to the SQL:2003 specification. |
| CALL | Invokes a previously defined stored procedure that was created using the CREATE PROCEDURE statement. |
| BEGIN ... END | Contains a group of multiple statements for execution. |
| DECLARE | Used to define local variables, conditions, handlers, and cursors. |
| SET | Used to alter the values of both local variables and global server variables. |
| SELECT ... INTO | Used to store the indicated columns directly into variables. |
| OPEN | Used to open a cursor. |
| FETCH | Retrieves the next row using the specified cursor and advances the cursor one row. |
| CLOSE | Used to close and open cursor. |
| IF | An if-then-else-end if condition statement. |
| CASE ... WHEN | A case statement conditional construct. |
| LOOP | A simple looping structure; exiting is performed using the LEAVE statement. |
| LEAVE | Used to exit IF, CASE, LOOP, REPEAT and WHILE statements. |
| ITERATE | Used within loops to restart at the beginning of the loop. |
| REPEAT | A loop with the conditional test at the end. |
| WHILE | A loop with the conditional test at the beginning. |
| RETURNS | Returns a value from a stored function. |
| |
Stored procedure statements supported in MySQL 5.0
New Arcticle
This document introduces the subject of procedural programming within MySQL, highlights the main features and functions and gives a quick tutorial on the basics. It will be followed by part 2 which will introduce more advanced features of stored procedures within MySQL.
With the release of MySQL 5 AB has addressed one of the major reasons ‘serious’ database developers may have avoided using MySQL, despite its self confessed title of “The World's Most Popular Open Source Database”.
Stored procedures and functions are executable program units stored within the database server and can be used for a number of important data manipulation tasks that cannot be completed by SQL alone. Its true to say that with a mixture of some external programming and SQL inside MySQL it may be possible to achieve the same results as using stored procedures, but that’s not a reason to dismiss them or even think of them as in some way inferior, in fact the opposite is often true.
One of the great advantages of stored program units within a database is the ability to deal with data at source rather than pulling it down, manipulating it and passing it back. This is particularly useful in situations where a database is accessed by multiple interfaces. For example lets say you had both a Visual Basic application and a web front end accessing a MySQL database, you may have written some code both in VB and PHP to enforce a business rule, what happens when this business rule changes? Both sections of code need to be rewritten which introduces not just two programming jobs but two testing jobs and the possibility of two points of failure or error. Using procedural programming within MySQL we can create one set of business rules callable by both the VB and web front end, in fact callable from any front end we chose to implement in the future. There are certainly those who prefer to do all their processing at the front end but to a database developer that sounds as logical as writing your presentation layer in the database, data processing surely belongs in the database.
So onto MySQL’s implementation of procedural programming, lets first point out its early days and only available in the very latest releases (5.0 and up), when compared with other implementations such as Oracle and MS SQL Server it is very immature but MySQL have taken the decision to get the basic’s right first rather than pack it full of useful but ultimately problematic features. Having said this seasoned database developers will be pleased to see many of the features available in other databases.
So enough of the theory lets get straight into developing a few programs and see what can be done. Unfortunately the installation and set up of MySQL is outside the scope of this document, but we will be using version 5.0.0alpha release if you would like to play along. This can be downloaded free from the MySQL website and is relatively easy to install. Note that stored procedures are only available in version 5 and up so if your using a lower version you won’t be able to try it out.
So lets start. We will create a new database and set up a few tables first so we have some thing to work with.
create database pers;
use pers;
create table emps (emp_no integer, ename varchar(30),dept varchar(5));
create table dept (dept_id varchar(5),description varchar(30));
insert into emps values (1,'Bob','IT'), (2,'Alan','SAL'),(3,’Jane’,’SAL’);
insert into dept values (‘SAL’,’Sales’),(‘IT’,’Information Technology’);
We will come back to the tables later so now we will turn our attention to creating a simple function.
When writing procedures and functions we need tell the compiler when the line ends, this is done with a semi colon (;). However MySQL interprets this as a delimiter character when we are using the console so when entering our procedures and functions we need to use a different delimiter.
So lets set that now
delimiter //
Now whenever we would have normally used the ; we use //. So lets enter the source for the function.
Create function HelloWorld() Returns varchar(20)
Return ‘Hello World’;
//
Query OK, 0 rows affected (0.00 sec)
Hopefully you should have created the function and received the Query OK message. If you haven’t check what you have typed carefully.
Now lets run the function.
select helloworld() //
+--------------+
| helloworld() |
+--------------+
| Hello World |
+--------------+
1 row in set (0.00 sec)
So that shows you how simple it is to create a function in MySQL, to call that function and see the results. That’s all there is to it.
Lets now go into some more detail about how a procedural program is built within MySQL.
To create procedures and functions we use the following syntax
CREATE PROCEDURE sp_name ([parameter[,.
[characteristic ...] routine_body
CREATE FUNCTION sp_name ([parameter[,..
[RETURNS type]
[characteristic ...] routine_body
A program unit name will follow the basic naming conventions used with in MySQL, its best to use simple but descriptive names and to avoid anything other than alphanumeric characters and underscores. The routine body is the main body of the code where you write your SQL statements. We will talk about parameters and characteristics later, so at this stage the only thing worth mentioning about the create syntax is the RETURNS keyword. As yet we haven’t talked about the difference between functions and procedures. A function may or not accept a parameter or parameters but must always return 1 and only 1 value to the calling program. A procedure again can accept none or more parameters but can also return none or more values. As a general rule of thumb functions are used to make simple changes to individual values, procedures are used to do more complex processing. So back to the RETURNS keyword, RETURNS must be used when writing a function to specify what datatype the procedure will return as being a function it will return a value.
The Routine Body
The routine body is where we place our SQL statements that will actually perform the calculations and manipulation of data. In our first simple function we had a single line of code, which in itself was fun but pretty useless. When adding more than one line of code we need to enclose our statements within a BEGIN and END. Even with a single line function its still possible to use BEGIN and END and in fact it should be positively encouraged. So lets do that now with our simple one line function. Firstly we need to drop the function.
Drop function HelloWorld
//
CREATE FUCNTION HelloWorld() RETURNS VARCHAR2
BEGIN
RETURN ‘Hello World’;
END
//
It may at this point be worth creating a file to store your function so that we don’t have to keep dropping it and then recreating it by hand. Create a file like so
use pers
//
DROP FUNCTION IF EXISTS HelloWorld
//
CREATE FUNCTION HelloWorld() RETURNS VARCHAR(20)
RETURN 'Hello World 2';
//
I have saved mine in a folder called source in my MySQL folder. So now all I have to do to run the function is call the following
source c:/mysql/source/helloworld.sql
select HelloWorld() //
A word of warning, use / rather than \ as certain folder names can cause MySQL to think your calling various MySQL commands.
Variables
Those who are familiar with other programming languages will be equally familiar with the term variable. A variable is in essence a named and reserved area of memory, which can be referenced from within your program. We can create variables using any datatype supported in MySQL.
To create a variable we using the following syntax
DECLARE variable_name variable_type;
Where the variable_name is the unique name we wish to assign and variable type is the datatype of the variable we wish to create.
So to create a variable called output_text containing a VARCHAR of 20 characters we use the following syntax
DECLARE output_text VARCHAR(20);
Now within our program we can set and reference that variable like so.
CREATE FUNCTION HelloWorld() RETURNS VARCHAR(20)
BEGIN
DECLARE outtext VARCHAR(20);
SET outtext = 'Hello World';
RETURN outtext;
END
The SET keyword simple allows us to assign a value to the variable. However what we can do is give the variable a default value so on creation it is populated.
Lets try that out using out HelloWorld function.
CREATE FUNCTION HelloWorld() RETURNS VARCHAR(20)
BEGIN
DECLARE output_text VARCHAR(20) DEFAULT 'HelloWorld';
RETURN output_text;
END
We can also declare more than one variable of the same type at the same time.
DECLARE output_text, name, department VARCHAR;
Parameters
Parameters are another common programming tool. Using parameters we can pass data into and in the case of procedures out of our program units. Lets extend our HelloWorld function to accept a parameter add this to our ‘hello world’ variable and return the result.
CREATE FUNCTION HelloWorld(p_inparam VARCHAR(20)) RETURNS
VARCHAR(20)
BEGIN
DECLARE output_text VARCHAR(20);
SET output_text = p_inparam;
RETURN output_text;
END
This time when we run the function we need to supply the parameter like so
select HelloWorld(' Hello World') //
Running Procedures
Because we know functions only ever return one value its easy for us to run them using the select command within MySQL, however procedures do not need to return a value or can in fact return more than one. Lets see how to create and run a procedure, accept the values it returns and how we can display those values.
Creating a procedure is just as simple as creating a function. Here we are going to create a procedure which will return a simple string.
use pers
//
DROP PROCEDURE IF EXISTS HelloWorld
//
CREATE procedure HelloWorld(out p_text VARCHAR(30))
BEGIN
set p_text = 'Hello World';
END
//
So now we need to run the procedure. Because MySQL doesn’t know if the procedure will return a value we need to call it in a different way. We can pass values in and out of the procedure using user variables (@). The syntax to run the procedure is as follows.
CALL HelloWorld(@out)
Then to see the result.
Select @out //
+-------------+
| @out |
+-------------+
| Hello World |
+-------------+
1 row in set (0.00 sec)
So as you can see, it’s easy to call both functions and procedures with MySQL.
Select Into
So far our function or procedures are not offering us a great deal so lets use another feature of stored procedures called select into. Rather than hard coding the variable value or passing in as a parameter as we have done so far we can select the value from a table.
Note: Currently functions are restricted so that they cannot access tables. This also applies to some set and select statements. In the MySQL documentation it says this restriction will be lifted soon.
So lets try and use the select into functionality in a procedure. We will use the database tables we have set up to do this. A useful procedure would be if we could request a department description (we could do this easily using a straight SQL statement but that would bring a quick end to this document).
CREATE procedure GetDept(out p_dept_description VARCHAR(30))
BEGIN
SELECT description INTO p_dept_description FROM DEPT LIMIT 1;
END
//
Now lets call the procedure and see what the result was.
Select @dept //
+-------+
| @dept |
+-------+
| Sales |
+-------+
1 row in set (0.00 sec)
In this simple example we are simply returning the first department, what would be much more useful would be the ability to pass in a department id and get the corresponding description. So lets do that now.
CREATE procedure GetDept(out p_dept_description VARCHAR(30), in p_dept_id
VARCHAR(5))
BEGIN
SELECT description INTO p_dept_description FROM DEPT WHERE dept_id = p_dept_id;
END
//
call GetDept(@out,'IT') //
mysql> select @out //
+---------------------------+
| @out |
+---------------------------+
| Information Technology |
+---------------------------+
1 row in set (0.00 sec)
We’re now starting to see the power of stored procedures within MySQL and how they might be more useful than standard SQL statements. It’s true that we could have achieved the same thing using an SQL select statement, but using stored procedures allows us to protect the database and increase the level of security. Rather than giving direct access to the tables we can place procedures in between to do a number of tasks and maintain the data we store in tables.
This concludes the first part of this introductory look at stored procedures within MySQL, but there is still more to learn and in part 2 we will look at how we can expand on the functionality we have seen so far.
