서브 프로그램

서브 프로그램을 작성하는 방법을 설명합니다.

개요

서브 프로그램(subprogram)은 다른 tbPSM 프로그램 내에서 호출할 수 있는 프로그램 블록입니다. 서브 프로그램을 호출할 때에는 해당 서브 프로그램 내에서 사용할 파라미터를 함께 전달해야 합니다.

서브 프로그램의 실행이 모두 끝나면 해당 서브 프로그램을 호출한 뒷부분부터 프로그램의 실행은 계속됩니다. 서브 프로그램은 항상 이름을 가져야 하며, 서브 프로그램 내에서 또 다른 서브 프로그램을 호출할 수 있습니다.

서브 프로그램을 이용함으로써 얻을 수 있는 장점은 다음과 같습니다.

• 모듈성(modularity) 기능별로 각각의 모듈로 분리하여 프로그램을 작성할 수 있습니다. 이러한 모듈은 복잡한 모듈이 될 수도 있고 작은 모듈일 수도 있습니다.

• 재사용성(reusability), 관리의 편의성(maintainability) 서브 프로그램은 정해진 특정 기능을 수행하므로, 동일한 기능을 필요로 하는 여러 프로그램에서 해당 서브 프로그램을 공통적으로 이용할 수 있습니다. 재사용성은 관리의 편의성(maintainability)을 의미하기도 합니다.

• 추상화(abstraction) 기능이 같습니다면 서브 프로그램 내부를 변경하더라도 프로그램 전체에 영향을 주지 않습니다. 즉, 인터페이스에 변화가 없으며 문제가 발생되지 않습니다.

서브 프로그램의 구분

서브 프로그램은 결과 값의 반환 여부에 따라 프러시저(procedure)와 함수(function)로 구분됩니다.

프러시저

프러시저는 함수와는 다르게 반환값이 없습니다.

프러시저를 선언하는 방법은 다음과 같습니다.

[CREATE [OR REPLACE]] PROCEDURE 프러시저_이름 [(파라미터[, 파라미터])] 
    [AUTHID {DEFINER | CURRENT_USER}] {AS | IS}
[PRAGMA AUTONOMOUS_TRANSACTION;]
[선언부] 
BEGIN
[실행부]
[예외 처리부] 
END;

프러시저는 한 개 이상의 파라미터를 가질 수 있으며, 두 개 이상의 파라미터를 갖는 경우 각 파라미터는 콤마(,)로 분리하여 사용합니다.

프러시저_이름과 동일한 이름을 갖는 프러시저가 이미 존재하는 경우에도 OR REPLACE 문이 삽입된 경우 해당 프러시저를 생성할 수 있습니다. 또한 동일한 이름을 갖는 프러시저가 이미 존재하는 경우 프러시저의 내용은 새롭게 작성된 프러시저의 내용으로 갱신됩니다.

다음은 새로운 직원 정보를 EMP 테이블에 삽입하는 프러시저입니다.

CREATE OR REPLACE PROCEDURE NEW_EMP (ename VARCHAR2, deptno NUMBER)     ... ① ...
IS
    salary NUMBER;
    deptno_not_found EXCEPTION;
BEGIN
    IF deptno = 5 THEN 
        salary := 30000;
    ELSIF deptno = 6 THEN 
        salary := 35000;
    ELSE
        RAISE deptno_not_found;                                         ... ② ...
    END IF;

    INSERT INTO EMP (ENAME, SALARY, DEPTNO)                             ... ③ ...
        VALUES (ename, salary, deptno);
EXCEPTION
WHEN deptno_not_found THEN
    -- Report Unknown Deptno
    ROLLBACK;                                                           ... ④ ...
END NEW_EMP;

① 두 개의 입력 파라미터를 받습니다.

② 입력된 부서 번호에 따라 봉급(salary)을 결정하고, 만약 미리 정해진 부서 번호가 아니면 예외 상황을 발생시킵니다. 사용자가 정의한 예외 상황을 발생시키는 것은 RAISE 문을 이용합니다. 이때 사용자가 정의한 예외 상황은 선언부에 미리 정의되어 있어야 합니다.

③ 봉급이 결정되면 EMP 테이블에 새로운 로우를 삽입합니다.

④ 프러시저를 종료합니다. 프러시저 또는 함수의 맨 마지막에 COMMIT 또는 ROLLBACK 문을 포함시킵니다. 단, 하나의 트랜잭션에서 여러 프러시저나 함수를 호출하는 경우에는 모든 프러시저와 함수를 호출한 후에 커밋 또는 롤백을 수행해야 합니다.

함수

함수는 프러시저와는 다르게 반환값이 있습니다. 따라서 RETURN 문이 반드시 삽입되어야 합니다.

함수를 선언하는 방법은 다음과 같습니다.

[CREATE [OR REPLACE]] FUNCTION 함수_이름 [(파라미터[, 파라미터])] 
RETURN 반환_타입 [AUTHID {DEFINER | CURRENT_USER}] 
[DETERMINISTIC]
[PARALLEL_ENABLE]
[RESULT_CACHE [RELIES_ON (데이터소스이름[,데이터소스이름])]] 
[PIPELINED]
{AS | IS}
    [PRAGMA AUTONOMOUS_TRANSACTION;]
    [선언부] 
BEGIN
    [실행부]
    RETURN 반환값; 
    [예외 처리부]
END;

RETURN문 다음에는 함수의 선언부에서 해당 함수가 반환할 값의 데이터 타입을 미리 지정해야 합니다.

만약 예외 처리부가 있는 경우에는 예외 처리부가 시작하기 직전에 반환할 값을 입력하고, 예외 처리부가 없는 경우에는 실행부의 마지막에 반환할 값을 입력합니다.

다음은 함수의 예입니다. salary 값을 반환하는 NEW_EMP 함수입니다.

CREATE OR REPLACE FUNCTION NEW_EMP (ename VARCHAR, deptno INT)
RETURN NUMBER IS        ... ① ...
    ...
BEGIN
    ...
    RETURN salary;      ... ② ...
EXCEPTION
    ...
END NEW_EMP;

① 반환값의 데이터 타입을 선언합니다.

② RETURN 문장을 포함합니다.

서브 프로그램의 파라미터

파라미터

실제 파라미터의 값은 프러시저가 호출될 때 프러시저에 전달되며, 프러시저 내에서 형식 파라미터를 사용하여 참조됩니다. 이때 값뿐만 아니라 변수에 대한 제한도 함께 전달됩니다.

프러시저의 내부에 선언된 변수는 다른 프러시저의 인수로 전달됩니다. 이를 실제 파라미터라고 합니다. 또한 프러시저의 인수 부분에 선언된 파라미터는 형식 파라미터라고 합니다.

• 실제 파라미터 실제 파라미터는 호출될 때 프러시저에 전달되는 값을 포함하고, 반환할 때 프러시저로부터 결과를 받습니다. 실제 파라미터의 값은 프러시저에서 사용될 값입니다.

• 형식 파라미터 형식 파라미터는 실제 파라미터의 값에 대한 위치 지정자입니다. 프러시저가 호출될 때 형식 파라미터는 실제 파라미터의 값을 할당 받고, 프러시저 내에서 형식 파라미터에 의해 참조됩니다. 프러시저가 호출될 때 실제 파라미터는 형식 파라미터의 값을 할당 받습니다. 이 할당은 필요하다면 타입 변환을 실행합니다.

프러시저를 선언할 때는 형식 파라미터의 데이터 타입에 대한 제한이 없습니다. 데이터 타입에 대한 제한은 실제 프로그램 내에서 선언한 프러시저를 사용할 때 명시합니다.

파라미터 모드

형식 파라미터는 다음과 같이 4개의 모드를 제공합니다. 만약 모드를 지정하지 않으면 디폴트는 IN 모드로 선언됩니다.

다음은 각 모드에 대한 설명입니다.

모드	설명
IN 모드	실제 파라미터의 값은 프러시저가 호출될 때 프러시저에 전달 프러시저 내에서 형식 파라미터는 읽기 전용으로 간주된다. 즉, 값을 변경할 수 없다는 뜻 프러시저가 끝나고 제어가 호출 환경에 반환될 때 실제 파라미터는 변경되지 않음
OUT 모드	프러시저가 호출될 때 실제 파라미터가 가지는 값은 무시된다. 프러시저 내에서 형식 파라미터는 쓰기 전용으로 간주된다. 즉, 할당만 될 수 있고 읽을 수는 없습니다. 프러시저가 끝나고 제어가 호출 환경에 반환될 때 형식 파라미터 의 내용은 실제 파라미터에 전달된다.
INOUT 모드	IN 모드와 OUT 모드의 조합입니다. 실제 파라미터의 값은 프러시저가 호출될 때 프 러시저에 전달합니다. 프러시저 내에서 형식 파라미터는 읽거나 쓸 수 있습니다. 프러시저가 끝나고 제어가 호출 환경에 반환될 때 형식 파라미터의 내용은 실제 파라미터에 할당된다.
NOCOPY 모드	실제 파라미터가 형식 파라미터에 전달될 때 복사 여부를 결정합니다. NOCOPY 모드가 선언된 경우에는 복사하지 않고, 단순히 참조(reference)만 하므 로 서브 프로그램의 호출 속도를 높일 수 있습니다. 단, OUT 모드 또는 IN OUT 모드에 만 사용할 수 있습니다.

중복 선언

tbPSM에서는 서브 프로그램의 이름을 중복 선언(Overloading)하는 것을 허용합니다. 단, 다음과 같은 몇 가지 제약조건이 있습니다.

• 중복 선언은 패키지 내의 서브 프로그램이나 선언부에 정의된 서브 프로그램 사이에서만 가능합니다.

• 파라미터의 이름만 다른 경우에는 중복 선언을 할 수 없습니다.

• 파라미터의 모드만 다른 경우에는 중복 선언을 할 수 없습니다.

• 파라미터의 서브타입만 다른 경우에는 중복 선언을 할 수 없습니다.

• 함수에서 반환값의 타입만 다른 경우에는 중복 선언을 할 수 없습니다.

다음은 중복 선언의 예입니다.

DECLARE
    PROCEDURE compute_mass(age NUMBER, name VARCHAR2) IS 
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Type of age is NUMBER'); 
    END;
    PROCEDURE compute_mass(age DATE, name VARCHAR2) IS 
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Type of age is DATE'); 
    END;
BEGIN
    compute_mass(24, 'Tibero');                     -- 첫 번째 프러시저 호출
    compute_mass(date '2010/10/24', 'Tibero');      -- 두 번째 프러시저 호출
END;
/
Type of age is NUMBER 
Type of age is DATE

PSM completed
SQL>

정의자 권한과 호출자 권한

tbPSM에서는 다음과 같이 두 개의 권한을 정의하고 있습니다.

정의자 권한(Definer's Rights)

서브 프로그램이 처음 정의될 때 해당 스키마(schema)가 가진 권한으로 항상 수행되며, 그 스키마가 소유한 객체만 볼 수 있습니다. 따라서 항상 같은 스키마에 같은 객체를 보게 됩니다.

호출자 권한(Invoker's Rights)

서브 프로그램을 수행하는 주최자(또는 호출자)의 스키마가 가진 권한으로 수행되며, 호출자가 소유한 스키마의 객체만 볼 수 있습니다. 호출자 권한으로 정의한 경우 다음과 같은 장점이 있습니다.

• 프로그램의 유연성 소스 코드를 재사용할 수 있어 하나의 프로그램으로 여러 사용자에게 프로그래밍의 유연성을 제공할 수 있습니다.

• 중요한 데이터 보호 중요한 정보에 접근하는 프로그램은 정의자 권한으로 생성하고, 다시 이 프로그램을 호출자 권한으 로 생성하면 다수의 사용자로부터 중요한 데이터를 보호할 수 있습니다.

정의자 권한으로 선언할 때에는 AUTHID 절에 DEFINER를 정의하고, 호출자 권한으로 선언할 때에는 CURRENT_USER로 정의합니다.

예를 들면 다음과 같이 선언합니다.

CREATE PROCEDURE count_total_days_per_year(year NUMBER) 
AUTHID {CURRENT_USER | DEFINER} IS
    ....
BEGIN
    ....
END;

캐싱 가능 함수

다음 조건에서 캐싱 가능 함수를 정의하고 있습니다.

• 함수 내에서 패키지 변수를 사용하지 않을 때

• 함수 내에서 시퀸스를 사용하지 않을 때

• 함수 내의 모든 SQL이 캐싱 가능할 때

  • SQL이 캐싱 가능할 조건 : INSERT, UPDATE, DELETE, MERGE, CURRENT_TIME, CUR RENT_TIMESTAMP, LOCALTIMESTAMP, SYSTIMESTAMP, USERENV, SYS_CONTEXT, SYS_CONTEXT, SYS_GUID, CURRENT_USER, ROWNUM, USER, UID, SYSDATE 등이 들어가지 않을 때

캐싱 가능 함수는 SQL에서 실행시 결과값을 캐싱하여 실행합니다.

함수 선언 시 DETERMINISTIC 절을 명시하면 강제로 캐싱 가능 함수로 정의할 수 있습니다. 예를 들면 다음과 같이 선언합니다.

CREATE OR REPLACE FUNCTION NEW_EMP (ename VARCHAR, deptno INT) 
RETURN NUMBER
[DETERMINISTIC] IS
    ....
BEGIN
    ....
END;

RESULT CACHE 함수

RESULT_CACHE 절을 명시하면 함수 실행시 RESULT CACHE 기능을 사용합니다.

RESULT CACHE 기능은 SGA(Shared Global Area) 메모리에 결과를 캐싱할 수 있는 기능입니다. RELIES_ON 절은 함수 결과가 의존하는 데이터 소스들을 지정합니다.

RELIES_ON 절은 더이상 사용되지 않는 기능입니다. 결과 캐시 기능이 실행되는 동안 쿼리되는 모든 데이터 소스들을 감지하므로, 적는 것이 무의미합니다.

함수 선언 시 RESULT_CACHE 절을 명시하면 RESULT CACHE 함수로 정의할 수 있습니다. 예를 들면 다음과 같이 선언합니다.

CREATE OR REPLACE FUNCTION NEW_EMP (ename VARCHAR, deptno INT) 
RETURN NUMBER
[RESULT_CACHE [RELIES_ON (데이터소스이름[,데이터소스이름])]] IS
    ....
BEGIN
    ....
END;

RESULT CACHE 함수에 SEQUENCE는 허용된다. 단 SEQUENCE가 결과값에 영향을 줄 경우 결과가 달라질 수 있습니다.

RESULT CACHE 함수는 다음과 같은 제약조건이 있습니다. 제약조건에 위배될 경우 컴파일 에러가 발생합니다.

• RESULT CACHE 함수에서는 IN OUT, OUT 파라미터를 허용하지 않는다.

• RESULT CACHE 함수에서는 아래와 같은 데이터 타입의 IN, RETURN 파라미터를 허용하지 않는다.

  • BLOB

  • CLOB

  • NCLOB

  • XMLTYPE

  • GEOMETRY

  • BFILE

  • JSON

  • REF CURSOR

  • Collection

  • Object

  • Record

• 서브 프로그램, 익명 블록 내부에 정의된 RESULT CACHE 함수는 허용하지 않는다.

• 파이프라인드 테이블 함수는 RESULT CACHE 옵션을 허용하지 않는다.