Cursors

You use a cursor when you have a SELECT statement that returns more than one row from the database. A cursor is basically a set of rows that you can access one at a time. You retrieve the rows into the cursor using your SELECT statement and then fetch the rows from the cursor. You may follow five steps when using a cursor:

Declare variables to store the column values from the SELECT statement.
Declare the cursor, specifying your SELECT statement.
Open the cursor.
Fetch the rows from the cursor.
Close the cursor.

You ll learn the details of these five steps in the following sections. You ll also learn how to access the rows in a cursor using a FOR loop.

Step 1: Declare the Variables to Store the Column Values

The first step is to declare the variables that will be used to store the column values. These variables must be compatible with the column types.

Tip	Earlier you saw that %TYPE may be used to get the type of a column. If you use %TYPE when declaring your variables, your variables will automatically be of the correct type.

The following example declares three variables to store the product_id, name , and price columns from the products table using %TYPE:

 DECLARE   v_product_id products.product_id%TYPE;   v_name products.name%TYPE;   v_price products.price%TYPE;

Step 2: Declare the Cursor

A cursor declaration consists of a name that you assign to the cursor and the SELECT statement that you want to execute ”this SELECT statement is not actually run until you open the cursor. The cursor declaration, like all other declarations, is placed in the declaration section. The syntax for declaring a cursor is as follows :

 CURSOR  cursor_name  IS  SELECT_statement;

where

cursor_name specifies the name of the cursor.
SELECT_statement is a SELECT statement.

The following example declares a cursor named cv_product_cursor whose SELECT statement retrieves the product_id, name, and price columns from the products table:

 CURSOR cv_product_cursor IS   SELECT product_id, name, price   FROM products   ORDER BY product_id;

Step 3: Open the Cursor

The next step is to open the cursor, which runs the SELECT statement. You open a cursor using the OPEN statement, which must be placed in the executable section of the block.

The following example opens cv_product_cursor, and therefore also runs the SELECT statement that retrieves the rows from the products table:

 OPEN cv_product_cursor;

Step 4: Fetch the Rows from the Cursor

To read each row from the cursor, you can use the FETCH statement. The FETCH statement reads the column values into the variables that you specify; FETCH uses the following syntax:

 FETCH  cursor_name  INTO  variable  [,  variable  ...];

where

cursor_name specifies the name of the cursor.
variable is a previously declared variable into which values from the cursor s SELECT statement are stored.

The following example uses FETCH to retrieve a row from cv_product_cursor and stores the column values in the v_product_id, v_name, and v_price variables created earlier in step 1:

 FETCH cv_product_cursor INTO v_product_id, v_name, v_price;

Note

Of course, a cursor may contain many rows; therefore, a loop is required is to read each row in turn . To determine when the loop is to end, you can use the Boolean variable cv_product_cursor%NOTFOUND. This variable is true when the FETCH statement has reached the end of the rows in the cursor, and there are no further rows to read. The following example shows a loop that reads each row from cv_product_cursor:

 LOOP   FETCH cv_product_cursor   INTO v_product_id, v_name, v_price;   -- exit the loop when there are no more rows, as indicated by   -- the Boolean variable cv_product_cursor%NOTFOUND (= true when   -- there are no more rows)   EXIT WHEN cv_product_cursor%NOTFOUND;   -- use DBMS_OUTPUT.PUT_LINE() to display the variables   DBMS_OUTPUT.PUT_LINE(    'v_product_id = '  v_product_id  ', v_name = '  v_name     ', v_price = '  v_price   ); END LOOP;

Notice I ve used DBMS_OUTPUT.PUT_LINE() to display the v_product_id, v_name, and v_price variables that were read for each row.

Step 5: Close the Cursor

Once you ve finished with the cursor, the final step is to close the cursor using the CLOSE statement. Closing your cursors frees up system resources. The following example closes cv_product_cursor:

 CLOSE cv_product_cursor;

The following section shows a complete example script that you can run using SQL*Plus. This script contains all of the five steps for using a cursor.

Complete Example: product_cursor.sql

The SQL*Plus script product_cursor.sql is contained in the SQL directory where you unzipped the files for this book. The product_cursor.sql script is as follows:

 -- product_cursor.sql displays the product_id, name, -- and price columns from the products table using a cursor SET SERVEROUTPUT ON DECLARE   -- step 1: declare the variables   v_product_id products.product_id%TYPE;   v_name products.name%TYPE;   v_price products.price%TYPE;   -- step 2: declare the cursor   CURSOR cv_product_cursor IS     SELECT product_id, name, price     FROM products     ORDER BY product_id; BEGIN   -- step 3: open the cursor   OPEN cv_product_cursor;   LOOP     -- step 4: fetch the rows from the cursor     FETCH cv_product_cursor     INTO v_product_id, v_name, v_price;     -- exit the loop when there are no more rows, as indicated by     -- the Boolean variable cv_product_cursor%NOTFOUND (= true when     -- there are no more rows)     EXIT WHEN cv_product_cursor%NOTFOUND;     -- use DBMS_OUTPUT.PUT_LINE() to display the variables     DBMS_OUTPUT.PUT_LINE(       'v_product_id = '  v_product_id  ', v_name = '  v_name        ', v_price = '  v_price     );   END LOOP;   -- step 5: close the cursor   CLOSE cv_product_cursor; END; /

To run this script, follow these steps:

Start SQL*Plus.
Connect to the database as store with the password store_password.
Run the product_cursor.sql script using a command similar to the following:
```
 @ C:\SQL\product_cursor.sql 
```
Note
If your product_cursor.sql script is in a different directory from C:\SQL, you should use that directory in the previous command.

The output from product_cursor.sql is as follows:

 v_product_id = 1, v_name = Modern Science, v_price = 19.95 v_product_id = 2, v_name = Chemistry, v_price = 30 v_product_id = 3, v_name = Supernova, v_price = 25.99 v_product_id = 4, v_name = Tank War, v_price = 13.95 v_product_id = 5, v_name = Z Files, v_price = 49.99 v_product_id = 6, v_name = 2412: The Return, v_price = 14.95 v_product_id = 7, v_name = Space Force 9, v_price = 13.49 v_product_id = 8, v_name = From Another Planet, v_price = 12.99 v_product_id = 9, v_name = Classical Music, v_price = 10.99 v_product_id = 10, v_name = Pop 3, v_price = 15.99 v_product_id = 11, v_name = Creative Yell, v_price = 14.99 v_product_id = 12, v_name = My Front Line, v_price = 13.49

Cursors and FOR Loops

You can combine the power of a FOR loop to access the rows in a cursor. When you use a FOR loop, you don t have to explicitly open and close the cursor ”the FOR loop does this automatically for you. The following product_cursor2.sql script uses a FOR loop to access the rows in cv_product_cursor. Notice how concise the FOR loop is:

 -- product_cursor2.sql displays the product_id, name, -- and price columns from the products table using a cursor -- and a FOR loop SET SERVEROUTPUT ON DECLARE   CURSOR cv_product_cursor IS     SELECT product_id, name, price     FROM products     ORDER BY product_id; BEGIN   FOR v_product IN cv_product_cursor LOOP     DBMS_OUTPUT.PUT_LINE(       'product_id = '  v_product.product_id        ', name = '  v_product.name        ', price = '  v_product.price     );   END LOOP; END; /

To run the product_cursor2.sql script, you issue a command similar to the following:

 @ C:\SQL\product_cursor2.sql

The output from this script is as follows:

 product_id = 1, name = Modern Science, price = 19.95 product_id = 2, name = Chemistry, price = 30 product_id = 3, name = Supernova, price = 25.99 product_id = 4, name = Tank War, price = 13.95 product_id = 5, name = Z Files, price = 49.99 product_id = 6, name = 2412: The Return, price = 14.95 product_id = 7, name = Space Force 9, price = 13.49 product_id = 8, name = From Another Planet, price = 12.99 product_id = 9, name = Classical Music, price = 10.99 product_id = 10, name = Pop 3, price = 15.99 product_id = 11, name = Creative Yell, price = 14.99 product_id = 12, name = My Front Line, price = 13.49