MySQL Stored Procedure Programming (2009)

Part I. Stored Programming Fundamentals

Chapter 5. Using SQL in Stored Programming

While we can use the MySQL stored program language to perform traditional programming tasks, in reality almost all stored programs will engage in an interaction with the database through the execution of SQL statements. This chapter focuses on how you can use SQL within your stored programs.

In this chapter we'll look at the various ways in which you can use SQL inside of stored programs:

§ Simple (non-SELECT) SQL statements that do not return a result set can be freely embedded within stored procedures and functions.

§ A SELECT statement that returns only a single row can pass its result INTO local variables.

§ A SELECT statement that returns multiple rows can form the basis for a cursor that allows you to loop through each row, taking whatever action you deem appropriate for that row.

§ Any SELECT statement can be included in a stored procedure (but not in a stored function) "unbound" by an INTO clause or a CURSOR statement. The result set from such a SQL statement will be returned to the calling program (but not, alas, to a calling stored procedure).

§ SQL statements can be prepared dynamically using MySQL server-side prepared statements (in stored procedures only).

Using Non-SELECT SQL in Stored Programs

When we include a SQL statement that does not return a result set—such as an UPDATE, INSERT, or SET statement—within a stored program, it will execute exactly as it would if it were executed in some other context (such as if it were called from PHP or issued from the MySQL command line).

SQL statements within stored programs follow the same syntax as they would outside of the stored program. The SQL statements have full access to any stored program variables, which can be used wherever a literal or expression would normally be provided to the SQL.

You can use all the major categories of SQL statements inside stored programs. DML, DDL, and utility statements can be used without restriction.

Example 5-1 uses a combination of DDL and DML to create and manipulate the data in a table.

Example 5-1. Embedding non-SELECT statements in stored programs

CREATE PROCEDURE simple_sqls( )

BEGIN

DECLARE i INT DEFAULT 1;

/* Example of a utility statement */

SET autocommit=0;

/* Example of DDL statements */

DROP TABLE IF EXISTS test_table ;

CREATE TABLE test_table

(id INT PRIMARY KEY,

some_data VARCHAR(30))

ENGINE=innodb;

/* Example of an INSERT using a procedure variable */

WHILE (i<=10) DO

INSERT INTO TEST_TABLE VALUES(i,CONCAT("record ",i));

SET i=i+1;

END WHILE;

/* Example of an UPDATE using procedure variables*/

SET i=5;

UPDATE test_table

SET some_data=CONCAT("I updated row ",i)

WHERE id=i;

/* DELETE with a procedure variable */

DELETE FROM test_table

WHERE id>i;

END;

Using SELECT Statements with an INTO Clause

If you have a SELECT statement that returns only a single row, you can return that row into stored program variables by using the INTO statement within the SELECT statement. The format for such a SELECT is:

SELECT expression1 [, expression2 ....]

INTO variable1 [, variable2 ...]other SELECT statement clauses

Example 5-2 shows how we can retrieve details from a single customer. The customer ID is passed in as a parameter.

Example 5-2. Using a SELECT-INTO statement

CREATE PROCEDURE get_customer_details(in_customer_id INT)

BEGIN

DECLARE l_customer_name VARCHAR(30);

DECLARE l_contact_surname VARCHAR(30);

DECLARE l_contact_firstname VARCHAR(30);

SELECT customer_name, contact_surname,contact_firstname

INTO l_customer_name,l_contact_surname,l_contact_firstname

FROM customers

WHERE customer_id=in_customer_id;

/* Do something with the customer record */

END;

If the SQL statement returns more than one row, a runtime error will result. For instance, if we omitted the WHERE clause in Example 5-2, the following error would result when we tried to run the stored procedure:

mysql> CALL get_customer_details(2) ;

ERROR 1172 (42000): Result consisted of more than one row

Creating and Using Cursors

To handle a SELECT statement that returns more than one row, we must create and then manipulate a cursor. A cursor is an object that provides programmatic access to the result set returned by your SELECT statement. Use a cursor to iterate through the rows in the result set and take action for each row individually.

Currently, MySQL only allows us to fetch each row in the result set from first to last as determined by the SELECT statement. We cannot fetch from the last to first row, and cannot jump directly to a specific row in the result set.

Defining a Cursor

Define a cursor with the DECLARE statement, which has the following syntax:

DECLARE cursor_name CURSOR FOR SELECT_statement;

As we mentioned in Chapter 3, cursor declarations must occur after all of our variable declarations. Declaring a cursor before declaring our variables generates error 1337, as shown in Example 5-3.

Example 5-3. Declaring a cursor before a variable generates a 1337 error

mysql> CREATE PROCEDURE bad_cursor( )

BEGIN

DECLARE c CURSOR FOR SELECT * from departments;

DECLARE i INT;

END;

ERROR 1337 (42000): Variable or condition declaration after cursor or handler declaration

A cursor is always associated with a SELECT statement; Example 5-4 shows a simple cursor declaration that retrieves certain columns from the customers table.

Example 5-4. Simple cursor declaration

DECLARE cursor1 CURSOR FOR

SELECT customer_name, contact_surname,contact_firstname

FROM customers;

A cursor can reference stored program variables within the WHERE clause or (less frequently) the column list. In Example 5-5, the cursor includes a reference to a stored procedure parameter, both in the WHERE clause and in the SELECT list. When the cursor is opened, it will use the value of the parameter variable to determine which rows to return.

Example 5-5. Cursor definition including a stored procedure variable

CREATE PROCEDURE cursor_demo (in_customer_id INT)

BEGIN

DECLARE v_customer_id INT;

DECLARE v_customer_name VARCHAR(30);

DECLARE c1 CURSOR FOR

SELECT in_customer_id,customer_name

FROM customers

WHERE customer_id=in_customer_id;

Cursor Statements

The MySQL stored program language supports three statements for performing operations on cursors :

OPEN

Initializes the result set for the cursor. We must open a cursor before fetching any rows from that cursor. The syntax for the OPEN statement is very simple:

OPEN cursor_name;

FETCH

Retrieves the next row from the cursor and moves the cursor "pointer" to the following row in the result set. It has the following syntax:

FETCH cursor_name INTO variable list;

The variable list must contain one variable of a compatible data type for each column returned by the SELECT statement contained in the cursor declaration. We'll discuss FETCH in more detail later in this chapter.

CLOSE

Deactivates the cursor and releases the memory associated with that cursor. The syntax for this statement is:

CLOSE cursor_name ;

We should close a cursor when we have finished fetching from it, or when we need to open that cursor again after changing a variable that affects the cursor's result set.

In the following sections, we will see many examples of these statements in action.

Fetching a Single Row from a Cursor

This is the most basic use of a cursor: we open a cursor, fetch a single row, and then close the result set, as shown in Example 5-6 (opening the cursor defined in Example 5-4). This is logically equivalent to a simple SELECT with an INTO clause.

Example 5-6. Fetching a single row from a cursor

OPEN cursor1;

FETCH cursor1 INTO l_customer_name,l_contact_surname,l_contact_firstname;

CLOSE cursor1;

Fetching an Entire Result Set

The most common way that cursors are processed is to fetch each row identified by the cursor's SELECT statement, perform one or more operations on the data retrieved, and then close the cursor after the last row has been retrieved.

Example 5-7 shows how we can declare and open a cursor, then fetch rows from the cursor in a loop, and finally close the cursor.

Example 5-7. Simple (flawed) cursor loop

DECLARE c_dept CURSOR FOR

SELECT department_id

FROM departments;

OPEN c_dept;

dept_cursor: LOOP

FETCH c_dept INTO l_dept_id;

END LOOP dept_cursor;

CLOSE c_dept;

While this code might seem sensible and complete, there is a problem: if we attempt to fetch a row after the last row in the cursor has been fetched, MySQL will raise the "no data to fetch" error (MySQL error 1329; SQLSTATE 02000). So the code in Example 5-7 will abort as shown here:

mysql> call simple_cursor_loop( );

ERROR 1329 (02000): No data to FETCH

To avoid this error, we declare an error handler that will catch "no data to fetch" and set a flag (implemented as a local variable). We then interrogate that variable to determine if the last row has been fetched. Using this technique, we can terminate our loop and close the cursor with intuitive, easy-to-understand code.

We discuss error handlers in detail in Chapter 6. However, in this situation, we will add the following statement to our code:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row_fetched=1;

This handler instructs MySQL to do two things when the "no data to fetch" scenario occurs:

1. Set the value of the "last row variable" (l_last_row_fetched) to 1.

2. Allow the program to continue executing.

Our program can now check the value of l_last_row_fetched. If it is set to 1, then we know that the last row has been fetched, and we can terminate the loop and close the cursor.

It is very important that we reset the "end of result set" indicator after the cursor has been closed. Otherwise, the next time we try to fetch from this cursor, the program will immediately terminate the loop, thinking that we are done.

Example 5-8 shows all of these steps: declare the CONTINUE handler, loop through the rows of the result set, leave the loop if the variable has been set, and then clean up.

Tip

Almost all cursor loops require a NOT FOUND handler to avoid raising a fatal "no data to fetch" condition.

Example 5-8. Simple cursor loop

DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row_fetched=1;

SET l_last_row_fetched=0;

OPEN cursor1;

cursor_loop:LOOP

FETCH cursor1 INTO l_customer_name,l_contact_surname,l_contact_firstname;

IF l_last_row_fetched=1 THEN

LEAVE cursor_loop;

END IF;

/*Do something with the row fetched*/

END LOOP cursor_loop;

CLOSE cursor1;

SET l_last_row_fetched=0;

Note that we don't have to process all the rows in the result set; we can issue the LEAVE statement at any time to terminate the cursor loop if we have processed all the data we need.

Types of Cursor Loops

We can use any of the three looping constructs (simple loop, WHILE loop, and REPEAT UNTIL loop) to iterate through the rows returned by a cursor. In each case, we need to construct the loop so that the loop will terminate when the "last row variable" is set by the NOT FOUND handler.

Consider the cursor and the NOT FOUND handler shown in Example 5-9.

Example 5-9. Cursor declaration with associated handler

DECLARE dept_csr CURSOR FOR

SELECT department_id,department_name, location

FROM departments;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1;

The simplest construct is the LOOP-LEAVE-END LOOP sequence. In this case, our cursor loop would look like that shown in Example 5-10.

Example 5-10. A LOOP-LEAVE-END LOOP cursor loop

OPEN dept_csr;

dept_loop1:LOOP

FETCH dept_csr INTO l_department_id,l_department_name,l_location;

IF no_more_departments=1 THEN

LEAVE dept_loop1;

END IF;

SET l_department_count=l_department_count+1;

END LOOP;

CLOSE dept_csr;

SET no_more_departments=0;

The logic of Example 5-10 is simple: we open the cursor and then iteratively fetch the rows. If we try to fetch beyond the end of the result set, the handler sets no_more_departments to 1 and we call the LEAVE statement to terminate the loop. Finally, we close the cursor and reset theno_more_departments variable.

The WHILE loop is very familiar to programmers and might therefore seem like a natural choice for constructing a cursor loop. In fact, however, you will very likely find that the REPEAT UNTIL loop is a more appropriate construct for a cursor loop. The REPEAT always executes its body at least once before evaluating the continuation expression. In the context of cursor processing, we usually will want to fetch at least once before checking to see if we are done processing the cursor's result set. Hence, using the REPEAT UNTIL loop can produce more readable code, as shown inExample 5-11.

Example 5-11. Cursor loop with REPEAT UNTIL loop

DECLARE dept_csr CURSOR FOR

SELECT department_id,department_name, location

FROM departments;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1;

SET no_more_departments=0;

OPEN dept_csr;

REPEAT

FETCH dept_csr INTO l_department_id,l_department_name,l_location;

UNTIL no_more_departments

END REPEAT;

CLOSE dept_csr;

SET no_more_departments=0;

However, this loop only works because we did nothing with each row fetched by the cursor. Fetching rows from a cursor just for the heck of it is very unusual—it is far more common to do something with the rows returned. For instance, in our first LOOP-LEAVE-END LOOP example, we at least counted the rows returned by the cursor. However, since the final fetch returns no rows, we need a way to avoid processing after that final fetch. So in fact, even if we use the REPEAT UNTIL loop, we still need a LEAVE statement to avoid processing the nonexistent row returned (or rather, not returned) by the final fetch. Thus, if we want to count the number of rows returned by the cursor (or do anything else with the results) we will need to include loop labels and a LEAVE statement, as in the amended version of our previous example, shown in Example 5-12.

Example 5-12. Most REPEAT UNTIL loops also need a LEAVE statement

DECLARE dept_csr CURSOR FOR

SELECT department_id,department_name, location

FROM departments;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1;

SET no_more_departments=0;

OPEN dept_csr;

dept_loop:REPEAT

FETCH dept_csr INTO l_department_id,l_department_name,l_location;

IF no_more_departments THEN

LEAVEdept_loop;

END IF;

SET l_department_count=l_department_count+1;

UNTIL no_more_departments

END REPEAT dept_loop;

CLOSE dept_csr;

SET no_more_departments=0;

The necessity of including a LEAVE statement in almost every REPEAT UNTIL loop makes the presence of the UNTIL clause redundant—although it arguably improves readability and protects you against the possibility of an infinite loop if your LEAVE statement fails to execute (perhaps you miscoded the IF clause). In the end, valid cursor loops can be established in either fashion, and there is no compelling case to recommend one style over the other. All we can say is that your code as a whole will be more readable if you use a consistent style for all of your cursor loops.

An alternative to a LEAVE statement would be an IF statement that executes whatever post-processing occurs once we determine that the FETCH has reached the end of the result set. Example 5-13 shows how we could construct this loop for our example. In this case, an IF statement is added that performs row processing only if the no_more_departments variable has not been set.

Example 5-13. Using an IF block as an alternative to a LEAVE statement in a REPEAT UNTIL cursor loop

DECLARE dept_csr CURSOR FOR

SELECT department_id,department_name, location

FROM departments;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1;

SET no_more_departments=0;

OPEN dept_csr;

dept_loop:REPEAT

FETCH dept_csr INTO l_department_id,l_department_name,l_location;

IF no_more_departments=0 THEN

SET l_department_count=l_department_count+1;

END IF;

UNTIL no_more_departments

END REPEAT dept_loop;

CLOSE dept_csr;

SET no_more_departments=0;

The third style of cursor loop involves the WHILE-END WHILE loop. WHILE evaluates its condition before the first execution of the loop, so it is a less logical choice than REPEAT-UNTIL or LOOP-END LOOP, since logically we cannot know if we have reached the end of the cursor until we have fetched at least one row. On the other hand, WHILE is probably the looping construct used in the widest variety of other programming languages, so it might confer a clearer understanding of the program's intentions to those who are not familiar with the MySQL stored program language.

In any case, the WHILE loop also requires a LEAVE statement if there is any processing of the cursor results attempted within the loop, so the code in Example 5-14 looks very similar to our previous examples.

Example 5-14. A cursor WHILE loop

DECLARE dept_csr CURSOR FOR

SELECT department_id,department_name, location

FROM departments;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1;

SET no_more_departments=0;

OPEN dept_csr;

dept_loop:WHILE(no_more_departments=0) DO

FETCH dept_csr INTO l_department_id,l_department_name,l_location;

IF no_more_departments=1 THEN

LEAVE dept_loop;

END IF;

SET l_department_count=l_department_count+1;

END WHILE dept_loop;

CLOSE dept_csr;

SET no_more_departments=0;

Nested Cursor Loops

It is not uncommon to nest cursor loops. For instance, one loop might retrieve a list of interesting customers, while an inner loop retrieves all the orders for those customers. The most significant issue relating to this sort of nesting is that the NOT FOUND handler variable will be set whenever either cursor completes—so you are going to need to be very careful to ensure that a NOT FOUND condition does not cause both cursors to be closed.

For instance, consider the nested cursor loops shown in Example 5-15.

Example 5-15. A (flawed) nested cursor loop

CREATE PROCEDURE bad_nested_cursors( )

READS SQL DATA

BEGIN

DECLARE l_department_id INT;

DECLARE l_employee_id INT;

DECLARE l_emp_count INT DEFAULT 0 ;

DECLARE l_done INT DEFAULT 0;

DECLARE dept_csr cursor FOR

SELECT department_id FROM departments;

DECLARE emp_csr cursor FOR

SELECT employee_id FROM employees

WHERE department_id=l_department_id;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_done=1;

OPEN dept_csr;

dept_loop: LOOP — Loop through departments

FETCH dept_csr into l_department_id;

IF l_done=1 THEN

LEAVE dept_loop;

END IF;

OPEN emp_csr;

SET l_emp_count=0;

emp_loop: LOOP -- Loop through employee in dept.

FETCH emp_csr INTO l_employee_id;

IF l_done=1 THEN

LEAVE emp_loop;

END IF;

SET l_emp_count=l_emp_count+1;

END LOOP;

CLOSE emp_csr;

SELECT CONCAT('Department ',l_department_id,' has ',

l_emp_count,' employees');

END LOOP dept_loop;

CLOSE dept_csr;

END;

This stored procedure contains a subtle bug. When the first "inner" loop through the emp_csr cursor completes, the value of l_done is set to 1. Consequently, at the next iteration through the "outer" loop through the dept_csr, the value of l_done is still set to 1 and the outer loop is inadvertently terminated. As a result, we only ever process a single department. There are two possible solutions to this problem: the easier of the two is simply to reset the "not found" variable at the end of each loop, as in Example 5-16.

Example 5-16. A correct nested cursor example

CREATE PROCEDURE good_nested_cursors1( )

READS SQL DATA

BEGIN

DECLARE l_department_id INT;

DECLARE l_employee_id INT;

DECLARE l_emp_count INT DEFAULT 0 ;

DECLARE l_done INT DEFAULT 0;

DECLARE dept_csr cursor FOR

SELECT department_id FROM departments;

DECLARE emp_csr cursor FOR

SELECT employee_id FROM employees

WHERE department_id=l_department_id;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_done=1;

OPEN dept_csr;

dept_loop: LOOP -- Loop through departments

FETCH dept_csr into l_department_id;

IF l_done=1 THEN

LEAVE dept_loop;

END IF;

OPEN emp_csr;

SET l_emp_count=0;

emp_loop: LOOP -- Loop through employee in dept.

FETCH emp_csr INTO l_employee_id;

IF l_done=1 THEN

LEAVE emp_loop;

END IF;

SET l_emp_count=l_emp_count+1;

END LOOP;

CLOSE emp_csr;

SET l_done=0;

SELECT CONCAT('Department ',l_department_id,' has ',

l_emp_count,' employees');

END LOOP dept_loop;

CLOSE dept_csr;

END;

It is always good practice to reset the value of a "not found" variable once it has been used so that subsequent cursor iterations are not affected.

Tip

Always reset the "not found" variable set by a NOT FOUND handler after you terminate a cursor loop. Failure to do this may cause subsequent or nested cursor loops to terminate prematurely.

A slightly more complex—but arguably more robust solution—is to give each cursor its own handler. Because you can only have one NOT FOUND handler active within any particular block, this can only be done by enclosing each cursor in its own block. For instance, we could place the sales cursor in its own block with its own NOT FOUND handler, as in Example 5-17.

Example 5-17. Nested cursors using nested blocks

]

DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_customer=1;

SET l_last_customer=0;

OPEN customer_csr;

cust_loop:LOOP /* Loop through overdue customers*/

FETCH customer_csr INTO l_customer_id;

IF l_last_customer=1 THEN LEAVE cust_loop; END IF; /*no more rows*/

SET l_customer_count=l_customer_count+1;

sales_block: BEGIN

DECLARE l_last_sale INT DEFAULT 0;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_sale=1;

OPEN sales_csr;

sales_loop:LOOP /* Get all sales for the customer */

FETCH sales_csr INTO l_sales_id;

IF l_last_sale=1 THEN LEAVE sales_loop; END IF; /*no more rows*/

CALL check_sale(l_sales_id); /* Check the sale status */

SET l_sales_count=l_sales_count+1;

END LOOP sales_loop;

SET l_last_sale=0;

CLOSE sales_csr;

END sales_block;

END LOOP cust_loop;

SET l_last_customer=0;

CLOSE customer_csr;

Note that we now have a separate "not found" variable for each cursor, and we have eliminated any possibility that the closing of one cursor could affect the status of another. However, also note that we still reset the "not found" variables after we completed each cursor loop—this remains highly recommended since you may still wish to reopen a cursor within the same block.

Exiting the Cursor Loop Prematurely

Don't assume that you can only exit the cursor loop when the last row has been retrieved—you can issue a LEAVE statement at any time that you think that your processing has been completed. You may be looking for only one or a limited number of candidate records in the result set, or you may have detected some other condition suggesting that further processing is unnecessary.

Cursor Error Conditions

Cursor statements must occur in the sequence OPEN-FETCH-CLOSE. Any variation on this sequence will result in runtime errors.

For instance, if you try to CLOSE or FETCH from a cursor that is not open, you will encounter a Cursor is not open error, as shown in Example 5-18.

Example 5-18. Cursor is not open error

mysql> CREATE PROCEDURE csr_error2( )

BEGIN

DECLARE x INT DEFAULT 0;

DECLARE c cursor for select 1 from departments;

CLOSE c;

END;

Query OK, 0 rows affected (0.00 sec)

mysql> CALL csr_error2( );

ERROR 1326 (24000): Cursor is not open

Attempting to open a cursor that is already open results in a Cursor is already open error, as shown in Example 5-19.

Example 5-19. Cursor is already open error

mysql> CREATE PROCEDURE csr_error3( )

BEGIN

DECLARE x INT DEFAULT 0;

DECLARE c cursor for select 1 from departments;

OPEN c;

OPEN c;

END;

//

Query OK, 0 rows affected (0.00 sec)

mysql> CALL csr_error3( );

ERROR 1325 (24000): Cursor is already open

Using Unbounded SELECT Statements

MySQL stored procedures (but not functions) can return result sets to the calling program (though not, unfortunately, directly to another stored procedure). A result set is returned from a stored procedure whenever a SQL statement that returns a result set is not associated with either an INTOclause or a cursor. We call these SQL statements unbounded. Such SQL statements will usually be SELECT statements, although other statements that return result sets—SHOW, EXPLAIN, DESC, and so on—can also be included within the stored procedure.

We have used unbounded SELECT statements throughout many of our examples in order to return information about stored procedure execution. You'll most likely do the same either for debugging purposes or to return some useful status information to the user or calling program. Example 5-20 shows an example of a stored procedure that uses this feature to return a list of employees within a specific department.

Example 5-20. Using unbounded SELECTs to return data to the calling program

CREATE PROCEDURE emps_in_dept(in_department_id INT)

BEGIN

SELECT department_name, location

FROM departments

WHERE department_id=in_department_id;

SELECT employee_id,surname,firstname

FROM employees

WHERE department_id=in_department_id;

END;

When run, the stored procedure from Example 5-20 produces the following output:

mysql> CALL emps_in_dept(31) //

+-------------------+----------+

| department_name | location |

+-------------------+----------+

| ADVANCED RESEARCH | PAYSON |

+-------------------+----------+

1 row in set (0.00 sec)

+-------------+----------+-----------+

| employee_id | surname | firstname |

+-------------+----------+-----------+

| 149 | EPPLING | LAUREL |

| 298 | CHARRON | NEWLIN |

| 447 | RAMBO | ROSWALD |

| 596 | GRESSETT | STANFORD |

| 745 | KANE | CARLIN |

| 894 | ABELL | JAMIE |

| 1043 | BROOKS | LYNN |

| 1192 | WENSEL | ZENAS |

| 1341 | ZANIS | ALDA |

| 1490 | PUGH | ALICE |

| 1639 | KUEHLER | SIZA |

| 1788 | RUST | PAINE |

| 1937 | BARRY | LEO |

+-------------+----------+-----------+

13 rows in set (0.00 sec)

In some respects, using stored procedures to return result sets in this way provides similar functionality to creating a view to support specific queries. Like a view, the stored procedure can encapsulate complex SQL operations, thus making it easier for a user to retrieve data without necessarily having to understand the complexities of the schema design. Encapsulating SQL inside a stored procedure can also improve security, because you can perform complex validation checks or even encryption/decryption before returning the result set.

Unlike a view, a stored procedure can return multiple result sets, as shown in Example 5-20. Returning multiple result sets can be a convenient way to encapsulate all of the logic required to produce multiple sets of application data in a single call to the database.

Retrieving the Result Sets in the Calling Program

It is relatively easy to retrieve a result set from a stored procedure. Provided that the stored procedure returns only a single result set, it can be handled in the same way as a normal SQL call. Example 5-21 shows a PHP program using the mysqli interface that retrieves a single result set from a stored procedure call.

Example 5-21. Retrieving a stored procedure result set from PHP

1 <h1>Department listing</h1>

2 <table border="1" width="90%">

3 <tr> <td><b>Department ID</b></td>

4 <td><b>Department Name</b></td>

5 <?php

6 $hostname="localhost";

7 $username="root";

8 $password="secret";

9 $database="sqltune";

10

11 $p1="";

12 $p2="";

13

14

15 $dbh = new mysqli($hostname, $username, $password, $database);

16

17 /* check connection */

18 if (mysqli_connect_errno( )) {

19 printf("Connect failed: %s\n", mysqli_connect_error( ));

20 exit( );

21 }

22

23 if ($result_set = $dbh->query("call department_list( )"))

24 {

25 printf('');

26 while($row=$result_set->fetch_object( ))

27 {

28 printf("<tr><td>%s</td><td>%s</td></tr>\n",

29 $row->department_id, $row->department_name);

30 }

31 }

32 else // Query failed - show error

33 {

34 printf("<p>Error retrieving stored procedure result set:%d (%s) %s\n",

35 mysqli_errno($dbh),mysqli_sqlstate($dbh),mysqli_error($dbh));

36 $dbh->close( );

37 exit( );

38 }

39 /* free result set */

40 $result_set->close( );

41 $dbh->close( );

42

43 ?>

44 </table>

45 </body>

46 </html>

The significant lines of code from Example 5-21 include:

The output of the PHP program is shown in Figure 5-1.

Figure 5-1. Output of a PHP program that retrieves a stored procedure result set

The ability to return multiple result sets from a stored procedure can be either a blessing or a curse, depending on your perspective. The multiple result set feature can allow you to return multiple logically related sets of data in a single operation. For instance, all the result sets necessary to populate a multilevel master-detail report can be requested from the database in one operation. This could result in a greater level of separation between presentation (often web) logic and data access (database) logic.

However, handling multiple result sets may require unfamiliar processing requirements in our client-side programming. Some third-party report-generating tools may be unprepared for the possibility of multiple result sets being sent out by a single database call. In fact, some of these third-party tools may be unable to cope with a stored procedure sending out a result set at all.

Luckily, the major programming interfaces we use with MySQL—PHP, Java, Perl, Python, and .NET C# and VB.NET—are all capable of handling multiple result sets. In Chapters 13 through 17, we explore in detail how to process result sets and perform other operations on MySQL stored procedures in these languages. To give you a preview of the general process, Example 5-22 shows how we retrieve multiple results sets from a MySQL stored procedure in Java.

Example 5-22. Retrieving multiple result sets from a stored procedure in Java

1 private void empsInDept(Connection myConnect, int deptId) throws SQLException {

2

3 CallableStatement cStmt = myConnect

4 .prepareCall("{CALL sp_emps_in_dept(?)}");

5 cStmt.setInt(1, deptId);

6 cStmt.execute( );

7 ResultSet rs1 = cStmt.getResultSet( );

8 while (rs1.next( )) {

9 System.out.println(rs1.getString("department_name") + " "

10 + rs1.getString("location"));

11 }

12 rs1.close( );

13

14 /* process second result set */

15 if (cStmt.getMoreResults( )) {

16 ResultSet rs2 = cStmt.getResultSet( );

17 while (rs2.next( )) {

18 System.out.println(rs2.getInt(1) + " " + rs2.getString(2) + " "

19 + rs2.getString(3));

20 }

21 rs2.close( );

22 }

23 cStmt.close( );

24 }

Let's step through the important parts of Example 5-22:

Returning Result Sets to Another Stored Procedure

We know that we can return result sets to a calling program (such as PHP)—but is there a way to return the result set to another stored procedure?

Unfortunately, the only way to pass a result set from one stored procedure to another is to pass the results via a temporary table. This is an awkward solution, and— because the temporary table has scope throughout the entire session—it creates many of the same maintainability issues raised by the use of global variables. But if one stored program needs to supply another stored program with results, then a temporary table can be the best solution.

Let's look at an example. In Example 5-23, we have a stored procedure that is responsible for creating a temporary table that contains all overdue sales. Although this SQL is simple enough that we could replicate the SQL in every stored procedure that needs to process overdue orders, our performance is improved if we create this list only once during our batch run, and modularity and maintainability are improved if we define this query in only one place.

Example 5-23. Stored procedure that creates a temporary table

CREATE PROCEDURE sp_overdue_sales ( )

BEGIN

DROP TEMPORARY TABLE IF EXISTS overdue_sales_tmp;

CREATE TEMPORARY TABLE overdue_sales_tmp AS

SELECT sales_id,customer_id,sale_date,quantity,sale_value

FROM sales

WHERE sale_status='O';

END;

In Example 5-24 we see a stored procedure that calls the previous stored procedure and consumes the rows placed in the temporary table. In practice, this is pretty much equivalent to passing the result set from one stored procedure to another.

Example 5-24. Stored procedure that consumes data from a temporary table

CREATE PROCEDURE sp_issue_invoices( )

BEGIN

DECLARE l_sale_id INT;

DECLARE l_last_sale INT DEFAULT 0;

DECLARE sale_csr CURSOR FOR

SELECT sales_id

FROM overdue_sales_tmp;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_sale=1;

CALL sp_overdue_sales( );

OPEN sale_csr;

sale_loop:LOOP

FETCH sale_csr INTO l_sale_id;

IF l_last_sale THEN

LEAVE sale_loop;

END IF;

CALL sp_issue_one_invoice(l_sale_id);

END LOOP sale_loop;

CLOSE sale_csr;

END;

Note that in MySQL, temporary tables have scope only within the specific session that creates the table, and they are automatically de-allocated when that session completes. So we don't have to worry about cleaning up the temporary table or be concerned that the table could be simultaneously updated by another session.

Performing Dynamic SQL with Prepared Statements

MySQL supports a facility known as server-side prepared statements , which provides an API-independent way of preparing a SQL statement for repeated execution efficiently and securely. Prepared statements are interesting from a stored programming perspective because they allow us to create dynamic SQL calls.

We create a prepared statement with the PREPARE statement:

PREPARE statement_name FROMsql_text

The SQL text may contain placeholders for data values that must be supplied when the SQL is executed. These placeholders are represented by ? characters.

The prepared statement is executed with the, EXECUTE statement:

EXECUTE statement_name [USING variable [,variable...]]

The USING clause can be used to specify values for the placeholders specified in the PREPARE statement. These must be supplied as user variables (prefixed with the @ character), which we described in Chapter 3.

Finally, we can drop the prepared statement with the DEALLOCATE statement:

DEALLOCATE PREPARE statement_name

An example of using prepared statements within the MySQL command-line client is shown in Example 5-25.

Example 5-25. Using prepared statements

mysql> PREPARE prod_insert_stmt FROM "INSERT INTO product_codes VALUES(?,?)";

Query OK, 0 rows affected (0.00 sec)

Statement prepared

mysql>

mysql> SET @code='QB';

Query OK, 0 rows affected (0.00 sec)

mysql> SET @name='MySQL Query Browser';

Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE prod_insert_stmt USING @code,@name;

Query OK, 1 row affected (0.00 sec)

mysql> SET @code='AD';

Query OK, 0 rows affected (0.00 sec)

mysql> SET @name='MySQL Administrator';

Query OK, 0 rows affected (0.02 sec)

mysql> EXECUTE prod_insert_stmt USING @code,@name;

Query OK, 1 row affected (0.00 sec)

mysql> DEALLOCATE PREPARE prod_insert_stmt;

Query OK, 0 rows affected (0.00 sec)

Now, the idea of prepared statements is to reduce the overhead of re-parsing (preparing) a SQL statement for execution if all that has changed is a few data values, and to enhance security by allowing SQL statement parameters to be supplied in a way that prevents SQL injection (for more about SQL injection, see Chapter 18). Stored procedures don't need prepared statements for these reasons, since the SQL statements in stored procedures are already "prepared" for execution. Moreover, SQL injection is not really a threat in stored programs (ironically enough, unless you use prepared statements!).

However, prepared statements come in handy in stored programs, because they allow you to execute dynamic SQL from within a procedure (but not from within a trigger or function). A SQL statement is dynamic if it is constructed at runtime (whereas a static SQL statement is one that is constructed at the time of compilation of the program unit). You will generally rely on dynamic SQL only when you don't have all the information you need at compile time to complete your statement. This usually occurs because you need input from a user or from some other data source.

The stored procedure in Example 5-26 offers a demonstration of running dynamic SQL as a prepared statement; it will, in fact, execute any SQL that is passed in as an argument.

Example 5-26. Stored procedure with dynamic SQL

CREATE PROCEDURE execute_immediate(in_sql VARCHAR(4000))

BEGIN

SET @tmp_sql=in_sql;

PREPARE s1 FROM @tmp_sql;

EXECUTE s1;

DEALLOCATE PREPARE s1;

END;

SQL executed as a prepared statement within a stored procedure acts pretty much the same way as a static SQL statement that is embedded inside the stored procedure. However, the EXECUTE statement does not support an INTO clause, nor is it possible to define a cursor from a prepared statement. Therefore, any results from a prepared statement will be returned to the calling program and cannot be trapped in the stored procedure. To catch the rows returned by a dynamic SQL call, store them in a temporary table, as outlined in the section "Returning Result Sets to Another Stored Procedure," earlier in this chapter.

You should rely on dynamic SQL only when needed. It is more complex and less efficient than static SQL, but it does allow you to implement otherwise impossible tasks and create useful, generic utility routines. For instance, the stored procedure in Example 5-27 accepts a table name, column name, WHERE clause, and value; the procedure uses these parameters to build up an UPDATE statement that can update any table column value.

Example 5-27. Stored procedure that can update any column in any table

CREATE PROCEDURE set_col_value

(in_table VARCHAR(128),

in_column VARCHAR(128),

in_new_value VARCHAR(1000),

in_where VARCHAR(4000))

BEGIN

DECLARE l_sql VARCHAR(4000);

SET l_sql=CONCAT_ws(' ',

'UPDATE',in_table,

'SET',in_column,'=',in_new_value,

' WHERE',in_where);

SET @sql=l_sql;

PREPARE s1 FROM @sql;

EXECUTE s1;

DEALLOCATE PREPARE s1;

END;

We could call this program to zero-out the salary of employee ID 1 (eat this, CEO!) by invoking the procedure as follows:

mysql> CALL set_col_value('employees','salary','0','employee_id=1')

Another common application of dynamic SQL is to build up conditional WHERE clauses. Often, we construct user interfaces in which the user may specify multiple search criteria. Handling the "missing" conditions without dynamic SQL can lead to complex and awkward SQL, which can be difficult for MySQL to optimize. Example 5-28 shows a simple example of a search procedure that allows the user to specify any combination of customer name, contact name, or phone number.

Example 5-28. Search procedure without dynamic SQL

CREATE PROCEDURE sp_customer_search

(in_customer_name VARCHAR(30),

in_contact_surname VARCHAR(30),

in_contact_firstname VARCHAR(30),

in_phoneno VARCHAR(10))

BEGIN

SELECT *

FROM customers

WHERE (customer_name LIKE in_customer_name

OR in_customer_name IS NULL)

AND (contact_surname LIKE in_contact_surname

OR in_contact_surname IS NULL)

AND (contact_firstname LIKE in_contact_firstname

OR in_contact_firstname IS NULL)

AND (phoneno LIKE in_phoneno

OR in_phoneno IS NULL) ;

END;

The SQL in Example 5-28 is not yet unbearably complex, but as the number of candidate search columns increases, the maintainability of this statement will rapidly diminish. Even with this statement, however, we may be legitimately concerned that the SQL is not correctly optimized for the specific search criteria supplied by the end user. We may therefore wish to build up a more customized search query. Example 5-29 shows a procedure in which we construct the WHERE clause dynamically to match the search criteria supplied by the user and call that SQL dynamically using prepared statements.

Example 5-29. Search procedure with dynamic SQL

CREATE PROCEDURE sp_customer_search_dyn

(in_customer_name VARCHAR(30),

in_contact_surname VARCHAR(30),

in_contact_firstname VARCHAR(30),

in_phoneno VARCHAR(10))

BEGIN

DECLARE l_where_clause VARCHAR(1000) DEFAULT 'WHERE';

IF in_customer_name IS NOT NULL THEN

SET l_where_clause=CONCAT(l_where_clause,

' customer_name="',in_customer_name,'"');

END IF;

IF in_contact_surname IS NOT NULL THEN

IF l_where_clause<>'WHERE' THEN

SET l_where_clause=CONCAT(l_where_clause,' AND ');

END IF;

SET l_where_clause=CONCAT(l_where_clause,

' contact_surname="',in_contact_surname,'"');

END IF;

IF in_contact_firstname IS NOT NULL THEN

IF l_where_clause<>'WHERE' THEN

SET l_where_clause=CONCAT(l_where_clause,' AND ');

END IF;

SET l_where_clause=CONCAT(l_where_clause,

' contact_firstname="',in_contact_firstname,'"');

END IF;

IF in_phoneno IS NOT NULL THEN

IF l_where_clause<>'WHERE' THEN

SET l_where_clause=CONCAT(l_where_clause,' AND ');

END IF;

SET l_where_clause=CONCAT(l_where_clause,

' phoneno="',in_phoneno,'"');

END IF;

SET @sql=CONCAT('SELECT * FROM customers ',

l_where_clause);

PREPARE s1 FROM @sql;

EXECUTE s1;

DEALLOCATE PREPARE s1;

END;

Although the procedure in Example 5-29 is longer and more complicated than the static example shown in Example 5-28, it may execute faster because we have eliminated redundant WHERE clauses from the SQL that is finally executed. In that way, we give MySQL better data on which to base its decisions regarding indexes and other optimizations.

You will probably not need to use dynamic SQL and prepared statements very often, but they can certainly save the day when you are faced with the need to construct a SQL statement based on user input or stored program parameters. However, a final word of caution: when you construct SQL based on user input, you allow for the security attack known as SQL injection to occur, and SQL injection in stored procedures can pose a particularly high risk because of the unique execution context of stored procedures. We discuss SQL injection in stored programs in detail withinChapter 18.

Handling SQL Errors: A Preview

Error handling in MySQL stored programs is such an important and complex topic that we have dedicated an entire chapter—Chapter 6—to this topic. However, let's provide a quick summary here.

By default, if a SQL statement within a stored program generates an error, the stored program will cease execution and the error will be returned to the calling program. If you don't want this to happen, you must specify an error handler using the following syntax:

DECLARE {CONTINUE | EXIT} HANDLER FOR

{SQLSTATE sqlstate_code| MySQL error code| condition_name}stored_program_statement

The handler nominates an error condition—using a MySQL error code, an ANSI-standard SQLSTATE, or a named condition—and describes what is to happen if the error is encountered. The handler can do one of two things:

§ Allow execution to CONTINUE.

§ Immediately exit the block or stored program containing the handler.

The handler specifies stored program statements that will be executed when the handler is activated. These statements often set a status variable that could be checked within the main line of the program but that could also specify a BEGIN-END block containing many lines of code.

We have already looked at the use of handlers in determining when a cursor has returned the last row of its result set (see "Fetching an Entire Result Set" earlier in this chapter).

We discuss handlers in depth in the next chapter.

Conclusion

In this chapter we reviewed the facilities MySQL provides for including SQL within stored programs. The following types of SQL statements can appear in stored programs:

§ Simple embedded non-SELECT statements, including DML statements (INSERT, DELETE, UPDATE) and DDL statements (CREATE, DROP, ALTER, etc.) can be included within stored programs without any particular restrictions.

§ SELECT statements that return only one row may include an INTO clause that stores the results of the SELECT statement into stored program variables.

§ SELECT statements allow you to iterate through the rows returned by a multirow SELECT statement by using a cursor. Cursors involve a bit more programming effort, including a looping structure and a condition handler to prevent "no data to fetch" errors when all rows from the cursor have been retrieved. Nevertheless, cursors will probably be your main mechanism for performing complex data processing in stored programs.

§ "Unbounded" SELECT statements—those without an INTO clause or a CURSOR statement—can be included within stored procedures (but not within stored functions). The output from these SELECT statements will be returned to the calling program (but not to a calling stored procedure). You will need to employ special code in your calling program to handle result sets from stored procedures, especially if more than a single result set is returned.

SQL statements can also be prepared dynamically using MySQL server-side prepared statements.

If your SQL statements generate an error, your stored program will terminate and return control to the calling program unless you create an error handler that "catches" the error and takes appropriate action. We saw a simple example of an error handler in this chapter and looked at NOT FOUND handlers that handle the end of a cursor result set. In the next chapter we'll cover the topic of error handlers in greater detail.