[Oracle] Stored Sub Program: SF

Stored Function

 저장된 함수(SF)는 프로시저와 동일한 역할을 하는 서브 프로그램입니다. 프로시저와 다른 점은 프로시저는 EXEC 명령어로 단독 호출할 수 있고, 다른 PL/SQL문에서"만" 실행 가능합니다만, 함수는 EXEC 단독 호출 및 PL/SQL 구문에서뿐만 아니라 쿼리문에서도 호출이 가능합니다.

 또한, 프로시저의 파라미터는 다양한 모드(MODES)로 지정하지만 함수는 오직 IN 모드로만 사용할 수 있습니다. 또한, 프로시저는 OUT, IN OUT을 통해 여러개의 값을 리턴하지만 함수는 오직 하나의 리턴값만으로 RETURN문을 통해서 반환할 수 있습니다.

 

함수는 다음과 같이 생성합니다.

  CREATE OR REPLACE FUNCTION 함수명
  (
        파라미터1 [IN] 자료형1,
        파라미터2 [IN] 자료형2,
        ....
  )
  RETURN 자료형
  IS
    선언부
  BEGIN
    실행부
    RETURN 위에서 지정한 자료형의 값.
  EXCEPTION
    예외처리부
  END [함수명];

함수를 삭제할 때에는 DROP을 활용합니다.

DROP FUNCTION 함수명;

 

 

한번 예제를 통해 살펴보겠습니다. 급여에 따라 세금을 조회하는 함수를 만들어보겠습니다. IS에서 TAX를 선언하고, IF-ELSIF-ELSE 문을 통해 연봉의 구간에 따라 다른 세율을 부과하도록 했습니다. RETURN문에서 최종 세금이 결정되어 반환하도록 합니다.

--급여별로 세금 조회하는 함수
CREATE OR REPLACE FUNCTION F_GETTAX
(
    SAL NUMBER
)
RETURN NUMBER

IS
    TAX NUMBER;
BEGIN
    IF SAL >= 7000 THEN TAX := 0.1;
    ELSIF SAL >= 6000 THEN TAX := 0.07;
    ELSIF SAL >= 5000 THEN TAX := 0.05;
    ELSE TAX := 0.03;
    END IF;

    RETURN ROUND(SAL * TAX);
END;
/

 

이렇게 만든 F_GETTAX 함수는 아래 쿼리문에서 호출해서 파라미터를 입력하여 실행할 수 있습니다. 이렇게 감싸서 실행이 가능하다는 점이 장점입니다.

--F_GETTAX 함수 쿼리문에서 호출
SELECT E.*
     , F_GETTAX(E.SAL) AS TAX --프로시저는 안되지만 함수는 이렇게 감쌀 수 있다.
    FROM EMP E;