커서 활용 -1

▣ 커서보다 쉬운 for문 예제

SQL> begin => 적은 row일 때 사용되지만 권장되지 않는다.

2 for e in (select * from emp) loop

3 dbms_output.put_line(e.ename);

4 end loop;

5 end;

6 /

SMITH

ALLEN

WARD

JONES

MARTIN

BLAKE

CLARK

SCOTT

KING

TURNER

ADAMS

JAMES

FORD

MILLER

   

PL/SQL procedure successfully completed.

   

▣ cursor 의 확장

SQL> declare

2 cursor ec is select * from emp;

3 begin

4 for e in ec loop

5 dbms_output.put_line(e.ename);

6 end loop;

7 end;

8 /

SMITH

ALLEN

MILLER

   

PL/SQL procedure successfully completed.

   

   

SQL> declare

2 cursor ec is select * from emp;

3 e emp%rowtype;

4 begin

5 open ec;

6 loop

7 fetch ec into e;

8 exit when ec%NOTFOUND;

9 dbms_output.put_line(e.ename);

10 end loop;

11 end;

12 /

SMITH

FORD

MILLER

   

PL/SQL procedure successfully completed.

 

   

   

SQL> declare

2 cursor ec is select * from emp;

3 e emp%rowtype;

4 begin

5 if ec%ISOPEN then

6 dbms_output.put_line('cursor is opened');

7 else

8 open ec;

9 end if;

10 loop

11 fetch ec into e;

12 exit when ec%NOTFOUND;

13 dbms_output.put_line(e.ename);

14 end loop;

15 end;

16 /

SMITH

ALLEN

MILLER

   

   

PL/SQL procedure successfully completed.

   

   

SQL> r

1 declare

2 cursor ec is select * from emp;

3 e emp%rowtype;

4 begin

5 open ec;

6 if ec%ISOPEN then

7 dbms_output.put_line('cursor is opened');

8 else

9 open ec;

10 end if;

11 loop

12 fetch ec into e;

13 exit when ec%NOTFOUND;

14 dbms_output.put_line(e.ename);

15 end loop;

16* end;

cursor is opened

SMITH

ALLEN

MILLER

   

PL/SQL procedure successfully completed.