Cursor variables

Like cursors, cursor variables are pointers to the current data row in a multi-row query result set.

However, unlike cursors, cursor variables are dynamic, while cursors are static. A cursor can only be associated with a specific query, meaning it always points to the memory processing area of that query. In contrast, a cursor variable can be associated with different query statements, pointing to the memory processing areas of those queries (but not multiple areas at the same time; it can only be connected to one query statement at a time), provided the return types of these queries are compatible.

Declare a cursor variable

A cursor variable is a pointer of type REF CURSOR. Therefore, you must define the type of the cursor variable before you declare the cursor variable. In PL/SQL, you can declare a cursor variable reference type in the declaration section of a block, subprogram, or package.

The syntax is as follows:

TYPE ref_type_name IS REF CURSOR [ RETURN return_type];

Here, ref_type_name specifies the name of the cursor variable type to be defined, and return_type specifies the return value type of the cursor variable, which must be a record variable.

You can define a cursor variable type as strongly typed or weakly typed. In a strongly typed definition, you must specify the return value type of the cursor variable. In a weakly typed definition, you do not need to specify the return value type.

Here is an example of a strongly typed cursor variable:

obclient> CREATE TABLE emp(  
   empno          NUMBER(4,0),  
   empname        VARCHAR(10),  
   job            VARCHAR(10),   
   deptno         NUMBER(2,0)   
);
Query OK, 0 rows affected 

obclient>INSERT INTO emp VALUES (200,'Jennifer','AD_ASST',1);
obclient>INSERT INTO emp VALUES (202,'Pat','MK_REP',2);
obclient>INSERT INTO emp VALUES (119,'Karen','PU_CLERK', 4);
obclient>INSERT INTO emp VALUES (118,'Guy','PU_CLERK', 4);
obclient>INSERT INTO emp VALUES (201,'Michael','MK_MAN', 3);

obclient> DECLARE
        TYPE rec_emp_job IS RECORD(
             employee_id emp.empno%TYPE,
             employee_name emp.empname%TYPE,
             job_id emp.job%TYPE
           );
         TYPE emp_job_refcur_type IS REF CURSOR RETURN rec_emp_job;
         refcur_emp emp_job_refcur_type;
         emp_job rec_emp_job;
    BEGIN
         OPEN refcur_emp FOR
               SELECT empno, empname, job
               FROM emp
               ORDER BY deptno;
         FETCH refcur_emp INTO emp_job;
         WHILE refcur_emp%FOUND LOOP
             DBMS_OUTPUT.PUT_LINE(emp_job.employee_id||': '||emp_job.employee_name||' is a '||emp_job.job_id);
         FETCH refcur_emp INTO emp_job;
         END LOOP;
     END;
     /
Query OK, 0 rows affected 

200: Jennifer is a AD_ASST
202: Pat is a MK_REP
201: Michael is a MK_MAN
119: Karen is a PU_CLERK
118: Guy is a PU_CLERK
...

Here is an example of a weakly typed cursor variable:

obclient> DECLARE
TYPE rec_emp_job IS RECORD(
         employee_id emp.empno%TYPE,
             employee_name emp.empname%TYPE,
             job_id emp.job%TYPE
             );
         -- Here, emp_job_refcur_type does not specify the return type.

       TYPE emp_job_refcur_type IS REF CURSOR;
         refcur_emp emp_job_refcur_type;
         emp_job rec_emp_job;
         BEGIN
     OPEN refcur_emp FOR
         SELECT empno, empname, job
             FROM emp
               ORDER BY deptno;
               FETCH refcur_emp INTO emp_job;
         WHILE refcur_emp%FOUND LOOP
         DBMS_OUTPUT.PUT_LINE(emp_job.employee_id||': '||emp_job.employee_name||' is a '||emp_job.job_id);
             FETCH refcur_emp INTO emp_job;
         END LOOP;
         END;
     /
     Query OK, 0 rows affected
200: Jennifer is a AD_ASST

202: Pat is a MK_REP
201: Michael is a MK_MAN
119: Karen is a PU_CLERK
118: Guy is a PU_CLERK
...

A weakly typed cursor does not return a value. It is a generic type that can be directly defined by using the system-built-in type SYS_REFCURSOR. Therefore, the preceding example can be rewritten as follows:

obclient> DECLARE
TYPE rec_emp_job IS RECORD(
         employee_id emp.empno%TYPE,
             employee_name emp.empname%TYPE,
             job_id emp.job%TYPE
             );
         -- Define the refcur_emp type as SYS_REFCURSOR.
         refcur_emp SYS_REFCURSOR;
         emp_job rec_emp_job;
         BEGIN
     OPEN refcur_emp FOR
         SELECT empno, empname, job
               FROM emp
               ORDER BY deptno;
               FETCH refcur_emp INTO emp_job;
         WHILE refcur_emp%FOUND LOOP
         DBMS_OUTPUT.PUT_LINE(emp_job.employee_id||': '||emp_job.employee_name||' is a '||emp_job.job_id);
             FETCH refcur_emp INTO emp_job;
         END LOOP;
         END;
     /
     Query OK, 0 rows affected
200: Jennifer is a AD_ASST

202: Pat is a MK_REP
201: Michael is a MK_MAN
119: Karen is a PU_CLERK
118: Guy is a PU_CLERK
...

Dynamic SQL is an advanced programming method. It refers to generating and executing SQL statements during the execution of a PL/SQL program. A cursor variable also supports dynamic binding. You can use the OPEN... FOR clause to define a cursor variable by using a string. In the following example, the cursor cv is defined by using the string variable query_2.

obclient> DECLARE

cv SYS_REFCURSOR;     -- Cursor variable
query_2 VARCHAR2(200) :=
       'SELECT * FROM emp
       ORDER BY deptno';
         v_employees emp%ROWTYPE;    -- Record variable of a table row
         BEGIN
       OPEN cv FOR query_2;
     LOOP
       FETCH cv INTO v_etails;
       EXIT WHEN cv%NOTFOUND;
           DBMS_OUTPUT.PUT_LINE(v_employees.empno||'-'||v_employees.empname);
           END LOOP;
           CLOSE cv;
       END;
       /
     Query OK, 0 rows affected
     200-Jennifer
202-Pat

201-Michael
119-Karen
118-Guy
...

For more information about dynamic SQL, see Dynamic SQL.

Use a cursor variable to loop through the records in a recordset

To use a cursor variable to loop through the records in a recordset, perform the following steps:

1. In the declaration section, perform the following steps:

1. Define a dynamic cursor type and execute the following statement:

      TYPE cursor_type IS REF CURSOR [ RETURN return_type ]; 
   

2. Declare a variable of the dynamic cursor type and execute the following statement:

   cursor_variable cursor_type; 
    ```
   
   <li> Declare a record to store the records returned by the cursor and execute the following statement:
   
   ```sql
   record_name return_type; 
    ```
   
   </ol>
   
   2. In the execution section, perform the following steps:
   
   <ol><li> Open the cursor variable bound to a specific query and execute the following statement:
   ```sql
   OPEN cursor_variable FOR query; 
   

3. Retrieve row data from the cursor recordset. Each row can be retrieved using a LOOP statement in the following format: ```sql LOOP FETCH cursor_variable INTO record_name; EXIT WHEN cursor_variable%NOTFOUND; statement; [ statement; ]... END LOOP; ```
4. Close the cursor and execute the following statement:

    CLOSE cursor_name; 
     ```
   
   Or, reopen the cursor variable bound to another query (which automatically closes the current query's cursor) and repeat the previous steps.</ol>
    **Example: Use a cursor variable to loop through the records in a recordset.**
   
     ```sql
   obclient> CREATE OR REPLACE PACKAGE pkg_ware_mgmt AS
    TYPE TYPE_REFCURSOR_WARE    IS REF CURSOR RETURN ware%ROWTYPE;
    PROCEDURE sp_record_print;
   END;
   /
   Query OK, 0 rows affected
   
   obclient> CREATE OR REPLACE PACKAGE BODY pkg_ware_mgmt 
   AS  
      PROCEDURE sp_record_print_by_record(p_cursor IN TYPE_REFCURSOR_WARE)
      AS
          rec_ware    ware%ROWTYPE;
      BEGIN
          LOOP
              dbms_output.put_line('Try to fetch a row from the ref cursor .');
              FETCH p_cursor INTO rec_ware;
              EXIT WHEN p_cursor%NOTFOUND ;
        
              dbms_output.put_line('Print a record :');
              dbms_output.put_line('W_ID : ' || rec_ware.w_id 
                  || ', W_YTD : ' || rec_ware.w_ytd 
                  || ', W_TAX : ' || rec_ware.w_tax 
                  || ', W_NAME : ' || rec_ware.w_name
                  || ', W_STREET_1 : ' || rec_ware.w_street_1
                  || ', W_STREET_2 : ' || rec_ware.w_street_2
                  || ', W_CITY : ' || rec_ware.w_city
                  || ', W_STATE : ' || rec_ware.w_state
                  || ', W_ZIP : ' || rec_ware.w_zip )
              ;
              dbms_output.put_line('');
          END LOOP;
          dbms_output.put_line('Processed ' || p_cursor%ROWCOUNT || ' rows. ');
      END;
   
      PROCEDURE sp_record_print
      IS
          cursor_ware     TYPE_REFCURSOR_WARE ;
      BEGIN
   
          OPEN cursor_ware FOR SELECT * FROM ware ORDER BY w_id ;
          dbms_output.put_line('Open a ref cursor using query at ware.');
    
          sp_record_print_by_record(cursor_ware);
    
          CLOSE cursor_ware;
    
      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();
   Query OK, 0 rows affected
   
   Open a ref cursor using query at ware.
   Try to fetch a row from the ref cursor .
   Processed 0 rows.