PL/SQL - Cursor

OracleDataBase기초

PL/SQL - Cursor

본이qq 2022. 6. 15. 14:46

cursor

-주로 프로시져 내부의 SQL 명령중 SELECT 명령의 결과가 다수의 행으로 얻어졌을 때 사용하는 결과를 저장하는 메모리 영역을 말합니다.

DECLARE
    v_job varchar2(30);
BEGIN
    select job into v_job from emp where deptno=30;
    DBMS_OUTPUT.PUT_LINE(v_job);
END;
->위의 익명블럭은 SELECT 명령의 결과가 1행(ROW)이므로 실행이 가능하지만, SELECT명령의 결과가 2행 이상이라면
에러가 발생합니다.

->2행이상의 결과를 담을 수 있는 메모리 영역(또는 변수)으로 사용하는 것이 CURSOR이며
자바의 리스트와 비슷한 구조를 가지고 있습니다. 또는 반복실행문을 이용하여 그 값들을 참조하고 출력하고 리턴할 수 있습니다.

▶CURSOR의 생성 - 실행단계

1. CURSOR의 생성 (정의)

CURSOR 사용할 커서의 이름[ (매개변수1, 매개변수2 ....)]
IS
SELECT ..SQL 문장

->매개변수의 역할 : select명령에서 사용할 값들을 저장 (주로 where절에서 사용할 값들)
->SELECT ...SQL 문장 : 실행되어 CURSOR에 결과를 안겨줄 SQL 명령

2. CURSOR의 OPEN (호출)

OPEN 커서이름[(전달인수1, 전달인수2 ....)]

->실제로 전달인수를 전달하여 커서안의 SQL문을 실행하고 결과를 커서에 저장합니다.

3.결과를 반복실행문과 함께 필요에 맞게 처리

LOOP
FETCH 커서이름 INTO 변수(들);
EXIT WHEN 커서이름%NOTFOUND; --select에 의해 얻어진 레코드가 다 소진되어 없을때까지 ...반복계속
필요에 맞는 처리 실행
END LOOP;

->FETCH 커서이름 INTO 변수; 커서에 담긴 데이터들중 한줄씩 꺼내서 변수(들)에 넣는 동작입니다.
->EXIT WHEN 커서이름%NOTFOUND; 꺼냈는데 데이터가 없으면 종료합니다.
->LOOP 안에서 필요에 맞는 처리를 데이터가 없을때까지 반복합니다.

4.CURSOR 닫기

-CLOSE 커서명

->전달인수로 부서번호를 전달한 후 그 부서의 사원이름들을 얻어오는 커서
DECLARE
v_ename emp.ename%TYPE;
v_job emp.job%TYPE;-- 사원의 이름을 저장할 변수

    --1. 커서의 생성 - 정의
    cursor cur_emp(p_deptno emp.deptno%TYPE)
    IS
    select ename, job from emp where deptno = p_deptno;
BEGIN
    --2. 커서를 호출 실행
    open cur_emp(30);
    --3. 반복실행문으로 얻어진 커서안의 내용을 하나씩 꺼내서 출력합니다.
    LOOP
        FETCH cur_emp into v_ename, v_job;
        exit when cur_emp%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_ename || '-' || v_job);
    END LOOP;
    --4.커서 close
    CLOSE cur_emp;
END;

▶커서와 FOR문

->기존의 FOR문
FOR 인덱스변수 IN [REVERSE] 처음값..끝값
LOOP
실행문
END LOOP;

->커서와 함께 사용하는 FOR문
FOR 레코드변수 IN 커서이름(전달인수1, 전달인수2 ....)
LOOP
실행문
END LOOP

->OPEN과 LOOP가 합쳐진 예입니다.

->FOR 레코드변수 IN 커서이름(전달인수1, 전달인수2...) : OPEN으로 실행(호출) 하던 동작이 FOR문의 IN 다음으로 이동
-> 실행결과는 하나씩(1행, 1레코드) 레코드 변수에 저장되어 반복실행됩니다.
-> 자동으로 실행결과의 갯수만큼 반복실행됩니다.

DECLARE
    --레코드 변수를 사용하기 때문에 각 필드값을 저장할 변수는 만들지 않습니다.
    CURSOR cur_emp(p_deptno emp.deptno%TYPE)
    IS
    SELECT ename FROM emp WHERE deptno = p_deptno;
BEGIN
    FOR emp_rec IN cur_emp(30)  --커서에서 한 행을 꺼내서 한개의 행을 저장할 수 있는 변수 (emp_rec)에 저장
    LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.ename);    --필요한 필드를 커서에 담았다면, emp_rec뒤로 필드명을 지정하여 사용이 가능합니다.
    END LOOP;
END;

->FOR문을 이용하여 커서변수의 사용이 조금 더 간단해집니다.
DECLARE

BEGIN
    FOR emp_rec IN (SELECT * FROM emp WHERE deptno = 30)
    LOOP
         DBMS_OUTPUT.PUT_LINE(emp_rec.ename || '-' || emp_rec.job);


    END LOOP;
END;

--연습문제
-부서번호가 30번인 사원의 이름, 부서명, 급여, 급여수준(높음, 보통, 낮음) 을 출력하세요
-급여(sal)는 1000 미만 낮음, 1000~2500 보통 나머지 높음으로 출력하세요
--이름 - 부서명 - 급여 - 높음 순으로 출력하세요 .

DECLARE
    v_grade VARCHAR(10);

BEGIN

    FOR emp_rec IN(SELECT a.ename, b.dname, a.sal FROM emp a, dept b WHERE a.deptno = b.deptno and a.deptno = 30)

    LOOP
        IF emp_rec.sal < 1000 THEN
            v_grade := '낮음';
        ELSIF  emp_rec.sal BETWEEN 1000 and 2500 THEN
            v_grade := '보통';
        ELSE
            v_grade := '높음';
        END IF;
        DBMS_OUTPUT.PUT_LINE(emp_rec.ename || '-' || emp_rec.dname || '-' || emp_rec.sal || '-' || v_grade);
    END LOOP;
END;


▶커서 변수
-앞에서 생성한 커서의 이름은 함수처럼 호출되는 이름이기도 하고, 커서를 대표하는 이름이었습니다.
-그러나 커서의 이름으로 다른 커서를 만들지는 못합니다.
-변수로 치면 앞에서 만든 커서의 이름은 상수 정도로 표현이 가능합니다.
-앞으로 나올 이름은 변수로서 사용되고, 다른 커서도 저장할 수 있게 사용하고자 합니다.
-커서 변수를 사용해야 프로시져 내에서 커서변수를 OUT 변수로 지정하고 리턴동작으로 활용할 수 있습니다.

▶커서 변수의 선언
TYPE 사용할 커서의 타입이름 IS REF CURSOR[RETURN 반환타입 ];    ->생성된 커서타입의 이름으로 커서 변수를 선언할 예정입니다.
커서변수이름    커서타입이름;

->커서 타입을 만들 때 RETURN 값을 지정하면 강한커서타입이 생성되는 것이고, RETURN이 없으면 약한 커서타입이라고 지칭합니다.
TYPE dep_curtype1 IS REF CURSOR RETURN emp%ROWTYPE; -->강한 커서 타입
TYPE dep_curtype2 IS REF CURSOR; -->약한 커서 타입
-> 위 두줄의 명령은 커서의 이름을 생성한 것이 아니라, 커서를 선언할 수 있는 "커서자료형(TYPE)"을 생성한겁니다.

-> 커서자료형(TYPE)을 이용하여 이제 실제 커서변수를 선언할 수 있습니다.
cursor1 dep_curtype1;
cursor2 dep_curtype2;

->cursor1과 cursor2 변수에는 select 명령을 담아서 커서를 완성할 수 있습니다.
->또한 커서내용 (select문) 이 고정적이지 않고 바뀔 수 있습니다.
-> 다만 cursor1은 강한 커서타입이므로 정의되어 있는대로 (RETURN departments%ROWTYPE) 레코드 전체의 결과를 얻는 select만 저장할 수 있습니다.

OPEN cursor1 FOR SELECT empno, ename FROM emp WHERE deptno = 30; --x 불가능
OPEN cursor1 FOR SELECT * FROM emp WHERE deptno = 30; --o 가능

OPEN cursor2 FOR SELECT empno, ename FROM emp WHERE deptno = 30; --o 가능
OPEN cursor2 FOR SELECT * FROM emp WHERE deptno = 30; --o 가능

DECLARE
    v_deptno emp.deptno%TYPE;    --일반 변수 선언
    v_ename emp.ename%TYPE;     --일반 변수 선언
    TYPE emp_dep_curtype IS REF CURSOR; --약한 커서타입 선언
    emp_curvar emp_dep_curtype; --생성한 커서타입으로 커서변수 선언
BEGIN
    OPEN emp_curvar FOR SELECT ename, deptno FROM emp WHERE deptno = 20; --커서변수에 select문 설정

    LOOP
        FETCH emp_curvar INTO v_ename, v_deptno;
        EXIT WHEN emp_curvar%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_ename || '-' || v_deptno);
    END LOOP;

    --OPEN emp_curvar FOR SELECT....
    --기존의 커서변수에 다른 SELECT를 적용하여 커서변수를 재활용 가능합니다.

END;

dep_curtype3 SYS_REFCURSOR;     --시스템에서 제공해주는 커서 타입

-SYS_REFCURSOR를 사용하면
TYPE emp_dep_curtype IS REF CURSOR; --> 커서타입 생략가능
emp_curvar emp_dep_curtype; --> 변수 선언 SYS_REFCURSOR 형태로 선언

1. SYS_REFCURSOR 변수 생성
2. 변수에 select 연결
3. FETCH로 꺼내서 처리(반복실행)

DECLARE
    v_deptno emp.deptno%TYPE;    --일반 변수 선언
    v_ename emp.ename%TYPE;     --일반 변수 선언
    emp_curvar SYS_REFCURSOR; -- SYS_REFCURSOR 타입의 커서변수 선언(커서 자료형 생성이 필요없이 사용 가능합니다.)
BEGIN
    OPEN emp_curvar FOR SELECT ename, deptno FROM emp WHERE deptno = 20; --커서변수에 select문 설정

    LOOP
        FETCH emp_curvar INTO v_ename, v_deptno;
        EXIT WHEN emp_curvar%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_ename || '-' || v_deptno);
    END LOOP;

    --OPEN emp_curvar FOR SELECT....
    --기존의 커서변수에 다른 SELECT를 적용하여 커서변수를 재활용 가능합니다.

END;

->프로시저에서의 커서 사용 예 - select의 결과를 커서에 담아서 프로시져를 호출한 지점에 보내주려고 합니다.

CREATE OR REPLACE PROCEDURE testCursorArg(  p_curvar OUT SYS_REFCURSOR ) --OUT매개 변수로 커서 변수 선언

IS
    temp_curvar SYS_REFCURSOR; --프로시져에서 사용할 커서 변수
BEGIN
    OPEN temp_curvar FOR SELECT ename, sal FROM emp WHERE deptno=10;
    --현재 위치에서 커서의 내용을 fetch하지 않습니다.
    --반복실행도, fetch도 쓰지 않습니다.
    --OUT변수에 실행된 커서 변수의 내용을 담습니다.
    p_curvar := temp_curvar;
END;

DECLARE
    curvar SYS_REFCURSOR; --커서변수 생성
    v_ename emp.ename%TYPE;
    v_sal emp.sal%TYPE;
BEGIN
    testCursorArg(curvar);
    LOOP
        FETCH curvar INTO v_ename, v_sal;
        EXIT WHEN curvar%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_ename || '-' || v_sal);
    END LOOP;
END;