PL/SQL Cursor with Parameter

 

To process any DML or SELECT queries, Oracle allocates an area of memory on the database server, known as context area

PL/SQL Cursor is a pointer to the context area

Context area contains information about the SQL statement and the set of data returned or affected by that statement

         

           PL/SQL Cursor is a mechanism by which one can name that work area and  manipulate the information within it

Parameter Cursor:

 

Parameters allow values to be passed to a cursor when it is opened and to be used in the query when it executes

Different active set each time based on a parameter value that is passed to a cursor

Parameter Cursor Syntax:

CURSOR cursor_name(para_name1  datatype,

    para_name2  datatype,…)                           

          IS  select_statement;

  
Parameter Cursors Example:

 

DECLARE

   CURSOR par_cur (p_deptno NUMBER, p_job VARCHAR2)

   IS

      SELECT empno, ename

        FROM emp

       WHERE deptno = p_deptno AND UPPER (job) = UPPER (p_job);

   v_deptno   emp.deptno%TYPE   := &dno;

   v_job      emp.job%TYPE      := '&job';

BEGIN

   FOR emp_record IN par_cur (v_deptno, v_job)

   LOOP

      DBMS_OUTPUT.put_line('Empno      :' || emp_record.empno);

      DBMS_OUTPUT.put_line('Ename       :' || emp_record.ename);

   END LOOP;

END;