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
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
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
Explicit Cursor: The set of rows
returned by a query can consist of zero, one, or multiple rows, depending on
how many rows meet your search criteria. When a query returns multiple rows,
you can explicitly define a cursor to process the rows. You use three commands
to control the cursor:
CURSOR STAGES
CURSOR STAGES
Open
Fetch
Close
Implicit
Cursors: ORACLE implicitly opens a cursor to process each SQL statement
not associated with an explicitly declared cursor.PL/SQL lets you refer to the
most recent implicit cursor as the SQL” cursor. So, although you
cannot use the OPEN,
FETCH, and CLOSE statements to control an implicit cursor, you can still use cursor attributes to access information about the most recently executed SQL statement.
FETCH, and CLOSE statements to control an implicit cursor, you can still use cursor attributes to access information about the most recently executed SQL statement.
CURSOR
ATTRIBUTES
%found
%notfound
%rowcount
%isopen
%bulk_rowcount
%bulk_exceptions
CURSOR
DECLERATION
Syntax:
Cursor is select statement;
Ex:
Cursor c is select *from
dept;
Syntax:
Cursor
Ex:
CURSOR LOOPS
Simple loop
While loop
For loop
SIMPLE LOOP
Syntax:
Loop
Fetch
into ;
Exit when % notfound;
;
End loop;
Ex:
Syntax:
Loop
Fetch
Exit when
End loop;
Ex:
PgSQL
|
1
2
3
4
5
6
7
8
9
10
11
12
|
<span style="color:
#3366ff;"><em>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;</em></span>
|
Output:
Name = saketh
Name = srinu
Name = satish
Name = sudha
Name = saketh
Name = srinu
Name = satish
Name = sudha
WHILE LOOP
Syntax:
While %
found loop
Fetch
nto ;
;
End loop;
Ex:
Syntax:
While
Fetch
End loop;
Ex:
PgSQL
|
1
2
3
4
5
6
7
8
9
10
11
12
|
<span style="color:
#3366ff;"><em>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;</em></span>
|
Output:
Name = saketh
Name = srinu
Name = satish
Name = sudha
Name = saketh
Name = srinu
Name = satish
Name = sudha
FOR LOOP
Syntax:
for in loop
;
End loop;
Ex:
Syntax:
for
End loop;
Ex:
PgSQL
|
1
2
3
4
5
6
7
|
<span style="color:
#3366ff;"><em>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;</em></span>
|
Output:
Name = saketh
Name = srinu
Name = satish
Name = sudha
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:
PgSQL
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
<span style="color:
#3366ff;"><em>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</em></span>
|
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.
PgSQL
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
<span style="color:
#3366ff;"><em>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</em></span>
|
REF CURSORS AND
CURSOR VARIABLES
This is unconstrained cursor which will return
different types depends upon the user input.
Ref cursors cannot be closed implicitly.
Ref cursor with return type is called strong
cursor.
Ref cursor without 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 parameters
to the subprograms.
Cursor variables modes are in or out or in
out.
Cursor variables cannot be declared in package
spec and package body (excluding subprograms).
You cannot user remote procedure calls to pass
cursor variables from one server to another.
Cursor variables cannot use for update clause.
You can not assign nulls to cursor variables.
You cannot compare cursor variables for
equality, inequality and nullity.
PgSQL
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
|
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 cannot 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 cannot be used when
declaring a view.
Cursor expressions can be used as an argument
to table function.
You cannot perform bind and execute operations
on cursor expressions when using the cursor expressions in dynamic SQL.
USING NESTED
CURSORS OR CURSOR EXPRESSIONS
PgSQL
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
<span style="color:
#3366ff;"><em>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;</em></span>
|
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;
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.
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];
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;
Syntax:
Where current of cursor;
PgSQL
|
1
2
3
4
5
6
7
8
9
|
<span style="color:
#3366ff;"><em>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;</em></span>
|
BULK COLLECT
This is used for array fetches
With this you can retrieve multiple rows of
data with a single roundtrip.
This reduces the number of context switches
between the pl/sql and sql engines.
Reduces the overhead of retrieving data.
You can use bulk collect in both dynamic and
static sql.
You can use bulk collect in select, fetch into
and returning into clauses.
SQL engine automatically initializes and
extends the collections you reference in the bulk collect clause.
Bulk collect operation empties the collection
referenced in the into clause before executing the query.
You can use the limit clause of bulk collect
to restrict the no of rows retrieved.
You can fetch into multible collections with
one column each.
Using the returning clause we can return data
to the another collection.
BULK COLLECT IN
FETCH
PgSQL
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
<span style="color:
#3366ff;"><em>Ex:
DECLARE
Type t is table of dept%rowtype;
nt t;
Cursor c is select *from dept;
BEGIN
Open c;
Fetch c bulk collect into nt;
Close c;
For i in nt.first..nt.last loop
dbms_output.put_line('Dname = ' || nt(i).dname || ' Loc = ' ||
nt(i).loc);
end loop;
END;</em></span>
Output:
Dname = ACCOUNTING Loc = NEW YORK
Dname = RESEARCH Loc = DALLAS
Dname = SALES Loc = CHICAGO
Dname = OPERATIONS Loc = BOSTON
|
BULK COLLECT IN
SELECT
PgSQL
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
<span style="color:
#3366ff;"><em>Ex:
DECLARE
Type t is table of dept%rowtype;
Nt t;
BEGIN
Select * bulk collect into nt from dept;
for i in nt.first..nt.last loop
dbms_output.put_line('Dname = ' || nt(i).dname || ' Loc = ' ||
nt(i).loc);
end loop;
END;</em></span>
Output:
Dname = ACCOUNTING Loc = NEW YORK
Dname = RESEARCH Loc = DALLAS
Dname = SALES Loc = CHICAGO
Dname = OPERATIONS Loc = BOSTON
|
LIMIT IN BULK
COLLECT
You can use this to limit the number of rows to be fetched.
You can use this to limit the number of rows to be fetched.
PgSQL
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
<span style="color:
#3366ff;"><em>Ex:
DECLARE
Type t is table of dept%rowtype;
nt t;
Cursor c is select *from dept;
BEGIN
Open c;
Fetch c bulk collect into nt limit 2;
Close c;
For i in nt.first..nt.last loop
dbms_output.put_line('Dname = ' || nt(i).dname || ' Loc = ' || nt(i).loc);
end loop;
END;</em></span>
Output:
Dname = ACCOUNTING Loc = NEW YORK
Dname = RESEARCH Loc = DALLAS
|
MULTIPLE
FETCHES IN INTO CLAUSE
PgSQL
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
|
<span style="color:
#3366ff;"><em>Ex1:
DECLARE
Type t is table of dept.dname%type;
nt t;
Type t1 is table of dept.loc%type;
nt1 t;
Cursor c is select dname,loc from dept;
BEGIN
Open c;
Fetch c bulk collect into nt,nt1;
Close c;
For i in nt.first..nt.last loop
dbms_output.put_line('Dname = ' || nt(i));
end loop;
For i in nt1.first..nt1.last loop
dbms_output.put_line('Loc = ' || nt1(i));
end loop;
END;</em></span>
Output:
Dname = ACCOUNTING
Dname = RESEARCH
Dname = SALES
Dname = OPERATIONS
Loc = NEW YORK
Loc = DALLAS
Loc = CHICAGO
Loc = BOSTON
<span style="color:
#3366ff;"><em>Ex2:
DECLARE
type
t is table of dept.dname%type;
type
t1 is table of dept.loc%type;
nt t;
nt1 t1;
BEGIN
Select
dname,loc bulk collect into nt,nt1 from dept;
for
i in nt.first..nt.last loop
dbms_output.put_line('Dname = ' || nt(i));
end
loop;
for
i in nt1.first..nt1.last loop
dbms_output.put_line('Loc = ' || nt1(i));
end
loop;
END;</em></span>
Output:
Dname = ACCOUNTING
Dname = RESEARCH
Dname = SALES
Dname = OPERATIONS
Loc = NEW YORK
Loc = DALLAS
Loc = CHICAGO
Loc = BOSTON
|
RETURNING
CLAUSE IN BULK COLLECT
You can use this to return the processed
data to the ouput variables or typed variables.
PgSQL
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
<span style="color:
#3366ff;"><em>Ex:
DECLARE
type t is table of number(2);
nt t := t(1,2,3,4);
type t1 is table of varchar(2);
nt1 t1;
type t2 is table of student%rowtype;
nt2 t2;
BEGIN
select name bulk collect into nt1 from student;
forall v in nt1.first..nt1.last
update student set no = nt(v) where name
= nt1(v) returning
no,name,marks bulk collect into nt2;
for v in nt2.first..nt2.last loop
dbms_output.put_line('Marks = ' || nt2(v));
end loop;
END;</em></span>
Output:
Marks = 100
Marks = 200
Marks = 300
Marks = 400
|
POINTS TO
REMEMBER
Cursor name can be up to 30 characters in
length.
Cursors declared in anonymous blocks or
subprograms closes automatically when that block terminates execution.
%bulk_rowcount and %bulk_exceptions can be
used only with forall construct.
Cursor declarations may have expressions with
column aliases.
These expressions are called virtual columns
or calculated columns.
- See more at:
http://alloracleapps.com/oracle/cursors-and-bulk-collect/#sthash.P3XKFEKv.dpuf
