Learn about oracle Cursor with example
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)
Now prepare the pl-sql code,
C:\Users\DELL> ed count_sal
SQL> select * from grade;
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
%FOUND
Common attribute of implicit cursor
%ISOPEN
SQL%ISOPEN always has the value FALSE.%FOUND
SQL%FOUND has one of these values:- If no
SELECTor DML statement has run,NULL. - If the most recent
SELECTor DML statement returned a row,TRUE. - If the most recent
SELECTor DML statement did not return a row,FALSE.
SQL%NOTFOUND has one of these values:- If no
SELECTor DML statement has run,NULL. - If the most recent
SELECTor DML statement returned a row,FALSE. - If the most recent
SELECTor DML statement did not return a row,TRUE.
SQL%ROWCOUNT has one of these values:- If no
SELECTor DML statement has run,NULL. - If a
SELECTor 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:-
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,
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>
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,
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>
