Cursor
is an active set area or a temporary working area. Which is used to retrieve the
values.
A cursor is a temporary work area created in the system
memory when a SQL statement is executed
Cursor
attributes:
%ISOPEN : Evaluates
true
when cursor is opened
Evaluates false when cursor is opened
%FOUND: Evaluates
true
when the rows are available.
Evaluates
false
when the rows are available.
%NOTFOUND : It is opposite to the FOUND
%ROWCOUNT: It will gives the number of rows available
Structure
of cursor:
Create
cursor
↓
Open
cursor
↓
Fetch
cursor values
↓
Close
cursor
Access Types:
%type : which is used to access the single row of the same data type.
%rowtype: which is used to access the
multiple rows of the different data types.
Types of
cursors:
There are
two types of cursors
1)
Implicit
Cursors: Which is used retrieve the
single row.
2)
Explicit
Cursors: Which is used to retrieve the
multiple rows.
Syntax:
Create : CURSOR cur_name is SELECT statement
Open : OPEN Cus_name;
Fetch : FETCH cus_name into <variables>
Close : CLOSE cus_name
Implicit cursors
Implicit cursors
These are created by default when DML
statements like, INSERT, UPDATE, and DELETE statements are executed. They are
also created when a SELECT statement that returns just one row is executed.
An implicit
cursor is a cursor which is internally created by Oracle. It is created
by Oracle for each individual SQL.
DECLARE var_rows number(5);
BEGIN
UPDATE employee SET salary = salary + 1000;
IF SQL%NOTFOUND THEN
dbms_output.put_line('None
of the salaries where updated');
ELSIF SQL%FOUND THEN
var_rows
:= SQL%ROWCOUNT;
dbms_output.put_line('Salaries
for ' || var_rows || 'employees are updated');
END IF;
END;
Explicit cursors:
They must be created when you are
executing a SELECT statement that returns more than one row. Even though the
cursor stores multiple records, only one record can be processed at a time,
which is called as current row. When you fetch a row the current row position
moves to next row.
Both implicit and explicit cursors have the
same functionality, but they differ in the way they are accessed.
Example:
DECLARE
Cursor c1 is select * from emp;
V_emp
c1%rowtpe;
BEGIN
Open c1;
IF c1%isopend
THEN
Loop
Fetch c1 into
v_emp;
Exit WHEN c1%notfound;
End loop;
END IF;
CLOSE C1;
END;
SELECT FOR UPDATE
Statement
The Select
For Update statement
allows you to lock the records in the cursor result set. You are not required
to make changes to the records in order to use this statement. The record locks
are released when the next commit or rollback statement is issued.
CURSOR cursor_name
IS
select_statement
FOR UPDATE [of column_list] [NOWAIT];
For example, you could use the Select
For Update statement as follows:
CURSOR c1
IS
SELECT course_number, instructor
from courses_tbl
FOR UPDATE of instructor;
IS
SELECT course_number, instructor
from courses_tbl
FOR UPDATE of instructor;
WHERE CURRENT OF Statement
if you plan on updating or
deleting records that have been referenced by a Select For Update statement, you can use the Where Current Of statement.
UPDATE table_name
SET set_clause
WHERE CURRENT OF cursor_name;
OR
DELETE FROM table_name
WHERE CURRENT OF cursor_name;
WHERE CURRENT OF cursor_name;
The Where Current Of statement allows you to update or
delete the record that was last fetched by the cursor.
Updating
using the WHERE CURRENT OF Statement
Here is an example where we
are updating records using the Where Current Of Statement:
CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;
( name_in IN varchar2 )
RETURN number
IS
cnumber number;
CURSOR c1
IS
SELECT course_number from courses_tbl where course_name = name_in
FOR UPDATE of instructor;
BEGIN
IS
SELECT course_number from courses_tbl where course_name = name_in
FOR UPDATE of instructor;
BEGIN
open c1;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;
else
UPDATE courses_tbl SET instructor = 'SMITH' WHERE CURRENT OF c1;
UPDATE courses_tbl SET instructor = 'SMITH' WHERE CURRENT OF c1;
COMMIT;
end if;
close c1;
close c1;
RETURN cnumber;
END;
Deleting using the WHERE
CURRENT OF Statement
Here is an example where we
are deleting records using the Where Current Of Statement:
CREATE OR REPLACE Function FindCours ( name_in IN varchar2 )
RETURN number
IS
cnumber number;
CURSOR c1
IS
SELECT course_number from courses_tbl where course_name = name_in
FOR UPDATE of instructor;
BEGIN
IS
SELECT course_number from courses_tbl where course_name = name_in
FOR UPDATE of instructor;
BEGIN
open c1;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;
else
DELETE FROM courses_tbl WHERE CURRENT OF c1;
DELETE FROM courses_tbl WHERE CURRENT OF c1;
COMMIT;
end if;
close c1;
close c1;
RETURN cnumber;
END;
