[PLSQL] cursor

DataBase/PLSQL 2022. 9. 9. 17:11

SET SERVEROUTPUT ON;
-- 주로 프로시져 내부의 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;
--결과: exact fetch returns more than requested number of rows

위의 익명블럭은 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;

4. CURSOR 닫기

 CLOSE 커서명;

-- CURSOR의 사용
--전달인수로 부서번호를 전달한 후 
--그 부서의 사원이름들을 얻어오는 CURSOR
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;

CURSOR와 FOR문

1. 기존 FOR문

 FOR 인덱스변수 in [reverse] 처음값...끝값
 LOOP
      실행문
 END LOOP;

-- LOOP FETCH 사용
DECLARE
    v_ename emp.ename%TYPE;
    CURSOR cur_emp(p_deptno emp.deptno%TYPE)
    IS SELECT ename FROM emp WHERE deptno=p_deptno;
BEGIN
    OPEN cur_emp(30);
    LOOP
        FETCH cur_emp INTO v_ename; 
--한 행을 꺼내서 담겨있는 필드를 저장할 변수(v_ename)에 필드값을 저장
        EXIT WHEN cur_emp%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_ename);
    END LOOP;
    CLOSE cur_emp;
END;

2. CURSOR와 함께 사용하는 FOR문

 FOR 레코드변수 IN 커서명(전달인수1, 전달인수2, ...)
 LOOP
      실행문
 END LOOP;

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

-- FOR문을 이용하여 커서변수의 사용이 조금 간단해짐
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문과 CURSOR 사용
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번인 사원의 이름, 부서명, 급여, 급여수준을 출력
-- 급여수준은 1000미만 '낮음', 1000~2500 '보통', 나머지 '높음' 출력
-- 이름 - 부서명 - 급여 - 높음 순으로 출력

DECLARE
    level varchar2(30);
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<5500 THEN
            level:='낮음';
        ELSIF emp_rec.sal >= 5500 AND emp_rec.sal <= 6500 THEN
            level:='보통';
        ELSIF emp_rec.sal > 6500 THEN 
            level:='높음';
        END IF;
        DBMS_OUTPUT.PUT_LINE(emp_rec.ename||' - '||emp_rec.dname||
                             ' - '||emp_rec.sal||' - '||level);
    END LOOP;
END;

CURSOR 변수

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

CURSOR 변수 선언

1. CURSOR를 선언할 수 있는 "커서자료형(TYPE)"을 생성

TYPE 커서타입명 IS REF CURSOR[RETURN 반환타입];
--생성된 커서타임명으로 커서 변수를 선언할 예정

타입을 만들 때 RETURN 값을 지정하면 강한커서타입이 생성되는 것이고 RETURN이 없으면 약한커서타입이라고 지정

--강한 커서 타입
TYPE dep_curtypel IS REF CURSOR RETURN emp%ROWTYPE; 
--약한 커서 타입
TYPE dep_curtypel IS REF CURSOR;

2. 커서자료형(TYPE)을 이용하여 이제 실제 커서변수 선언

커서변수명 커서타입명;

cursor1 dep_curtype1;
cursor2 dep_curtype2;

3. CURSOR 완성
-cursor1과 cursor2 변수에는 select 명령을 담아서 커서를 완성 가능하며 커서 내용(select문)이 고정적이지 않고 바뀔 수 있음.
-다만 cursor1은 강한커서타입이므로 정의되어 있는데로 (return departments%ROWTYPE) 레코드 전체의 결과를 얻는 SELECT만 저장 가능

--불가능
OPEN cursor1 FOR SELECT empno, ename FROM emp WHERE deptno=30; 
--가능
OPEN cursor1 FOR SELECT * FROM emp WHERE deptno=30; 
OPEN cursor2 FOR SELECT empno, ename FROM emp WHERE deptno=30; 
OPEN cursor1 FOR SELECT * FROM emp WHERE deptno=30; -
-- 커서 변수를 만들어서 필요할 때마다 
-- 커서내용을 저장하고 호출해서 그 결과를 사용하려고 변수를 만듦

// 연습
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;

SYS_REFCURSOR로 변수 선언

-시스템에서 제공해주는 커서타입
-커서자료형 생성 생략 가능
-선언 방법: SYS_REFCURSOR 변수 생성 → 변수에 SELECT 연결 → 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;
END;

1. 프로시저에서의 커서 사용
-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;

저작자표시

'DataBase > PLSQL' 카테고리의 다른 글

[PLSQL] procedure (1)	2022.09.09
[PLSQL] function (0)	2022.09.09
[PLSQL] if, case, loop (0)	2022.09.09
[PLSQL] block, 출력, 변수, 자료형, SQL (0)	2022.09.09
[PLSQL] Procedural Language for SQL 설치 (0)	2022.09.09

ABOUT ME

im hippo im hippo

'DataBase > PLSQL' 카테고리의 다른 글

티스토리툴바

ABOUT ME

'DataBase > PLSQL' 카테고리의 다른 글

관련글 관련글 더보기

티스토리툴바