Like a cursor, a cursor variable points to the current row in the result set of a multi-row query. A cursor variable is more flexible because it is not tied to a specific query. You can open a cursor variable for any query that returns the right set of columns.
You pass a cursor variable as a parameter to local and stored subprograms. Opening the cursor variable in one subprogram, and processing it in a different subprogram, helps to centralize data retrieval. This technique is also useful for multi-language applications, where a PL/SQL subprogram might return a result set to a subprogram written in a different language.
Cursor variables are available to every PL/SQL client. For example, you can declare a cursor variable in a PL/SQL host environment such as an OCI or Pro*C program, then pass it as an input host variable (bind variable) to PL/SQL. Application development tools such as Oracle Forms and Oracle Reports, which have a PL/SQL engine, can use cursor variables entirely on the client side. Or, you can pass cursor variables back and forth between a client and the database server through remote procedure calls.
What Are Cursor Variables (REF CURSORs)?
Cursor variables are like pointers to result sets. You use them when you want to perform a query in one subprogram, and process the results in a different subprogram (possibly one written in a different language). A cursor variable has datatype REF CURSOR, and you might see them referred to informally as REF CURSORs.
Unlike an explicit cursor, which always refers to the same query work area, a cursor variable can refer to different work areas. You cannot use a cursor variable where a cursor is expected, or vice versa.
Why Use Cursor Variables?
You use cursor variables to pass query result sets between PL/SQL stored subprograms and various clients. PL/SQL and its clients share a pointer to the query work area in which the result set is stored. For example, an OCI client, Oracle Forms application, and Oracle database server can all refer to the same work area.
A query work area remains accessible as long as any cursor variable points to it, as you pass the value of a cursor variable from one scope to another. For example, if you pass a host cursor variable to a PL/SQL block embedded in a Pro*C program, the work area to which the cursor variable points remains accessible after the block completes.
If you have a PL/SQL engine on the client side, calls from client to server impose no restrictions. For example, you can declare a cursor variable on the client side, open and fetch from it on the server side, then continue to fetch from it back on the client side. You can also reduce network traffic by having a PL/SQL block open or close several
host cursor variables in a single round trip.
Declaring REF CURSOR Types and Cursor Variables
To create cursor variables, you define a REF CURSOR type, then declare cursor variables of that type. You can define REF CURSOR types in any PL/SQL block, ubprogram, or package. In the following example, you declare a REF CURSOR type that represents a result set from the DEPARTMENTS table:
DECLARE
TYPE DeptCurTyp IS REF CURSOR RETURN departments%ROWTYPE;
REF CURSOR types can be strong (with a return type) or weak (with no return type).
Strong REF CURSOR types are less error prone because the PL/SQL compiler lets you associate a strongly typed cursor variable only with queries that return the right set of
columns. Weak REF CURSOR types are more flexible because the compiler lets you associate a weakly typed cursor variable with any query.
Because there is no type checking with a weak REF CURSOR, all such types are interchangeable. Instead of creating a new type, you can use the predefined type SYS_REFCURSOR.
Once you define a REF CURSOR type, you can declare cursor variables of that type in any PL/SQL block or subprogram.
DECLARE
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; -- strong
TYPE GenericCurTyp IS REF CURSOR; -- weak
cursor1 EmpCurTyp;
cursor2 GenericCurTyp;
my_cursor SYS_REFCURSOR; -- didn't need to declare a new type above
The following example declares the cursor variable dept_cv:
DECLARE
TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;
dept_cv DeptCurTyp; -- declare cursor variable
To avoid declaring the same REF CURSOR type in each subprogram that uses it, you can put the REF CURSOR declaration in a package spec. You can declare cursor variables of that type in the corresponding package body, or within your own procedure or function.
Example 1 Cursor Variable Returning %ROWTYPE
In the RETURN clause of a REF CURSOR type definition, you can use %ROWTYPE to refer
to a strongly typed cursor variable:
DECLARE
TYPE TmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE;
tmp_cv TmpCurTyp; -- declare cursor variable
TYPE EmpCurTyp IS REF CURSOR RETURN tmp_cv%ROWTYPE;
emp_cv EmpCurTyp; -- declare cursor variable
BEGIN
NULL;
END;
/
Example 2 Cursor Variable Returning %TYPE
You can also use %TYPE to provide the datatype of a record variable:
DECLARE
dept_rec departments%ROWTYPE; -- declare record variable
TYPE DeptCurTyp IS REF CURSOR RETURN dept_rec%TYPE;
dept_cv DeptCurTyp; -- declare cursor variable
BEGIN
NULL;
END;
/
Example 3 Cursor Variable Returning Record Type
This example specifies a user-defined RECORD type in the RETURN clause:
DECLARE
TYPE EmpRecTyp IS RECORD (
employee_id NUMBER,
last_name VARCHAR2(30),
salary NUMBER(7,2));
TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
emp_cv EmpCurTyp; -- declare cursor variable
BEGIN
NULL;
END;
/
Passing Cursor Variables As Parameters
You can declare cursor variables as the formal parameters of functions and procedures.
The following example defines a REF CURSOR type, then declares a cursor variable of that type as a formal parameter:
DECLARE
TYPE EmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE;
emp EmpCurTyp;
-- Once we have a result set, we can process all the rows
-- inside a single procedure rather than calling a procedure
-- for each row.
PROCEDURE process_emp_cv (emp_cv IN EmpCurTyp) IS
person employees%ROWTYPE;
BEGIN
dbms_output.put_line('-----');
dbms_output.put_line('Here are the names from the result set:');
LOOP
FETCH emp_cv INTO person;
EXIT WHEN emp_cv%NOTFOUND;
dbms_output.put_line('Name = ' || person.first_name ||
' ' || person.last_name);
END LOOP;
END;
/
BEGIN
-- First find 10 arbitrary employees.
OPEN emp FOR SELECT * FROM employees WHERE ROWNUM < 11;
process_emp_cv(emp);
CLOSE emp;
-- Then find employees matching a condition.
OPEN emp FOR SELECT * FROM employees WHERE last_name LIKE 'R%';
process_emp_cv(emp);
CLOSE emp;
END;
/
Controlling Cursor Variables: OPEN-FOR, FETCH, and CLOSE
You use three statements to control a cursor variable: OPEN-FOR, FETCH, and CLOSE.
First, you OPEN a cursor variable FOR a multi-row query. Then, you FETCH rows from
the result set. When all the rows are processed, you CLOSE the cursor variable.
Opening a Cursor Variable
The OPEN-FOR statement associates a cursor variable with a multi-row query, executes
the query, and identifies the result set.
OPEN {cursor_variable | :host_cursor_variable} FOR
{ select_statement
| dynamic_string [USING bind_argument[, bind_argument]...] };
The cursor variable can be declared directly in PL/SQL, or in a PL/SQL host environment such as an OCI program.
The SELECT statement for the query can be coded directly in the statement, or can be a string variable or string literal. When you use a string as the query, it can include placeholders for bind variables, and you specify the corresponding values with a USING clause.
Note: This section discusses the static SQL case, in which select_statement is
used.
Unlike cursors, cursor variables take no parameters. Instead, you can pass whole queries (not just parameters) to a cursor variable. The query can reference host variables and PL/SQL variables, parameters, and functions.
The example below opens a cursor variable. Notice that you can apply cursor attributes (%FOUND, %NOTFOUND, %ISOPEN, and %ROWCOUNT) to a cursor variable.
DECLARE
TYPE EmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE;
emp_cv EmpCurTyp;
BEGIN
IF NOT emp_cv%ISOPEN THEN
/* Open cursor variable. */
OPEN emp_cv FOR SELECT * FROM employees;
END IF;
CLOSE emp_cv;
END;
/
Other OPEN-FOR statements can open the same cursor variable for different queries. You need not close a cursor variable before reopening it. (Recall that consecutive OPENs of a static cursor raise the predefined exception CURSOR_ALREADY_OPEN.)
When you reopen a cursor variable for a different query, the previous query is lost.
Example 4 Stored Procedure to Open a Ref Cursor
Typically, you open a cursor variable by passing it to a stored procedure that declares an IN OUT parameter that is a cursor variable. For example, the following procedure opens a cursor variable:
CREATE PACKAGE emp_data AS
TYPE EmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE;
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp);
END emp_data;
/
CREATE PACKAGE BODY emp_data AS
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS
BEGIN
OPEN emp_cv FOR SELECT * FROM employees;
END open_emp_cv;
END emp_data;
/
DROP PACKAGE emp_data;
You can also use a standalone stored procedure to open the cursor variable. Define the REF CURSOR type in a package, then reference that type in the parameter declaration for the stored procedure.
Example 4 Stored Procedure to Open Ref Cursors with Different Queries
To centralize data retrieval, you can group type-compatible queries in a stored procedure. In the example below, the packaged procedure declares a selector as one of its formal parameters. When called, the procedure opens the cursor variable emp_cv for the chosen query.
CREATE PACKAGE emp_data AS
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp, choice INT);
END emp_data;
CREATE PACKAGE BODY emp_data AS
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp, choice INT) IS
BEGIN
IF choice = 1 THEN
OPEN emp_cv FOR SELECT * FROM emp WHERE comm IS NOT NULL;
ELSIF choice = 2 THEN
OPEN emp_cv FOR SELECT * FROM emp WHERE sal > 2500;
ELSIF choice = 3 THEN
OPEN emp_cv FOR SELECT * FROM emp WHERE deptno = 20;
END IF;
END;
END emp_data;
Example 5 Cursor Variable with Different Return Types
For more flexibility, a stored procedure can execute queries with different return types:
CREATE PACKAGE admin_data AS
TYPE GenCurTyp IS REF CURSOR;
PROCEDURE open_cv (generic_cv IN OUT GenCurTyp, choice INT);
END admin_data;
/
CREATE OR REPLACE PACKAGE BODY admin_data AS
PROCEDURE open_cv (generic_cv IN OUT GenCurTyp, choice INT) IS
BEGIN
IF choice = 1 THEN
OPEN generic_cv FOR SELECT * FROM employees;
ELSIF choice = 2 THEN
OPEN generic_cv FOR SELECT * FROM departments;
ELSIF choice = 3 THEN
OPEN generic_cv FOR SELECT * FROM countries;
END IF;
END;
END admin_data;
/
Fetching from a Cursor Variable
The FETCH statement retrieves rows from the result set of a multi-row query. It works the same with cursor variables as with explicit cursors.
Example 6 Fetching from a Cursor Variable into a Record
The following example fetches rows one at a time from a cursor variable into a record:
DECLARE
TYPE EmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE;
emp_cv EmpCurTyp;
emp_rec employees%ROWTYPE;
BEGIN
OPEN emp_cv FOR SELECT * FROM employees WHERE salary < 3000;
LOOP
/* Fetch from cursor variable. */
FETCH emp_cv INTO emp_rec;
EXIT WHEN emp_cv%NOTFOUND; -- exit when last row is fetched
-- process data record
dbms_output.put_line('Name = ' || emp_rec.first_name || ' ' ||
emp_rec.last_name);
END LOOP;
CLOSE emp_cv;
END;
/
Example 7 Fetching from a Cursor Variable into Collections
Using the BULK COLLECT clause, you can bulk fetch rows from a cursor variable into
one or more collections:
DECLARE
TYPE EmpCurTyp IS REF CURSOR;
TYPE NameList IS TABLE OF employees.last_name%TYPE;
TYPE SalList IS TABLE OF employees.salary%TYPE;
emp_cv EmpCurTyp;
names NameList;
sals SalList;
BEGIN
OPEN emp_cv FOR SELECT last_name, salary FROM employees WHERE salary < 3000;
FETCH emp_cv BULK COLLECT INTO names, sals;
CLOSE emp_cv;
-- Now loop through the NAMES and SALS collections.
FOR i IN names.FIRST .. names.LAST
LOOP
dbms_output.put_line('Name = ' || names(i) || ', salary = ' ||
sals(i));
END LOOP;
END;
/
Any variables in the associated query are evaluated only when the cursor variable is opened. To change the result set or the values of variables in the query, reopen the cursor variable with the variables set to new values. You can use a different INTO clause on separate fetches with the same cursor variable. Each fetch retrieves another row from the same result set.
PL/SQL makes sure the return type of the cursor variable is compatible with the INTO clause of the FETCH statement. If there is a mismatch, an error occurs at compile time if the cursor variable is strongly typed, or at run time if it is weakly typed. At run time, PL/SQL raises the predefined exception ROWTYPE_MISMATCH before the first fetch. If you trap the error and execute the FETCH statement using a different (compatible)
INTO clause, no rows are lost.
When you declare a cursor variable as the formal parameter of a subprogram that fetches from the cursor variable, you must specify the IN or IN OUT mode. If the subprogram also opens the cursor variable, you must specify the IN OUT mode. If you try to fetch from a closed or never-opened cursor variable, PL/SQL raises the predefined exception INVALID_CURSOR.
Closing a Cursor Variable
The CLOSE statement disables a cursor variable and makes the associated result set undefined. Close the cursor variable after the last row is processed.
When declaring a cursor variable as the formal parameter of a subprogram that closes the cursor variable, you must specify the IN or IN OUT mode.
If you try to close an already-closed or never-opened cursor variable, PL/SQL raises the predefined exception INVALID_CURSOR.
Reducing Network Traffic When Passing Host Cursor Variables to PL/SQL
When passing host cursor variables to PL/SQL, you can reduce network traffic by grouping OPEN-FOR statements. For example, the following PL/SQL block opens multiple cursor variables in a single round trip:
/* anonymous PL/SQL block in host environment */
BEGIN
OPEN :emp_cv FOR SELECT * FROM employees;
OPEN :dept_cv FOR SELECT * FROM departments;
OPEN :loc_cv FOR SELECT * FROM locations;
END;
This technique might be useful in Oracle Forms, for instance, when you want to populate a multi-block form.
When you pass host cursor variables to a PL/SQL block for opening, the query work areas to which they point remain accessible after the block completes, so your OCI or Pro*C program can use these work areas for ordinary cursor operations. In the following example, you open several such work areas in a single round trip:
BEGIN
OPEN :c1 FOR SELECT 1 FROM dual;
OPEN :c2 FOR SELECT 1 FROM dual;
OPEN :c3 FOR SELECT 1 FROM dual;
END;
/
The cursors assigned to c1, c2, and c3 behave normally, and you can use them for any purpose. When finished, release the cursors as follows:
BEGIN
CLOSE :c1;
CLOSE :c2;
CLOSE :c3;
END;
Avoiding Errors with Cursor Variables
If both cursor variables involved in an assignment are strongly typed, they must have exactly the same datatype (not just the same return type). If one or both cursor variables are weakly typed, they can have different datatypes.
If you try to fetch from, close, or refer to cursor attributes of a cursor variable that does not point to a query work area, PL/SQL raises the INVALID_CURSOR exception. You can make a cursor variable (or parameter) point to a query work area in two ways:
■ OPEN the cursor variable FOR the query.
■ Assign to the cursor variable the value of an already OPENed host cursor variable or PL/SQL cursor variable.
If you assign an unopened cursor variable to another cursor variable, the second one remains invalid even after you open the first one.
Be careful when passing cursor variables as parameters. At run time, PL/SQL raises ROWTYPE_MISMATCH if the return types of the actual and formal parameters are
incompatible.
Restrictions on Cursor Variables
Currently, cursor variables are subject to the following restrictions:
■ You cannot declare cursor variables in a package spec. For example, the following declaration is not allowed:
CREATE PACKAGE emp_stuff AS
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
emp_cv EmpCurTyp; -- not allowed
END emp_stuff;
■ You cannot pass cursor variables to a procedure that is called through a database link.
■ If you pass a host cursor variable to PL/SQL, you cannot fetch from it on the server side unless you also open it there on the same server call.
■ You cannot use comparison operators to test cursor variables for equality, inequality, or nullity.
■ You cannot assign nulls to a cursor variable.
■ Database columns cannot store the values of cursor variables. There is no equivalent type to use in a CREATE TABLE statement.
■ You cannot store cursor variables in an associative array, nested table, or varray.
■ Cursors and cursor variables are not interoperable; that is, you cannot use one where the other is expected. For example, you cannot reference a cursor variable in a cursor FOR loop.
0 comments:
Post a Comment