Like a cursor, a cursor variable is also a pointer to the current data row in a query result set with multiple rows.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.
The difference is that a cursor variable is dynamic whereas a cursor is static. A cursor can connect only to a specified query. In other words, a cursor fixedly points to the memory processing area of a query. However, a cursor variable can connect to different query statements and point to the memory processing areas of different query statements, provided that the data types of the return values of these query statements are compatible. Take note that a cursor variable cannot point to multiple memory processing areas at the same time. In other words, it can connect to only one query statement at a point in time.
Declare a cursor variable
A cursor variable is a pointer of the REF CURSOR type. Therefore, you must first define a cursor variable type before you declare it. PL allows you to define a cursor variable type in the declarative part of a block, subprogram, or package.
Syntax:
TYPE ref_type_name IS REF CURSOR [ RETURN return_type];
ref_type_name specifies the name of the newly defined cursor variable type. return_type specifies the type of the return value of the cursor variable, and the return value must be a record variable.
When you define a cursor variable type, you can use a strongly-typed definition or a weakly-typed definition. The type of the return value of the cursor variable must be specified for a strongly-typed definition, and does not need to be specified for a weakly-typed definition.
The following example describes a cursor variable with a strongly-typed definition:
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
...
The following example describes a cursor variable with a weakly-typed definition:
obclient> DECLARE
TYPE rec_emp_job IS RECORD(
employee_id emp.empno%TYPE,
employee_name emp.empname%TYPE,
job_id emp.job%TYPE
);
-- No type is defined for the return value.
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 cursor variable with a weakly-typed definition has no return value and is of a universal type. Such a cursor variable can be directly defined by using the 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
);
-- The type of refcur_emp is defined 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
...
Notice
- For a cursor variable with a weakly-typed definition, the PL engine is unaware of the variable type during compilation. Therefore, a variable type must be bound during execution. This means that the program will consume more resources and time during execution.
- Cursor variables of the
SYS_REFCURSORtype can be reused.
Define a cursor variable by using a dynamic SQL statement
Dynamic SQL is an advanced programming method which allows statements to be generated and executed during the execution of a PL program. Cursor variables can be dynamically bound. You can use the OPEN... FOR clause to define a cursor variable by using a string. The following example defines a cursor variable named cv by using the query_2 string:
obclient> DECLARE
cv SYS_REFCURSOR; -- The cursor variable.
query_2 VARCHAR2(200) :=
'SELECT * FROM emp
ORDER BY deptno';
v_employees emp%ROWTYPE; -- The record variable of table rows.
BEGIN
OPEN cv FOR query_2;
LOOP
FETCH cv INTO v_employees;
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 fetch all rows from a record set in a loop manner
Procedure:
Perform the following steps in the declarative part:
- Define a dynamic cursor type.
TYPE cursor_type IS REF CURSOR [ RETURN return_type ]; - Declare a variable of the defined dynamic cursor type.
cursor_variable cursor_type; - Declare a record to store the value returned by the cursor.
record_name return_type;
- Define a dynamic cursor type.
Perform the following steps in the executable part:
- Open the cursor variable and bind it to a specific query.
OPEN cursor_variable FOR query; - Fetch row data from the record set of the cursor. One row is fetched each time. You can use the following LOOP statement to fetch multiple rows:
LOOP FETCH cursor_variable INTO record_name; EXIT WHEN cursor_variable%NOTFOUND; statement; [ statement; ]... END LOOP; - Close the cursor.
CLOSE cursor_name;Alternatively, you can reopen the cursor variable, bind it to another query (the cursor of the current query is automatically closed), and repeat the preceding steps.
- Open the cursor variable and bind it to a specific query.
Example: Use a cursor variable to fetch all rows from a record set in a loop manner
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 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();
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.