Total Pageviews

February 5, 2015

2/05/2015 08:53:00 PM

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;