Use of Oracle Advanced explicit cursor

1. parameterized cursor
Purpose: Open the same cursor multiple times and return different result sets. The parameters in the declaration part of the cursor must correspond to the real parameters provided in the OPEN statement (quantity and data type ).
 
Example 1:
DECLARE
V_empno emp1.empno % TYPE;
V_ename emp1.ename % TYPE;
CURSOR emp_cursor
(P_deptno NUMBER, p_job VARCHAR2) IS -- you do not need to specify the precision when defining parameters.
SELECT empno, ename
FROM emp1
WHERE deptno = p_deptno
AND job = p_job;
BEGIN
OPEN emp_cursor (30, 'salesman'); -- a cursor with Parameters
LOOP
FETCH emp_cursor INTO v_empno, v_ename;
Exit when emp_cursor % notfound or emp_cursor % notfound is null;
DBMS_OUTPUT.PUT_LINE ('empno is '| v_empno | 'and' | 'ename is' | v_ename );
End loop;
CLOSE emp_cursor;
END;
 

2. Record + FOR + cursor Parameters
 
DECLARE
CURSOR emp_c (p_job VARCHAR2)
Is select ename, sal FROM emp
WHERE job = p_job for update nowait;
BEGIN
FOR e_record IN emp_c (UPPER ('& job') -- Use Substitution variable
LOOP
Dbms_output.put_line (e_record.ename | '''s sal: '| e_record.sal );
End loop;
END;
/
 
Enter value for jobs: manager
Old 6: for e_record in emp_c (upper ('& jobs '))
New 6: for e_record in emp_c (upper ('manager '))
JONES's sal: 2975
BLAKE's sal: 2850
CLARK's sal: 2450

1 2 Next Page