Parameterized Cursor(Passing parameter to the cursor)

             Cursors can use variables to adjust which rows they select when opened. Instead of hard-coding a value into the WHERE clause of a query, you can use a variable as a placeholder for a literal value. The variable placeholder will substituted with the value of the variable when the cursor is opened. This makes a query more flexible.

DECLARE
  v_deptno NUMBER;
  v_job VARCHAR2(15);
  v_sum_sal NUMBER;

/* Since v_deptno and v_job are declared above, they are in scope,
 * and can be referenced in the cursor body.  They will be used as
 * placeholders until the cursor is opened, at which
 * point the values of the variables will be substituted (bound)
 * into the query as literals.
 */
  CURSOR emp_stats_cursor IS
    SELECT SUM(sal) sum_sal
      FROM emp
      WHERE deptno=v_deptno
        AND job=v_job;     
BEGIN
  v_deptno:=10;    
  v_job:='MANAGER';

  OPEN emp_stats_cursor;
  /* When the cursor is opened, the values of the PL/SQL
   * variables are bound into the query. 
   * In this example, the cursor would return the
   * result set using the following query:
    SELECT SUM(sal) sum_sal
      FROM emp
      WHERE deptno=10       --current value of v_deptno is 10
        AND job='MANAGER';  --current value of v_job is 'MANAGER'     
    */

  FETCH emp_stats_cursor INTO v_sum_sal;
  CLOSE emp_stats_cursor; 
  DBMS_OUTPUT.PUT_LINE(v_deptno||' : '||v_job||' : '||v_sum_sal);

  v_deptno:=30;    
  v_job:='SALESMAN';

  OPEN emp_stats_cursor;
   /* In this example, the cursor would
    * return the result set using the following query:

    SELECT SUM(sal) sum_sal
      FROM emp
      WHERE deptno=30       --current value of v_deptno is 30
        AND job='SALESMAN';  --current value of v_job is 'SALESMAN'     
    */  FETCH emp_stats_cursor INTO v_sum_sal;
  CLOSE emp_stats_cursor; 
  DBMS_OUTPUT.PUT_LINE(v_deptno||' : '||v_job||' : '||v_sum_sal);
END;
/

This method works, but there is a better way.  You can declare a cursor using parameters; then whenever you open the cursor, you pass in appropriate parameters.  This technique is just as flexible, but is easier to maintain and debug. The above example adapted to use a parameterized cursor:

DECLARE

  v_sum_sal NUMBER;

/* The parameters are declared in the cursor declaration. 
 * Parameters have a datatype, but NO SIZE; that is, you
 * can declare a parameter of datatype VARCHAR2, but never
 * VARCHAR2(20).
 * As above, the parameters will be placeholders (that is,
 * formal parameters) until the cursor is opened, at which
 * point the actual parameter values will be substituted
 * (bound) into the query as literals.
 * The parameters are in scope (that is, they can be referenced)
 * only inside the cursor body.
 */

  CURSOR emp_stats_cursor(cp_deptno NUMBER, cp_job VARCHAR2) IS
    SELECT SUM(sal) sum_sal
      FROM emp
      WHERE deptno=cp_deptno
        AND job=cp_job;     

BEGIN

  OPEN emp_stats_cursor(10,'MANAGER');
  /* When the cursor is opened, the values of the parameters
   * are bound into the query. In other words, the actual parameters
   * (values) will replace the formal parameters (placeholders).
   * In this example, the cursor would return the result set using
   * the following query:

    SELECT SUM(sal) sum_sal
      FROM emp
      WHERE deptno=10      
        AND job='MANAGER';       
    */

  FETCH emp_stats_cursor INTO v_sum_sal;
  CLOSE emp_stats_cursor; 

  DBMS_OUTPUT.PUT_LINE('10 : MANAGER : '||v_sum_sal);

  OPEN emp_stats_cursor(30,'SALESMAN');
   /* In this example, the cursor would return the result set
    * using the following query:

    SELECT SUM(sal) sum_sal
      FROM emp
      WHERE deptno=30      
        AND job='SALESMAN'; 
    */

  FETCH emp_stats_cursor INTO v_sum_sal;
  CLOSE emp_stats_cursor; 
  DBMS_OUTPUT.PUT_LINE('30 : SALESMAN : '||v_sum_sal);
END;
/

0         Parameterized cursors are open easier to debug in larger PL/SQL blocks.  This is because the the declaration of the cursor body  is often far from where the cursor is opened, but processing of the cursor's result set is usually close to where the cursor is opened. 

• When opening a cursor which uses variables, you must assign appropriate values to those variables before opening the cursor.  So when you are debugging how the cursor is opened, you must confirm the appropriate variable names where the cursor is declared .  This is often inconvenient. 
• When using PL/SQL variables, it's difficult to confirm the values of the variables when the cursor is opened because the values could be set at an any point from the declaration on.  
• Parameterized cursors eliminate both these problems because the values used in the cursor can be determined in one place, the OPEN statement.  And you don't need to know the names of the cursor parameters.  (Though you do need to know the order of the parameters.)

·An example which combines a cursor FOR loop with a parameterized query:

DECLARE
  v_sum_sal NUMBER;
  CURSOR emp_stats_cursor(cp_deptno NUMBER, cp_job VARCHAR2) IS
    SELECT SUM(sal) sum_sal
      FROM emp
      WHERE deptno=cp_deptno
        AND job=cp_job;  

BEGIN
  FOR dept_job_rec IN (SELECT DISTINCT deptno,job FROM emp) LOOP
    OPEN emp_stats_cursor(dept_job_rec.deptno, dept_job_rec.job);
    FETCH emp_stats_cursor INTO v_sum_sal;
    CLOSE emp_stats_cursor; 
    DBMS_OUTPUT.PUT_LINE(dept_job_rec.deptno ||' : '||dept_job_rec.job||' : '||v_sum_sal);
  END LOOP;
END;
/