Database Cursors

Cursors-What is a Cursor? 

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

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

¢Cursor is a mechanism by which one can name a work area in the memory on the database server

¢Cursors are one of the most common and fundamental terms in the database terminology. It is one of the core database programming concepts, which forms a basic unit of execution of SQL statement.

Cursors are one of the most common and fundamental terms in the database terminology. It is one of the core database programming concepts, which forms a basic unit of execution of SQL statement.

A cursor is a pointer, which points towards a pre allocated memory location in the SGA. For transparent understanding, it is a handle or gateway adopted by Oracle to execute a SQL query. The memory location to which it points is known as Context area. Oracle associates every SELECT statement with a cursor to hold the query information in this context area.

Cursor follows a defined execution cycle to execute the SQL statement associated with it. The article describes the Oracle cursors and their usage.

There are two types of cursors: Implicit cursors and explicit cursors.

Cursor Types

¢A cursor is a pointer, which points towards a pre allocated memory location in the SGA. For transparent understanding, it is a handle or gateway adopted by Oracle to execute a SQL query.

¢The memory location to which it points is known as Context area. Oracle associates every SELECT statement with a cursor to hold the query information in this context area.

¢Cursor follows a defined execution cycle to execute the SQL statement associated with it. The article describes the Oracle cursors and their usage.

¢Cursor Types:

¢Implicit Cursor

¢Implicit Cursor is defined and controlled by Oracle Internally. It is easy¢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 have to  explicitly declare a cursor to process the rows individually

¢We can refer to the most recent implicit cursor by the name SQL

¢Explicit Cursor

¢Explicit Cursors are defined and controlled programatically. There are two types of explicit cursors,

¢Static Cursors

¢Ref Cursors.

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

A SQL (implicit) cursor is opened by the database to process each SQL statement that is not associated with an explicit cursor. Every SQL (implicit) cursor has six attributes, each of which returns useful information about the execution of a data manipulation statement.

Syntax

sql_cursor ::=

SQL% { FOUND
     | ISOPEN
     | NOTFOUND
     | ROWCOUNT
     | BULK_ROWCOUNT ( index )
     | BULK_EXCEPTIONS ( index ).{ ERROR_INDEX | ERROR_CODE }
     }

Keyword and Parameter Descriptions

%BULK_ROWCOUNT

A composite attribute designed for use with the FORALL statement. This attribute acts like an index-by table. Its ith element stores the number of rows processed by the ith execution of an UPDATE or DELETE statement. If the ith execution affects no rows,%BULK_ROWCOUNT(i) returns zero.

%BULK_EXCEPTIONS

An associative array that stores information about any exceptions encountered by a FORALL statement that uses the SAVE EXCEPTIONSclause. You must loop through its elements to determine where the exceptions occurred and what they were. For each index value ibetween 1 and SQL%BULK_EXCEPTIONS.COUNT, SQL%BULK_EXCEPTIONS(i).ERROR_INDEX specifies which iteration of the FORALL loop caused an exception. SQL%BULK_EXCEPTIONS(i).ERROR_CODE specifies the Oracle Database error code that corresponds to the exception.

%FOUND

Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.

%ISOPEN

Always returns FALSE, because the database closes the SQL cursor automatically after executing its associated SQL statement.

%NOTFOUND

The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.

%ROWCOUNT

Returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.

SQL

The name of the implicit cursor.

Usage Notes

You can use cursor attributes in procedural statements but not in SQL statements. Before the database opens the SQL cursor automatically, the implicit cursor attributes return NULL. The values of cursor attributes always refer to the most recently executed SQL statement, wherever that statement appears. It might be in a different scope. If you want to save an attribute value for later use, assign it to a variable immediately.

If a SELECT INTO statement fails to return a row, PL/SQL raises the predefined exception NO_DATA_FOUND, whether you checkSQL%NOTFOUND on the next line or not. A SELECT INTO statement that invokes a SQL aggregate function never raises NO_DATA_FOUND, because those functions always return a value or a NULL. In such cases, SQL%NOTFOUND returns FALSE. %BULK_ROWCOUNT is not maintained for bulk inserts because a typical insert affects only one row. See Counting Rows Affected by FORALL (%BULK_ROWCOUNT Attribute).

You can use the scalar attributes %FOUND, %NOTFOUND, and %ROWCOUNT with bulk binds. For example, %ROWCOUNT returns the total number of rows processed by all executions of the SQL statement. Although %FOUND and %NOTFOUND refer only to the last execution of the SQL statement, you can use %BULK_ROWCOUNT to deduce their values for individual executions. For example, when %BULK_ROWCOUNT(i) is zero,%FOUND and %NOTFOUND are FALSE and TRUE, respectively.

Implicit Cursor: Example 1

¢Using SQL%ROWCOUNT

     SET SERVEROUTPUT ON;

DECLARE

  v_total_rows NUMBER(3); 

BEGIN

  DELETE FROM emp WHERE deptno=11;

  /* PL/SQL will use an implicit cursor to process the above statemnt*/

  v_total_rows := SQL%ROWCOUNT;

  /*Implicit Atrribute %ROWCOUNT is used to find the number of rows affected by the DELETE command */

  DBMS_OUTPUT.PUT_LINE('Total records deleted : '||v_total_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;

/

NOTE:

¢If SELECT INTO returns no rows: predefined exceptionNO_DATA_FOUND is raised immediately (beforeSQL%NOTFOUND can be checked).

¢SELECT INTO that invokes a SQL aggregate function (i.e. avg, sum, count, etc) always returns a value (possibly NULL). HereSQL%NOTFOUND is always FALSE.

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:

Also called a named cursor, since unlike implicit ones, an explicit cursor can be referenced by its name

is a named pointer to a private SQL area that stores information for processing a specific query or DML statement

Before using an explicit cursor, you must declare (cursor specification) and define it (cursor body).

You cannot assign a value to an explicit cursor, use it in an expression, or use it as a formal subprogram parameter or host variable.

You can do those things with a cursor variable.

OPEN, FETCH, and CLOSE 

Process multiple result sets in parallel, using multiple cursors.

Process multiple rows in a single loop iteration, skip rows, or split the processing into multiple loops.

Specify the query in one PL/SQL unit but retrieve the rows in another.

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

  if c_emp%found=true then

  DBMS_OUTPUT.PUT_LINE ('true');

  elsif c_emp%found=false then

  DBMS_OUTPUT.PUT_LINE ('false');

  else

  DBMS_OUTPUT.PUT_LINE ('null');

  end if;

  WHILE c_emp%FOUND loop

  DBMS_OUTPUT.PUT_LINE (v_empno || ', ' || v_ename); 

  FETCH c_emp INTO v_empno,v_ename;     

  if c_emp%found=true then

  DBMS_OUTPUT.PUT_LINE ('true');

  elsif c_emp%found=false then

  DBMS_OUTPUT.PUT_LINE ('false');

  else

  DBMS_OUTPUT.PUT_LINE ('null');

  end if;

  END LOOP;

CLOSE c_emp;

END;

Attributes	Return values	Example
%FOUND	TRUE, if fetch statement returns at least one row	Cursor_name%FOUND
	FALSE, if fetch statement doesn’t return a row
%NOTFOUND	TRUE, if fetch statement doesn’t return a row.	Cursor_name%NOTFOUND
	FALSE, if fetch statement returns at least one row
%ROWCOUNT	Yields the number of rows fetched by the cursor so far	Cursor_name%ROWCOUNT
%ISOPEN	TRUE, if the cursor is already open in the program FALSE, if the cursor is not opened in the program	Cursor_name%ISNAME

Explicit Cursor: Example -1

DECLARE

  --STEP 1 : Cursor Declaration

  CURSOR empcur IS SELECT empno,ename FROM EMP;

  v_empno emp.EMPNO%TYPE;

  v_ename emp.ENAME%TYPE;

BEGIN

  OPEN empcur;--STEP 2 : 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_ename;

      EXIT WHEN empcur%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(v_empno ||' '||v_ename);

    END LOOP;

  CLOSE empcur;--STEP 4 CLOSING A CURSOR

END;

Explicit Cursor: using %TYPE and RECORD

DECLARE

  --DEFINING A TYPE RECORD

    TYPE t_emprec IS RECORD

    (

        ename emp.ENAME%TYPE,

        sal emp.SAL%TYPE,

        jobId emp.JOB%TYPE   

    );

    r_emp t_emprec;

    --DECLARING A CURSOR

    CURSOR cur_emp IS SELECT ENAME,SAL,JOB from emp;

BEGIN

--OPENING A CURSOR

    OPEN cur_emp;     

    LOOP

    --FETCHING

      FETCH cur_emp into r_emp;

      EXIT WHEN cur_emp%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(r_emp.ename||' , '||r_emp.sal||' , '||r_emp.jobId);

    END LOOP;

    --CLOSING

    CLOSE cur_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;

---The above code is used to process the first 5 rows if present.

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

¢The cursor FOR LOOP statement implicitly declares its loop index as a record variable of the row type that a specified cursor returns, and then opens a cursor.

¢With each iteration, the cursor FOR LOOP statement fetches a row from the result set into the record.

¢When there are no more rows to fetch, the cursor FOR LOOP statement closes the cursor.

¢The cursor also closes if a statement inside the loop transfers control outside the loop or raises an exception.

The cursor FOR LOOP statement implicitly declares its loop index as a record variable of the row type that a specified cursor returns, and then opens a cursor. With each iteration, the cursor FOR LOOP statement fetches a row from the result set into the record. When there are no more rows to fetch, the cursor FOR LOOP statement closes the cursor. The cursor also closes if a statement inside the loop transfers control outside the loop or raises an exception.

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 SAL    :’ || 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 FOR Loop

CREATE TABLE myBonus(

  myEno NUMBER(4),

  bonusAmt NUMBER(5)

);

DECLARE

  bonus mybonus.bonusamt%type;

  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 at  the time OPEN returns the rows

¢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

Examples

In  one session, give the following command :

Update emp set sal=sal/2;

In another session, run the following blocks and see the output :

 declare

 cursor c1 is select * from emp where deptno=10 for update nowait;

 x c1%rowtype;

 begin

 open c1;

 loop

 fetch c1 into x;

 exit when c1%notfound;

 update emp set sal=sal +(x.sal *.25) where current of c1;

 end loop;

 end;

/

 declare

 cursor c1 is select * from emp where deptno=10 for update wait 12;

 x c1%rowtype;

 begin

 open c1;

 loop

 fetch c1 into x;

 exit when c1%notfound;

 update emp set sal=sal +(x.sal *.25) where current of c1;

 end loop;

 end;

/

 declare

 cursor c1 is select * from emp where deptno=10 for update;

 x c1%rowtype;

 begin

 open c1;

 loop

 fetch c1 into x;

 exit when c1%notfound;

 update emp set sal=sal +(x.sal *.25) where current of c1;

 end loop;

 end;

/

In one session run the block :

 declare

 cursor c1 is select * from emp where deptno=10 for update;

 x c1%rowtype;

 begin

 open c1;

 loop

 fetch c1 into x;

 exit when c1%notfound;

 update emp set sal=sal +(x.sal *.25) where current of c1;

 end loop;

 close c1;

 end;

 /

Here, the cursor is closed, but the lock is not released until the transaction is ended(thru a commit, rollback), hence in the second session the following command will go in a wait mode

Update emp set sal=sal/2;

Now in one session, run the following block :

 declare

 cursor c1 is select * from emp where deptno=10 for update

 x c1%rowtype;

 begin

 open c1;

 loop

 fetch c1 into x;

 exit when c1%notfound;

 update emp set sal=sal +(x.sal *.25) where current of c1;

 end loop;

 close c1;

Commit;

 end;

 /

Now, since the lock achieved is released by the commit command, in another session the following command will work :

Update emp set sal=sal/2;

Advanced Cursors : 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

¢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 depend upon use of the WHERE clause to pinpoint correctly(for an updation/deletion) to the source row in the table whose copy is the current row  in the cursor UPDATE/DELETE statement. 

Examples

 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 IN emp_cur

  LOOP

  INSERT INTO emp_history VALUES   (emp_rec.empno, emp_rec.hiredate);

  DELETE FROM emp WHERE CURRENT OF emp_cur; END LOOP;

END; 

Eg. # 1

 --WHERE CURRENT OF Clause

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;

              DBMS_OUTPUT.PUT_LINE('updated sal:'  || emp_record.sal);

      END LOOP;

      COMMIT;

END;

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;

if emp_cursor%isopen then

 dbms_output.put_line('open');

elsif emp_cursor%isopen=false then

 dbms_output.put_line('not open');

else

 dbms_output.put_line('null');

end if;

END;

/

Output

Not open

Cursors with subqueries 

declare

  cursor c1 is

    select t1.deptno, dname, staff

    from dept t1,

         ( select deptno, count(*) as staff

           from emp

           group by deptno

          ) t2

    where (t1.deptno = t2.deptno)

      and staff >= 5

    order by staff;

begin

  for dept in c1 loop

    dbms_output.put_line ('Department = '

        || dept.dname || ', staff = ' || dept.staff);

  end loop;

end;

Cursor Sharing

Multiple private SQL areas in the same or different sessions can reference a single shared SQL area, a phenomenon known as cursor sharing. For example, an execution of SELECT * FROM emp in one session and an execution of the SELECT * FROM emp(accessing the same table) in a different session can use the same parse tree and plan. A shared SQL area that is accessed by multiple statements is known as a shared cursor.

Oracle Database automatically determines whether the SQL statement or PL/SQL block being issued is identical to another statement currently in the library cache, using the following steps:

The text of the statement is hashed.

If no matching hash value exists, then the SQL statement does not currently exist in the shared pool, so the database performs a hard parse.

The database looks for a matching hash value for an existing SQL statement in the shared pool. The following options are possible:

No matching hash value exists.

In this case, the SQL statement does not currently exist in the shared pool, so the database performs a hard parse. This ends the shared pool check.

A matching hash value exists.

In this case, the database compares the text of the matched statement to the text of the hashed statement to see if they are identical. The text of the SQL statements or PL/SQL blocks must be identical, character for character, including spaces, case, and comments. For example, the following statements cannot use the same shared SQL area:

SELECT * FROM emp; SELECT * FROM emp; SELECT * FROM emp; Usually, SQL statements that differ only in literals cannot use the same shared SQL area. For example, the following statements do not resolve to the same SQL area:

SELECT count(1) FROM emp WHERE manager_id = 121; SELECT count(1) FROM emp WHERE manager_id = 247; The only exception to this rule is when the parameter CURSOR_SHARING has been set to FORCE, in which case similar statements can share SQL areas. The costs involved in using CURSOR_SHARING are explained in "Do Not Use CURSOR_SHARING = FORCE as a Permanent Fix".

The database compares objects referenced in the issued statement to the referenced objects of all existing statements in the pool to ensure that they are identical.

References to schema objects in the SQL statements or PL/SQL blocks must resolve to the same object in the same schema. For example, if two users issue the following SQL statement, and if each user has its own emp table, then the following statement is not identical because the statement references different emp tables for each user:

SELECT * FROM emp;

The database determines whether bind variables in the SQL statements match in name, data type, and length.

For example, the following statements cannot use the same shared SQL area because the bind variable names differ:

SELECT * FROM emp WHERE deptno = :deptno; SELECT * FROM emp WHERE deptno = :dept_id;

The database determines whether the session environment is identical.

Examples

Cursor

Cursor is one of the fundamental feature of Oracle. Oracle cursor is a memory handle to the SQL area. Cursors are associated with SQL and variables. Oracle holds all relevant information about SQL and variables in the context area.

Example : – Cursor Cur_Emp is Select * from EMP where deptno =10;

Oracle Cursor ClassificationMainly there are two type of cursors. Implicit Cursors and Explicit Cursors

Implicit Cursor

Implicit Cursor is defined and controlled by Oracle Internally. It is easy use.

Example :-

declare
v_empname varchar2(50);
begin
select empname into v_empname from emp where empno = 10;
end;

select query used in above PL/SQL block is an implicit cursor

Explicit Cursor

Explicit Cursors are defined and controlled programatically. There are two types of explicit cursors, Static Cursors and Ref Cursors.

Static Cursors :- Defining and associating before run time. SQL is constructed and associated prior to execution or run time.

Example :-

declare
v_empname varchar2(50);
Cursor Cur_ename is select empname from emp where empno = 10;
begin
Open Cur_ename;
Fetch Cur_ename into v_empname;
dbms_output.put_line(‘Employee Name –>’|| v_empname);
Close Cur_ename;
end;

A cursor has mainly 4 stages. Declaration, Opening, Fetching and Closing.

Declaring Cursor

Generally Cursors all declared at declare section of the PL/SQL block or sub program and it must be associated with an SQL.

declare
Cursor Cur_ename is select empname  from emp where empno = 10;
begin

………….

Here Cur_ename is the name of the cursor and “select ename into v_ename from emp where empno = 10″is the SQL associated with it.

Opening Cursor

After declaration cursor need to open. OPEN keyword use to open a cursor

OPEN Cur_ename;

Fetching Cursor

Fetching cursor into variables. Note that number of columns in the SQL and number of variables must match and also variables must be compatible.

Fetch Cur_ename into v_ename;

In above case SQL has only one column which will return a varchar2. So we have declared a variable v_ename as varchar2.

Closing Cursor

CLOSE command will close the opened cursor.

Close Cur_ename;

Explicit Cursor Attributes

Explicit cursor has 4 attributes. %FOUND, %NOTFOUND, %ISOPEN and %ROWCOUNT.

%FOUND – Using to check the cursor is success or not. Returns true or false. If cursor returns any row then %FOUND will be true. Only used after opening the cursor.

%NOTFOUND – Using to check the cursor is not a success or not. Returns true or false. If cursor returns no rows then %NOTFOUND will be true. Only used after opening the cursor.

%ISOPEN – This is self-explanatory. Used to check whether cursor is open or not. Returns true or false.

%ROWCOUNT – To find the number of rows the cursor returned. Return as an integer.

( Note that SQL cursor has one more attribute %BULK_ROWCOUNT which using the bulk collect functionality. We will discuss this in another section)

Examples:-

1.  Cursor example with single record

declare
v_ename varchar2(50);
Cursor Cur_ename is select empname from emp where empno = 10;
begin
Open Cur_ename;
Fetch Cur_ename into v_ename;
dbms_output.put_line(‘Employee Name –>’|| v_ename);
Close Cur_ename;
end;
/

Output would be

Employee Name –>Bill

In the above example Cur_ename is associated with SQL which will return only one record. So only one fetch is required here.

2.  Cursor example with multiple record

declare
v_ename varchar2(20);
Cursor Cur_ename is select empname from emp;
begin
Open Cur_ename;
loop
Fetch Cur_ename into v_ename;
exit when Cur_ename%notfound;
dbms_output.put_line(‘Employee Name –>’|| v_ename);
end loop;
Close Cur_ename;
end;
/

Output would be

Employee Name –>Bill
Employee Name –>Solomon
Employee Name –>Susan
Employee Name –>Wendy
Employee Name –>Benjamin
Employee Name –>Tom
Employee Name –>Henry
Employee Name –>Robert
Employee Name –>Paul

In general

OPEN cursor
Fetch first record and print the variables
Fetch second record and print the variable
Fetch third record and print the variable
….
Fetch last record and print the variable
CLOSE cursor

set serveroutput on;
--IMPLICIT CURSOR SIMPLE EXAMPLE
DECLARE
  v_ename emp.ename%type;
BEGIN
  SELECT ename INTO v_ename FROM emp WHERE empno=8888;
  DBMS_OUTPUT.PUT_LINE('Employee name is : '||v_ename);
END;
--IMPLICIT CURSOR Using SQL%ROWCOUNT
DECLARE
  v_total_rows NUMBER(3);
BEGIN
  DELETE FROM EMP WHERE DEPTNO=11;
   /* PL/SQL will use an implicit cursor to process the above statemnt*/
  v_total_rows := SQL%ROWCOUNT;
  /*Implicit Atrribute %ROWCOUNT is used to find the number of rows affected by the DELETE command */
  DBMS_OUTPUT.PUT_LINE('Total records deleted : '||v_total_rows);
END;
SELECT * FROM EMP;
--IMPLICIT CURSOR 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');
      ELSE
          DBMS_OUTPUT.PUT_LINE('RECORD NOT FOUND');
  END IF;
  END;
--SELECT INTO
DECLARE
  V_ENAME EMP.ENAME%TYPE;
 BEGIN
  SELECT ENAME INTO V_ENAME FROM emp WHERE empno = 1;
  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('RECORD FOUND');
      END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('RECORD NOT FOUND');
  END;
--id no record found then error is raised IN CASE OF SELECT INTO
/*Error report -
ORA-01403: no data found
ORA-06512: at line 5
01403. 00000 -  "no data found"*/
----SELECT INTO AGGREGATE FUNCTIONS
DECLARE
  V_TOTAL_SAL EMP.SAL%TYPE;
 BEGIN
  SELECT SUM(SAL) INTO V_TOTAL_SAL FROM emp;
  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('RECORD FOUND -TOTAL SAL IS : '||V_TOTAL_SAL);
      ELSE
          DBMS_OUTPUT.PUT_LINE('RECORD NOT FOUND -TOTAL SAL IS : '||V_TOTAL_SAL);
  END IF;    
  END;
--EXPLICIT CURSOR
DECLARE
  --STEP 1 : Cursor Declaration
  CURSOR empcur IS SELECT empno,ename FROM EMP;
  v_empno emp.EMPNO%TYPE;
  v_ename emp.ENAME%TYPE;
BEGIN
  OPEN empcur;--STEP 2 : 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_ename;
      EXIT WHEN empcur%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_empno ||' '||v_ename);
    END LOOP;
  CLOSE empcur;--STEP 4 CLOSING A CURSOR
END;
--isopen
declare
 cursor c1 is
   select last_name, salary from employees
   where rownum < 11;
 v1 employees.last_name%type;
 v2 employees.salary%type;
begin
 if not c1%isopen then
    open c1;
 end if;
 
 fetch c1 into v1, v2;
 
 if c1%isopen then
  DBMS_OUTPUT.PUT_LINE ('cursor is open , now closing it...');
   close c1;
 end if;
end;

---
Declare
--STEP 1 : Cursor Declaration
CURSOR c_emp IS  SELECT empno, ename FROM emp;
v_empno emp.empno%type;
v_ename emp.ename%type;
BEGIN
--STEP 2 : OPEN THE CURSOR FOR A QUERY
OPEN c_emp;  
--STEP 3 FETCH THE VALUES FROM TABLE RESULTS INTO PL/SQL VARIABLES
FETCH c_emp INTO v_empno,v_ename;
if c_emp%FOUND=true then
DBMS_OUTPUT.PUT_LINE ('RECORD FOUND');
elsif c_emp%FOUND=false then
DBMS_OUTPUT.PUT_LINE ('RECORD NOT FOUND');
else
DBMS_OUTPUT.PUT_LINE ('NULL RECORD');
end if;

WHILE c_emp%FOUND loop
DBMS_OUTPUT.PUT_LINE (v_empno || ', ' || v_ename);
FETCH c_emp INTO v_empno,v_ename;    
if c_emp%found=true then
DBMS_OUTPUT.PUT_LINE ('RECORD FOUND');
elsif c_emp%found=false then
DBMS_OUTPUT.PUT_LINE ('RECORD NOT FOUND');
else
DBMS_OUTPUT.PUT_LINE ('NULL RECORD');
end if;
END LOOP;
--STEP 4 CLOSING A CURSOR
CLOSE c_emp;
END;
--Explicit Cursor: using %TYPE and RECORD
DECLARE
  --DEFINING A TYPE RECORD
    TYPE t_emprec IS RECORD
    (
        ename emp.ENAME%TYPE,
        sal emp.SAL%TYPE,
        jobId emp.JOB%TYPE  
    );
    r_emp t_emprec;
    --DECLARING A CURSOR
    CURSOR cur_emp IS SELECT ENAME,SAL,JOB from emp;
BEGIN
--OPENING A CURSOR
    OPEN cur_emp;    
    LOOP
    --FETCHING
      FETCH cur_emp into r_emp;
      EXIT WHEN cur_emp%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(r_emp.ename||' , '||r_emp.sal||' , '||r_emp.jobId);
    END LOOP;
    --CLOSING
    CLOSE cur_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(v_empno || ':' ||v_ename|| ' , ' );
      FETCH c_emp INTO v_empno,v_ename;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(' ');
  CLOSE c_emp;
END;
--cursor with for loop
CREATE TABLE myBonus(
  myEno NUMBER(4),
  bonusAmt NUMBER(5)
);

DECLARE
bonus mybonus.bonusamt%type;
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;
select * from MYBONUS;

--FOR
DECLARE
      CURSOR  emp_dept_cur  IS
      SELECT empno,ename,sal,dept.deptno AS dno,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 SAL    :' || v_rec.sal);
      DBMS_OUTPUT.PUT_LINE('Employee Deptno   :' || v_rec.dno);
      DBMS_OUTPUT.PUT_LINE('Emp Dept Name :' || v_rec.dname);
     END LOOP;
END;
--PARAMETERIZED CURSOR

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;
--advance cursor ...wait ...nowait
declare
 cursor c1 is select * from emp where deptno=10 for update nowait;
 x c1%rowtype;
 begin
 open c1;
 loop
 fetch c1 into x;
 exit when c1%notfound;
 update emp set sal=sal +(x.sal *.25) where current of c1;
 end loop;
 end;
 -- wait
 declare
 cursor c1 is select * from emp where deptno=10 for update wait 12;
 x c1%rowtype;
 begin
 open c1;
 loop
 fetch c1 into x;
 exit when c1%notfound;
 update emp set sal=sal +(x.sal *.25) where current of c1;
 end loop;
 end;

 --WHERE CURRENT OF Clause
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;
              DBMS_OUTPUT.PUT_LINE('updated sal:'  || emp_record.sal);
      END LOOP;
      COMMIT;
END;
  -- cursor with subqueries
declare
  cursor c1 is
    select t1.deptno, dname, staff
    from dept t1,
         ( select deptno, count(*) as staff
           from emp
           group by deptno
          ) t2
    where (t1.deptno = t2.deptno)
      and staff >= 5
    order by staff;
begin
  for dept in c1 loop
    dbms_output.put_line ('Department = '
        || dept.dname || ', staff = ' || dept.staff);
  end loop;
end;