Oracle PL/SQL [함수]

함수는 값을 반환하는 PL/SQL 블록의 일종이다. 함수는 단 하나의 결과 값만을 반드시 반환해야 하는 특징을 가진다.

또다른 차이점은 함수는 SQL 문에서 바로 호출하여 사용할 수 있다는 점이다.

그러나 SQL 문에서 무분별 하게 함수를 호출하여 사용하면 성능이 저하될 수 있으므로 주의가 필요하다.

 

함수생성

기본적인 생성 구문은 프로시저와 거의 동일하다 입력 매개변수가 없을경우 Default구문을 사용하여 기본 값을 설정할 수 있으며, RETURN 문과 함께 반환할 값의 데이터 타입을 선언해야 한다. 또한 함수 내에 반드시 하나 이상의 RETURN 문이 포함되어야 한다.

CREATE OR REPLACE FUNCTION <FUNC_NAME>(<PARAMETER>)
RETURN DATATYPE
IS
<VAIRABLE DECLARATION>
BEGIN
<LOGIC>
RETURN<..>;
EXCEPTION
 <EXCEPTION>
 END<FUNC_NAME>;

 

CREATE OR REPLACE FUNCTION GET_AVG_FUNC(P_DEPTNO NUMBER)
RETURN NUMBER
IS
I_RESULT NUMBER :=0;
BEGIN

    SELECT AVG(SAL)
    INTO I_RESULT
    FROM EMP
    WHERE DEPTNO = P_DEPTNO;
    
    RETURN I_RESULT;
END;
    /

 

SELECT GET_AVG_FUNC(10) FROM DUAL;

 

부서번호를 입력받아서 부서의평균 급여를 계산하는 함수를 생성했다, 반환 RETURN 할 변수의 데이터 타입을 Number 형으로 선언하였으며, Return함수를 이용하여  평균 값을 반환하도록 했다 만일 하나 이상의 결과 값이 반환되면 에러가 발생하므로 주의가 필요하다.

 

Deterministic 함수생성

Deterministic 함수란 동일한 입력 값이 들어올 경우 풀력 값도 동일함을 보장하는 함수이다.

따라서 함수 기반 인덱스와 같이 입력 값이 동일하면 동일한 결과 값이 출력되어야 하는 함수에 대해서는 Deterministic 함수로 정의할수 있다.

함수를 Deterministic으로 선언하면 동일한 입력 값에 대해서는 캐시를 이용해 결과 값을 저장해 두었다가 이전에 사용된 동일한 입력 값이 들어오면 함수를 수행하지 않고 캐시에 저장된 결과 값을 출력하므로 성능이 좋아질수 있다. 그러나 시간이 지나면 값이 변경될 수 있는 경우 또는 동일한 입력 값이 사용되지 않는 경우에는 주의해서 사용해야 한다.

 

CREATE OR REPLACE FUNCTION <PROC_NAME>(<PARAM> VARCHAR2 DEFAULT NULL)
DETERMINISTIC
IS
<VAIRABLE DECLARATION>
BEGIN
<LOGIC>
EXCEPTION
<EXCEPTION>
END<PROC_NAME>;

 

함수실행

아래는 생성된 함수를 실행하는 방법이다.

DECLARE 
    I_RESULT NUMBER;
    BEGIN
    I_RESULT := GET_AVG_FUNC(10);
    
    DBMS_OUTPUT.PUT_LINE('RESULT : ' || I_RESULT);
    END;
    /
    
    
    EXEC DBMS_OUTPUT.PUT_LINE('RESULT : ' || ROUND(GET_AVG_FUNC(10),2));
    
    
    SELECT ROUND(GET_AVG_FUNC(10),2) FROM DUAL;

함수제거

DROP FUNCTION function_name;

 

프로시저와 함수의 차이

아래의 표는 프로시저와 함수의 차이점을 요약한 내용이다. 가장 큰 차이점으로 함수에는 반드시 RETURN 절이 포함되어야 하고 하나의 결과만 반환할 수 있다는 것이다. 또한 함수는 표현식의 부분으로서 프로시저나 함수의 입력 매개변수 위치에서 실행이 가능하며 SQL 안에서도 자유롭게 실행할수 있다.

프로시저	함수
PL/SQL 블록에서 실행	표현식의 부분으로 실행될 수 있음
선언부에 RETURN 절이 없음	반드시 선언부에  RETURN 절이 필요함
반환값이 없거나 하나 또는 여러 건의 결과가 반환될 수 있음	반드시 하나의 값만 반환될 수 있음
RETURN 문이 필요없음	적어도 한번은 반드시  RETURN 문이 포함되어야함