Cursor is a location in the memory where the SQL statement is processed. That is when ever we send an SQL statement; the system allocates some memory by giving a name. After processing the statement it automatically cleans that memory. There are four steps, which are processed by the system whenever we send an SQL statement they are
1.Creating cursor : This step allocates memory
2.Opening cursor :
In this step process the sql statement
3.Fetching cursor:
The values satisfied by the sql statement are fetched from the
table into cursor row by row
4.Closing cursor : This statement close the memory allocated for the cursor
Types Of Cursors
There Are Two Types Of Cursors
1. Implicit cursors or simple cursors
2. Explicit Cursors
Implicit Cursors
Oracle implicitly opens a cursor to process each sql statement not associated with explicitly declared cursor. Pl/sql lets you refer to the most recent implicit cursors as the sql cursor.
Implicit cursors have four attributes, you can use in your program
- sql%rowcount
It returns the number of rows affected by an insert, update or delete
- sql%found
This attributes evaluates to true, if an insert or update or delete
affected to one or more rows. It evaluates to false if no row is affected
- sql%notfound
This attributes is logical opposite to sql%found. It evaluates to true,
if an insert or update or delete does not affected to any row. It evaluates to
false if any one row is affected
- %isopen
It checks whether the cursor has been opened or not
Example : 1
write a program to check whether there is atleast one row satisfying the given select statement or not using implicit cursors
Declare
e emp%rowtype;
Begin
select * into e from emp where empno=&empno;
if sql%found then
dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');
dbms_output.put_line(' Record Found ');
dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');
End if;
End;
/
Example : 2
write a program to change the ename column values to upper case
using implicit cursors
begin
update employee set ename=upper(ename);
if sql%found then
dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');
dbms_output.put_line(sql%rowcount || ' Records Updated ');
dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');
else
dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');
dbms_output.put_line(' Sorry Changes Are not Made ');
end if;
end;
/
Example : 3
write a program to update the salary of those employees to 20% whose salary is greater than the given salary, using implicit cursors
begin
update employee set sal=sal+(sal*20)/100 where sal>&sal;
if sql%found then
dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');
dbms_output.put_line(sql%rowcount || ' Records Updated ');
dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');
end if;
if sql%notfound then
dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');
dbms_output.put_line(' Sorry No Record is Satisfied ');
end if;
end;
/
Explicit Cursors
The user creates these cursors for applying 2 or more DML operations on a table. using a cursor we can fetch all the row values from the table into a variable for printing them or performing some action with them
In an explicit cursor, there are four steps related to execute cursors by the user. It is also used to compare a variable value with all values of the column.
The four steps are
1. Declaring a Cursor
2. Opening a Cursor
3. Fetching a Cursor
4. Closing a Cursor
Declaring a Cursor
A cursor is defined in the declarative part of the PL/SQL block by naming it and associating it with a query
Syntax :
Declare
Cursor <cursor_name> is <select Statement>
Opening a Cursor
The cursor must be initialized or opened with the open statement. Opening the cursor excutes the query and identifies the result set which consists of all the rows that meets the query search criteria
Syntax :
Declare
Cursor <cursor_name> is <select Statement>
Begin
Open <cursor Name>
-----------
End;
Fetching With a Cursor
This statement is used to retrieve the cursors current row. Fetching can be excuted repeatedly until all rows have been retrieved
Syntax :
Declare
Cursor <cursor_name> is <select Statement>
Begin
Open <cursor Name>
Fetch <cursor Name> into <variable1>,<variable2>,----
-----------
End;
Closing a Cursor
When the last row has been processed, the cursor is closed with the close statement. The close statement disable the cursor
Syntax :
Declare
Cursor <cursor_name> is <select Statement>
Begin
Open <cursor Name>
Fetch <cursor Name> into <variable1>,<variable2>,----
-----------
Close <cursor Name>
End;
Examples
1. Write a program using an explicit cursor, which displays all the rows from the table
Declare
Cursor c1 is select * from emp;
e emp%rowtype;
Begin
Open c1;
Loop
Fetch c1 into e;
Dbms_output.put_line(e.empno||' '||e.ename||' '||e.sal);
Exit when c1%notfound;
End loop;
Close c1;
End;
/
2.Write a program using an explicit cursor, to insert values into a table empc from emp table, whose experience is >=23 years
Declare
cursor c2 is select * from emp where (sysdate-hiredate)/365>=23;
e emp%rowtype;
x number:=0;
begin
open c2;
loop
fetch c2 into e;
exit when c2%notfound;
insert into empc values(e.empno,e.ename,e.job,e.sal,e.hiredate);
x:=x+sql%rowcount;
end loop;
dbms_output.put_line(x||' Records inserted Successfully....');
close c2;
end;
/
3.Write a program using an explicit cursor, to display first 5 records
Declare
cursor c3 is select * from emp;
e emp%rowtype;
x number;
begin
open c3;
loop
fetch c3 into e;
exit when c3%rowcount>5;
dbms_output.put_line(e.empno||' '||e.ename||' '||e.sal);
end loop;
close c3;
end;
/
Cursor For Loop
A cursor for loop implicitly declares its loop index as a %rowtype, opens a cursor, repeatedly fetches the rows of values from the result set into fields in the records and closes the cursor when all rows have been processed or exit command is encountered
Syntax :
Declare
Cursor <cursor_name> is <select statement>
Begin
For <index> in <cursor name> loop
---------
end loop;
end;
/
Example : 1
Write a program to display all employee’s details working in the accepted department number using cursor for loop
declare
cursor c1 is select * from emp where deptno=&deptno;
e c1%rowtype;
begin
dbms_output.put_line('****************');
for e in c1 loop
dbms_output.put_line(e.empno||' '||e.ename||' '||e.job||' '||e.sal);
end loop;
dbms_output.put_line('****************');
end;
/
Example : 2
Write a program to display all employee’s details working in the accepted department number and also display all department details for the given department number using cursor for loops
declare
cursor c1 is select * from emp where deptno=&deptno;
e c1%rowtype;
cursor c2 is select * from dept where deptno=&deptno;
d c2%rowtype;
begin
dbms_output.put_line(' ****Employees Details ***** ');
for e in c1 loop
dbms_output.put_line(e.empno||' '|| e.ename||' '||e.job||' '||e.sal);
end loop;
dbms_output.put_line(' ****Department Details ***** ');
for d in c2 loop
dbms_output.put_line(d.dname||' '||d.loc
||' '||d.deptno);
end loop;
end;
/
No comments:
Post a Comment
Thank you :
- kareem