Cursor : PLSQL creates a temporary area for the sql query, and that area is called cursor
Types of Cursors
- Implicit Cursor
- Explicit cursor
Explicit : Defining a cursor explicitly in declare section for the select statement
which can return one or more than one record is called explicit cursor.
Syntax : Declare
cursor cursor_name is select statement;
variable cursor_name%rowtype;
Example : Declare
cursor emp_cur is select eno,ename,sal from emp;
emp_Rec emp_cur%rowtype;
Explicit cursor attributes
- Cursor%NOTFOUND: Returns true when no records available in explicit cursor
- Cursor%FOUND : Returns true when records found in explicit cursor
- Cursor%ROWCOUNT: Returns no.of records available in explicit cursor
- CURSOR%ISOPEN : Returns true if the cursor is already opened
Explicit cursor statements
Open Cursor : It is used to open the cursor
fetch : It will fetch records from the cursor and assign to a variable
close cursor: It is used to close the cursor opened already
For : It is used to open, fetch and close the cursor automatically
Complete Syntax using open/fetch/close statement:
Declare
cursor cursor_name is select statement;
var cursor%rowtype; -- to assign all columns
var cursor.column%type; -- to assing a single column
begin
open <cursor>
loop
fetch cursor into variable
exit condition
end loop;
close <cursor>
end;=============================================================================================
Example: create a cursor to select all records from employee table
SQL> ed ec1.sql
DECLARE
cursor emp_cur is select eno,ename,sal from emp;
emp_rec emp_cur%rowtype;
— ln_ename emp_cur.ename%type; — for single column
ln_tax number;
DECLARE
cursor emp_cur is select eno,ename,sal from emp;
emp_rec emp_cur%rowtype;
— ln_ename emp_cur.ename%type; — for single column
ln_tax number;
BEGIN
open emp_cur;
dbms_output.put_line (‘ENO’||lpad(‘ENAME’,15,’ ‘)||’SAL TAX’);
dbms_output.put_line (‘——————————–‘);
loop
fetch emp_cur into emp_rec;
if emp_Rec.sal<10000 then
ln_tax := emp_rec.sal*5/100;
else
ln_tax := emp_rec.sal*7/100;
end if;
dbms_output.put_line (emp_rec.eno|| ' '||LPAD(emp_rec.ename,15,' ')||' '||emp_rec.sal||' '||ln_tax);
exit when emp_cur%notfound;
end loop;
close emp_cur;END;
SQL> @ ec1.sql
27 /
ENO ENAMESAL TAX
115 Chandu 28100 1967
105 Kumar 14200 994
111 Ram 14000 980
112 Ramesh 25000 1750
113 Krishna 30000 2100
114 Shekar 59100 4137
114 Shekar 59100 4137
Task1: Show all purchase order information along with vendor name
PONO Item QTY PRICE Total amt Vendor Id Vendor Name
———————————————–
