|
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>
|
|
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>
|
|
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>
|
|
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>
|
|
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>
|
|
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
|
|
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>
|
|
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>
|
|
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
|
|
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
|
|
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
|
|
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
|
|
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
|
