저장 서브프로그램 개요

지금까지 앞에서 살펴본 PL/SQL 프로그램은 대부분 서버에 저장되지 않는 프로그램들이었다. 저장되지 않는 프로그램은 일회성으로 실행되며 서버에 저장되지 않기 때문에 다른 사용자와 공유하거나 재사용할 수 없다. 반면에 저장 서브프로그램은 데이터베이스 서버에 저장되며 다른 사용자에 의해 호출하여 실행할 수 있으므로 동일한 소스코드를 여러 사용자가 공유할 수 있다.

 

저장 서브프로그램을 사용하는 이유

• 재사용성: 프로그램이 한 번 작성되어 데이터베이스에 저장되면 이후로는 소스코드를 다시 작성하거나 다른 프로그램에 포함시킬 필요 없이 그 이름만 알면 언제라도 실행할 수 있다.
• 유지보수성: 소스코드를 다수의 개별 프로그램에 중복하여 저장하지 않고 데이터베이스 서버에 단 한 번만 저장할 수 있다. 이렇게 하면 프로그램의 생성, 삭제, 변경, 배포가 서버에서만 이루어지기 때문에 프로그램의 관리가 집중화되어 유지보수가 용이해진다. 업무처리가 바뀌더라도 데이터베이스 서버에 저장된 서브프로그램 하나만 변경하면 되고 이를 사용하는 클라이언트 프로그램들은 수정할 필요가 없다. 저장 서브프로그램을 사용하지 않고 처리를 클라이언트 프로그램들에 포함시키는 경우라면 무언가 변경 사항이 발생할 때마다 이를 구현하는 각 프로그램들을 모두 찾아서 수정해 주어야 하는 번거로움이 생긴다.
• 모듈화: 크기가 큰 프로그램을 적당한 크기의 통제 가능한 모듈로 분할하여 작성할 수 있다. 모듈화된 프로그램은 설계와 구현이 용이할 뿐만 아니라 다른 개발자가 이해하기도 쉽다.
• 표준화: 프로그래머가 서브프로그램을 작성하고 컴파일한 후 서브프로그램 명세(이름과 호출 방법)만 문서화하여 배포하면 어떤 사용자라도 해당 서브프로그램을 실행 시에 항상 동일한 처리결과을 얻을 수 있음을 보장받는다. 개발조직의 크기가 커질수록 표준화로 인한 이득은 비례하여 증가한다.
• 성능: 프로그램은 보통 여러 건의 DB처리를 포함한다. 이를 클라이언트 프로그램에 구현하면 DB 호출 시마다 클라이언트와 서버 사이의 통신이 일어나서 성능 저하의 원인이 된다. 저장 서브프로그램으로 작성하면 모든 작업이 서버 안에서 이루어지므로 더 나은 성능을 얻을 수 있다.

저장 서브프로그램 종류

기본적으로 함수(FUNCTION)과 프로시저(PROCEDURE)가 있고 연관된 여러 개의 타입, 상수, 변수, 예외, 커서, 함수와 프로시저 등을 묶어서 모듈화시킨 패키지(Package)가 있으며. 데이터베이스에 특정 이벤트가 발생할 경우에 자동으로 실행되는 트리거(Trigger)와 관계형 데이터베이스에 객체 지향 프로그래밍을 도입하여 객체 관계형 데이텁베이스(ORDB, Object Relational DataBase)로 변모시킨 객체 타입(Object Type)이 있다.

 

프로그램을 서버에 저장하고 공유하기

저장 서브프로그램은 이름처럼 서브프로그램이 데이터베이스 서버에 저장된다. 프로그래머는 함수, 프로시저와 같은 서브프로그램을 작성하여 데이터베이스 서버에 저장하기 위해 컴파일한다. 컴파일된 서브프로그램은 데이터베이스 서버의 딕셔너리에 이름과 함께 저장된다.

 

서브프로그램이 데이터베이스 서버에 저장될 때에는 소스코드와 함께 컴파일된 실행코드가 바이트코드(Byte Code)형태로 같이 저장되므로 실행 시에는 컴파일 과정 없이 빠르게 실행될 수 있다.(옵션으로 네이티브 코드로 컴파일할 수도 있다.) 한 번 저장된 서브프로그램은 명시적으로 삭제되기 전까지는 데이터베이스에 영구적으로 존재한다. 저장 서브프로그램은 이름을 식별자로 하여 호출되어 실행될 수 있다. 저장 서브프로그램은 이를 생성한 사용자뿐만 아니라 권한이 부여된 사용자라면 누구라도 사용이 가능하다.

 

저장 서브프로그램의 컴파일과 실행 방법

-- 저장 함수
CREATE OR REPLACE FUNCTION get_wage(a_empno NUMBER)
RETURN NUMBER
-- 사원의 급여와 커미션의 합을 반환하는 함수
IS
	v_wage NUMBER;
BEGIN
	-- 사번이 a_empno인 사원의 급여와 커미션의 합을 조회한다.
    SELECT sal + NVL(comm, 0) comm
      INTO v_wage
      FROM emp
     WHERE empno = a_empno;
    -- 급여를 반환
    RETURN v_wage;
END;

-- 저장 프로시저
CREATE OR REPLACE PROCEDURE raise_salary(a_empno NUMBER, a_amt NUMBER)
-- 사원의 급여를 인상하는 프로시저
IS
BEGIN
	-- 급여를 인상
    IF a_amt IS NOT NULL
    THEN
    	UPDATE emp
           SET sal = sal + a_amt
         WHERE empno = a_empno;
     END IF;
END;

함수와 프로시저 외에도 패키지, 트리거, 타입과 같은 다른 유형의 저장 서브프로그램이 있지만 컴파일하고 발생한 오류를 조회하는 방법은 동일하므로 위 두 예제를 사용한 컴파일과 오류 조회 방법을 동일하게 응용하면 된다.

 

컴파일 하기

서브프로그램을 컴파일하는 방법은 간단한데, 작성한 소스코드를 SQL*Plus에 입력한 후 마지막 줄에 SQL*Plus 실행 명령인 /를 입력하고 엔터를 누르면 된다.

SCOTT> CREATE OR REPLACE FUNCTION get_wage(a_empno NUMBER)
	RETURN NUMBER
    -- 사원의 급여와 커미션의 합을 반환하는 함수
    IS
    	v_wage NUMBER;
    BEGIN
    	-- 사번이 a_empno인 사원의 급여와 커미션의 합을 조회
        SELECT sal + NVL(comm, 0) comm
          INTO v_wage
          FROM emp
         WHERE empno = a_empno;
        -- 급여 반환
        RETURN v_wage;
    END;
  /

실행 결과로 메시지 '함수가 생성되었습니다.' 가 나타나면 성공이다. 프로시저를 컴파일하는 방법도 동일하다.

 

컴파일 오류 조회

오류 발생 시에는 컴파일 오류가 있다는 메시지가 나타난다. 하지만 어떤 오류가 발생했는지는 보여주지 않는다.

발생한 오류를 확인하기 위해서는 SQL*Plus 명령어 SHOW ERROR를 사용한다.

SQL*Plus의 명령 SHOW ERROR는 데이터베이스의 딕셔너리 뷰 USER_ERRORS에 들어있는 메시지를 보여주는 명령이다.

 

저장 서브프로그램 실행하기

성공적으로 컴파일된 서브프로그램을 사용하는 방법은 함수와 프로시저가 약간 다르다. 

패키지에 정의된 서브함수와 서브 프로시저의 실행은 함수나 프로시저 이름 앞에 '패키지명'을 추가하여 '패키지명.함수명' 또는 '패키지명.프로시저명'과 같이 사용하면 된다.

 

저장 함수 실행하기

첫 번째 방법은 SQL에 포함하여 실행하는 방법이다. 저장 함수는 SELECT문과 DML문에 포함하여 사용할 수 있다.

저장함수는 SELECT문의 FROM절을 제외하고는 어디에서라도 사용 가능하다.

SCOTT> SELECT ename, empno, sal, comm, get_wage(empno) 급여합계
		 FROM emp
        WHERE deptno = 30
          AND get_wage(empno) >= 2000
        ORDER BY ename;
        
ENAME		EMPNO		SAL		COMM	급여합계
---------  ---------   ------  ------- ----------
BLAKE		7698		2850			 2850
MARTIN		7654		1250	1400	 2790

 

두 번째 방법은 PL/SQL문에서 사용하는 방법이다. 함수는 결과를 반환하므로 PL/SQL 프로그램에서는 결과를 변수에 저장할 수 있도록 할당 연산자 :=의 오른쪽에 사용하거나(r-value) 다른 서브프로그램이나 표현식의 일부로 사용해야 한다.

SCOTT> SET SERVEROUTPUT ON
SCOTT> DECLARE
	   v_wage	NUMBER;
       v_wage2	NUMBER;
      BEGIN
      	-- r-value로 사용
        v_wage := get_wage(7654);
        
        -- 표현식에 사용
        v_wage2 := 100 + get_wage(7654);
        
        DBMS_OUTPUT_PUT_LINE('급여 : '||get_wage(7654));
      END;
    /
    

 

저장 프로시저 실행하기

프로시저는 반환값이 없다. 따라서 함수와 같이 r-value로 사용할 수 없다. 같은 이유로 프로시저는 쿼리문에 포함시켜서 사용할 수도 없다. 저장 프로시저는 독립적으로 실행되어야 한다.

 

1. PL/SQL문 안에서 프로시저를 호출하는 방법

SCOTT> BEGIN
		raise_salary(7788, 100);
      END;
      /

 2. CALL문을 사용하여 프로시저를 호출하는 방법

SCOTT> CALL raise_salary(7788, 100);

3. EXECUTE 또는 EXEC를 사용하여 프로시저를 호출하는 방법.

   CALL문은 세미콜론으로 끝나지만 EXEC는 세미콜론이 필요하지 않다는 것에 주의!

SCOTT> EXECUTE raise_salary(7788, 100)

SCOTT> EXEC raise_salary(7788, 100)

 

서버에 저장되지 않는 서브프로그램

저장 서브프로그램 중에서 서브프로그램에 속하는 함수와 프로시저는 저장되지 않는 형태로도 사용이 가능하다.

• 저장 서브프로그램: 데이터베이스 서버에 저장되어 재사용이 가능한 서브프로그램
• 저장되지 않는 서브프로그램: 데이터베이스에 저장되지 않는 익명(Anonymous) PL/SQL에 포함된 서브프로그램.

다음은 익명 PL/SQL에 포함된 저장되지 않는 서브프로그램의 사용을 보여준다.

DECLARE
	-- 저장되지 않는 함수 정의
    FUNCTION get_ename(a_empno NUMBER) RETURN VARCHAR2
    IS
    	v_ename dept.dname%TYPE;
    BEGIN
      SELECT ENAME
        INTO v_ename
        FROM emp
       WHERE empno = a_empno;
      RETURN v_ename;
   END;
   
   == 저장되지 않는 프로시저 정의
   PROCEDURE print_ename(a_empno NUMBER)
   IS
   BEGIN
     DBMS_OUTPUT_PUT_LINE(get_ename(a_empno));
   END;
BEGIN
	print_ename(7566);
END;

예제에서는 익명 블록의 선언부에서 함수 get_ename과 이 함수를 호출하는 프로시저 print_ename을 정의하였고, 블록의 본체에서 프로시저 print_ename을 호출하고 있다. 익명 프로그램은 서버에 저장되지 않고 일회성으로 실행된 후 버려진다. 따라서 위 예제의 두 서브 프로그램 get_ename과 print_ename은 서버에 저장되지 않는 서브프로그램이다.

 

저장 서브프로그램과 마찬가지로 저장되지 않는 서브프로그램도 서브프로그램 내부에 다른 서브프로그램을 정의하는 서브프로그램 중첩과 뒤에서 설명할 서브프로그램 중복 정의(Overloading)를 사용할 수 있다.

 

DECLARE
	-- 저장되지 않는 프로시저 정의
    PROCEDURE print_ename(a_empno NUMBER)
    IS
    	-- print_ename 내부에 중첩된 서브프로그램 정의
        FUNCTION get_ename(a_empno NUMBER) RETURN VARCHAR2
        IS
        	v_ename dept.dname%TYPE;
        BEGIN
          SELECT ENAME
            INTO v_ename
            FROM emp 
           WHERE empno = a_empno;
          RETURN v_ename;
        END;
     BEGIN
     	DBMS_OUTPUT_PUT_LINE(get_ename(a_empno));
     END;
     
     -- 3번 중의 프로시저 print_ename에 대한 중복 정의
     PROCEDURE print_ename(a_ename VARCHAR2)
     IS
     BEGIN
     	DBMS_OUTPUT_PUT_LINE(a_ename);
     END;
     
  BEGIN
  	print_ename(7566);		-- 3번 줄의 print_ename호출
    print_ename('SMITH');	-- 21번 줄의 print_ename호출
  END;

예제에서 print_ename은 3번 줄에 매개변수로 숫자(사번)를 받는 것과 21번 줄에 매개변수로 문자열을 받는 것 두 개가 중복 정의되었다. 그리고 6번 줄의 get_ename은 3번 줄의 print_ename 내부에 중첩되어 정의되었다, 정의된 세 개의 서브프로그램 중에서 어떤 것도 데이터베이스 서버에 저장되지 않는 다 서버에 저장되는 서브프로그램은 CREATE [ OR REPLACE ] 문을 사용하여 생성되는 서브프로그램 뿐이다.