OPEN {cursor_variable_name | :host_cursor_variable_name} FOR select_statement;
/* 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; OPEN :pay_cv FOR SELECT * FROM payroll; OPEN :ins_cv FOR SELECT * FROM insurance; END;
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.
Unlike cursors, cursor variables do not take parameters. No flexibility is lost, however, because you can pass whole queries (not just parameters) to a cursor variable.
You can pass a cursor variable to PL/SQL by calling a stored procedure that declares a cursor variable as one of its formal parameters. However, remote subprograms on another server cannot accept the values of cursor variables. Therefore, you cannot use a remote procedure call (RPC) to open a cursor variable.
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.
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;
To centralize data retrieval, you can group type-compatible queries in a stored procedure. When called, the following packaged procedure opens the cursor variable emp_cv for the chosen query:
CREATE PACKAGE emp_data AS TYPE GenericCurTyp IS REF CURSOR; 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;
For more flexibility, you can pass a cursor variable to a stored procedure that executes queries with different return types, as follows:
CREATE PACKAGE BODY emp_data AS PROCEDURE open_cv (generic_cv IN OUT GenericCurTyp, choice IN NUMBER) 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 open_cv; END emp_data;