In PL/SQL, parameterized cursors offer a great deal of flexibility and clarity of the code.
Let’s illustrate this point with a specific (though artificial) example.
Let say, we need to show all employees who is paid above average in their respective department. In SQL, the solution would be quite simple:
SQL> SELECT deptno, ename, sal
2 FROM emp e
3 WHERE sal>(SELECT AVG(sal)
4 FROM emp
5 WHERE deptno=e.deptno)
6 ORDER BY deptno, sal DESC, ename;
DEPTNO ENAME SAL
---------- ---------- ----------
10 KING 5000
20 FORD 3000
20 SCOTT 3000
20 JONES 2975
30 BLAKE 2850
30 ALLEN 1600
We are going to solve the same simple problem in PL/SQL (using anonymous block). The first example will utilize 2 PL/SQL variables instead of cursor parameters:
SET SERVEROUTPUT ON FORMAT WRAPPED
DECLARE
CURSOR d IS
SELECT deptno, AVG(sal) avg_sal
FROM emp
GROUP BY deptno
ORDER BY 1;
v_deptno NUMBER;
v_avg_sal NUMBER;
CURSOR e IS
SELECT ename, sal
FROM emp
WHERE deptno=v_deptno
AND sal>v_avg_sal
ORDER BY sal DESC, ename;
BEGIN
DBMS_OUTPUT.PUT_LINE('deptno ename sal');
DBMS_OUTPUT.PUT_LINE('------ -------- ------');
FOR v1 IN d LOOP
v_deptno:=v1.deptno;
v_avg_sal:=v1.avg_sal;
FOR v2 IN e LOOP
DBMS_OUTPUT.PUT_LINE(LPAD(v1.deptno,6) || ' ' || RPAD(v2.ename, 9) || LPAD(v2.sal, 6));
END LOOP;
END LOOP;
END;
/
deptno ename sal
------ -------- ------
10 KING 5000
20 FORD 3000
20 SCOTT 3000
20 JONES 2975
30 BLAKE 2850
30 ALLEN 1600
Note the use of the “SET SERVEROUTPUT ON FORMAT WRAPPED” sqlplus command. You can read more about it here.
Alternatively, we can you a record variable:
DECLARE
CURSOR d IS
SELECT deptno, AVG(sal) avg_sal
FROM emp
GROUP BY deptno
ORDER BY 1;
v_dept d%ROWTYPE;
CURSOR e IS
SELECT ename, sal
FROM emp
WHERE deptno=v_dept.deptno
AND sal>v_dept.avg_sal
ORDER BY sal DESC, ename;
BEGIN
DBMS_OUTPUT.PUT_LINE('deptno ename sal');
DBMS_OUTPUT.PUT_LINE('------ -------- ------');
FOR v1 IN d LOOP
v_dept:=v1;
FOR v2 IN e LOOP
DBMS_OUTPUT.PUT_LINE(LPAD(v1.deptno,6) || ' ' || RPAD(v2.ename, 9) || LPAD(v2.sal, 6));
END LOOP;
END LOOP;
END;
A better way to pass variables to a cursor is to use cursor parameters:
DECLARE
CURSOR d IS
SELECT deptno, AVG(sal) avg_sal
FROM emp
GROUP BY deptno
ORDER BY 1;
CURSOR e(c_deptno NUMBER, c_avg_sal NUMBER) IS
SELECT ename, sal
FROM emp
WHERE deptno=c_deptno
AND sal>c_avg_sal
ORDER BY sal DESC, ename;
BEGIN
DBMS_OUTPUT.PUT_LINE('deptno ename sal');
DBMS_OUTPUT.PUT_LINE('------ -------- ------');
FOR v1 IN d LOOP
FOR v2 IN e(v1.deptno, v1.avg_sal) LOOP
DBMS_OUTPUT.PUT_LINE(LPAD(v1.deptno,6) || ' ' || RPAD(v2.ename, 9) || LPAD(v2.sal, 6));
END LOOP;
END LOOP;
END;
/
deptno ename sal
------ -------- ------
10 KING 5000
20 FORD 3000
20 SCOTT 3000
20 JONES 2975
30 BLAKE 2850
30 ALLEN 1600
As you can see, no variable declaration and assignment is needed we; instead, we declare cursor parameters. This gives the code better clarity and readability as both, the cursor parameters and the cursor itself are defined in one place. You don’t need any intermediary variable for opening a nested cursor.
The following example will optimize the last block by using a single cursor parameter:
DECLARE
CURSOR d IS
SELECT deptno, AVG(sal) avg_sal
FROM emp
GROUP BY deptno
ORDER BY 1;
CURSOR e(c_dept d%ROWTYPE) IS
SELECT ename, sal
FROM emp
WHERE deptno=c_dept.deptno
AND sal>c_dept.avg_sal
ORDER BY sal DESC, ename;
BEGIN
DBMS_OUTPUT.PUT_LINE('deptno ename sal');
DBMS_OUTPUT.PUT_LINE('------ -------- ------');
FOR v1 IN d LOOP
FOR v2 IN e(v1) LOOP
DBMS_OUTPUT.PUT_LINE(LPAD(v1.deptno,6) || ' ' || RPAD(v2.ename, 9) || LPAD(v2.sal, 6));
END LOOP;
END LOOP;
END;
/
So what is the main advantages of using parameterized cursors over using cursors with [bind] variables?
- Parameterized cursors support default values for cursor parameters
- The cursors can be referenced more than once with different parameter values
- A cursor with parameter(s) encapsulates all information necessary for opening and fetching data which makes it safer for use as you don’t need to trace the assignment of cursor parameters/variables all over the place.
My Oracle Group on Facebook:
If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/
Would you like to read about many more tricks and puzzles?
For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.
sqlpatterns 