PLSQL CURSOR

CURSOR IN DATABASE. TWO TYPES OF CURSOR. IMPLICIT CURSOR AND EXPLICIT CURSOR.

1. Prepared By JAVATECH Search us in The World

1

Mar. 30
PLSQL CURSOR

Cursor is a pointer, used to point individual rows in the table. It is associated each
rows from the database table. If we want to perform any operation on individual
rows from the table, we use cursor. Cursor contains more than one rows or single
which returns from sql statements. They are created by default when any DML
(inser, update and delete) operation performed on database. It is used to check
DML operation in database successful performed or not.
E.g.
Single row contains
Cursor c1 is select * from stude where roll=1;
Above cursor name ‘c1’ which contains one record of roll no. 1.
Multi Rows Contains
Cursor c1 is select * from student;
Above we have not specify condition so Cursor ‘c1’ contains all rows from table
student.
Cursor are two types
1. Implicit cursor
2. Explicit Cursor
Discuss about Implicit Cursor
No need to declare cursor keywords. Sql statements are executed in
personal sql work area. Cursor are used to point that work area
implicitly(Automatically). Then question is how we know that whether it points to
rows or not. For that to check from table we use some attributes of cursor.

2. Prepared By JAVATECH Search us in The World

2

Mar. 30


Attributes of Implicit Cursor
1. %Found :-It is Boolean attributes which returns either true or false. If recent
sql statement affects single row or multiple rows then it returns true.
2. %NotFound:- It is opposite to %Found attribute. If recent sql statement
doesn’t affect any rows it returns true.
3. %IsOpen :- Though it is Implicit cursor so after executed it automatically
closed immediate. If you use %IsOpen attribute it always returns false.
Because before IsOpen attributes cursor is already closed.
4. %RowCount:- It count number of rows affected in most recent sql
statements.
Discuss about Explicit Cursor
To keep control over the cursor we explicitly declared it with Cursor keyword. And
cursor should define in declaration statement. It is created with select statement,
that returns more than one rows or single rows.
Explicit Cursor declaration
Cursor c1 is select * from stude where roll=1; returns only one rows from table
Cursor c1 is select * from student; returns more thn one rows from table.

Following steps are used for Explicit cursor
1. Declare the cursor
2. Open the cursor
3. Fetching the cursor
4. Close the cursor

3. Prepared By JAVATECH Search us in The World

3

Mar. 30
Declare the cursor
The cursor declaration is specify in declare part of PLSQL. It is associated with
name and select statement.
E.g.
Declare
Cursor c1 is select roll,name,mark from student where roll=1;
Begin
End;

Above c1 is the name of cursor and that is stored single rows from table where
roll=1;
Open the cursor
Open cursor is used to active the cursor and after active it will allocate memory
for cursor. If it will not activate then it can’t fetch its value to specified variable.
E.g.
Open c1;
Fetching the Cursor
Fetching the cursor value to variable. It will fetch value from cursor at time one
row only. If you will not fetch then how you extract value from cursor.
E.g.
Fetch c1 into roll,name,mark;
After write above line you can find the value of cursor c1 populate(send) to roll,
name & mark variable.
Then You can extend your program like as
If mark > 30 then
Dbms_output.put_line(‘Student is passes’);

4. Prepared By JAVATECH Search us in The World

4

Mar. 30
Else
Dbms_output.put_line(‘Student is failed’);
End if;
Close Cursor
It is used to close the PLSQL cursor. After closed it free or de-allocate the
memory.
************STEPS ARE CLOSED*********
Difference Between Implicit Cursor & Explicit Cursor
SLNO Implicit Cursor Explicit Cursor
1 It automatically associated with
database Cursor
To take control over cursor we
declare it
2 No Need Cursor Keyword for declare It require declaration Like
“Cursor C1”
3 It can associated with Single/Multiple
Rows
It can associated with
Single/Multiple Rows
4 Though cursor has no name then to
check we use SQL keyword.
“SQL%Attribute” SQL%Found,
SQL%isOpen SQL%NotFound
SQL%RowCount
It has explicit cursor name c1. So
to check we use “C1%attribute”
C1%Found C1%NotFound
C1%IsOpen like this….
5 No required Requited Fetching
6 No Requied Required To Close
Prepared By JAVATECH Search us In the World

E.g. of Implicit Cursor. Remember it has no name and declaration. And to use
attributes of Implicit Cursor use the SQL keyword.

5. Prepared By JAVATECH Search us in The World

5

Mar. 30


Wap in PLSQL to delete given roll no which is not present in database. After DML
operation we can check status of DML operation by using Implicit cursor.

Wap in PLSQL to delete given roll no which is not present in database. After DML
operation we can check status of DML operation by using Explicit cursor.
Here we declare explicit cursor and follow 4 steps which previously defined. Read
it.

6. Prepared By JAVATECH Search us in The World

6

Mar. 30

Wap In PLSQL to display all rows from table by using Explicit cursor.

7. Prepared By JAVATECH Search us in The World

7

Mar. 30







SEE ALL NETWORKING PROGRAM AND NOTES
VISIT OUR PAGE IN FACEBOOK “JAVATECH 123”


VISIT OUR NEXT SLIDE EXCEPTION IN PLSQL

***************THANKING YOU************