Oracle PL/SQL 프로시저[커서와FOR문]

FOR 문은 커서와함께 또 다른 형태로 시용할수 있다.

FOR 인덱스 IN [REVERS] 초기값..최종값
LOOP
처리문;
END LOOP;

커서와 함께 사용될 경우 FOR 문 구문형식

FOR 레코드 IN 커서명(매게변수1.. )
LOOP
처리문;
END LOOP;

해당커서의 패치가 끝나면 자동으로  LOOP 가 종료된다. 레코드는 테이블 로우 전체를 담아둘 수 있는 오라클 데이터 타입이다.

 

DECLARE
      --커서선언
      CURSOR cur_emp_dep (cp_department_id employees.department_id%TYPE)
      IS
      SELECT employee_name
      FROM employees     
      WHERE department_id = cp_department_id
      BEGIN
        -- FOR 문을 통한레코드 작업
        FOR emp_rec IN cur_emp_dep(90)
          LOOP
            DBMS_OUTPUT.PUT_LINE(emp_rec.employee_name);
            END LOOP;
            END;

FOR문을 같이 사용할때는 커서를 열고 닫을 필요가 없다 코드가 훨씬 줄어 들고 깔끔해졌다. 커서 선언 부분을 없애고 FOR문에 직접 커서 정의 내용을 넣을 수도 있다.

 

DECLARE
  BEGIN
       FOR EMP_REC  IN ( SELECT EMPLOYEE_NAME
                           FROM EMPLOYEES
                           WHERE DEPARTMENT_ID = 80)
                           
                           
         LOOP
           DBMS_OUTPUT.PUT_LINE(HRDAILYRSN.HRNM);
           END LOOP;
           END;

정의하는 SELECT 문을 직접 FOR문 안으로 옮기더라도 동일한 결과가 나옴을 확인할수 있다. 

 

 

커서변수

커서변수는 정확히 말하자면 변수라기 보다 상수라고 할수 있다. 변수는 한번 값을 할당한 뒤에도 다른 값을 할당해 사용할 수 있지만 상수는 맨 처음 정의할때 설정했던 값으로만 사용할수 있다. 즉 '명시적 커서'는 '커서 상수' 라고도 할수 있다.

 

한개 이상의 쿼리를 연결해 사용할 수 있다

하나의 커서 변수를 선언해 쿼리를 연결해서 사용한뒤, 같은 변수를 또 다른 쿼리에 연결해서 사용할 수 있다.

 

변수처럼 커서 변수를 함수나 프로시저의 매개변수로 전달할 수 있다

 

커서 속성을 사용할 수 있다

CUR_VAR 이란 커서 변수를 선언하고 커서를 할당했다면 CUR_VAR%FOUND.CUR_CAR%ISOPEN 형태로 속성을 사용할수 있다.

 

커서변수 선언하기

TYPE 커서_타입명 IS REF CUSOR [RETURN 반환 타입];
커서_변수명_타입명;

"RETURN 반환타입" 에서 반환타입은 해당 커서가 반환하는 결과 집합을 나타낸다. % ROWTYPE 속성을 사용해서 정의한다.

 

테이블의 특정 컬럼 값을 받는 변수를 선언할떄는 

변수명 테이블명.컬럼명%TYPE

커서가 반환하는 걸과는 한 개 이상의 컬럼 즉 레코드 타입이므로 %TYPE 대신 %ROWTYPE를 사용한다

변수명 테이블명.컬럼명%ROWTYPE

 

"RETURN 반환타입" 을 명시하는것은 해당 커서의 결과 집합을 고정 하는 것이므로 강한타입이라 하는반면, 명시하지 않는 경우는 결과 집합이 유동적이므로 약한 타입이라고 부르는 것이다.

 

TYPE DEP_CURTYPE IS REF CUSOR RETURN DEPARTMENTS%ROWTYPE; -- 강한타입
TYPE DEP_CURTYPE IS REF --약한티입

 

TEST_CURSOR SYS_REFCURSOR;

SYS_REFCURSOR 란 타입을 사용하는 것이다. 따라서 SYS_REFCURSOR를 사용할 때는 별도로 커서타입을 선언할 필요 없이 다음과 같이 커서 변수만 선언하면 된다.

 

커서 변수 사용하기

커서 변수와 쿼리문을 연결할 때는 다음과 같이 OPEN .. FOR 구문을 사용한다.

OPEN 커서 변수명 FOR SELECT 문;

커서 변수의 특징중 하나가 여러개의 쿼리를 연결해서 사용할 수 있다는 점이다.

OPEN .. FOR 구문에서 SELECT 문의 세부 내용은 변수경 가능하므로 여러개의 쿼리를 한커서 변수에 할당해서 사용할 수 있는 것이다. 특히 선언한 커서 타입이 약한 커서타입이면 SELECT 문에 따라 사용할수 있는 커서 결과 집합의 종류는 무궁무진해진다.

 

TYPE TEST_CURTYPE IS REF CUSOR RETURN DEPARTMENTS%ROWTYPE;
TEST_CURVAR TEST_CURTYPE;

강한 커서 타입은 결과집합의 구조가 이미 결정되었으므로 실제 커서를 정의하는 쿼리도 이에 맞춰줘야 한다.

OPEN TEST_CURVAR FOR SELECT * FROM DEPARTMENTS; -- 정상
OPEN TEST_CURVAR FOR SELECT DEPARTMENT_ID FROM DEPARTMENTS; -- DEPARTMENT_ID만 선택했기 때문에 (X)
OPEN TEST_CURVAR FOR SELECT * FROM EMPLOYEES -- 전혀 다른 사원 테이블을 조회 하므로 (X)

 

TYPE TEST IS REF CURSOR;
TEST_CURVAR TEST_CURTYPE;

OPEN TEST_CURVAR FOR SELECT * FROM DEPARTMENTS; -- 정상
OPEN TEST_CURVAR FOR SELECT DEPARTMENT_ID FROM DEPARTMENTS;-- 정상
OPEN TEST_CURVAR FOR SELECT * FROM EMPLOYEES;-- 정상

 

커서 변수에서 집합 결과 가져오기

FETCH 커서변수명 INTO 변수1, 변수2..
FETCH 커서변수명 INTO 레코드명;

해당 커서에 정의한 결과 집합 개수에 따라 변수에 받아올 수도, 레코드 정의해서 받아올 수도 있다. 또한 커서 변수를 사용할 때는 커서를 닫는 작업이 필요하다.

 

 

    DECLARE
       -- 사원명을 받아오기 위한 변수 선언
       vs_emp_name employees.emp_name%TYPE;

       -- 약한 커서 타입 선언
       TYPE emp_dep_curtype IS REF CURSOR;
       -- 커서 변수 선언
       emp_dep_curvar emp_dep_curtype;
    BEGIN

      -- 커서 변수를 사용한 커서 정의 및 오픈
      OPEN emp_dep_curvar FOR SELECT emp_name
                         FROM employees
                        WHERE department_id = 90;

      -- LOOP문
      LOOP
         -- 커서 변수를 사용해 결과 집합을 vs_emp_name 변수에 할당
         FETCH emp_dep_curvar INTO vs_emp_name;

        -- 더 이상 패치된 참조 로우가 없으면 LOOP 탈출(커서 변수를 이용한 커서 속성 참조)
        EXIT WHEN emp_dep_curvar%NOTFOUND;

        -- 사원명을 출력
        DBMS_OUTPUT.PUT_LINE(vs_emp_name);

      END LOOP;
    END;

 

 

 

       
DECLARE
       --사원명을 받아오기 위한 변수선언
       VS_EMP_NAME EMPLOYEES.EMPLOYEE_NAME%TYPE;
       --SYS_REFCURSOR 타압의 커서변언
       VN_DEPT_CURVAR IS REF SYS_REFCURSOR;
       
      OPEN VN_DEPT_CURVAR FOR SELECT EMPLOYEE_NAME
                              FROM EMPLOYEES
                              WHERE DEPTCD = 80;
                              
       LOOP
         --커서 변수를 사용해 결과 집합을 변수에 할당
         FETCH VN_DEPT_CURVAR INTO VS_EMP_NAME;
         -- 더 이상 패치된 참조 로우가 없으면 LOOP 탈출
         EXIT WHEN VN_DEPT_CURVAR %NOTFOUND;
         DBMS_OUTPUT.PUT_LINE(VS_EMP_NAME)
         END LOOP;
         END;

SYS_REFCURSOR 사용하면 별도로 커서 타입을 선언하지 않고도 같은 결과를 얻을 수 있다. 커서 변수에 대해 정리해 보면, 강한 커서타입이 아닌 약한 커서타입을 사용할때 SYS_REFCURSOR 타입을, 강한커서 타입을 사용해야 한다면 REFCURSOR를 사용하는 것이 여러모로 편리하다.

 

커서 변수를 매개변수로 전달하기

사용자 정의 함수나 프로시저의 매개변수로 전달할 수 있다. 매개변수를 전달해서 받아 사용해야 하므로, 전달하는 쪽이나 받는 쪽이나 모두 같은 커서 타입의 변수를 사용해야한다. 

 

 DECLARE
      -- Ⓐ SYS_REFCURSOR 타입의 커서 변수 선언
      emp_dep_curvar SYS_REFCURSOR;

      -- 사원명을 받아오기 위해 변수를 선언한다.
      vs_emp_name employees.emp_name%TYPE;

      -- Ⓑ 커서 변수를 매개변수로 받는 프로시저, 매개변수는 SYS_REFCURSOR 타입의 IN OUT형
      PROCEDURE test_cursor_argu ( p_curvar IN OUT SYS_REFCURSOR)
      IS
        c_temp_curvar SYS_REFCURSOR;
      BEGIN
        -- 커서를 오픈
        OPEN c_temp_curvar FOR
        SELECT emp_name
         FROM employees
        WHERE department_id = 90;

        -- Ⓒ 오픈한 커서를 IN OUT 매개변수에 다시 할당
        p_curvar := c_temp_curvar;
      END;

    BEGIN
      -- 프로시저 호출
      test_cursor_argu (emp_dep_curvar);
      -- 프로시저 호출 후 emp_dep_curvar 변수에는 이 프로시저의 c_temp_curvar 결과가 담겨 있음

       -- Ⓒ 전달해서 받은 매개변수를 LOOP문을 사용해 결과를 출력
      LOOP
        -- 커서 변수를 사용해 결과 집합을 vs_emp_name 변수에 할당
        FETCH emp_dep_curvar INTO vs_emp_name;
        -- 더 이상 패치된 참조 로우가 없으면 LOOP 탈출(커서 변수를 이용한 커서 속성 참조)
        EXIT WHEN emp_dep_curvar%NOTFOUND;
        -- 사원명을 출력
        DBMS_OUTPUT.PUT_LINE(vs_emp_name);

      END LOOP;

    END;

 

A SYS_REFCURSOR 타입 커서 변수를 생성하고, B 커서 변수를 매개 변수로 받는  프로시저를 만들었다.

이 프로시저의 매개변수는 당연히 SYS_REFCURSOR 타입으로 IN OUT 매개변수로 만들어 프로시저에서 커서를 오픈 정의한뒤, C 패치한 결과를 매개변수에 할당하고 마지막으로 프로시저를 호출하는 부분에서 전달해 받은 매개변수를 LOOP문을 사용해 이 매개변수에 담겨 있는 값을 출력해 봤다.

 

커서 표현식

 

 

 

SELECT D.DEPARTMENT_NAME
	(SELECT EMPLOYEE_NAME
    	FROM EMPLOYEES
        WHERE E.DEPARTMENT_ID = D.DEPARTMENE_ID) AS EMP_NAME
        
        FROM DEPARTMENTS D
        WHERE D.DEPARTMENT_ID = 90;

 

 

SELECT  리스트에서 컬럼용으로 서브 쿼리를 사용할때 반드시 반환되는 로우는 한개여야 한다 위 쿼리는 현재 3개의 로우를 반환하기 때문에 오류가 날것이다.

 

CURSOR(서브쿼리)

SELECT D.DEPARTMENT_NAME
	CURSOR(SELECT E.EMPLOYEE_NAME
    		FROM EMPLOYEES
            WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID)
            
            FROM DEPARTMENTS
            WHERE D.DEPARTMENT_ID = 90;

반횐되는 로우는 한개 뿐이다. 왜냐면 90 번 부서는 한개만 있기 때문이다.

다만 커서 표현식을 사용한 두번째 컬럼은 로우는 한 개지만 내부적으로 3개의 데이터를 담고 있다.

 

DECLARE
         --커서표현식을 사용한 명시적 커서 선언
         CURSOR MY_CURSOR_TEST 
         IS SELECT D.DEPARTMENT_ID
            CURSOR(SELECT E.DEPARTMENT_ID
                    FROM EMPLOYEES E
                    WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID) AS EMP NAME
                    
                    FROM DEPARTMENTS D
                    WHERE D.DEPARTMENT_ID = 90;
                    
                    --부서명을 받아오기위한 변수
                    
                    VS_DEPARTMENT_NAME DEPARTMENTS.DEPARTMENT_NAME %TYPE;
                    
                    C_EMP_NAME SYS_REFCURSOR;
                    
                    VS_EMP_NAME EMPLOYEES.EMP_NAME%TYPE;
                    
                    BEGIN
                      OPEN MY_CURSOR_TEST;
                      --명시적 커서를 받아 오는 첫번째 LOOP
                      
                      LOOP
                        --부서명은 변수, 사원명 결과 집합은 커서 변수에 패치
                        FETCH MY_TEST_CURSOR INTO VS_DEPARTMENT_NAME,C_EMP_NAME;
                        EXIT WHEN MY_CURSOR_TEST %NOTFOUND;
                        DBMS_OUTPUT.PUT_LINE('부서명:' || VS_VS_DEPARTMENT_NAME);
                        
                        --사원명을 출력하기 위한 두번째 LOOP
                        FETCH MY_TEST_CURSOR INTO C_EMP_NAME;
                        EXIT WHEN MY_CURSOR_TEST %NOTFOUND;
                        
                        DBMS_OUTPUT.PUTLINE('사원명' || C_EMP_NAME);
                        END LOOP;
                      END LOOP;
                    END;

명시적 커서에 대한 LOOP문 한개와 커서표현식이 반환한 결과에 대한LOOP 한개를 중첩해서 사용한 것이다.