Oracle PL/SQL Best Practices

Name

SQL-12: Parameterize explicit cursors.

Synopsis

Cursors return information, just as functions do, and they can accept parameters just as functions do (but only IN parameters). By defining your explicit cursors to accept parameterized values, these cursors are more easily reused in different circumstances and programs. This added value becomes most apparent when you define cursors in package specifications.

Example

Instead of this:

DECLARE
   CURSOR r_and_d_cur IS
      SELECT last_name FROM employee
       WHERE department_id = 10;
BEGIN
   OPEN r_and_d_cur;

move your cursor to a package:

CREATE OR REPLACE PACKAGE dept_info_pkg
IS
   CURSOR name_cur (dept IN INTEGER) IS
      SELECT last_name FROM employee
       WHERE department_id = dept;

and then open it like this:

BEGIN
   open dept_info_pkg.name_cur (10);

or, even better, do this to avoid the hard-coded literal:

DECLARE
   r_and_d_dept CONSTANT PLS_INTEGER := 10;
BEGIN
   open dept_info_pkg.name_cur (r_and_d_dept);

Benefits

Application improvement is likely to improve, because parameters in a cursor are treated as bind variables. So, no matter what value is passed to the cursor, the SQL statement stays the same and isn’t parsed repeatedly.

You will achieve higher levels of reuse in your application, reducing maintenance requirements.