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

Pages

Wednesday, 6 April 2016

PL/SQL Cursors


What is a PL/SQL Cursor?

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

PL/SQL Cursor Types

Two Types

1.Static
Implicit Cursors
Explicit Cursors

2.Dynamic
REF Cursors

Implicit Cursors

PL/SQL implicitly declares a cursor for all SQL data manipulation statements on a set of   rows, including queries that return only one row
For queries that return more than one row, you can explicitly declare a cursor to process the rows individually
We can refer to the most recent implicit cursor by the name SQL

Implicit Cursors - Attributes

%FOUND Attribute: used to check whether a DML Statement has affected one or many rows


Until the DML or SELECT statement is executed, it yields NULL
Yields TRUE if the DML affects one or more rows, or a SELECT INTO statement returns one or more rows.
Otherwise yields FALSE.

%ISOPEN Attribute: always FALSE for Implicit Cursors

Yields TRUE if the cursor is open else returns FALSE. 
Oracle closes the SQL cursor automatically after executing its associated SQL statement. As a result, %ISOPEN always yields FALSE.

%NOTFOUND Attribute: used to check whether a DML statement has failed to change rows


%NOTFOUND is the logical opposite of %FOUND
Yields TRUE if the DML statement affects no rows, or a SELECT INTO statement returns no rows
Otherwise it yields FALSE

%ROWCOUNT Attribute: used to find out how many rows are affected so far


%ROWCOUNT yields the number of rows affected by the DML statement, or returned by a SELECT INTO statement
Yields 0 if the DML statement affected no rows, or a SELECT INTO statement returned no rows

Implicit Cursor: Example 1

         Using SQL%ROWCOUNT 

 DECLARE
   v_tot_rows   NUMBER (3);
BEGIN
   DELETE FROM emp
         WHERE deptno = 10;

   /* PL/SQL will use an implicit cursor to process the above statement */
   v_tot_rows := SQL%ROWCOUNT;
   /* Implicit Attribute %ROWCOUNT is used to find the number of  rows affected by the DELETE command */
   DBMS_OUTPUT.put_line('Total records deleted : ' || v_tot_rows);
END;

Implicit Cursor: Example 2

       Using SQL%FOUND

DECLARE
   v_empno   NUMBER (4) := &eno;
BEGIN
   DELETE FROM emp
         WHERE empno = v_empno;

   IF SQL%FOUND
   THEN
      /* Making use of %FOUND to find out whether at least one row was affected by the DELETE statement */
      DBMS_OUTPUT.put_line('Delete successful');
   END IF;
END;

Need for Explicit Cursors

Implicit cursors are declared and managed automatically by PL/SQL. We do not have any control over the processing of implicit cursors.

Explicit cursors give us the complete control over opening, closing and fetching from an explicit cursor, including the number of rows fetched.

We can declare explicit cursors only for SELECT queries. Generally explicit cursors are used when the SELECT query is returning multiple rows and we want to process one row at a time.

Explicit cursors can take parameters. So the cursor can be opened for a different result set in each execution by passing on different values for the parameters.

Explicit Cursors

Are declared and named explicitly by the developer


Manipulated through specific statements in the block’s executable section for queries that return more the one row

Processing Explicit Cursors:

PLSQL Cursors


Steps to Process Explicit Cursors

Cursor Declaration
Declaring a cursor defines the name of the cursor and associates it with a SELECT Statement
          CURSOR  cursor_name  IS  select_statement;

Opening a Cursor
The OPEN statement executes the query and binds any variables that are referenced. Rows identified by the query are called the active set. The active set pointer is set to the first row
                      OPEN cursor_name;

Fetching from a Cursor
The row pointed by active set pointer can be fetched from a cursor into variable/s
After each fetch the cursor is checked for any existing rows
          FETCH cursor_name INTO list_of_variables;
          FETCH cursor_name INTO record_type_variable;

Closing a Cursor
When all of the active set has been retrieved, the cursor should be closed. This tells PL/SQL that the program is finished with the cursor, and the resources associated with it can be freed.
          CLOSE cursor_name;

Explicit Cursors: Attributes

   Can be used with Implicit as well as Explicit cursors for obtaining information about a cursor

Explicit Cursors Attributes


Explicit Cursor: Example -1

DECLARE
   -- Step 1: Cursor declaration
   CURSOR empcur
   IS
      SELECT empno, ename
        FROM emp;

   v_empno   emp.empno%TYPE;
   v_name    emp.ename%TYPE;
BEGIN
   OPEN empcur;                         -- Step2: Open the Cursor for a query

   LOOP
      -- Step 3: Fetch the values from table results into PL/SQL variables.
      FETCH empcur
       INTO v_empno, v_name;

      EXIT WHEN empcur%NOTFOUND;
      DBMS_OUTPUT.put_line(v_empno || ' ' || v_name);
   END LOOP;

   CLOSE empcur;                                   -- Step 4: Close the cursor
END;

Explicit Cursor: using %TYPE and RECORD


DECLARE
   TYPE t_emprec IS RECORD (
      ename   emp.ename%TYPE,
      sal     emp.sal%TYPE,
      job     emp.job%TYPE
   );

   r_emp   t_emprec;

   CURSOR c_emp
   IS
      SELECT ename, sal, job
        FROM emp;
BEGIN
   OPEN c_emp;

   LOOP
      FETCH c_emp
       INTO r_emp;

      EXIT WHEN c_emp%NOTFOUND;
      DBMS_OUTPUT.put_line(r_emp.ename || ', ' || r_emp.sal || ', '
                            || r_emp.job
                           );
   END LOOP;

   CLOSE c_emp;
END;

Cursor WHILE Loop: Example

DECLARE
   CURSOR c_emp
   IS
      SELECT empno, ename
        FROM emp;

   v_empno   emp.empno%TYPE;
   v_ename   emp.ename%TYPE;
BEGIN
   OPEN c_emp;

   FETCH c_emp
    INTO v_empno, v_ename;

   WHILE c_emp%FOUND AND c_emp%ROWCOUNT <= 5
   LOOP
      DBMS_OUTPUT.put_line(v_empno || ', ' || v_ename);

      FETCH c_emp
       INTO v_empno, v_ename;
   END LOOP;

   CLOSE c_emp;
END;

Cursor FOR Loops

LOOP … END LOOP as well as WHILE LOOP … END LOOP, require explicit processing of the cursor with OPEN, FETCH and CLOSE statements
  
With CURSOR FOR LOOPs, no opening, fetching, closing of Cursors is required

Implicitly declares Record Type Variable

Cursor FOR Loops: Example

DECLARE
   CURSOR emp_dept_cur
   IS
      SELECT empno, ename, sal, dept.deptno AS dno, dept.dname
        FROM emp, dept
       WHERE emp.deptno = dept.deptno;
BEGIN
   FOR v_rec IN emp_dept_cur
/* The loop variable v_rec is automatically declared and is allocated the same data type as the ROWTYPE of the cursor */
   LOOP
      DBMS_OUTPUT.put_line('Employee Number :' || v_rec.empno);
      DBMS_OUTPUT.put_line('Employee Name :' || v_rec.ename);
      DBMS_OUTPUT.put_line('Employee Salary    :' || v_rec.esal);
      DBMS_OUTPUT.put_line('Employee Deptno   :' || v_rec.dno);
      DBMS_OUTPUT.put_line('Emp Dept Name :' || v_rec.dname);
   END LOOP;
END;

Example: Using INSERT inside the FOR Loop

CREATE TABLE mybonus(
  myeno NUMBER(4),
  bonusamt NUMBER(5)
);
 /
DECLARE
   bonus   REAL;

   CURSOR empcur
   IS
      SELECT empno, sal, comm
        FROM emp;
BEGIN
   FOR emprec IN empcur
   LOOP
      bonus := (emprec.sal * 0.05) + (emprec.comm * 0.25);

      INSERT INTO mybonus
           VALUES (emprec.empno, bonus);
   END LOOP;

   COMMIT;
END;

Parameterized Cursors

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

CURSOR cursor_name(para_name1  datatype,
    para_name2  datatype,…)                           
          IS  select_statement;

Parameterized 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;

Advanced Cursors                         

SELECT FOR UPDATE CURSOR

If FOR UPDATE clause is present, exclusive row locks are taken on the rows in the active set before the OPEN returns
Locks prevent other sessions from changing the rows in the active set until the transaction is committed

CURSOR cursor_name IS SELECT……FROM….FOR UPDATE [OF column_reference] [NOWAIT|WAIT n]

Example:
          CURSOR emp_cursor IS
   SELECT empno,ename,sal FROM emp
   WHERE deptno=30
    FOR UPDATE OF sal NOWAIT;
NOWAIT: OPEN will return immediately with Oracle error if rows are locked by another session
WAIT n:If rows are locked by another session and are not released in “n” seconds then OPEN will return with Oracle error

WHERE CURRENT OF Clause

Used for referencing the current row from an explicit cursor

Used along with UPDATE & DELETE Statements for Current Row

Must include FOR UPDATE clause while declaring a cursor

Eg. # 1
DECLARE
   CURSOR emp_cursor
   IS
      SELECT        sal
               FROM emp
              WHERE deptno = 30
      FOR UPDATE OF sal NOWAIT;
BEGIN
   FOR emp_record IN emp_cursor
   LOOP
      UPDATE emp
         SET sal = emp_record.sal * 0.1
       WHERE CURRENT OF emp_cursor;
   END LOOP;

   COMMIT;
END;

UPDATE and DELETE statements can use a WHERE CURRENT OF clause if they reference a cursor declared with FOR UPDATE clause.

It indicates that the UPDATE or DELETE should modify the current row identified by the FOR UPDATE cursor.

Syntax:
                   [UPDATE | DELETE ] ... WHERE CURRENT OF cursor_name;

By using WHERE CURRENT OF, you do not have to repeat the WHERE clause in the UPDATE/DELETE statement.

WHERE CURRENT OF Clause: Example

CREATE TABLE emp_history(
          myeno NUMBER(4),
  myhiredt DATE);
/
DECLARE
   CURSOR emp_cur
   IS
      SELECT     empno, hiredate
            FROM emp
           WHERE hiredate < SYSDATE - 7
      FOR UPDATE;
BEGIN
   FOR emp_rec INemp_cur
   LOOP
      INSERT INTO emp_history
           VALUES (emp_rec.empno, emp_rec.hiredate);

      DELETE FROM emp
            WHERE CURRENT OF emp_cur;
   END LOOP;
END;                  

Cursor Variable

Unlike an explicit cursor, which names the PL/SQL work area for the result set, a cursor variable is a reference to that work area
Explicit and implicit cursors are both static in that they are tied to specific queries. The cursor variable can be opened for any query, even different queries within a single program execution. It’s dynamic.

Features

Single cursor variable can be used to fetch from different result sets
Pass a cursor variable as an argument to a procedure or function
Employ the full functionality of static PL/SQL cursors for cursor variables
Assign the contents of one cursor (and its result set) to another cursor variable

REF CURSOR: Types

REF CURSOR types can be strong or weak

A strong REF CURSOR type definition specifies a return type, but a weak definition does not

Strong REF CURSOR types are less error-prone because PL/SQL lets you associate a strongly typed cursor variable only with type-compatible queries

Weak REF CURSOR types are more flexible because you can associate a weakly typed cursor variable with any query

Declaring REF CURSOR Types and Cursor Variables

Two distinct declaration steps to create a cursor variable

Create a REF CURSOR type
Declare a cursor variable based on REF CURSOR type

Creating a REF CURSOR type:

          TYPE cur_type_name IS REF CURSOR [ RETURN return_type ];

          return-type is the RETURN data specification for the cursor type. In short, what kind of data will be returned by a cursor
        
 Strong cursor type: cursor type with a RETURN clause; associated with a specific data structure

          TYPE emp_type REF CURSOR RETURN emp%ROWTYPE;

Weak cursor type: cursor type without a RETURN clause; not associated with a specific data structure

          TYPE emp_type REF CURSOR;

Declaring a Cursor variable

              cursor_variable_name cursor_type_name;
                   e.g. v_empno emp_type;

Opening a Cursor variable

          OPEN cursor_variable_name FOR select_statement;

Fetching from a Cursor

          FETCH cursor_variable_name INTO record_variable;
          FETCH cursor_variable_name INTO variable1, variable2, …;

REF Cursor – Weak: Example

 DECLARE
   TYPE emp_cur IS REF CURSOR;

   v_weak_cursor   emp_cur;
   v_emp_rec       emp%ROWTYPE;
   v_dept_rec      dept%ROWTYPE;
   v_operation     NUMBER (1)     := &op;
BEGIN
   IF v_operation = 1
   THEN
      OPEN v_weak_cursor FOR
         SELECT *
           FROM emp;

      LOOP
         FETCH v_weak_cursor
          INTO v_emp_rec;

         EXIT WHEN v_weak_cursor%NOTFOUND;
         DBMS_OUTPUT.put_line('Ename :' || v_emp_rec.ename);
      END LOOP;

      CLOSE v_weak_cursor;
   ELSE
      OPEN v_weak_cursor FOR
         SELECT *
           FROM dept;

      LOOP
         FETCH v_weak_cursor
          INTO v_dept_rec;

         EXIT WHEN v_weak_cursor%NOTFOUND;
         DBMS_OUTPUT.put_line('Deptname :' || v_dept_rec.dname);
      END LOOP;

      CLOSE v_weak_cursor;
   END IF;
END;

REF Cursor – Strong: Example

DECLARE
   TYPE curtype IS REF CURSOR
      RETURN emp%ROWTYPE;

   refcur   curtype;
   emprec   emp%ROWTYPE;
BEGIN
   DBMS_OUTPUT.put_line('Dept 10 employees are ');

   OPEN refcur FOR
      SELECT *
        FROM emp
       WHERE deptno = 10;

   LOOP
      FETCH refcur
       INTO emprec;

      EXIT WHEN refcur%NOTFOUND;
      DBMS_OUTPUT.put_line('Ename :' || emprec.ename);
   END LOOP;

   CLOSE refcur;

   DBMS_OUTPUT.put_line('Dept 20 employees are ');

   OPEN refcur FOR
      SELECT *
        FROM emp
       WHERE deptno = 20;

   LOOP
      FETCH refcur
       INTO emprec;

      EXIT WHEN refcur%NOTFOUND;
      DBMS_OUTPUT.put_line('Ename :' || emprec.ename);
   END LOOP;

   CLOSE refcur;
END;
/

 Limitations of Cursor

Comparison operators cannot be used to test cursor variables for equality, inequality, null, or not null

Null cannot be assigned to a cursor variable

The value of a cursor variable cannot be stored in a database column

Static cursors and cursor variables are not interchangeable; For example, a static cursor cannot be used in an OPEN FOR statement

You May Like:

PL/SQL Nested tables
PL/SQL Varray

Share this article :

0 comments:

Post a Comment