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:

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 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 Ref Cursor

PLSQL Record

PL/SQL Bulk Collect And Bulk bind

PL/SQL Index by tables (or) PL/SQL tables (or) associative arrays

PL/SQL Nested tables
PL/SQL Varray