커서란 SQL*Plus에서 사용자가 실행한 SQL문의 단위를 의미합니다. 오라클에서 수행한 모든 쿼리문은 커서 단위로 처리합니다. 커서는 암시적 커서와 명시적 커서로 나뉩니다. PL/SQL의 SQL문처럼 하나의 결과를 리턴하는 경우 커서 없이도 SQL문의 실행된 결과가 암시적으로 커서에 저장되므로 이를 암시적 커서라고 합니다.
- SQL문을 수행한 후에 결과로 얻어지는 행이 여러 개일 경우에는 암시적인 커서에 정보를 저장할 수 없기에 에러가 발생합니다. 이런 경우에 반드시 명시적인 커서를 사용해야 합니다.
# 커서의 상태
- 명시적인 커서는 PL/SQL의 레코드(RECORD)와 PL/SQL 의 테이블(TABLE)을 결합한 것으로서 프로그램 언어의 구조체 배열과 유사합니다. 커서를 사용하기 위해서는 다음과 같은
4가지 단계를 거쳐야 합니다.
1. 커서를 선언한다. (CURSOR cur_name)
2. 커서를 오픈한다. (OPEN cur_name)
3. 커서의 조회한 결과를 인출해 저장한다. (FETCH cur_name)
4. 커서를 닫는다. (CLOSE cur_name)
1. 커서를 선언한다.
CURSOR cursor_name
IS statement;
- 커서를 선언할 때에는 실행할 SELECT 문을 IS 다음에 서브쿼리 형태로 기술합니다.
CURSOR C1 IS
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE DEPTNO = 20;
- C1은 커서의 이름이고 이 커서는 사원 테이블에서 부서번호가 20인 행을 조회합니다.
2. 커서를 오픈한다.
- 커서를 오픈 하게 되면 커서를 선언할 때 작성한 SELECT 문을 실행하게 됩니다.
OPEN C1;
3. 커서에 조회한 결과를 인출해 저장한다.
- FETCH 명령어를 수행하면 오픈한 SELECT문에 의해 검색된 한 개의 정보를 읽어오게 됩니다.
읽어온 정보는 변수에 저장합니다.
FETCH C1 INTO VEMPNO, VENAME, VSAL;
- SELECT 문의 결과로 얻어지는 행이 여러개일 경우에는 LOOP-END LOOP와 같은 반복문을 이용해서 마지막 행을 읽을 때가지 반복해서 수행해야 합니다.
LOOP
FETCH C1 INTO VEMPNO, VENAME, VSAL;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(VEMPNO) || VNAME || TO_CHAR(VSAL));
END LOOP;
- 만족하는 행을 발견하지 못할 때 즉, 마지막 행을 읽었을 때 반복문을 벗어나도록 하기 위해서는
'EXIT WHEN C1%NOTFOUND'와 같이 기술 합니다.
4. 커서를 닫는다.
- CLOSE 명령문으로 더 이상 사용되지 않을 커서를 닫습니다.
CLOSE C1;
- 커서를 닫으면 (CLOSE) 커서에 선언되 SELECT문의 선언이 해제됩니다.
- 20번 부서에 근무하는 사원의 정보를 출력해 봅니다.
SQL> SET SERVEROUTPUT ON -- DBMS_OUTPUT 출력 가능하도록 설정.
SQL> DECLARE
-- 커서를 실행한 결과를 저장할 지역 변수 선언
2 vempno NUMBER(4);
3 vename VARCHAR2(20);
4 vsal NUMBER(7, 2);
5
-- 번호가 20인 사원의 정보를 조회하는 SELECT문을 커서로 선언.
6 CURSOR C1
7 IS
8 SELECT EMPNO, ENAME, SAL
9 FROM EM01
10 WHERE DEPTNO = 20;
11
12 BEGIN
13 OPEN C1; -- 커서를 오픈하면 커서를 선언할 때 작성한 SELECT 문이 실행됩.
14 dbms_output.put_line('번호 이름 급여');
15
16 LOOP
17 FETCH C1 INTO VEMPNO, VENAME, VSAL; -- SELECT 문을 실행한 결과를 한행씩 가져와서 변수에 저장
18 EXIT WHEN C1%NOTFOUND; -- 마지막 행까지 읽어서 행이 발견되지 않으면 반복문을 벗어남.
19 DBMS_OUTPUT.PUT_LINE(TO_CHAR(VEMPNO) || ' ' || VENAME || ' ' || TO_CHAR(VSAL)); -- 검색된 결과를 사용자 화면에 출력.
END LOOP ;
-- LOOP 구문 : 커서 실행 결과 구해진 행이 여러 개이면 반복문을 사용하여 처리.
20 21 CLOSE C1;
22 END ;
23 /
번호 이름 급여
7566 JONES 2975
7902 FORD 3000
7368 SMITH 800
7788 SCOTT 3000
7876 TEST3 1100
PL/SQL procedure successfully completed.
SQL>
# OPEN-FETCH-CLOSE가 없이 커서 처리
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 -- 커서를 수행한 결과 값을 저장할 레코드 형태의 변수.
3 vemp emp%ROWTYPE;
4
5 -- 커서 선언
6 CURSOR C1
7 IS
8 SELECT EMPNO, ENAME, SAL
9 FROM EM01
10 WHERE DEPTNO = 20;
11
12 BEGIN
13 dbms_output.put_line('번호 이름 급여');
14
15 /* FOR 레코드명 IN 커서명 LOOP */
16 -- OPEN-FETCH-CLOSE문을 하나의 FOR문으로 대체할 수 있음.
17
18 FOR vemp IN C1 LOOP
19 EXIT WHEN C1%NOTFOUND;
20 DBMS_OUTPUT.PUT_LINE(TO_CHAR(vemp.empno) || ' ' || vemp.ename || ' ' || TO_CHAR(vemp.sal) || '------------->'||C1%ROWCOUNT);
21 END LOOP;
22 END ;
23 /
번호 이름 급여
7566 JONES 2975------------->1
7902 FORD 3000------------->2
7368 SMITH 800------------->3
7788 SCOTT 3000------------->4
7876 TEST3 1100------------->5
PL/SQL procedure successfully completed.
# 커서를 활용한 실용 예제
SQL> DECLARE
2 tot NUMBER := 0;
3 CURSOR emp_cursor
4 IS
5 SELECT ENAME, SAL
6 FROM EM01;
7 BEGIN
8 dbms_output.put_line('이름 급여');
9 dbms_output.put_line('-------------------------');
10
11 FOR cur_var IN emp_cursor LOOP
12 tot := tot + cur_var.sal;
13 dbms_output.put_line(cur_var.ename);
14 dbms_output.put_line('_ ' || cur_var.sal);
15 END LOOP;
16 dbms_output.put_line('-------------------------');
17 dbms_output.put_line('_ ' || tot);
18 END;
19 /
이름 급여
-------------------------
MARTIN
_ 1250
JONES
_ 2975
TURNER
_ 1500
FORD
_ 3000
SMITH
_ 800
KING
_ 5000
WARD
_ 1250
BLAKE
_ 2850
CLARK
_ 2450
MILLER
_ 1300
ALLEN
_ 1600
SCOTT
_ 3000
JAMES
_ 1957
TEST
_ 800
TEST3
_ 1100
-------------------------
_ 30832
PL/SQL procedure successfully completed.
- 사원별 급여 현황을 그래프로 표현
SQL> DECLARE
2 CURSOR emp_cursor
3 IS
4 SELECT ENAME, SAL
5 FROM EMP
6 ORDER BY SAL DESC;
7
8 star VARCHAR2(100);
9 cnt NUMBER := 0;
10 BEGIN
11 dbms_output.put_line(' 사원별 급여 현황');
12 dbms_output.put_line('----------------------------------------');
13
14 FOR cur_var IN emp_cursor LOOP
15 star := NULL;
16 cnt := round(cur_var.sal/100, 0);
17
18 FOR i IN 1.. cnt LOOP
19 star := star || '*';
20 END LOOP;
21
22 dbms_output.put_line(cur_var.ename);
23 dbms_output.put_line('_ ' || star || ' ' || ' (' || cur_var.sal || ')');
24 END LOOP;
25 END;
26 /
사원별 급여 현황
----------------------------------------
KING
_ ************************************************** (5000)
FORD
_ ****************************** (3000)
SCOTT
_ ****************************** (3000)
JONES
_ ****************************** (2975)
BLAKE
_ ***************************** (2850)
CLARK
_ ************************* (2450)
JAMES
_ ******************** (1957)
ALLEN
_ **************** (1600)
TURNER
_ *************** (1500)
MILLER
_ ************* (1300)
MARTIN
_ ************* (1250)
WARD
_ ************* (1250)
ADAMS
_ *********** (1100)
SMITH
_ ******** (800)
SMITH
_ ******** (800)
PL/SQL procedure successfully completed.
