Opening a cursor variable v17
Once you declare a cursor variable, you must open it with an associated SELECT
command. The OPEN FOR
statement specifies the SELECT
command to use to create the result set:
OPEN <name> FOR query;
Where:
name
is the identifier of a previously declared cursor variable.
query
is a SELECT
command that determines the result set when the statement is executed.
The value of the cursor variable after the OPEN FOR
statement is executed identifies the result set.
This example shows a result set that's a list of employee numbers and names from a selected department. You can use a variable or parameter in the SELECT
command anywhere an expression can normally appear. In this case, a parameter is used in the equality test for department number.
CREATE OR REPLACE PROCEDURE emp_by_dept ( p_deptno emp.deptno%TYPE ) IS emp_refcur SYS_REFCURSOR; BEGIN OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno; ...