Cursor(커서) - 2

커서 칼럼의 앨리어스 사용

칼럼의 앨리어스(Alias)는 칼럼의 이름을 변경하거나 단순 칼럼이 아니고 표현식인 칼럼에 이름을 지정하기 위해서 사용된다. 커러의 칼럼에 계산식이 사용된 경우에 반드시 앨리어스를 사용해야 하는 것은 아니다. 하지만 다음과 같은 경우에는 반드시 앨리어스를 사용해야 한다.

• CURSOR FOR LOOP에 사용된 커서에서 칼럼에 계산식이 사용된 경우
• 선언된 커서를 %ROWTYPE을 사용하여 앵커로 참조하는 경우

DECLARE
	-- 명시적 커서
    CURSOR emp_cursor IS
    	SELECT empno	사번
        	,  ename	이름
            ,  sal+NVL(comm,0) 총급여  -- 앵커로 참조되는 칼럼이 표현식이라면 앨리어스가 필요
          FROM emp;
       v_emp_rec  emp_cursor%ROWTYPE;
BEGIN
	OPEN emp_cursor;
    
    DBMS_OUTPUT_PUT_LINE('사번  이름		총급여');
    DBMS_OUTPUT_PUT_LINE('===	===			=====');
    LOOP
    	FETCH	emp_cursor INTO v_emp_rec;
        EXIT WHEN emp_cursor%NOTFOUND;
        DBMS_OUTPUT_PUT_LINE(' ' ||TO_CHAR(v_emp_rec.사번, '9999') || ' ' ||
        					RPAD(v_emp_rec.이름, 10) || ' ' ||
                            TO_CHAR(v_emp_rec.총급여, '99999');
   END LOOP;
   
  -- 커서를 CLOSE
  CLOSE emp_cursor;
END;

 

두 경우 모두 계산 표현식에 앨리어스를 주지 않으면 이 표현식에 해당하는 커서의 필드가 유효한 이름을 가지지 못하기 때문에 PL/SQL 측에서도 레코드의 필드에 이름을 부여할 방법이 없다(아예 없는 것은 아니고 오라클이 내부적으로 만들어주는 이름이 있기는 하다.)

 

커서 매개변수

명시적 커서에는 매개변수를 사용할 수 있다.

명시적 커서의 매개변수는 함수의 매개변수와 유사한 개념이다. 커서를 열 때 매개변수로 값을 넘겨받아서 바인드 변수로 사용한다.

DECLARE
	v_name emp.ename%TYPE;
    v_empno NUMBER := 7788;
    
    -- 매개변수 a_empno를 가지는 명시적 커서
    CURSOR ename_cursor(a_empno NUMBER) IS
    	SELECT ename
          FROM emp
         WHERE empno = a_empno;
BEGIN
	-- 매개변수를 사용하여 커서를 OPEN, 매개변수 v_empno는 사번 7788
    OPEN ename_cursor(v_empno);
    
    -- SELECT 결과를 FETCH
    FETCH ename_cursor
      INTO v_name;
    DBMS_OUTPUT_PUT_LINE('이름 = '||v_name);
    
    -- 커서 CLOSE
    CLOSE ename_cursor;
END;

사실, 커서의 매개변수를 사용하지 않고도 단순히 상위 블록의 변수를 직접 사용하여 위와 동일한 결과를 얻을 수 있다.

하지만 매개변수를 사용하는 것이 더 명료하고 작성 의도도 이해하기 쉽다. 커서에 매개변수를 사용하지 않고 외부 변수를 직접 사용하는 것은 마치 함수에 값을 전달할 때 매개 변수를 사용하지 않고 전역변수(글로벌 변수)를 사용하여 전달하는 것과 같다,

 

커서 변수

커서변수는 선언 시에 REF CURSOR 키워드를 사용하기 때문에 단순히 REF CURSOR라고도 불린다.

커서변수는 명시적 커서와 상당히 유사한데 명시적 커서가 가지는 네 개의 커서속성도 동일하게 가진다.

명시적 커서와 다른 점은 다음과 같다.

• 커서 변수는 하나의 쿼리에만 국한되어 사용되지 않는다. 커서변수를 하나의 쿼리에 대해 OPEN/FETCH/CLOSE한 후 다른 쿼리에 대해 다시 사용할 수 있다.
• 서브프로그램의 매개변수로 사용할 수 있다. 매개변수로 사용하여 쿼리 결과 집합을 서브프로그램들 간에 전달하는 것이 가능하다.
• 다른 프로그래밍 언어의 호스트 변수로 선언할 수 있다. 이를 사용하여 쿼리의 결과 집합을 PL/SQL 프로그램과 클라이언트 프로그램 간에 전달할 수 있다.

커서 변수는 REF CURSOR 키워드를 사용하여 타입 선언한 후 해당 타입을 통해 사용할 수 있다.

TYPE 타입명 IS REF CURSOR [ RETURN 반환데이터타입 ]
변수명 타입명;

RETURN 다음의 반환데이터타입으로 명시할 수 있는 데이터타입에는 테이블에 대한 %ROWTYPE, 커서나 다른 커서변수등에 대한 %ROWTYPE, 레코드 타입등 다양한 타입이 가능하다. 커서 변수는 RETURN 타입의 지정여부에 따라 강한 타입과 약한 타입으로 나누어 진다.

• 깅한 타입: RETURN 타입을 지정하는 REF CURSOR타입. 특정 데이터 타입을 명시하므로 PL/SQL 컴파일러가 이에 대한 사전 검사를 할 수 있어서 오류가 발생할 가능석이 적다.
• 약한 타입: RETURN 타입을 지정하지 않는 REF CURSOR타입. 반환형에 관계없이 임의의 쿼리에 대해 사용할 수 있어서 유연성이 높다.

DECLARE
	TYPE empcursor_type		IS REF CURSOR RETURN emp%ROWTYPE;	--강한 타입(테이블%ROWTYPE)
    TYPE genericcursor_type IS REF CURSOR;						--약한 타입
    
    v_c1 empcursor_type;
    v_c2 genericcursor_type;
    v_c3 SYS_REFCURSOR;		-- 타입 선언 없이 사용 가능
    
    TYPE empcursor_type2 IS REF CURSOR RETURN v_c1%ROWTYPE;		-- 강한 타입(변수%ROWTYPE)
    v_c4 empcursor_type2;
    
    CURSOR emp_cursor IS
     SELECT empno, ename
       FROM emp;
     TYPE empcursor_type3 IS REF CURSOR RETURN emp_cursor%ROWTYPE;	-- 강한 타입(커서%ROWTYPE)
     v_c5 empcursor_type3;
     
     TYPE emp_rec IS RECORD(
     	empno emp.empno%TYPE,
        ename emp.ename%TYPE
      );
      TYPE empcursor_type4 IS REF CURSOR RETURN emp_rec;			-- 강한 타입(레코드 타입)
      v_c6 empcursor_type4;
 BEGIN
 	NULL;
 END;

커서 변수는 커서와 달리 하나의 쿼리에만 국한하여 사용되지는 않는다.

강한 타입의 커서 변수는 반환되는 칼럼의 개수와 타입만 일치하면 어떤 SELECT문에 대해서도 OPEN이 가능하다.

DECLARE
	TYPE emp_rec IS RECORD(
    	empno emp.empno%TYPE,
        ename emp.ename%TYPE,
        sal	  emp.sal%TYPE
     );
     v_emprec emp_rec;	-- FETCH 결과를 저장할 레코드변수
     TYPE	emp_cursor_type IS REF CURSOR RETURN emp_rec;	-- 커서 타입
     v_empcur emp_cursor_type;								-- 커서 변수
BEGIN
	-- 첫번째 SQL문에 대해 커서변수를 OPEN
    OPEN v_empcur FOR SELECT empno, ename, sal FROM EMP WHERE deptno = 10;
    LOOP
    	FETCH v_empcur INTO v_empcur;
        EXIT WHEN v_empcur%NOTFOUND;
        DBMS_OUTPUT_PUT_LINE('EMPNO='||v_empcur.empno||', ENAME='||v_empcur.ename||
        					', SAL='||v_empcur.sal);
    END LOOP;
    CLOSE v_empcur;
    
    DBMS_OUTPUT_PUT_LINE(' ');
    
    -- 두번째 SQL문에 대해 커서변수 OPEN
    OPEN v_empcur FOR SELECT empno, ename, sal+NVL(comm,0) FROM EMP WHERE deptno = 20;
    LOOP
    	FETCH v_empcur INTO v_emprec;
        EXIT WHEN v_empcur%NOTFOUND;
        DBMS_OUTPUT_PUT_LINE('EMPNO='||v_empcur.empno||', ENAME='||v_empcur.ename||
        					', SAL='||v_empcur.sal);
    END LOOP;
    CLOSE v_empcur;
END;

커서변수는 매개변수로 서브프로그램에 전달될 수 있다.

DECLARE
	TYPE emp_rec IS RECORD(
    	empno emp.empno%TYPE,
        ename emp.ename%TYPE
    );
    TYPE	emp_cursor_type IS REF CURSOR RETURN emp_rec; -- 레코드 타입의 커서변수
    v_empcur emp_cursor_type;							-- 커서변수
    
    PROCEDURE print_emp(a_empcur emp_cursor_type) IS -- 커서변수를 프로시저의 매개변수로 사용
    	v_emprec emp_rec;
    BEGIN
      LOOP
      	FETCH a_empcur INTO v_emprec;
        EXIT WHEN a_empcur%NOTFOUND;
        DBMS_OUTPUT_PUT_LINE('EMPNO='||v_emprec.empno||', ENAME='||v_emprec.ename);
      END LOOP;
    END;
BEGIN
	OPEN v_emprec FOR SELECT empno, ename FROM EMP;
    print_emp(v_emprec);	-- 커서를 매개변수로 전달
    CLOSE v_emprec;
END;

매개변수로 커서변수가 전달될 때 강한 타입의 경우 컴파일 시에 반환형을 검사하며 타입이 일치하지 않는 경우에는 컴파일 오류가 발생한다, 약한 타입인 경우에는 사전 검사가 불가능하므로 타입 불일치가 있다면 실행시에 미리 정의된 예외인 ROWTYPE_MISMATCH가 발생한다. 약한 타입의 커서 변수는 반환 타입이 서로 다른 쿼리에 대해서도 사용할 수 있다.

 

SELECT FOR UPDATE

SELECT FOR UPDATE문을 사용하면 커서에서 SELECT문에 의해 FETCH되는 로우에 즉시 락(Lock)을 걸 수 있다.

락은 로우의 변경 여부와 무관하게 걸리며 다음 커밋이나 롤백을 실행할 때까지 유지된다.

SELECT FOR UPDATE를 실행하면 일반 SELECT문과 마찬가지로 로우를 조회할 수 있는데 조회되는 로우를 SELECT할 수 있는 트랜잭션은 하나뿐이며 다른 SELECT FOR UPDATE문이 동일 로우를 조회해야 하는 경우에는 해당 로우를 SELECT하지 못하고 락을 대기하게 된다.(FOR UPDATE를 지정하지 않은 단순 SELECT문으로는 락이 걸리지 않는다.)

 

이 문장은 값을 조회하여 조건 검사 후 조건에 맞을 때만 변경을 하고자 하는 경우에 동시성 제어를 위해 사용할 수 있다. FOR UPDATE문을 사용하지 않는다면 테이블을 변경하기 위해서는 일단 SELECT문을 실행한 후 해당 로우를 UPDATE하는 방식을 사용해야 한다. 이에 SELECT문과 UPDATE문 사이에 시차가 존재하므로 동일한 변경 프로그램이 동시에 여러 개 실행되는 경우 한 트랜잭션의 SELECT문과 UPDATE문 사이에 다른 트랜잭션이 SELECT문이나 UPDATE문을 실행할 수 있어서 데이터의 무결성이 깨지는 문제가 발생할  수 있다.

 

이런 문제를 방지하기 위해 SELECT문과 UPDATE문을 하나로 만들어 주어 무결성을 보장하도록 하는 문장 기법이 바로 SELECT FOR UPDATE문이다. SELECT FOR UPDATE문은 조회 대상 로우에 대해 SELECT문과 동시에 락을 거는 방식으로 동작하여 SELECT문과 UPDATE문 사이에 다른 데이터 변경 거래가 끼어들 수 없도록 원천적으로 차단함으로써 SELECT문과 UPDATE문이 마치 하나의 연산인 것처럼 실행할 수 있도록 해준다.

 

SELECT FOR UPDATE문의 단점은 SELECT만 하고 UPDATE를 하지 않은 로우에 대해서도 락이 걸려서 커밋이나 롤백이 수행되기 전에는 락이 해제되지 않는다는 점이다. 이 때문에 SELECT FOR UPDATE문은 프로그램의 동시성을 크게 저해하는 부작용을 가지고 있다.

 

SELECT FOR UPDATE문을 사용할 때 현재 커서가 위치한 로우를 지정하기 위해서 WHERE절에 CURRENT OF 키워드를 사용한다, UPDATE문이나 DELETE문에 조건으로 'WHERE CURRENT OF 커서명'을 지정하면 오로지 커서가 현재 위치한 로우 한 건에 대해서만 UPDATE나 DELETE가 수행된다.

DECLARE
   CURSOR emp_cursor IS
     SELECT empno, ename, job, sal
       FROM emp
      WHERE sal < 1500
       FOR UPDATE;		-- 조회와 동시에 락을 검
   BEGIN
     FOR e IN emp_cursor
     LOOP
       IF e.job = 'SALESMAN' THEN
        UPDATE emp
           SET comm = comm * 1.1
         WHERE CURRENT OF emp_cursor;	-- 현재 커서가 위치한 로우만을 UPDATE
       END IF;
     END LOOP;
    EMD;