What Are Cursor Variables?
Cursor variables are like C or Pascal pointers, which hold the memory location (address) of some item instead of the item itself. So, declaring a cursor variable creates a pointer, not an item. In PL/SQL, a pointer has datatype REF X, where REF is short for REFERENCE and X stands for a class of objects. Therefore, a cursor variable has datatype REF CURSOR. To execute a multi-row query, Oracle opens an unnamed work area that stores processing information. To access the information, you can use an explicit cursor, which names the work area. Or, you can use a cursor variable, which points to the work area. Whereas a cursor always refers to the same query work area, a cursor variable can refer to different work areas. So, cursors and cursor variables are not interoperable; that is, you cannot use one where the other is expected.
Why Use Cursor Variables?
Mainly, you use cursor variables to pass query result sets between PL/SQL stored subprograms and various clients. Neither PL/SQL nor any of its clients owns a result set; they simply 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 server can all refer to the same work area. A query work area remains accessible as long as any cursor variable points to it. Therefore, you can pass the value of a cursor variable freely 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. Also, you can reduce network traffic by having a PL/SQL block open (or close) several host cursor variables in a single round trip.
Defining REF CURSOR Types
To create cursor variables, you take two steps. First, you define a REF CURSOR type, then declare cursor variables of that type. You can define REF CURSOR types in any PL/SQL block, subprogram, or package using the syntax TYPE ref_type_name IS REF CURSOR [RETURN return_type]; where ref_type_name is a type specifier used in subsequent declarations of cursor variables and return_type must represent a record or a row in a database table. In the following example, you specify a return type that represents a row in the database table dept:
DECLARE
TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;
REF CURSOR types can be strong (restrictive) or weak (nonrestrictive). As the next example shows, a strong REF CURSOR type definition specifies a return type, but a weak definition does not:
DECLARE
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; -- strong
TYPE GenericCurTyp IS REF CURSOR; -- weak
Strong REF CURSOR types are less error prone because the PL/SQL compiler lets you associate a strongly typed cursor variable only with type-compatible queries. However, weak REF CURSOR types are more flexible because the compiler lets you associate a weakly typed cursor variable with any query.
Declaring Cursor Variables
Once you define a REF CURSOR type, you can declare cursor variables of that type in any PL/SQL block or subprogram. In the following example, you declare the cursor variable dept_cv:
DECLARE
TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;
dept_cv DeptCurTyp; -- declare cursor variable
Note: You cannot declare cursor variables in a package. Unlike packaged variables, cursor variables do not have persistent state. Remember, declaring a cursor variable creates a pointer, not an item. So, cursor variables cannot be saved in the database. Cursor variables follow the usual scoping and instantiation rules. Local PL/SQL cursor variables are instantiated when you enter a block or subprogram and cease to exist when you exit. In the RETURN clause of a REF CURSOR type definition, you can use %ROWTYPE to specify a record type that represents a row returned by a strongly (not weakly) typed cursor variable, as follows:
DECLARE
TYPE TmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
tmp_cv TmpCurTyp; -- declare cursor variable
TYPE EmpCurTyp IS REF CURSOR RETURN tmp_cv%ROWTYPE;
emp_cv EmpCurTyp; -- declare cursor variable
Likewise, you can use %TYPE to provide the datatype of a record variable, as the
following example shows:
DECLARE
dept_rec dept%ROWTYPE; -- declare record variable
TYPE DeptCurTyp IS REF CURSOR RETURN dept_rec%TYPE;
dept_cv DeptCurTyp; -- declare cursor variable
In the final example, you specify a user-defined RECORD type in the RETURN clause:
DECLARE
TYPE EmpRecTyp IS RECORD (
empno NUMBER(4),
ename VARCHAR2(1O),
sal NUMBER(7,2));
TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
emp_cv EmpCurTyp; -- declare cursor variable
Cursor Variables As Parameters
You can declare cursor variables as the formal parameters of functions and procedures. In the following example, you define the REF CURSOR type EmpCurTyp, then declare a cursor variable of that type as the formal parameter of a procedure:
DECLARE
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS ...
Controlling Cursor Variables
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. Here is the syntax:
OPEN {cursor_variable :host_cursor_variable} FOR
{ select_statement
dynamic_string [USING bind_argument[, bind_argument]...] };
where host_cursor_variable is a cursor variable declared in a PL/SQL host environment such as an OCI program, and dynamic_string is a string expression that represents a multi-row query.
Note: This section discusses the static SQL case, in which select_statement is used. For the dynamic SQL case, in which dynamic_string is used, see "Opening the Cursor Variable" on page 10-7. Unlike cursors, cursor variables take no parameters. However, no flexibility is lost because 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. In the example below, you open the cursor variable emp_cv. Notice that you can
apply cursor attributes (%FOUND, %NOTFOUND, %ISOPEN, and %ROWCOUNT) to a cursor variable.
IF NOT emp_cv%ISOPEN THEN
/* Open cursor variable. */
OPEN emp_cv FOR SELECT * FROM emp;
END IF;
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. Typically, you open a cursor variable by passing it to a stored procedure that declares a cursor variable as one of its formal parameters. For example, the following packaged procedure opens the cursor variable emp_cv:
CREATE PACKAGE emp_data AS
...
TYPE EmpCurTyp IS REF CURSOR RETURN emp%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 emp;
END open_emp_cv;
END emp_data;
When you declare a cursor variable as the formal parameter of a subprogram that opens the cursor variable, you must specify the IN OUT mode. That way, the subprogram can pass an open cursor back to the caller. Alternatively, you can use a stand-alone procedure to open the cursor variable. Simply define the REF CURSOR type in a separate package, then reference that type in the stand-alone procedure. For instance, if you create the following bodiless package, you can create stand-alone procedures that reference the types it defines:
CREATE PACKAGE cv_types AS
TYPE GenericCurTyp IS REF CURSOR;
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;
END cv_types;
In the next example, you create a stand-alone procedure that references the REF CURSOR type EmpCurTyp, which is defined in the package cv_types:
CREATE PROCEDURE open_emp_cv (emp_cv IN OUT cv_types.EmpCurTyp) AS
BEGIN OPEN emp_cv FOR SELECT * FROM emp;
END open_emp_cv;
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. (In this context, a selector is a variable used to select one of several alternatives in a conditional control statement.) 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;
For more flexibility, you can pass a cursor variable and a selector to a stored procedure that executes queries with different return types. Here is an example:
CREATE PACKAGE admin_data AS
TYPE GenCurTyp IS REF CURSOR;
PROCEDURE open_cv (generic_cv IN OUT GenCurTyp, choice INT);
END admin_data;
CREATE 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 emp;
ELSIF choice = 2 THEN
OPEN generic_cv FOR SELECT * FROM dept;
ELSIF choice = 3 THEN
OPEN generic_cv FOR SELECT * FROM salgrade;
END IF;
END;
END admin_data;
Cursor variables are like C or Pascal pointers, which hold the memory location (address) of some item instead of the item itself. So, declaring a cursor variable creates a pointer, not an item. In PL/SQL, a pointer has datatype REF X, where REF is short for REFERENCE and X stands for a class of objects. Therefore, a cursor variable has datatype REF CURSOR. To execute a multi-row query, Oracle opens an unnamed work area that stores processing information. To access the information, you can use an explicit cursor, which names the work area. Or, you can use a cursor variable, which points to the work area. Whereas a cursor always refers to the same query work area, a cursor variable can refer to different work areas. So, cursors and cursor variables are not interoperable; that is, you cannot use one where the other is expected.Why Use Cursor Variables?
Mainly, you use cursor variables to pass query result sets between PL/SQL stored subprograms and various clients. Neither PL/SQL nor any of its clients owns a result set; they simply 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 server can all refer to the same work area. A query work area remains accessible as long as any cursor variable points to it. Therefore, you can pass the value of a cursor variable freely 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. Also, you can reduce network traffic by having a PL/SQL block open (or close) several host cursor variables in a single round trip.
Defining REF CURSOR Types
To create cursor variables, you take two steps. First, you define a REF CURSOR type, then declare cursor variables of that type. You can define REF CURSOR types in any PL/SQL block, subprogram, or package using the syntax TYPE ref_type_name IS REF CURSOR [RETURN return_type]; where ref_type_name is a type specifier used in subsequent declarations of cursor variables and return_type must represent a record or a row in a database table. In the following example, you specify a return type that represents a row in the database table dept:
DECLARE
TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;
REF CURSOR types can be strong (restrictive) or weak (nonrestrictive). As the next example shows, a strong REF CURSOR type definition specifies a return type, but a weak definition does not:
DECLARE
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; -- strong
TYPE GenericCurTyp IS REF CURSOR; -- weak
Strong REF CURSOR types are less error prone because the PL/SQL compiler lets you associate a strongly typed cursor variable only with type-compatible queries. However, weak REF CURSOR types are more flexible because the compiler lets you associate a weakly typed cursor variable with any query.
Declaring Cursor Variables
Once you define a REF CURSOR type, you can declare cursor variables of that type in any PL/SQL block or subprogram. In the following example, you declare the cursor variable dept_cv:
DECLARE
TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;
dept_cv DeptCurTyp; -- declare cursor variable
Note: You cannot declare cursor variables in a package. Unlike packaged variables, cursor variables do not have persistent state. Remember, declaring a cursor variable creates a pointer, not an item. So, cursor variables cannot be saved in the database. Cursor variables follow the usual scoping and instantiation rules. Local PL/SQL cursor variables are instantiated when you enter a block or subprogram and cease to exist when you exit. In the RETURN clause of a REF CURSOR type definition, you can use %ROWTYPE to specify a record type that represents a row returned by a strongly (not weakly) typed cursor variable, as follows:
DECLARE
TYPE TmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
tmp_cv TmpCurTyp; -- declare cursor variable
TYPE EmpCurTyp IS REF CURSOR RETURN tmp_cv%ROWTYPE;
emp_cv EmpCurTyp; -- declare cursor variable
Likewise, you can use %TYPE to provide the datatype of a record variable, as the
following example shows:
DECLARE
dept_rec dept%ROWTYPE; -- declare record variable
TYPE DeptCurTyp IS REF CURSOR RETURN dept_rec%TYPE;
dept_cv DeptCurTyp; -- declare cursor variable
In the final example, you specify a user-defined RECORD type in the RETURN clause:
DECLARE
TYPE EmpRecTyp IS RECORD (
empno NUMBER(4),
ename VARCHAR2(1O),
sal NUMBER(7,2));
TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
emp_cv EmpCurTyp; -- declare cursor variable
Cursor Variables As Parameters
You can declare cursor variables as the formal parameters of functions and procedures. In the following example, you define the REF CURSOR type EmpCurTyp, then declare a cursor variable of that type as the formal parameter of a procedure:
DECLARE
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS ...
Controlling Cursor Variables
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. Here is the syntax:
OPEN {cursor_variable :host_cursor_variable} FOR
{ select_statement
dynamic_string [USING bind_argument[, bind_argument]...] };
where host_cursor_variable is a cursor variable declared in a PL/SQL host environment such as an OCI program, and dynamic_string is a string expression that represents a multi-row query.
Note: This section discusses the static SQL case, in which select_statement is used. For the dynamic SQL case, in which dynamic_string is used, see "Opening the Cursor Variable" on page 10-7. Unlike cursors, cursor variables take no parameters. However, no flexibility is lost because 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. In the example below, you open the cursor variable emp_cv. Notice that you can
apply cursor attributes (%FOUND, %NOTFOUND, %ISOPEN, and %ROWCOUNT) to a cursor variable.
IF NOT emp_cv%ISOPEN THEN
/* Open cursor variable. */
OPEN emp_cv FOR SELECT * FROM emp;
END IF;
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. Typically, you open a cursor variable by passing it to a stored procedure that declares a cursor variable as one of its formal parameters. For example, the following packaged procedure opens the cursor variable emp_cv:
CREATE PACKAGE emp_data AS
...
TYPE EmpCurTyp IS REF CURSOR RETURN emp%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 emp;
END open_emp_cv;
END emp_data;
When you declare a cursor variable as the formal parameter of a subprogram that opens the cursor variable, you must specify the IN OUT mode. That way, the subprogram can pass an open cursor back to the caller. Alternatively, you can use a stand-alone procedure to open the cursor variable. Simply define the REF CURSOR type in a separate package, then reference that type in the stand-alone procedure. For instance, if you create the following bodiless package, you can create stand-alone procedures that reference the types it defines:
CREATE PACKAGE cv_types AS
TYPE GenericCurTyp IS REF CURSOR;
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;
END cv_types;
In the next example, you create a stand-alone procedure that references the REF CURSOR type EmpCurTyp, which is defined in the package cv_types:
CREATE PROCEDURE open_emp_cv (emp_cv IN OUT cv_types.EmpCurTyp) AS
BEGIN OPEN emp_cv FOR SELECT * FROM emp;
END open_emp_cv;
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. (In this context, a selector is a variable used to select one of several alternatives in a conditional control statement.) 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;
For more flexibility, you can pass a cursor variable and a selector to a stored procedure that executes queries with different return types. Here is an example:
CREATE PACKAGE admin_data AS
TYPE GenCurTyp IS REF CURSOR;
PROCEDURE open_cv (generic_cv IN OUT GenCurTyp, choice INT);
END admin_data;
CREATE 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 emp;
ELSIF choice = 2 THEN
OPEN generic_cv FOR SELECT * FROM dept;
ELSIF choice = 3 THEN
OPEN generic_cv FOR SELECT * FROM salgrade;
END IF;
END;
END admin_data;
Post a Comment