Cursor Example - MySQL

In this example we’ll use a cursor within a stored procedure named cursor1 in order to copy data from certain records found in one table (products) into a different table (curtest). We’ll be referring once again to the products table that we’ve used throughout this chapter. Let’s begin by examining the CREATE PROCEDURE statement:

(Once again, we’ve already used the DELIMITER command to set the pipe character as the statement delimiter.)

This procedure has no input or output parameters. It does have a number of variable and other declarations. Let’s repeat those here with some comments. First of all, we declare the cursor that we’ll use to fetch records from the products table:

DECLARE prodrecord
  CURSOR FOR
    SELECT name, price
    FROM products
    ORDER BY name;

This cursor is named prodrecord, and we indicate in the declaration’s FOR clause that it will be used for a query that selects the name and price columns for all rows in the products table, and that the results of this query will be ordered by the name column. It’s important that we get the declaration right, because this cursor can’t be used for any other query, not even one that’s only slightly different from this one. In other words, if we change the query as it’s actually used in the procedure, then we must change the query in the cursor declaration so that it matches it exactly.

Next, we declare an integer local variable named done, which we’ll use as a flag to let us know when the procedure has finished executing. We set its default value to 0 (FALSE). Then we declare a CONTINUE handler for SQLSTATE '02000' , which, as you saw in the last section, is the SQLSTATE error that results when MySQL can’t find any more records from a table. (Note that the error code needs to be set off in single quotation marks.) The handler tells MySQL that when this condition is encountered, it should set the value of done to 1 (TRUE) and then continue execution of the procedure.

DECLARE done INT DEFAULT 0 ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

For the error-handler declaration we could also have used this:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

Before getting into the procedure itself, we declare two additional variables, which we’ll use for temporary storage of column values from the rows to be fetched from the products table.

DECLARE prodname VARCHAR(30);
DECLARE prodprice DECIMAL(6, 2);

Notice that these are the same datatypes as those used in the original products table for the name and price columns. They’re also the same types as the name and price columns in the table named curtest, which we define next using a normal CREATE TABLE statement:

CREATE TABLE curtest(
  name VARCHAR(30),
  price DECIMAL(6, 2)
);

It isn’t necessary for you to use the same column names in both tables. We’ve done so here only as a matter of convenience. However, it is necessary that you use compatible datatypes for any variables or table columns that will be holding the same data. For example, if we used INT for either the prodprice local variable or the price column in the new curtest table that we’re storing the data in, we’d be very likely to truncate the values being retrieved from the price column of the products table.

Now we open the prodrecord cursor:

OPEN prodrecord;

The OPEN statement, as noted previously, prepares the cursor to receive rows returned by a FETCH operation. Trying to use a cursor prior to opening it will result in a fatal error, and the procedure will not execute past the point where this occurs—unless, of course, you’ve prepared for this eventuality by having declared the proper handler beforehand.

The remainder of this procedure consists of a WHILE loop in which we fetch successive records from products and store the column values in the variables prodname and prodprice. This is accomplished for each row selected by means of a single FETCH ... INTO statement. Next we test the value of prodprice to see if it’s greater than 50.00; if it is, then we store both column values in curtest using an INSERT command:

WHILE NOT done DO
  FETCH prodrecord INTO prodname, prodprice;
  IF prodprice > 50.00 THEN
    INSERT INTO curtest
    VALUES (prodname, prodprice);
  END IF;
END WHILE;

The statements inside the WHILE loop continue to execute so long as the value of done is FALSE (0). After the last row in the result set matching the query is retrieved, MySQL will return an SQLSTATE error condition (error code 02000) the next time that an attempt is made to fetch a row from that result set. When this occurs, the handler we declared previously will set the value of done to 1 and exe cution will continue: Here, the next point in program flow is the beginning of the WHILE loop, where done is tested again. Its value is now found to be TRUE, so the condition NOT done evaluates to FALSE, and the procedure exits the loop. We’re not going to use the prodrecord cursor anymore, so as a matter of good housekeeping we close the cursor with the statement

CLOSE prodrecord;

We can now run cursor1 using a CALL command, and then view the result by selecting all data from the curtest table, as shown here:

Let’s test this result by comparing it with the data that’s still in the products table:

You’re not limited to inserting data into just one table. For example, you could easily rewrite the WHILE loop in this example as something like this:

WHILE NOT done DO
  FETCH prodrecord INTO prodname, prodprice;
  IF prodprice > 50.00 THE N
    INSERT INTO curtest
    VALUES (prodname, prodprice);
  ELSE
    INSERT INTO curtest2
    VALUES (prodname.prodprice)
  END IF;
END WHILE;

Or even as this:

WHILE NOT done DO
  FETCH prodrecord INTO prodname, prodprice;
  CASE
    WHEN prodprice > 50.00 THEN
      INSERT INTO curtest
      VALUES (prodname, prodprice);
    WHEN prodprice > 25.00 THEN
      INSERT INTO curtest2
      VALUES (prodname, prodprice);
  ELSE
    INSERT INTO curtest3
    VALUES (prodname.prodprice)
  END CASE;
END WHILE;

Assuming that the tables curtest2 and curtest3  exist—because either they’re already present in the database or you’ve created them as part of the stored procedure—the first of these two loops will cause data for all products costing more than 50 dollars to be inserted into curtest and that for all products costing 50 dollars or less to be inserted into curtest2. The second loop would cause data for all products costing more than 50 dollars to be inserted into curtest, data for products costing more than 25 but less than or equal to 50 dollars to be inserted into curtest2, and data for products costing 25 dollars or less into curtest3. And so on: You can use any logical or other operators found in MySQL as well as any flow-control or looping constructs supported in MySQL stored procedures in testing or manipulating the data that you’ve extracted from table rows retrieved using FETCH .

You can also use most of the variations on CREATE TABLE supported by MySQL, such as CREATE TABLE IF NOT EXISTS and CREATE TEMPORARY TABLE , in a stored procedure.

Please be sure to check back next week for the next part of this article.