Cursor
The Oracle Engine uses a work area for its internal processing in order to execute an SQL statement. This work area is private to SQL’s operations and called a Cursor.
Two Types Cursor
A) Implicit: Declared for all DML and PL/SQL SELECT statements
B) Explicit: Declared and named by the programmer.
Controlling Explicit Cursors
1. Open the Cursor
2. Fetch a row.
3. Close the Cursor
-Declaring the Cursor
CURSOR emp_cursor is Select empno,ename from emp ;
-Opening the Cursor
OPEN emp_cursor ;
-Fetching Data from the Cursor
Fetch emp_cursor into v_empno,V_ename,V_sal;
-Closing the Cursor
Close Emp_cursor ;
Explicit Cursor Attributes
%ISOPEN - Evaluates TRUE if the Cursor Open
%NOTFOUND - Evaluates TRUE if the most resent fetch does not return a row
%FOUND - Evaluates TRUE if the most recent fetch return a row
%ROWCOUNT - Evaluates to the total number of rows returned so far.
Examples
Using BASIC LOOP
declare
v_empno emp.empno%type;
v_sal emp.sal%type ;
v_ename emp.ename%type ;
cursor emp_cursor is select empno,ename,sal from emp ;
begin
Open emp_cursor ;
LOOP
fetch emp_cursor into v_empno,V_ename,v_sal ;
dbms_output.put_line('The Employee No: ' ||v_empno||' and name is ' ||v_ename|| ' The salary is' ||v_sal);
exit WHEN emp_cursor%rowcount > 10 or emp_cursor%notFOUND;
End LOOP ;
end;
Using FOR LOOP
declare
v_empno emp.empno%type;
v_sal emp.sal%type ;
cursor emp_cursor is select empno,sal from emp;
begin
open emp_cursor;
for i in 1..10 loop
fetch emp_cursor into v_empno,v_sal;
dbms_output.put_line (v_empno||' '||v_sal);
end loop;
Close emp_cursor;
end;
Same Program using %notfound (Implicit Cursor )
declare
v_empno emp.empno%type;
v_sal emp.sal%type ;
cursor c1 is select empno,sal from emp;
begin
open c1;
for i in 1..10 loop
fetch c1 into v_empno,v_sal;
exit when c1%notfound;
dbms_output.put_line (v_empno||' '||v_sal);
end loop;
end;
----------------------------------------------------------------------------
declare
mename emp.ename%type ;
msal emp.sal%type ;
mjob emp.job%type;
mempno emp.empno%type;
cursor c1 is select empno,ename,job,sal from emp ;
begin
open c1;
loop
fetch c1 into mempno,mename,mjob,msal;
if msal <>
update emp set sal=msal*2 where empno=mempno;
dbms_output.put_line(mempno||' '||mename||' '||msal*2||' '||mjob) ;
elsif msal between 2000 and 4000 then
update emp set sal=msal*.5 where empno=mempno;
dbms_output.put_line(mempno||' '||mename||' '||msal*.5||' '||mjob) ;
elsif msal > 4000 then
update emp set sal=msal*.2 where empno=mempno;
dbms_output.put_line(mempno||' '||mename||' '||msal*.2||' '||mjob) ;
end if ;
exit when c1%notfound ;
end loop ;
close c1 ;
end ;
FOR LOOP
Declare
cursor c1 is select empno,ename,deptno from emp;
begin
for emp_record in c1 loop
if emp_record.deptno=20 then
dbms_output.put_line('Employee No ' ||emp_record.empno ||' Name is '|| emp_record.ename);
end if;
end loop;
end;
Implicit Cursor Attributes
SQL%ISOPEN - Is always false because Oracle automatically close an Implicit cursor after executing its SQL statement
SQL%NOTFOUND - Evaluates TRUE if the DML statement was not suceesful
%FOUND - Evaluates TRUE if the DML statement was suceesful
%ROWCOUNT – Returned to the total number of rows affected by an INSERT,UPDATE, DELETE or single row SELECT
OPEN, FETCH, CLOSE : cannot be used to maipulating the implicit cursor SQL
Cursor with parameter
-Pass parameter values to a cursor when the cursor is opened and the query is executed
-Open an explicit cursor several times with a different active set each time.
Syntax
CURSOR cursor_name (parameter name data type) is select statement
Begin
Open cursor_name (parameter values)
End ;
- Paremeter data types are the same as scalar data type but do not give them sizes.
Example:
declare
mename emp.ename%type;
mempno emp.empno%type;
cursor emp_cursor (mdeptno number, mjob varchar2) is select empno,ename from emp where
deptno=mdeptno and job=mjob;
begin
for emp_record in emp_cursor (30,'SALESMAN')
loop
fetch emp_cursor into mempno,mename ;
dbms_output.put_line(mename||' '||mempno);
end loop ;
end;
---------------------------------
Cursor with Subqueries
Subquery returns a value or set of values to outer query.
Syntax
Cursor emp_cur is select empno, ename, sal from EMP where deprno in (select deptno from dept)
Defining REF Cursor Type
- Explicit cursor is a static cursor
- It is always refers only one work area in memory
- REF CURSOR is dynamic cursor
- It is like a pointer in C
- It refer different work area in memory at run time more than one SQL statement can be associated to ref cursor at run time.
- Cursor variable is like a pointer define with REF type
Types of REF CURSOR
STRONG REF CURSOR – the REF CURSOR which is return type
WEAK REF CURSOR – the REF CURSOR without return type
- Define a REF cursor type:
Type ref_type_name is REF CURSOR
- Declare a variable of the type
ref_cv ref_type_name ;
STRONG REF CURSOR
Declare
Type ref_c is ref cursor return emp%rowtype;
C1 ref_c ;
Vrec emp%rowtype;
Vdno number(2) := &vdno ;
Begin
If vdno =10 then
Open c1 for select * from emp where job=’MANAGER’ ;
Elsif vdno = 20 then
Open c1 for select * from emp where job=’SALESMAN’ ;
Else
Open c1 for select * from emp where job=’CLERK’ ;
End if;
Loop
Fetch c1 into vrec ;
Exit when c1%notfound;
dbms_output.put_line(vrec.empno||’ ‘||vrec.ename) ;
end loop;
close c1;
end;
WEAK REF CURSOR
declare
v_empno emp.empno%type;
v_sal emp.sal%type ;
v_ename emp.ename%type ;
mdname dept.dname%type;
type r is ref cursor;
emp_cursor r;
begin
Open emp_cursor for select empno,ename,sal from emp ;
LOOP
fetch emp_cursor into v_empno,V_ename,v_sal ;
dbms_output.put_line('The Employee No: ' ||v_empno||' and name is ' ||v_ename|| ' The salary is ' ||v_sal);
exit WHEN emp_cursor%rowcount > 10 or emp_cursor%notFOUND;
End LOOP ;
Open emp_cursor for select dname from dept ;
LOOP
fetch emp_cursor into mdname;
dbms_output.put_line('The dept name is ' ||mdname);
exit WHEN emp_cursor%rowcount > 10 or emp_cursor%notFOUND;
End LOOP ;
end;
The Oracle Engine uses a work area for its internal processing in order to execute an SQL statement. This work area is private to SQL’s operations and called a Cursor.
Two Types Cursor
A) Implicit: Declared for all DML and PL/SQL SELECT statements
B) Explicit: Declared and named by the programmer.
Controlling Explicit Cursors
1. Open the Cursor
2. Fetch a row.
3. Close the Cursor
-Declaring the Cursor
CURSOR emp_cursor is Select empno,ename from emp ;
-Opening the Cursor
OPEN emp_cursor ;
-Fetching Data from the Cursor
Fetch emp_cursor into v_empno,V_ename,V_sal;
-Closing the Cursor
Close Emp_cursor ;
Explicit Cursor Attributes
%ISOPEN - Evaluates TRUE if the Cursor Open
%NOTFOUND - Evaluates TRUE if the most resent fetch does not return a row
%FOUND - Evaluates TRUE if the most recent fetch return a row
%ROWCOUNT - Evaluates to the total number of rows returned so far.
Examples
Using BASIC LOOP
declare
v_empno emp.empno%type;
v_sal emp.sal%type ;
v_ename emp.ename%type ;
cursor emp_cursor is select empno,ename,sal from emp ;
begin
Open emp_cursor ;
LOOP
fetch emp_cursor into v_empno,V_ename,v_sal ;
dbms_output.put_line('The Employee No: ' ||v_empno||' and name is ' ||v_ename|| ' The salary is' ||v_sal);
exit WHEN emp_cursor%rowcount > 10 or emp_cursor%notFOUND;
End LOOP ;
end;
Using FOR LOOP
declare
v_empno emp.empno%type;
v_sal emp.sal%type ;
cursor emp_cursor is select empno,sal from emp;
begin
open emp_cursor;
for i in 1..10 loop
fetch emp_cursor into v_empno,v_sal;
dbms_output.put_line (v_empno||' '||v_sal);
end loop;
Close emp_cursor;
end;
Same Program using %notfound (Implicit Cursor )
declare
v_empno emp.empno%type;
v_sal emp.sal%type ;
cursor c1 is select empno,sal from emp;
begin
open c1;
for i in 1..10 loop
fetch c1 into v_empno,v_sal;
exit when c1%notfound;
dbms_output.put_line (v_empno||' '||v_sal);
end loop;
end;
----------------------------------------------------------------------------
declare
mename emp.ename%type ;
msal emp.sal%type ;
mjob emp.job%type;
mempno emp.empno%type;
cursor c1 is select empno,ename,job,sal from emp ;
begin
open c1;
loop
fetch c1 into mempno,mename,mjob,msal;
if msal <>
update emp set sal=msal*2 where empno=mempno;
dbms_output.put_line(mempno||' '||mename||' '||msal*2||' '||mjob) ;
elsif msal between 2000 and 4000 then
update emp set sal=msal*.5 where empno=mempno;
dbms_output.put_line(mempno||' '||mename||' '||msal*.5||' '||mjob) ;
elsif msal > 4000 then
update emp set sal=msal*.2 where empno=mempno;
dbms_output.put_line(mempno||' '||mename||' '||msal*.2||' '||mjob) ;
end if ;
exit when c1%notfound ;
end loop ;
close c1 ;
end ;
FOR LOOP
Declare
cursor c1 is select empno,ename,deptno from emp;
begin
for emp_record in c1 loop
if emp_record.deptno=20 then
dbms_output.put_line('Employee No ' ||emp_record.empno ||' Name is '|| emp_record.ename);
end if;
end loop;
end;
Implicit Cursor Attributes
SQL%ISOPEN - Is always false because Oracle automatically close an Implicit cursor after executing its SQL statement
SQL%NOTFOUND - Evaluates TRUE if the DML statement was not suceesful
%FOUND - Evaluates TRUE if the DML statement was suceesful
%ROWCOUNT – Returned to the total number of rows affected by an INSERT,UPDATE, DELETE or single row SELECT
OPEN, FETCH, CLOSE : cannot be used to maipulating the implicit cursor SQL
Cursor with parameter
-Pass parameter values to a cursor when the cursor is opened and the query is executed
-Open an explicit cursor several times with a different active set each time.
Syntax
CURSOR cursor_name (parameter name data type) is select statement
Begin
Open cursor_name (parameter values)
End ;
- Paremeter data types are the same as scalar data type but do not give them sizes.
Example:
declare
mename emp.ename%type;
mempno emp.empno%type;
cursor emp_cursor (mdeptno number, mjob varchar2) is select empno,ename from emp where
deptno=mdeptno and job=mjob;
begin
for emp_record in emp_cursor (30,'SALESMAN')
loop
fetch emp_cursor into mempno,mename ;
dbms_output.put_line(mename||' '||mempno);
end loop ;
end;
---------------------------------
Cursor with Subqueries
Subquery returns a value or set of values to outer query.
Syntax
Cursor emp_cur is select empno, ename, sal from EMP where deprno in (select deptno from dept)
Defining REF Cursor Type
- Explicit cursor is a static cursor
- It is always refers only one work area in memory
- REF CURSOR is dynamic cursor
- It is like a pointer in C
- It refer different work area in memory at run time more than one SQL statement can be associated to ref cursor at run time.
- Cursor variable is like a pointer define with REF type
Types of REF CURSOR
STRONG REF CURSOR – the REF CURSOR which is return type
WEAK REF CURSOR – the REF CURSOR without return type
- Define a REF cursor type:
Type ref_type_name is REF CURSOR
- Declare a variable of the type
ref_cv ref_type_name ;
STRONG REF CURSOR
Declare
Type ref_c is ref cursor return emp%rowtype;
C1 ref_c ;
Vrec emp%rowtype;
Vdno number(2) := &vdno ;
Begin
If vdno =10 then
Open c1 for select * from emp where job=’MANAGER’ ;
Elsif vdno = 20 then
Open c1 for select * from emp where job=’SALESMAN’ ;
Else
Open c1 for select * from emp where job=’CLERK’ ;
End if;
Loop
Fetch c1 into vrec ;
Exit when c1%notfound;
dbms_output.put_line(vrec.empno||’ ‘||vrec.ename) ;
end loop;
close c1;
end;
WEAK REF CURSOR
declare
v_empno emp.empno%type;
v_sal emp.sal%type ;
v_ename emp.ename%type ;
mdname dept.dname%type;
type r is ref cursor;
emp_cursor r;
begin
Open emp_cursor for select empno,ename,sal from emp ;
LOOP
fetch emp_cursor into v_empno,V_ename,v_sal ;
dbms_output.put_line('The Employee No: ' ||v_empno||' and name is ' ||v_ename|| ' The salary is ' ||v_sal);
exit WHEN emp_cursor%rowcount > 10 or emp_cursor%notFOUND;
End LOOP ;
Open emp_cursor for select dname from dept ;
LOOP
fetch emp_cursor into mdname;
dbms_output.put_line('The dept name is ' ||mdname);
exit WHEN emp_cursor%rowcount > 10 or emp_cursor%notFOUND;
End LOOP ;
end;
