커서 활용 - 2

▣ 커서 기본 구문

SQL> declare

2 cursor ec is select * from emp;

3 e emp%rowtype;

4 begin

5 if ec%ISOPEN = FALSE then

6 open ec;

7 end if;

8 loop

9 fetch ec into e;

10 exit when ec%NOTFOUND;

11 dbms_output.put_line(e.ename);

12 end loop;

13 end;

14 /

   

▣ 부서명을 입력받아서 해당 부서의 직원명과 급여 출력

SQL> create or replace procedure empd(

2 dn emp.deptno%type

3 ) as

4 cursor ec is select * from emp where deptno = dn;

5 e emp%rowtype;

6 begin

7 if ec%ISOPEN = FALSE then

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 || '-' || e.sal);

14 end loop;

15 end;

16 /

   

Procedure created.

   

SQL> exec empd(10);

CLARK-2450

KING-5000

MILLER-1300

   

PL/SQL procedure successfully completed.

   

▣ ROWCOUNT

SQL> r

1 create or replace procedure empd(

2 dn emp.deptno%type

3 ) as

4 cursor ec is select * from emp where deptno = dn;

5 e emp%rowtype;

6 begin

7 if ec%ISOPEN = FALSE then

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 || '-' || e.sal || '-' || ec%ROWCOUNT); => 출력되는 값의 개수 출력

14 end loop;

15* end;

   

Procedure created.

   

SQL> exec empd(10);

CLARK-2450-1

KING-5000-2

MILLER-1300-3

   

PL/SQL procedure successfully completed.

   

▣ cursor 재 오픈

declare     =>     close 사용방법, 커서를 재 open 할 수 있다.

        dn emp.deptno%type;

        cursor ec is select * from emp where deptno=dn;

        e emp%rowtype;

begin

        dn := 10;

                open ec;

        loop

                fetch ec into e;

                exit when ec%NOTFOUND;

                dbms_output.put_line(e.ename ||'-'|| e.sal || '-' || e.deptno);

        end loop;

        close ec;

        dn := 20;

                open ec;

        loop

                fetch ec into e;

                exit when ec%NOTFOUND;

                dbms_output.put_line(e.ename ||'-'|| e.sal || '-' || e.deptno);

        end loop;

end;

/