Learn about oracle Cursor with example

A cursor is a pointer, which points towards a pre-allocated memory location in th SGA. 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.A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.

Note that if you do repetitive stuff inside a loop and you fail to close your cursors, you would soon run into the ORA-01000: maximum number of open cursors exceeded error.

There are two major types of cursors: Implicit cursors and explicit cursors. Again we can classify the cursor as static (normal cursor) and dynamic cursor (Reference cursor)

Implicit cursor

PL/SQL declares an implicit cursor for every DML command, and queries that return a single row. The name of the implicit cursor is SQL. You can directly use this cursor without any declaration. Implicit cursor are used for DML statements and you have no control over them. The cursor is opened at the point in time when the SQL statement is run by Oracle, the data is fetched if the statement is a query, and the cursor is closed as soon as the SQL statement finishes - all automatically.

You can however access the results of implicit cursors (such as how many rows were updated or whether or not any rows were found) as well as the data fetched from Oracle by an implicit cursor for a SELECT statement.

The process of an implicit cursor is as follows:-

• Whenever an SQL statement is executed, any given PL/SQL block issues an implicit cursor, as long as an explicit cursor does not exist for that SQL statement.
• A cursor is automatically associated with every DML statement (UPDATE, DELETE, and INSERT).PL/SQL employs an implicit cursor for each UPDATE, DELETE, or INSERT statement you execute in a program. You cannot, in other words, execute these statements within an explicit cursor, even if you want to. You have a choice between using an implicit or explicit cursor only when you execute a single-row SELECT statement (a SELECT that returns only one row).
• All UPDATE and DELETE statements have cursors those recognize the set of rows that will be affected by the operation.
• An INSERT statement requires a place to accept the data that is to be inserted in the database; the implicit cursor fulfills this need.
• The most recently opened cursor is called the “SQL%” Cursor.

For example:-

In the following UPDATE statement, which gives everyone in the company a 10% raise, PL/SQL creates an implicit cursor to identify the set of rows in the table which would be affected by the update:

UPDATE employee SET salary = salary * 1.1;

The following single-row query calculates and returns the total salary for a department. Once again, PL/SQL creates an implicit cursor for this statement:

SELECT SUM (salary) INTO department_total FROM employee

 WHERE department_number = 10;

Thus the implicit cursor is used to process INSERT, UPDATE, DELETE, and SELECT INTO statements. Oracle automatically performs the OPEN, FETCH, and CLOSE operations, during the processing of an implicit cursor.

Common attribute of implicit cursor
%ISOPEN
SQL%ISOPEN always has the value FALSE.
%FOUND
SQL%FOUND has one of these values:

• If no SELECT or DML statement has run, NULL.
• If the most recent SELECT or DML statement returned a row, TRUE.
• If the most recent SELECT or DML statement did not return a row, FALSE.

%NOTFOUND
SQL%NOTFOUND has one of these values:

• If no SELECT or DML statement has run, NULL.
• If the most recent SELECT or DML statement returned a row, FALSE.
• If the most recent SELECT or DML statement did not return a row, TRUE.

%ROWCOUNT
SQL%ROWCOUNT has one of these values:

• If no SELECT or DML statement has run, NULL.
• If a SELECT or DML statement has run, the number of rows fetched so far.

Here's a sample piece of Oracle PL/SQL code that uses an implicit cursor for a  SELECT statement.

Example.1

Note that the only variable declared was a record (gra) to hold the employee data obtained from the database and the only difference between our select statement and one we would issue using SQL*Plus is the INTO clause.

Example.2

If you can used any DML statement in your pl-sql code. At last you use SQL cursor name with its attributes so that you will meaning full information. Like SQL%ROWCOUNT which gives how many rows are affected by this plsql block.
Consider the PL/SQL Block that uses implicit cursor attributes as shown below:-

2.Explicit Cursor

Explicit cursors are defined explicitly (using the keyword CURSOR) and used only for SQL SELECT statements. Implicit cursors are used for both DML statements (UPDATE, INSERT, DELETE) and SELECT statements. PL/SQL’s implicit cursor can handle only single-row queries. If you ever need to select more than one row using SELECT in PL/SQL then you have to use explicit cursor.

The process of working with an explicit cursor consists of the following steps:

•          DECLARING the cursor. This initializes the cursor into memory.

•          OPENING the cursor. The previously declared cursor can now be opened; memory        is allotted.  

•          FETCHING the cursor. The previously declared and opened cursor can now retrieve data; this is the process of fetching the cursor.

•          CLOSING the cursor. The previously declared, opened, and fetched cursor must  now be closed to release memory allocation

Example.1

I created a table named salary and inserted some records as shown.Now i created a procedure to insert records into salary table so that rollno should take accordingly. The procedure utilize the cursor to take the maximum of rollno from salary table .

SQL> create table salary(

rollno number(5),empname varchar(15),

design varchar(15),

bpay number(7,2),

da number(6,2),

ta number(6,2),

pf number(6,2),

netsal number(8,2));

Insert the records by,

SQL> insert into salary values ( &rollno, '&empname', '&design', &bpay, &da, &ta, &pf, &netsal);

SQL> select * from salary;

    ROLLNO EMPNAME     DESIGN                  BPAY         DA         TA         PF     NETSAL

  ---------- --------------- ---------------           ---------- ---------- ---------- ---------- ----------

     10001 S.Krishnan        HOD                     25000       1500       1200        700      27000

     10002 K.K.Omana       Asst.Manager         20000       1500       1200        700      22000

     10003 Anishkumar.K    Asst.Manager         20000       1500       1200        700      22000

     10004 Girishkumar.K   Asst.Manager         20000       1500       1200        700      22000

Now create the procedure as follows,

C:\Users\DELL> ed salinsert

declare  

cursor ins_c is select max(rollno) from salary;---> declare section

prollno salary.rollno%type;

pname salary.empname%type;

pdesign salary.design%type;

pbpay salary.bpay%type;

pda salary.da%type;

pta salary.ta%type;

ppf salary.pf%type;

pnetsal salary.netsal%type;

begin

open ins_c;  ---> opening the cursor

fetch ins_c into prollno; ---> fetching the cursor

close ins_c; ---> closing the cursor

prollno := prollno + 1;

pname := '&pname';

pdesign := '&pdesign';

pbpay := &pbpay;

pda := pbpay * 12/100;

pta := pbpay *11/100;

ppf := pbpay *8/100;

pnetsal := pbpay + pda + pta -ppf;

insert into salary values (prollno , pname , pdesign, pbpay , pda , pta , ppf , pnetsal );

end;

/

Now execute the sql,

SQL> @salinsert

Enter value for pname: Arunkumar.K

old  24: pname := '&pname';

new  24: pname := 'Arunkumar.K';

Enter value for pdesign: Programmer

old  25: pdesign := '&pdesign';

new  25: pdesign := 'Programmer';

Enter value for pbpay: 12000

old  26: pbpay := &pbpay;

new  26: pbpay := 12000;

PL/SQL procedure successfully completed.

SQL> select * from salary;

    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL

---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------

     10001 S.Krishnan      HOD                       25000       1500       1200        700      27000

     10002 K.K.Omana       Asst.Manager         20000       1500       1200        700      22000

     10003 Anishkumar.K    Asst.Manager         20000       1500       1200        700      22000

     10004 Girishkumar.K   Asst.Manager         20000       1500       1200        700      22000

     10005 Arunkumar.K     Programmer          12000       1440       1320        960      13800

SQL>

Example.2

I created a table named grade , Now i want to insert salary table's rollno , name and their

grade accordig to their bpay into grade table.

SQL> create table grade (

rollno number(5),

name varchar(15),

grade char(1));

Now prepare the pl-sql code,

C:\Users\DELL> ed gradeinsert

declare

cursor crr is select rollno,empname,bpay from salary;

prollno salary.rollno%type;

pname salary.empname%type;

pbpay salary.bpay%type;

pgrade grade.grade%type;

begin

open crr;

loop

fetch crr into prollno,pname,pbpay;

exit when crr%notfound;

if pbpay > 20000 then

   pgrade := 'A';

elsif pbpay > 15000 then

   pgrade := 'B';

else

   pgrade := 'C';

end if;

insert into grade values (prollno,pname,pgrade);

end loop;

close crr;

end;

/

SQL> @gradeinsert

PL/SQL procedure successfully completed.

SQL> select * from grade;

    ROLLNO NAME            G

    ---------- --------------- -

     10001 S.Krishnan      A

     10002 K.K.Omana       B

     10003 Anishkumar.K    B

     10004 Girishkumar.K   B

     10005 Arunkumar.K     C

Example.3

This example shows the cursor to count the number of lines

C:\Users\DELL> ed count_sal

declare

        cursor c_countEmps is select count(*) from salary;

        v_out NUMBER;

    begin

        open c_countEmps;

        fetch c_countEmps into v_out;

        close c_countEmps;

       DBMS_OUTPUT.put_line('number of emps is:'||v_out);

   end;

/

SQL> set serveroutput on

SQL> @count_sal

number of emps is:5

PL/SQL procedure successfully completed.
SQL>

Parameterised cursors

PL/SQL Parameterized cursor pass the parameters into a cursor and use them in to query.Parameterizing the cursor makes it more usable and avoids the limitation of hard coding values in where the clause.

C:\Users\DELL>ed para_cursor

declare

cursor crr is select rollno,empname,bpay from salary

where rollno = &prollno; ----->> see the difference

prollno salary.rollno%type;

pname salary.empname%type;

pbpay salary.bpay%type;

pgrade grade.grade%type;

begin

open crr;

loop

fetch crr into prollno,pname,pbpay;

exit when crr%notfound;

if pbpay > 20000 then

   pgrade := 'A';

elsif pbpay > 15000 then

   pgrade := 'B';

else

   pgrade := 'C';

end if;

insert into grade values (prollno,pname,pgrade);

end loop;

close crr;

end;

/

SQL> delete from grade;

5 rows deleted.

SQL> commit;

Commit complete.

\SQL> select * from grade;

no rows selected

Now tun the sql,

SQL> @para_cursor

Enter value for prollno: 10003

old   4: where rollno = &prollno;

new   4: where rollno = 10003;

PL/SQL procedure successfully completed.

SQL> select * from grade;

    ROLLNO   NAME              G

     ---------- ---------------     -

     10003     Anishkumar.K    B

SQL>