After a cursor variable is declared, it must be opened with an associated SELECT statement. The OPEN FOR statement specifies the SELECT statement to be used to create the result set.
OPEN name FOR query;
name is the identifier of a previously declared cursor variable. query is a SELECT statement 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.
In the following example, the result set is a list of employee numbers and names from a selected department. Note that a variable or parameter can be used in the SELECT statement 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; ...