Explicit cursor

An explicit cursor is mainly used to process a query statement, especially when the query result contains multiple records.

Syntax of an explicit cursor

After you declare an explicit cursor, you can define it in the same subprogram or package, or declare and define it at the same time.

The syntax for declaring an explicit cursor is as follows:

CURSOR cursor_name [ parameter_list ] RETURN return_type;

The syntax for defining an explicit cursor is as follows:

CURSOR cursor_name [ parameter_list ] [ RETURN return_type ]
IS select_statement;

Processing an explicit cursor

To process an explicit cursor, you must perform the following four steps:

1. Declare the cursor.

2. Open the cursor.

3. Fetch data from the cursor.

4. Close the cursor.

Declare the cursor

Declare the cursor name and the corresponding SELECT statement.

The syntax is as follows:

CURSOR cursor_name[(parameter[, parameter]...)] IS select_statement;

Cursor parameters can only be input parameters. The syntax is as follows:

parameter_name [IN] datatype [{:= | DEFAULT} expression]

Open the cursor

The OPEN statement is used to open the cursor, execute the corresponding SELECT statement, store the query result in the workspace, and point the pointer to the beginning of the workspace to indicate the result set of the cursor. If the FOR UPDATE option is specified in the cursor query statement, the OPEN statement will lock the data rows corresponding to the cursor result set in the database table.

The syntax is as follows:

OPEN cursor_name[([parameter =>] value[, [parameter =>] value]...)];

When you pass parameters to the cursor, you can use the same value-passing methods as those for function parameters, that is, positional notation and named notation. PL programs cannot use the OPEN statement to open a cursor more than once.

Fetch data from the cursor

Use the FETCH statement to retrieve data rows from the result set and store them in the specified output variable.

The syntax is as follows:

FETCH cursor_name INTO {variable_list | record_variable };

/* You can use a cursor to fetch data in batches. */
FETCH { cursor | cursor_variable | :host_cursor_variable }
  { into_clause | BULK COLLECT INTO { collection | :host_array }
  [, { collection | :host_array } ]... [ LIMIT numeric_expression ] } ;

Typically, the FETCH statement is used in a LOOP statement to process each record in the result set until all records in the active set are processed. The cursor attribute %NOTFOUND is used to detect the exit condition.

Close the cursor

The CLOSE statement is used to close the cursor.

The syntax is as follows:

CLOSE cursor_name;

After you fetch and process the data from the cursor result set, you should close the cursor to release the system resources occupied by the cursor and make the workspace of the cursor invalid. After the cursor is closed, you cannot use the FETCH statement to fetch data from it. You can use the OPEN statement to reopen the cursor.

Use an explicit cursor to loop through records in a result set

The following steps show how to use an explicit cursor to loop through records in a result set.

1. Declare a cursor in the declaration section. For more information, see the following two methods.

   CURSOR cursor_name IS query;      --Method 1
   CURSOR cursor_name(para_name data_type) IS query;     --Method 2
   

2. Declare a record in the declaration section to store the rows returned by the cursor.

   record_name cursor_name%ROWTYPE;
   

3. Open the cursor in the execution section. For more information, see the following two methods.

   OPEN cursor_name;       --Method 1
   OPEN cursor_name(para_name);      --Method 2
   

4. Fetch row data from the cursor result set in the execution section. Each time you fetch one row, you can use the following LOOP statement to fetch multiple rows.

   LOOP
   FETCHcursor_name INTO record_name;
   EXIT WHEN cursor_name%NOTFOUND;
   statement;
   [ statement; ]...
   END LOOP;
   

5. Close the cursor.

   CLOSE cursor_name;
   

Example: Use an explicit cursor to loop through records in a result set.

obclient> CREATE TABLE ware (
    w_id int
  , w_ytd decimal(12,2)
  , w_tax decimal(4,4)
  , w_name varchar(10)
  , w_street_1 varchar(20)
  , w_street_2 varchar(20)
  , w_city varchar(20)
  , w_state char(2)
  , w_zip char(9)
  , unique(w_name, w_city)
  , primary key(w_id)
  );
Query OK, 0 rows affected

obclient> INSERT INTO ware VALUES(1, 1200, .1868, 'W_NAME_1', 'jTNkXKWXOdh',
                                  'lf9QXTXXGoF04IZBkCP7', 'srRq15uvxe5', 'GQ', 506811111);
Query OK, 1 row affected

obclient> INSERT INTO ware VALUES(2, 1200, .0862,  'W_NAME_2', 'xEdT1jkENtbLwoI1Zb0',
                                  'NT0j4RCQ4OqrS', 'vlwzndw2FPrO', 'XR', 063311111);
Query OK, 1 row affected

obclient> CREATE OR REPLACE PACKAGE pkg_ware_mgmt as
      PROCEDURE sp_record_print(p_w_id IN ware.w_id%type);
END;/
Query OK, 0 rows affected

obclient> CREATE OR REPLACE PACKAGE BODY pkg_ware_mgmt
     AS
     PROCEDURE sp_record_print_by_record(p_record IN ware%ROWTYPE)
    AS
  BEGIN
      dbms_output.put_line('Print a record :');
      dbms_output.put_line('W_ID : ' || p_record.w_id
        || ', W_YTD : ' || p_record.w_ytd
        || ', W_TAX : ' || p_record.w_tax
        || ', W_NAME : ' || p_record.w_name
        || ', W_STREET_1 : ' || p_record.w_street_1
        || ', W_STREET_2 : ' || p_record.w_street_2
        || ', W_CITY : ' || p_record.w_city
        || ', W_STATE : ' || p_record.w_state
        || ', W_ZIP : ' || p_record.w_zip )
      ;
      dbms_output.put_line('');
  END;

    PROCEDURE sp_record_print(p_w_id IN ware.w_id%TYPE )
  IS
     CURSOR c1(cp_w_id ware.w_id%TYPE ) IS SELECT * FROM ware WHERE w_id = cp_w_id;
     r_ware ware%ROWTYPE;
    BEGIN
    OPEN c1(p_w_id);
    dbms_output.put_line('Open a cursor with a parameter [ ' || p_w_id || ' ].');
    LOOP
      FETCH c1 INTO r_ware ;
      dbms_output.put_line('Fetch the cursor one time.');
      EXIT WHEN c1%NOTFOUND ;
      sp_record_print_by_record(r_ware);
    END LOOP;

  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('Raise an unkown exception !');
  END ;
END;
/
Query OK, 0 rows affected

obclient>SET SERVEROUTPUT ON;
Query OK, 0 rows affected

obclient>CALL pkg_ware_mgmt.sp_record_print(2);
Query OK, 0 rows affected

Open a cursor with a parameter [ 2 ].
Fetch the cursor one time.
Print a record :
W_ID : 2, W_YTD : 1200, W_TAX : .0862, W_NAME : W_NAME_2, W_STREET_1 : xEdT1jkENtbLwoI1Zb0, W_STREET_2 : NT0j4RCQ4OqrS, W_CITY : vlwzndw2FPrO, W_STATE : XR, W_ZIP : 63311111
Fetch the cursor one time.