[PL/SQL] 사용자 정의함수

사용자 정의함수

구조

CREATE OR REPLACE FUNCTION 함수명 (매개변수1, 매개변수2, ...)
RETURN  데이터타입
IS[AS]
    변수, 상수 등 선언
BEGIN
    실행부
 
    RETURN 반환값;
[EXCEPTION
    예외처리부]
END 함수명;

CREATE OR REPLACE FUNCTION  최초 함수를 만들고나서 수정을 하더라도 계속 컴파일할 수 있고 마지막으로 수정된 최종본이 반영된다.

매개변수  "매개변수명 데이터타입" 형태로 명시한다. 생략가능

RETURN  데이터타입  함수가 반환할 데이터 타입을 정한다.

RETURN 반환값  특정 연산을 진행한 후 반환할 값을 명시한다.

예제

CREATE OR REPLACE FUNCTION MY_MOD(NUM1 NUMBER, NUM2 NUMBER)
    RETURN NUMBER                               -- 반환타입 NUMBER
IS
    VN_REMAINDER    NUMBER := 0;                -- 반환할 나머지
    VN_QUOTIENT     NUMBER := 0;                -- 몫
BEGIN
    VN_QUOTIENT := FLOOR(NUM1 / NUM2);          -- 정수부분 거르기
    VN_REMAINDER:= NUM1 - (NUM2 * VN_QUOTIENT); -- 나머지
    
    RETURN VN_REMAINDER;                        -- 나머지 반환
END;

함수는 반환값이 있으므로 SELECT 문장에서 사용할 수 있고, PL/SQL 블록 내에서도 사용할 수 있다.

SELECT  MY_MOD(14, 3) AS REMAIND
FROM    DUAL;

국가테이블을 읽어 국가번호를 받아 국가명을 반환하는 함수를 작성해보자.

CREATE OR REPLACE FUNCTION FN_GET_COUNTRY_NAME(P_COUNTRY_ID NUMBER)
    RETURN  VARCHAR2
IS
    VN_COUNTRY_NAME     COUNTRIES.COUNTRY_NAME%TYPE;
BEGIN
    SELECT  COUNTRY_NAME
    INTO    VN_COUNTRY_NAME
    FROM    COUNTRIES
    WHERE   COUNTRY_ID = P_COUNTRY_ID;
    
    RETURN VN_COUNTRY_NAME;
END;

SELECT  FN_GET_COUNTRY_NAME(52777)   COUNTRY1
        , FN_GET_COUNTRY_NAME(10000) COUNTRY2
FROM    DUAL;

그렇담, 값이 존재하지 않을때 '없음'이라고 반환하려면 어떻게 해야할까?

CREATE OR REPLACE FUNCTION FN_GET_COUNTRY_NAME(P_COUNTRY_ID NUMBER)
    RETURN  VARCHAR2
IS
    VN_COUNT            NUMBER := 0;
    VN_COUNTRY_NAME     COUNTRIES.COUNTRY_NAME%TYPE;
BEGIN
    SELECT  COUNT(*)
    INTO    VN_COUNT
    FROM    COUNTRIES
    WHERE   COUNTRY_ID = P_COUNTRY_ID;
    
    IF  VN_COUNT = 0 THEN
        VN_COUNTRY_NAME := '없음';
    ELSE
        SELECT  COUNTRY_NAME
        INTO    VN_COUNTRY_NAME
        FROM    COUNTRIES
        WHERE   COUNTRY_ID = P_COUNTRY_ID;
    END IF;
    
    RETURN VN_COUNTRY_NAME;
END;

SELECT  FN_GET_COUNTRY_NAME(52777)   COUNTRY1
        , FN_GET_COUNTRY_NAME(10000) COUNTRY2
FROM    DUAL;

접속한 사용자 확인 함수를 작성해보자.

CREATE OR REPLACE FUNCTION FN_GET_USER
    RETURN  VARCHAR2
IS
    VS_USER_NAME    VARCHAR2(80);
BEGIN
    SELECT  USER
    INTO    VS_USER_NAME
    FROM    DUAL;
    
    RETURN  VS_USER_NAME;
END;

SELECT  FN_GET_USER
FROM    DUAL;