CURSORS
Cursor is a pointer to memory location which is called as context area which contains the
information necessary for processing, including the number of rows processed by
the statement, a pointer to the parsed representation of the statement, and the
active set which is the set of rows
returned by the query.
Cursor contains two parts
ü Header
ü Body
Header includes cursor
name, any parameters and the type of data being loaded.
Body includes the select
statement.
Ex:
Cursor c(dno in number) return dept%rowtype is select *from dept;
In the above
Header – cursor c(dno in
number) return dept%rowtype
Body – select *from dept
CURSOR TYPES
Ø Implicit (SQL)
Ø Explicit
ü Parameterized cursors
ü REF cursors
CURSOR STAGES
Ø Open
Ø Fetch
Ø Close
CURSOR ATTRIBUTES
Ø %found
Ø %notfound
Ø %rowcount
Ø %isopen
Ø %bulk_rowcount
Ø %bulk_exceptions
CURSOR DECLERATION
Syntax:
Cursor <cursor_name> is select
statement;
Ex:
Cursor c is select *from dept;
CURSOR LOOPS
Ø Simple loop
Ø While loop
Ø For loop
SIMPLE LOOP
Syntax:
Loop
Fetch <cursor_name> into <record_variable>;
Exit when
<cursor_name> % notfound;
<statements>;
End loop;
Ex:
DECLARE
cursor c is select * from student;
v_stud student%rowtype;
BEGIN
open c;
loop
fetch c into v_stud;
exit when c%notfound;
dbms_output.put_line('Name = ' ||
v_stud.name);
end loop;
close c;
END;
Output:
Name = saketh
Name = srinu
Name = satish
Name = sudha
WHILE LOOP
Syntax:
While <cursor_name>
% found loop
Fetch <cursor_name> nto <record_variable>;
<statements>;
End loop;
Ex:
DECLARE
cursor c is select * from student;
v_stud student%rowtype;
BEGIN
open c;
fetch c into v_stud;
while c%found loop
fetch c into v_stud;
dbms_output.put_line('Name = ' ||
v_stud.name);
end loop;
close c;
END;
Output:
Name = saketh
Name = srinu
Name = satish
Name = sudha
FOR LOOP
Syntax:
for <record_variable>
in <cursor_name> loop
<statements>;
End loop;
Ex:
DECLARE
cursor c is select * from student;
BEGIN
for v_stud in c loop
dbms_output.put_line('Name = ' ||
v_stud.name);
end loop;
END;
Output:
Name = saketh
Name = srinu
Name = satish
Name = sudha
PARAMETARIZED CURSORS
Ø This was used when you are
going to use the cursor in more than one place with different values for the
same where clause.
Ø Cursor parameters must be in mode.
Ø Cursor parameters may have
default values.
Ø The scope of cursor
parameter is within the select statement.
Ex:
DECLARE
cursor c(dno in number) is select * from dept
where deptno = dno;
v_dept dept%rowtype;
BEGIN
open c(20);
loop
fetch c into v_dept;
exit when c%notfound;
dbms_output.put_line('Dname = ' ||
v_dept.dname || ' Loc = ' || v_dept.loc);
end
loop;
close c;
END;
Output:
Dname = RESEARCH Loc = DALLAS
PACKAGED CURSORS WITH
HEADER IN SPEC AND BODY IN PACKAGE BODY
Ø cursors declared in
packages will not close automatically.
Ø In packaged cursors you
can modify the select statement without making any changes to the cursor header
in the package specification.
Ø Packaged cursors with must
be defined in the package body itself, and then use it as global for the
package.
Ø You can not define the
packaged cursor in any subprograms.
Ø Cursor declaration in
package with out body needs the return clause.
Ex1:
CREATE OR
REPLACE PACKAGE PKG IS
cursor c return dept%rowtype is select *
from dept;
procedure proc is
END PKG;
CREATE OR
REPLACE PAKCAGE BODY PKG IS
cursor c return dept%rowtype is select *
from dept;
PROCEDURE PROC
IS
BEGIN
for v in c loop
dbms_output.put_line('Deptno = ' || v.deptno || ' Dname = ' ||
v.dname || ' Loc = ' || v.loc);
end loop;
END PROC;
END PKG;
Output:
SQL> exec pkg.proc
Deptno = 10 Dname = ACCOUNTING Loc = NEW YORK
Deptno = 20 Dname = RESEARCH Loc = DALLAS
Deptno = 30 Dname = SALES Loc = CHICAGO
Deptno =
40 Dname = OPERATIONS Loc = BOSTON
Ex2:
CREATE OR
REPLACE PAKCAGE BODY PKG IS
cursor c return dept%rowtype is select *
from dept where deptno > 20;
PROCEDURE PROC
IS
BEGIN
for v in c loop
dbms_output.put_line('Deptno = ' ||
v.deptno || ' Dname = ' ||
v.dname || ' Loc = ' || v.loc);
end loop;
END PROC;
END PKG;
Output:
SQL> exec pkg.proc
Deptno = 30 Dname = SALES Loc = CHICAGO
Deptno =
40 Dname = OPERATIONS Loc = BOSTON
REF CURSORS AND CURSOR
VARIABLES
Ø This is unconstrained
cursor which will return different types depends upon the user input.
Ø Ref cursors can not be
closed implicitly.
Ø Ref cursor with return
type is called strong cursor.
Ø Ref cursor with out return
type is called weak cursor.
Ø You can declare ref cursor
type in package spec as well as body.
Ø You can declare ref cursor
types in local subprograms or anonymous blocks.
Ø Cursor variables can be
assigned from one to another.
Ø You can declare a cursor
variable in one scope and assign another cursor variable with different scope,
then you can use the cursor variable even though the assigned cursor variable
goes out of scope.
Ø Cursor variables can be
passed as a parameters to the subprograms.
Ø Cursor variables modes are
in or out or in out.
Ø Cursor variables can not
be declared in package spec and package body (excluding subprograms).
Ø You can not user remote
procedure calls to pass cursor variables from one server to another.
Ø Cursor variables can not
use for update clause.
Ø You can not assign nulls
to cursor variables.
Ø You can not compare cursor
variables for equality, inequality and nullity.
Ex:
CREATE OR REPLACE PROCEDURE REF_CURSOR(TABLE_NAME IN VARCHAR)
IS
type t is ref cursor;
c t;
v_dept
dept%rowtype;
type r is record(ename emp.ename%type,job
emp.job%type,sal emp.sal%type);
v_emp r;
v_stud student.name%type;
BEGIN
if table_name = 'DEPT' then
open c for select * from dept;
elsif table_name = 'EMP' then
open c for select ename,job,sal from
emp;
elsif table_name = 'STUDENT' then
open c for select name from student;
end if;
loop
if table_name = 'DEPT' then
fetch c into v_dept;
exit when c%notfound;
dbms_output.put_line('Deptno = ' ||
v_dept.deptno || ' Dname = ' ||
v_dept.dname || ' Loc = ' || v_dept.loc);
elsif table_name = 'EMP' then
fetch c into v_emp;
exit when c%notfound;
dbms_output.put_line('Ename = ' ||
v_emp.ename || ' Job = ' || v_emp.job
|| ' Sal = ' || v_emp.sal);
elsif
table_name = 'STUDENT' then
fetch c into v_stud;
exit when c%notfound;
dbms_output.put_line('Name = ' || v_stud);
end if;
end loop;
close c;
END;
Output:
SQL> exec ref_cursor('DEPT')
Deptno = 10 Dname
= ACCOUNTING Loc = NEW YORK
Deptno = 20 Dname
= RESEARCH Loc = DALLAS
Deptno = 30 Dname
= SALES Loc = CHICAGO
Deptno = 40 Dname
= OPERATIONS Loc = BOSTON
SQL> exec ref_cursor('EMP')
Ename = SMITH Job = CLERK Sal = 800
Ename = ALLEN Job = SALESMAN Sal = 1600
Ename = WARD Job = SALESMAN Sal = 1250
Ename = JONES Job = MANAGER Sal = 2975
Ename = MARTIN Job = SALESMAN Sal = 1250
Ename = BLAKE Job = MANAGER Sal = 2850
Ename = CLARK Job = MANAGER Sal = 2450
Ename = SCOTT Job = ANALYST Sal = 3000
Ename = KING Job = PRESIDENT Sal = 5000
Ename = TURNER Job = SALESMAN Sal = 1500
Ename = ADAMS Job = CLERK Sal = 1100
Ename = JAMES Job = CLERK Sal = 950
Ename = FORD Job = ANALYST Sal = 3000
Ename = MILLER Job = CLERK Sal = 1300
SQL> exec ref_cursor('STUDENT')
Name = saketh
Name = srinu
Name = satish
Name = sudha
CURSOR EXPRESSIONS
Ø You can use cursor
expressions in explicit cursors.
Ø You can use cursor
expressions in dynamic SQL.
Ø You can use cursor
expressions in REF cursor declarations and variables.
Ø You can not use cursor
expressions in implicit cursors.
Ø Oracle opens the nested
cursor defined by a cursor expression implicitly as soon as it fetches the data
containing the cursor expression from the parent or outer cursor.
Ø Nested cursor closes if
you close explicitly.
Ø Nested cursor closes
whenever the outer or parent cursor is executed again or closed or canceled.
Ø Nested cursor closes
whenever an exception is raised while fetching data from a parent cursor.
Ø Cursor expressions can not
be used when declaring a view.
Ø Cursor expressions can be
used as an argument to table function.
Ø You can not perform bind
and execute operations on cursor expressions when using the cursor expressions
in dynamic SQL.
USING NESTED CURSORS OR
CURSOR EXPRESSIONS
Ex:
DECLARE
cursor c is select
ename,cursor(select dname from dept d where e.empno = d.deptno) from emp e;
type t is ref cursor;
c1 t;
c2 t;
v1 emp.ename%type;
v2 dept.dname%type;
BEGIN
open c;
loop
fetch c1 into v1;
exit when c1%notfound;
fetch c2 into v2;
exit when c2%notfound;
dbms_output.put_line('Ename = ' || v1
|| ' Dname = ' || v2);
end loop;
end loop;
close c;
END;
CURSOR CLAUSES
Ø Return
Ø For update
Ø Where current of
Ø Bulk collect
RETURN
Cursor c
return dept%rowtype is select *from dept;
Or
Cursor c1 is
select *from dept;
Cursor c return c1%rowtype is select *from dept;
Or
Type t is
record(deptno dept.deptno%type, dname dept.dname%type);
Cursor c
return t is select deptno, dname from dept;
FOR UPDATE AND WHERE
CURRENT OF
Normally, a select
operation will not take any locks on the rows being accessed. This will allow
other sessions connected to the database to change the data being selected. The
result set is still consistent. At open time, when the active set is
determined, oracle takes a snapshot of the table. Any changes that have been
committed prior to this point are reflected in the active set. Any changes made
after this point, even if they are committed, are not reflected unless the
cursor is reopened, which will evaluate the active set again.
However, if the FOR UPDATE caluse is pesent,
exclusive row locks are taken on the rows in the active set before the open
returns. These locks prevent other sessions from changing the rows in the
active set until the transaction is committed or rolled back. If another
session already has locks on the rows in the active set, then SELECT … FOR UPDATE operation will wait for
these locks to be released by the other session. There is no time-out for this
waiting period. The SELECT…FOR UPDATE will hang until the other session releases the lock. To handle
this situation, the NOWAIT clause is available.
Syntax:
Select …from …
for update of column_name [wait n];
If the cursor
is declared with the FOR UPDATE clause, the WHERE CURRENT OF clause can be used in an update or delete statement.
Syntax:
Where current
of cursor;
Ex:
DECLARE
cursor c is select * from dept for
update of dname;
BEGIN
for v in c loop
update dept set dname = 'aa' where
current of c;
commit;
end loop;
END;
No comments:
Post a Comment