Cursor is a memory locations which is used to run SQL commands.
There are two types cursors
1) Implicit Cursors
2) Explicit Cursors
1) Implicit Cursors:
----------------------------
All the activited related to cursor like i) Opening the cursor ii) Processing the data in the cursor iii) closing the cursor
are done automatically.
Hence these cursors are called Implict cursors.
Implicit Cursor Attributes:
---------------------------------------
There are four Implicit cursor attributes
1) SQL%ISOPEN
2) SQL%FOUND
3) SQL%NOTFOUND
4) SQL%ROWCOUNT
1) SQL%ISOPEN:
--------------------------
It is a boolean attribute. It always returns false. It is not used in programming as it always returns false.
2) SQL%FOUND:
-----------------------------
It is a boolean attribute.
Returns TRUE -- if the SQL command effects the data.
Returns FALSE -- if the SQL commands do not effect the data.
3) SQL%NOTFOUND:
--------------------------------
It is a boolean attribute
Returns TRUE -- if the SQL command do not effect the data.
Returns FALSE -- if the SQL command effects the data
Note: It is exactly negation to SQL%FOUND
4) SQL%ROWCOUNT:
-------------------------------
Returns no of rows effected by the SQL command.
Using SQL%FOUND:
-----------------------------
Begin
Update emp set sal=2000
where empno=1111;
end;
/
By looking at the above message, we cannot know whether your update command is effecting the data or not.
To overcome this problem, we have SQL%FOUND attribute.
Have a look at this program
Begin
Update emp set sal=2000
where empno=1111;
if SQL%FOUND then
dbms_output.put_line('Update is successfull');
else
dbms_output.put_line('Update is failed');
end if;
end;
/
Output:
------------
Update is failed.
PL/SQL Procedure successfully completed.
Using SQL%NOTFOUND:
------------------------------
SQL%NOTFOUND is exactly opposite to SQL%FOUND.
We rewrite the above program using SQL%NOTFOUND
Begin
Update emp set sal=2000
where empno=1111;
if SQL%NOTFOUND then
dbms_output.put_line('Update is failed');
else
dbms_output.put_line('Update is successful');
end if;
end;
/
Output:
------------
Update is failed.
PL/SQL Procedure successfully completed.
Using SQL%ROWCOUNT:
-----------------------------------
SQL%ROWCOUNT attribute is used to find the no of rows effected by SQL command.
begin
update emp set sal=2000
where deptno=10;
dbms_output.put_line(SQL%ROWCOUNT||' rows updated');
end;
/
Output:
-----------
3 rows updated.
Note: As a developer, we cannot control the implicit cursor.
We can you these implicit cursor attributes to know whether the command is effecting the data or not.
Explicit Cursors:
-----------------------
Explicit cursors are used to run select stmt which returs more than one row in a PL/SQL block
Steps to use Explicit cursors:
------------------------------------
Step 1: Declare the cursor
Step 2: Open the cursor
Srep 3: Fetch the data from the cursor to the local variables
Step 4: close the cursor
Syntax of the above four steps:
-----------------------------------------------
Step 1: Declaring the cursor
cursor < cursor_name>
is < select stmt >;
step 2: Open the cursor
open < cursor_name >;
step 3: Fetch the data from the cursor to the local variables
1) %ISOPEN:
--------------------
It is a boolean attribute.
Returns TRUE -- if the cursor is open
Returns FALSE -- if the cursor is closed
2) %FOUND:
------------------
It is a boolean attribute
Returns TRUE -- if the fetch stmt is successfull
Returns FALSE -- if the fetch stmt fails
3) %NOTFOUND:
--------------------------
It is boolean attribute
Returns TRUE -- if the fetch stmt fails.
Returns FALSE -- if the fetch stmt is successfull
Note: 1) It is exactly opposite to %FOUND attribute
2) This attribute is used to break the loop of the fetch stmt.
4) %ROWCOUNT:
---------------------------
Returns no of rows fetched by the fetch stmt.
Example of Explicit cursor:
-----------------------------------
Write a PL/SQL block to display ename and sal of employees working in deptno no
Declare
cursor c1
is select ename , sal from emp
where deptno=10;
l_ename emp.ename%type;
l_sal emp.sal%type;
begin
open c1;
loop
fetch c1 into l_ename , l_sal;
exit when c1%notfound;
dbms_output.put_line( l_ename||'....'||l_sal);
end loop;
close c1;
end;
/
Output:
------------
CLARK 2450
KING 5000
MILLER 1300
Pl/SQL Proceudure successfully completed.
Ex2: Write a PL/SQL procedure to display dname , loc from dept table
Declare
cursor c1
is select dname , loc from dept;
l_dname dept.dname%type;
l_loc dept.loc%type;
begin
open c1;
loop
fetch c1 into l_dname, l_loc;
exit when c1%notfound;
dbms_output.put_line(l_dname||'.....'||l_loc);
end loop;
close c1;
end;
/
Output:
--------------
Accounting New York
Research Dallas
Sales Chicago
Operations Boston
Pl/SQL Procedure successfully completed.
Cursor For loops:
------------------------
It is shortcut way of writing explicit cursors.
When we use cursor for loops , following steps are not required.
1) Open the cursor
2) Fetch stmt
3) exit when condition
4) closing the cursor
5) declaring the local variables
Ex:
----------
Write a PL/SQL block which display ename and sal of employees working in deptno 10
Declare
cursor c1
is select ename , sal from emp
where deptno=10;
begin
for emp_rec in c1 loop
dbms_output.put_line(emp_rec.ename||'.....'||emp_rec.sal);
end loop;
end;
/
Output:
--------------
CLARK 2450
KING 5000
MILLER 1300
Pl/SQL Proceudure successfully completed.
Note: In the above program emp_rec in implicitly declared record variable,
which is capable of storing one row of the cursor.