PL/SQL Best Practices [4] [Cursor Management]- Cursor Parameters

Use Cursor Parameters for Reusability

1CURSOR company_cur 
2   (id_in IN company.company_id%TYPE,
3    status_in IN 
4       company.cstatus%TYPE := 'O')

Cursor parameters use the same structure as the parameter list for procedures and functions. You supply IN parameters. Neither OUT nor IN OUT modes are allowed. Cursor parameters can include default parameter values.

Cursor parameters increase flexibility/reusability of the cursor. You can use the same cursor in many programs.

Parameters are most important when a cursor is in a package. Because the cursor is not defined within a single program, you cannot bind it with local or host variables. This frees the cursor from any particular context or program unit.

Avoid Hard-Coding Cursor Parameters
Compare the following two cursors; one uses an embedded bind variable, the other a parameter. The first cursor requires the presence of the new_company_id local variable to compile and execute. The second cursor can be used wherever a company id is available.

 1DECLARE   CURSOR company_cur IS
 2         SELECT comp_nm
 3           FROM company
 4          WHERE company_id = new_company_id;
 5   BEGIN
 6      OPEN company_cur;
 7   DECLARE   CURSOR company_cur 
 8        (id_in IN company.company_id%TYPE) IS
 9         SELECT comp_nm
10           FROM company
11          WHERE company_id = id_in;
12   BEGIN
13      OPEN company_cur (new_company_id);
14