저장 서브프로그램 관리

정상적으로 컴파일되어 데이터베이스 서버에 저장된 서브프로그램이라도 관리가 필요하다.

업무 요건이나 환경이 바뀌면 응용 프로그램에도 변경이 필요한 것은 당연한 일이다. 때로는 인터페이스의 변경이나 추가가 필요할 수도 있고, 불필요한 서브프로그램은 삭제할 때도 있을 것이다.

변경과 삭제

저장 서브프로그램의 변경에는 ALTER문을, 삭제에는 DROP문을 사용한다.

ALTER문을 사용하여 변경할 수 있는 저장 서브프로그램의 속성에는 여러 가지가 있지만, 대부분 사용할 일은 그리 없고, 그나마 종종 사용하는 것은 소스 코드를 재컴파일하는 것이다. 

소스 코드를 재컴파일하는 문장은 다음과 같다.

ALTER FUNCTION		함수명 	COMPILE [ DEBUG ] ;
ALTER PROCEDURE		프로시저명 COMPILE [ DEBUG ] ;
ALTER PACKAGE		패키지명   COMPILE [ DEBUG ] ;
ALTER PACKAGE BODY  패키지명   COMPILE [ DEBUG ] ;
ALTER TRIGGER		트리거명   COMPILE [ DEBUG ] ;
ALTER TYPE			타입명	    COMPILE [ DEBUG ] ;
ALTER TYPE BODY 	타입명		COMPILE [ DEBUG ] ;

DEBUG 옵션은  PL/SQL 디버거를 사용할 수 있도록 컴파일하는 옵션이다.

 

DROP문은 존재하는 저장 서브프로그램을 데이터베이스에서 영구적으로 삭제하는 명령어다.

소스 코드를 삭제하는 문장은 다음과 같다.

DROP FUNCTION		함수명;
DROP PROCEDURE		프로시저명;
DROP PACKAGE		패키지명;
DROP PACKAGE BODY   패키지명;
DROP TRIGGER		트리거명;
DROP TYPE			타입명;
DROP TYPE BODY		타입명;

딕셔너리에서 저장 서브프로그램 조회하기

저장 서브프로그램과 관련된 정보는 데이터베이스의 데이터 딕셔너리에서 조회할 수 있다.

저장 서브프로그램의 목록은 ALL_OBJECTS이나 USER_OBJECTS 또는 권한이 있다면 DBA_OBJECTS에서 조회할 수 있다.

-- ALL_OBJECTS에서 저장 서브프로그램 목록 조회
SCOTT> COL OWNER		FORMAT A10
SCOTT> COL OBJECT_NAME  FORMAT A30
SCOTT> COL OBJECT_TYPE  FORMAT A20
SCOTT> SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
		 FROM ALL_OBJECTS
        WHERE OWNER = 'SCOTT'
          AND OBJECT_TYPE IN ('FUNCTION', 'PROCEDURE', 'PACKAGE',
          						'PACKAGE BODY', 'TRIGGER', 'TYPE', 'TYPE BODY')
        ORDER BY 1,2,3;
        
OWNER		OBJECT_NAME				OBJECT_TYPE
--------	---------------------	-------------------------------------
SCOTT		BOOLEAN_STRING			FUNCTION
SCOTT		CHECK_SALARY			PROCEDURE
SCOTT		CITIES					TYPE
SCOTT		COLORS					TYPE
SCOTT		COUNT_AUTH_CURRENT_USER FUNCTION
SCOTT		COUNT_AUTH_DEFINER		FUNCTION
SCOTT		EMPLOYEE_TYPE			TYPE
SCOTT		EMPLOYEE_TYPE			TYPE

DBA_OBJECTS는 ALL_OBJECTS와 조회할 수 있는 권한만 다를 뿐 완전히 동일한 구조를 가지고 있다.

따라서 DBA_OBJECTS를 사용할 때와 ALL_OBJECTS를 사용할 때의 쿼리는 FROM절에 오는 뷰 이름만 다르고 나머지는 완전히 동일하다. USER_OBJECTS는 자신의 스키마에 속하는 오브젝트만을 조회하기 때문에 OWNER 칼럼을 가지고 있지 않다. USER_OBJECTS를 사용할 경우에는 OWNER 칼럼을 제거하고 다음 예제처럼 조회하면 된다.

-- USER_OBJECTS에서 저장 서브프로그램 목록 조회
SELECT OBJECT_NAME, OBJECT_TYPE
  FROM USER_OBJECTS
 WHERE OBJECT_TYPE IN ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY',
 					   'TRIGGER', 'TYPE', 'TYPE BODY')
 ORDER BY 1,2;

저장된 서브프로그램의 소스 코드는 ALL_SOURCE나 USER_SOURCE 또는 권한이 있다면 DBA_SOURCE 딕셔너리에서 조회할 수 있다. 

-- ALL_SOURCE에서 저장 서브프로그램 소스 코드 조회
SCOTT> COL TEXT FORMAT A79
SCOTT> SELECT TEXT
		 FROM ALL_SOURCE
        WHERE OWNER = 'SCOTT'
          AND NAME = 'LOG_MSG'
        ORDER BY LINE;
        
TEXT
-----------------------------------------------------------------------------
PROCEDURE log_msg(a_log_text VARCHAR2)
IS
	PRAGMA AUTONOMOUS_TRANSACTION;	-- 자치 트랜잭션 선언
BEGIN
	INSERT INTO log_table(timestamp, log_text)
    VALUES (SYSTIMESTAMP, a_log_text);
    COMMIT; -- 자치 트랜잭션을 COMMIT한다. 메인 트랜잭션은 COMMIT되지 않는다.
END;

소스 코드에는 CREATE OR REPLACE가 들어 있지 않다. 이는 CREATE OR REPLACE가 저장 서브프로그램 자체의 문법이 아니라 저장 프로시저를 컴파일하는 명령이기 때문이다. 

 

트리거에 대한 정보는 ALL_TRIGGERS이나 USER_TRIGGERS 또는 권한이 있다면 DBA_TRIGGERS 딕셔너리 뷰에서 조회할 수 있다.

-- ALL_TRIGGERS에서 트리거 목록 조회
SCOTT> COL TABLE_OWNER		FORMAT A10
SCOTT> COL TABLE_NAME		FORMAT A30
SCOTT> COL TRIGGER_NAME		FORMAT A30
SCOTT> SELECT TABLE_OWNER, TABLE_NAME, TRIGGER_NAME
		 FROM ALL_TRIGGERS
        ORDER BY 1,2,3;
        
TABLE_OWNER			TABLE_NAME			TRIGGER_NAME
---------------		---------------		-------------------------
SCOTT				EMP					TR_EMP_INS_UPD_VALIDATE

위에서 소개한 딕셔너리 뷰 외에도 저장 서브프로그램과 관련된 딕셔너리 뷰는 몇 가지가 더 있다.

ALL/USER/DBA_PROCEDURES는 독립 함수와 독립 프로시저 목록과 더불어 패키지에 선언된 서브프로그램의 목록을 보여 준다. ALL/USER/DBA_ARGUMENTS는 서브프로그램의 매개변수 목록을 보여준다. DBA_로 시작하는 딕셔너리는 권한이 있는 사용자만 조회할 수 있다.

 

저장 서브프로그램 권한 관리

저장 서브프로그램을 생성한 계정에서만 사용하고자 하는 경우에는 권한 관리가 필요하지 않다.

하지만 실제에서는 저장 서브프로그램을 특정 계정에 생성하고 다른 계정에서 이 저장 서브프로그램들을 사용하는 경우가 많다. 한 계정에서 생성한 저장 서브프로그램을 다른 계정에서 사용할 수 있도록 하기 위해서는 다음 같이 GRANT문을 사용한다.

GRANT EXECUTE ON 저장서브프로그램명 TO 계정명;

GRANT 명령을 사용하여 부여할 수 있는 권한은 이 외에도 매우 많지만, 저장 서브프로그램을 실행할 수 있도록 하는 데에는 EXECUTE 권한 하나만 부여하면 된다.

 

권한을 부여해야 할 계정이 많고, 또 권한 부여의 대상이 되는 저장 서브프로그램이 많다면 ROLE을 사용하는 것이 효과적이다. 예를 들어 scott이 만든 저장 서브프로그램 factorial, pkg_emp, log_msg의 실행 권한을 계정 tiger, hr, sh에 부여해야 한다면 다음 예제와 같이 하면 된다.

CONN / AS SYSDBA
REM =====================================
REM DBA 게정에서 ROLE 생성
REM =====================================
CREATE ROLE scott_pgm_role;

REM ===========================================
REM scott_pgm_role을 계정 tiger, hr, sh에 부여
REM ===========================================
GRANT scott_pgm_role TO tiger;
GRANT scott_pgm_role TO hr;
GRANT scott_pgm_role TO sh;

CONN scott/tiger

REM ===========================================
REM 저장 서브프로그램의 실행 권한을 ROLE에 부여
REM ===========================================
GRANT EXECUTE ON factorial TO scott_pgm_role;
GRANT EXECUTE ON pkg_emp TO scott_pgm_role;
GRANT EXECUTE ON log_msg TO scott_pgm_role;

 예제와 같이 하면 tiger나 hr 또는 sh계정에서 scott계정의 저장 서브프로그램을 실행할 수 있다. 다른 계정의 서브프로그램이므로 저장 서브프로그램명 앞에 'scott.'을 붙여 주어야 한다. 'scott.'을 붙이지 않고도 실행 가능하게 하려면 해당 계정에 SYNONYM을 생성해 주거나 PUBLIC SYNONYM을 생성해 주면 된다.

 

부여된 권한을 회수(취소)하기 위해서는 REVOKE 명령을 사용한다.

REVOKE EXECUTE ON 저장서브프로그램명 FROM 계정명;

 앞에서 tiger에 부여한 factorial 함수의 EXECUTE 권한을 회수하는 명령은 다음과 같다.

REVOKE EXECUTE ON factorial FROM tiger;