Opening a cursor variable v14

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;
        ...