Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Tuesday, 27 June 2017

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;

Share this article :

0 comments:

Post a Comment