Explicit cursor

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

Syntax of an explicit cursor

After declaring 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 steps of 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;

The 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 also lock the data rows in the database table corresponding to the cursor result set.

The syntax is as follows:

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

When passing parameters to the cursor, you can use the same value-passing methods as for function parameters, namely positional notation and named notation. PL programs cannot use the OPEN statement to reopen a cursor.

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 loop until all records in the active set have been 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 fetching and processing the data from the cursor result set, you should close the cursor to release the system resources it occupies and make its workspace invalid. Once a cursor is closed, you cannot use the FETCH statement to retrieve data from it. A closed cursor can be reopened using the OPEN statement.

Use an explicit cursor to fetch records from a record set

The following steps show how to use an explicit cursor to fetch records from a record set.

1. Declare a cursor in the declaration section. You can use either of the following methods.

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

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

   record_name cursor_name%ROWTYPE;
   

3. Open the cursor in the execution section. You can use either of the following methods.

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

4. Fetch the row data from the cursor record set in the execution section. You can use the following LOOP statement to fetch multiple rows of data at a time.

   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 fetch records from a record 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.