Explicit cursors

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

Syntax for calling an explicit cursor

After you declare an explicit cursor, you can define it in the same subprogram or program package. You can also declare and define an explicit cursor 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;

Handling procedure of an explicit cursor

The handling procedure comprises four steps:

1. Define the cursor.

2. Open the cursor.

3. Fetch cursor data.

4. Close the cursor.

Define the cursor

Define a cursor name and the corresponding SELECT statement.

The syntax is as follows:

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

The cursor parameter can only be an input parameter in the following syntax:

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

Open the cursor

You can use the OPEN statement to open the cursor, execute the SELECT statement corresponding to the cursor, place the query result to the workspace, and enable the pointer to point to the header of the workspace to identify the result set of the cursor. If the query statement of the cursor contains the FOR UPDATE option, the OPEN statement will also lock the data rows corresponding to the result set of the cursor in the database table.

The syntax is as follows:

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

You can pass parameters to a cursor by using the same methods as passing function parameters, namely, by using positional notation or named notation. A PL program can use the OPEN statement to open a cursor only once.

Fetch cursor data

You can use the FETCH statement to fetch data rows from the result set and place them in the specified output variable.

The syntax is as follows:

FETCH cursor_name INTO {variable_list | record_variable };

/* Use the 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 ] } ;

Generally, the FETCH statement is used in a LOOP statement to process records in a collection in a loop manner until all records are processed. The cursor attribute %NOTFOUND is used to detect the exit condition.

Close the cursor

You can use the CLOSE statement to close the cursor.

The syntax is as follows:

CLOSE cursor_name;

After the data in the result set of the cursor is fetched and processed, you need to close the cursor in a timely manner to release the occupied system resources and invalidate the workspace of the cursor. Then, you can no longer use the FETCH statement to fetch data from the cursor. A closed cursor can be opened again by using the OPEN statement.

Use an explicit cursor to fetch all rows from a record set in a loop manner

The following describes the procedure for using an explicit cursor to fetch all rows from a record set in a loop manner.

1. Declare a cursor in the declarative part by using 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 in the declarative part to store the row records returned by the cursor.

   record_name cursor_name%ROWTYPE;
   

3. Open the cursor in the executable part by using either of the following methods:

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

4. Fetch row data from the record set of the cursor in the executable part. One row is fetched each time. You can use the 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 fetch all rows from a record set in a loop manner

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