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.

5.3.1. 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;

5.3.2. 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.

5.3.3. 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;

5.3.4. 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.

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.

5.3.5. 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 the no_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 in Example 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 unusualit 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 redundantalthough 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;

5.3.6. 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 completesso 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.

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 complexbut arguably more robust solutionis 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 loopthis remains highly recommended since you may still wish to reopen a cursor within the same block.

5.3.7. Exiting the Cursor Loop Prematurely

Don't assume that you can only exit the cursor loop when the last row has been retrievedyou 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.

5.3.8. 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

Part I: Stored Programming Fundamentals

Introduction to MySQL Stored Programs

MySQL Stored Programming Tutorial

Language Fundamentals

Blocks, Conditional Statements, and Iterative Programming

Using SQL in Stored Programming

Error Handling

Part II: Stored Program Construction

Creating and Maintaining Stored Programs

Transaction Management

MySQL Built-in Functions

Stored Functions

Triggers

Part III: Using MySQL Stored Programs in Applications

Using MySQL Stored Programs in Applications

Using MySQL Stored Programs with PHP

Using MySQL Stored Programs with Java

Using MySQL Stored Programs with Perl

Using MySQL Stored Programs with Python

Using MySQL Stored Programs with .NET

Part IV: Optimizing Stored Programs

Stored Program Security

Tuning Stored Programs and Their SQL

Basic SQL Tuning

Advanced SQL Tuning

Optimizing Stored Program Code

Best Practices in MySQL Stored Program Development



MySQL Stored Procedure Programming
MySQL Stored Procedure Programming
ISBN: 0596100892
EAN: 2147483647
Year: 2004
Pages: 208

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net