[10g] 커서 정의와 활용 예제

2012. 1. 20. 11:54

/********************************************************************************************
-- Title : [10g] 커서 정의와 활용 예제
-- Reference : netme.kr
-- Key word : cursor 커서 isopen rowcount
********************************************************************************************/
-- 커서 정의
커서란 SQL Plus에서 사용자가 실행한 SQL문의 단위를 의미합니다.
오라클렝서 수행한 모든 쿼리문은 커서 단위로 처리합니다.

PL/SQL의 SQL문처럼 하나의 결과를 리턴하는 경우 커서 없이도 SQL문의 실행결과가
암시적으로 커서에 저장되므로 이를 암시적 커서라고 합니다.

SQL문을 수행한 후에 결과로 얻어지는 행이 여러 개일 경우에는 암시적인 커서에 정보를
저장할 수 없기에 에러가 발생합니다. 이럴 경우에는 반드시 명시적인 커서를 사용해야 합니다.

명시적인 커서는 PL/SQL의 레코드(RECORD)와 PL/SQL의 테이블(TABLE)을 결합한 것으로서
프로그램 언어의 구조체 배열과 유사합니다.

-- 커서 상태
%NOTFOUND   커서 영역의 자료가 모두 FETCH 됬는가를 알려줌
%FOUND      커서 영역에 FETCH가 되지 않은 자료가 있는가를 알려줌
%ISOPEN     커서가 OPEN된 상태인가를 알려줌
%ROWCOUNT   FETCH된 RECORD가 몇 개 있는지 알려줌

-- 커서 활용
SET SERVEROUTPUT ON;

DECLARE
   vempno   NUMBER(4);
   vename   VARCHAR2(20);
   vsal   NUMBER(7, 2);


   CURSOR   C1
   IS
   select empno, ename, sal
   from scott.emp
   where deptno=20;
BEGIN
   OPEN C1;

dbms_output.put_line('empno ename sal');

   LOOP
      FETCH C1 INTO vempno, vename, vsal;
      EXIT WHEN C1%NOTFOUND;

dbms_output.put_line(to_char(vempno)||' '||vename||' '||to_char(vsal));
END LOOP;
ENd;
/

-- OPEN-FETCH-CLOSE없이 커서 처리
SET SERVEROUTPUT ON;

DECLARE
vemp emp%ROWTYPE;

   CURSOR   C1
   IS
   SELECT empno, ename, sal
   FROM scott.emp
   WHERE deptno=20;
BEGIN
   dbms_output.put_line('empno   ename   sal');

FOR vemp IN C1 LOOP
EXIT WHEN C1%NOTFOUND;

dbms_output.put_line(to_char(vemp.empno)||' '||vemp.ename||' '||to_char(vemp.sal));
END LOOP;
END;
/

-- %ROW_COUNT 사용
SET SERVEROUTPUT ON
DECLARE
vemp emp%ROWTYPE;

   CURSOR   C1
   IS
   SELECT empno, ename, sal
   FROM scott.emp
   WHERE deptno=20;

BEGIN
dbms_output.put_line('empno ename sal record count');

FOR vemp IN C1 LOOP
EXIT WHEN C1%NOTFOUND;

dbms_output.put_line(to_char(vemp.empno)||' '||vemp.ename||' '||to_char(vemp.sal)||' '||C1%ROWCOUNT);
END LOOP;
END;
/

-- 총합 구하기
SET SERVEROUTPUT ON;

DECLARE
tot NUMBER := 0;

   CURSOR   emp_cursor
   IS
   SELECT ename, sal
   FROM scott.emp;
BEGIN
   dbms_output.put_line('name   sal');
   dbms_output.put_line('------------------------------------------');

   FOR cur_var IN emp_cursor LOOP
      tot := tot + cur_var.sal;
      dbms_output.put_line(cur_var.ename || '-' || cur_var.sal);
   END LOOP;


   dbms_output.put_line('------------------------------------------');
   dbms_output.put_line('TOTAL   '||tot);
END;
/

-- 사원별 급여 현황
SET SERVEROUTPUT ON;

DECLARE
   CURSOR   emp_cursor
   IS
   SELECT ename, sal
   FROM scott.emp
   ORDER BY sal DESC;

star VARCHAR2(100);
cnt NUMBER := 0;

BEGIN
dbms_output.put_line(' sal of emp');
dbms_output.put_line('------------------------------------------');

   FOR cur_var IN emp_cursor
   LOOP
      star := NULL;
      cnt := round(cur_var.sal/100, 0);

      FOR i IN 1..cnt
      LOOP
         star := star||'*';
      END LOOP;

      dbms_output.put_line(cur_var.ename);
      dbms_output.put_line('-   '||star||'('||cur_var.sal||')');
   END LOOP;
END;
/

-- 커서 매개 변수 활용
create or replace procedure scott.up_emp_process
is
    v_empno       emp.empno%type;
    v_ename       emp.ename%type;
    v_sal         number(7,2);


    cursor emp_cursor(v_deptno number) is
    select empno, ename, sal
    from scott.emp where deptno=v_deptno;
begin
    dbms_output.put_line('------------------------------');

    open emp_cursor(10); /* 10번부서 */
        loop
            fetch emp_cursor into v_empno, v_ename, v_sal;
            exit when emp_cursor%rowcount > 5 or emp_cursor%notfound;
            dbms_output.put_line(v_empno||' '||v_ename||' '||v_sal);
        end loop;
    close emp_cursor;

    open emp_cursor(20); /* 20번부서 */
        loop
            fetch emp_cursor into v_empno, v_ename, v_sal;
            exit when emp_cursor%rowcount > 5 or emp_cursor%notfound;
            dbms_output.put_line(v_empno||' '||v_ename||' '||v_sal);
        end loop;
    close emp_cursor;
end up_emp_process;
/

저작자표시 비영리

디비랑[dɪ'bɪraŋ]

[10g] 커서 정의와 활용 예제

+ Recent posts

티스토리툴바