To create cursor variables, you take two steps. First, you define a REF CURSOR type, then declare cursor variables of that type.
TYPE ref_type_name IS REF CURSOR RETURN { cursor_name%ROWTYPE | cursor_variable_name%ROWTYPE | record_name%TYPE | record_type_name | table_name%ROWTYPE};
cursor_variable_declaration ::=
cursor_variable_name ref_type_name;
The Oracle Server also has a PL/SQL engine. So, you can pass cursor variables back and forth between an application and server via remote procedure calls (RPCs). And, 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.
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.
REF CURSOR types can be strong (restrictive) or weak (nonrestrictive). A strong REF CURSOR type definition specifies a return type, but a weak definition does not. 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.
Once you define a REF CURSOR type, you can declare cursor variables of that type. They 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.
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 one at a time. When all the rows are processed, you CLOSE the cursor variable.
Other OPEN-FOR statements can open the same cursor variable for different queries. You need not close a cursor variable before reopening it. When you reopen a cursor variable for a different query, the previous query is lost.
PL/SQL makes sure the return type of the cursor variable is compatible with the INTO clause of the FETCH statement. For each column value returned by the query associated with the cursor variable, there must be a corresponding, type-compatible field or variable in the INTO clause. Also, the number of fields or variables must equal the number of column values. Otherwise, you get an error.
If both cursor variables involved in an assignment are strongly typed, they must have the same datatype. However, if one or both cursor variables are weakly typed, they need not have the same datatype.
When declaring a cursor variable as the formal parameter of a subprogram that fetches from or closes the cursor variable, you must specify the IN (or IN OUT) mode. If the subprogram opens the cursor variable, you must specify the IN OUT mode.
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.
You can apply the cursor attributes %FOUND, %NOTFOUND, %ISOPEN, and %ROWCOUNT to a cursor variable. For more information, see "Using Cursor Attributes" .
If you try to fetch from, close, or apply cursor attributes to a cursor variable that does not point to a query work area, PL/SQL raises the predefined exception INVALID_CURSOR. You can make a cursor variable (or parameter) point to a query work area in two ways:
Currently, cursor variables are subject to the following restrictions, some of which future releases of PL/SQL will remove:
EXEC SQL BEGIN DECLARE SECTION; ... /* Declare host cursor variable. */ SQL_CURSOR generic_cv; int choice; EXEC SQL END DECLARE SECTION; ... /* Initialize host cursor variable. */ EXEC SQL ALLOCATE :generic_cv; ...
/* Pass host cursor variable and selector to PL/SQL block. */ EXEC SQL EXECUTE 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-EXEC;
Host cursor variables are compatible with any query return type. They behave just like weakly typed PL/SQL cursor variables.
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 three cursor variables in a single round-trip:
/* anonymous PL/SQL block in host environment */ BEGIN OPEN :emp_cv FOR SELECT * FROM emp; OPEN :dept_cv FOR SELECT * FROM dept; OPEN :grade_cv FOR SELECT * FROM salgrade; END;
You can also pass a cursor variable to PL/SQL by calling a stored procedure that declares a cursor variable as one of its formal parameters. To centralize data retrieval, you can group type-compatible queries in a packaged procedure, as the following example shows:
CREATE PACKAGE emp_data AS TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp, choice IN NUMBER); END emp_data; CREATE PACKAGE BODY emp_data AS PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp, choice IN NUMBER) 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 open_emp_cv; END emp_data;
Alternatively, you can use a standalone procedure to open the cursor variable. Simply define the REF CURSOR type in a separate package, then reference that type in the standalone procedure. For instance, if you create the following (bodiless) package, you can create standalone procedures that reference the types it defines:
CREATE PACKAGE cv_types AS TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE; TYPE BonusCurTyp IS REF CURSOR RETURN bonus%ROWTYPE; ... END cv_types;