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> into <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
Cursor Example:
declare
cursor c is
select * fromemp where deptno = 10 for update;
v_emp c%rowtype;
incr number;
begin
for v_emp in c loop
if v_emp.sal < 2000 then
incr := 0.20;
else
incr := 0.12;
end if;
update emp set sal = sal + sal * incr where current of c;
end loop;
end;
create or replace procedure wco is
cursor c is
select a, b from f where length(b) = 5 for update;
v_emp c%rowtype;
begin
open c;
loop
fetch c
into v_emp;
exit when c%notfound;
update f set a = v_emp.a * v_emp.a where current of c;
end loop;
close c;
end;
create table numbers_en (
id_num number primary key,
txt_num varchar2(10)
);
insert into numbers_en values (1, 'one' );
insert into numbers_en values (2, 'two' );
insert into numbers_en values (3, 'three');
insert into numbers_en values (4, 'four' );
insert into numbers_en values (5, 'five' );
insert into numbers_en values (6, 'six' );
create table lang (
id_lang char(2) primary key,
txt_lang varchar2(10)
);
insert into lang values ('de', 'german');
insert into lang values ('fr', 'french');
insert into lang values ('it', 'italian');
create table translations (
id_num references numbers_en,
id_lang references lang,
txt_trans varchar2(10) not null
);
insert into translations values (1, 'de', 'eins' );
insert into translations values (1, 'fr', 'un' );
insert into translations values (2, 'it', 'duo' );
insert into translations values (3, 'de', 'drei' );
insert into translations values (3, 'it', 'tre' );
insert into translations values (4, 'it', 'quattro');
insert into translations values (6, 'de', 'sechs' );
insert into translations values (6, 'fr', 'six' );
DECLARE
CURSOR CUR IS
SELECT ID_NUM, TXT_NUM, id_lang, TXT_LANG, TXT_TRANS
FROM numbers_en
JOIN TRANSLATIONS
USING (ID_NUM)
LEFT JOINLANG
USING (ID_LANG)
FOR UPDATE OF TRANSLATIONS.TXT_TRANS;
REC CUR%ROWTYPE;
BEGIN
FOR REC IN CUR LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(REC.ID_NUM, '999') || '-' ||RPAD(REC.TXT_NUM, 10) || '-' ||RPAD(NVL(REC.TXT_TRANS, ' '), 10) || '-' ||REC.ID_LANG || '-' || RPAD(REC.TXT_LANG, 10));
IF MOD(REC.ID_NUM, 2) = 0 THEN
UPDATE TRANSLATIONS
SET TXT_TRANS = UPPER(TXT_TRANS)
WHERE CURRENT OF CUR;
DBMS_OUTPUT.PUT_LINE('UPDATED');
else
dbms_output.new_line;
end if;
end loop;
end;
