[Oracle] 오라클 함수와 프로시저의 차이점 및 함수 생성과 사용 방법

 

오라클 함수와 프로시저의 차이점 및 함수 생성과 사용 방법

오라클 함수는 크게 내장 함수(built-in function)와 사용자 정의 함수(user defined function)로 분류됩니다. 이 글에서는 프로시저와 함수의 차이점, 함수의 생성 및 사용 방법에 대해 알아보겠습니다.

프로시저와 함수의 차이점

특징	프로시저	함수
실행	EXECUTE 명령어 또는 다른 PL/SQL 서브 프로그램 내에서 호출하여 실행	변수를 사용한 EXECUTE 명령어 또는 다른 PL/SQL 서브 프로그램에서 호출하여 실행하거나 SQL문에서 직접 실행 가능
파라미터 지정	필요에 따라 지정하지 않을 수도 있고, 여러 개를 지정할 수도 있으며 IN, OUT, IN OUT 세 가지 모드를 사용할 수 있음	프로시저와 같게 지정하지 않을 수도 있고 여러 개 지정할 수도 있지만 IN 모드(또는 생략)만 사용
값의 반환	실행 후 값의 반환이 없을 수도 있고, OUT, IN OUT 모드의 파라미터 수에 따라 여러 개 값을 반환할 수 있음	반드시 하나의 값을 반환해야 하며, 값의 반환은 프로시저와 달리 OUT, IN OUT 모드의 파라미터를 사용하는 것이 아니라 RETURN절과 RETURN문을 통해 반환

함수는 프로시저와 달리 SQL문에서도 사용할 수 있다는 특징이 있습니다. 함수는 RETURN절과 RETURN문을 통해 반드시 하나의 값을 반환해야 합니다.

함수 생성하기

함수 생성은 프로시저와 마찬가지로 CREATE [OR REPLACE] 명령어와 FUNCTION 키워드를 명시하여 생성합니다. 실행부의 RETURN문이 실행되면 함수 실행은 즉시 종료됩니다.

함수 생성 기본 형식

CREATE [OR REPLACE] FUNCTION 함수 이름
[(파라미터 이름1 [IN] 자료형1,
  파라미터 이름2 [IN] 자료형2,
  ...
  파라미터 이름N [IN] 자료형N
)]
RETURN 자료형
IS | AS
  선언부
BEGIN
  실행부
  RETURN (반환값);
EXCEPTION
  예외 처리부
END [함수 이름];

함수 예제

CREATE OR REPLACE FUNCTION func_aftertax(
   sal IN NUMBER
)
RETURN NUMBER
IS
   tax NUMBER := 0.05;
BEGIN
   RETURN (ROUND(sal - (sal * tax)));
END func_aftertax;
/

함수 실행하기

생성된 함수는 익명 블록 또는 프로시저 같은 저장 서브 프로그램, SQL문에서 사용할 수 있습니다. PL/SQL로 함수를 실행할 때는, 함수 반환 값을 대입받을 변수가 필요합니다.

PL/SQL로 함수 실행하기

DECLARE
   aftertax NUMBER;
BEGIN
   aftertax := func_aftertax(3000);
   DBMS_OUTPUT.PUT_LINE('after-tax income : ' || aftertax);
END;
/

SQL문에서 함수 실행하기

SELECT func_aftertax(3000)
  FROM DUAL;

함수에 테이블 데이터 사용하기

SELECT EMPNO, ENAME, SAL, func_aftertax(SAL) AS AFTERTAX
  FROM EMP;

함수 삭제하기

DROP FUNCTION 명령어를 사용하여 함수를 삭제할 수 있습니다.

함수 삭제 예제

DROP FUNCTION func_aftertax;

이와 같이 오라클에서 함수를 생성하고 사용하는 방법에 대해 알아보았습니다. 함수를 적절히 활용하면 데이터베이스 작업을 더욱 효율적으로 처리할 수 있습니다.