Oracle :: FUNCTION (함수)

■ FUNCTION

- 하나 이상의 PL/SQL 문으로 구성된 서브루틴으로 코드를 다시 사용할 수 있도록 캡슐화 하는데 사용

- 오라클에서는 오라클에 정의된 기본 제공 함수를 사용하거나 직접 스토어드 함수를 만들 수 있음(→ 사용자 정의 함수)

- 사용자 정의 함수는 시스템 함수처럼 쿼리에서 호출하거나 저장 프로시저처럼 EXCUTE 문을 통해 실행 가능

 

※ 사용자 정의 함수(스토어드 함수)는 IN 파라미터(입력 매개변수)만 사용할 수 있으며

    반드시 반환될 값의 데이터 타입을 RETURN 문에 선언해야 하고, FUNCTION은 반드시 단일 값만 반환!!

 

입력 매개변수 : 이사와서 이웃집에 떡 줌(주고 끝)

출력 매개변수 : 엄마한테 빈 김치통 건네면 엄마가 빈 김치통에 김치 넣어서 돌려줌
입출력 매개변수 : 김치통에 고구마 넣어서 엄마아빠 주고 김치통 비면 김치 넣어옴

 

■ 형식 및 구조

/*
CREATE [OR REPLACE] FUNCTION 함수명
[( 매개변수명1 자료형
 , 매개변수명2 자료형
)]
RETURN 데이터타입
IS
    -- 주요 변수 선언
BEGIN
    -- 실행문;
    ...
    RETURN (값);
    
    [EXCEPTION]
        -- 예외 처리 구문;
END;
*/

 

■ 함수 생성 예시

--○ TBL_INSA 테이블 전용 성별 확인 함수 정의(생성)
--   -------------------
--   매개변수 구조(형태) → '771212-1022432' → 'YYMMDD-NNNNNNN'
-- 함수명 : FN_GENDER( )
--                   ↑ 주민등록번호 → '771212-1022432'


CREATE OR REPLACE FUNCTION FN_GENDER(V_SSN VARCHAR2)    -- 매개변수   : 자릿수(길이) 지정 안함
RETURN VARCHAR2                                         -- 반환자료형 : 자릿수(길이) 지정 안함
IS
    -- 선언부 → 주요 변수 선언(및 초기화)
    V_RESULT    VARCHAR2(20);
BEGIN
    -- 실행부 → 연산 및 처리(그리고 결과값 반환)
    IF ( SUBSTR(V_SSN, 8, 1) IN ('1', '3') )
        THEN V_RESULT := '남자';
    ELSIF ( SUBSTR(V_SSN, 8, 1) IN ('2', '4') )
        THEN V_RESULT := '여자';
    ELSE 
        V_RESULT := '성별확인불가';
    END IF;
    
    -- 결과값 반환   CHECK~!! 꼭 써줘야 한다!!
    RETURN V_RESULT;
END;
--==>> Function FN_GENDER이(가) 컴파일되었습니다.

 

■ 실습1

--○ 임의의 정수 두 개를 매개변수(입력 파라미터)로 넘겨받아 → (A, B)
--   A 의 B 승의 값을 반환하는 사용자 정의 함수를 작성한다.
--   단, 기존의 오라클 내장 함수를 이용하지 않고, 반복문을 활용하여 작성한다.
--   함수명 : FN_POW()
/*
사용 예)
SELECT FN_POW(10, 3)
FROM DUAL;
--==>> 1000
*/

-- 제가 해보았습니다~!    반환값 초기화 값 확인해서 다시 생각해보기
CREATE OR REPLACE FUNCTION FN_POW(A NUMBER, B NUMBER)
RETURN NUMBER
IS
    V_RESULT    NUMBER := 1;
    N           NUMBER;             -- 곱해주는 횟수
BEGIN
    N := 1;
    LOOP
        V_RESULT := V_RESULT * A;
        N := N+1;    
    EXIT WHEN N > B ;
    END LOOP;   
    RETURN V_RESULT;
END;


-- 같이 했씁니다.
CREATE OR REPLACE FUNCTION FN_POW(A NUMBER, B NUMBER)
RETURN NUMBER
IS
    V_RESULT    NUMBER := 1;    -- 반환 결과값 변수 → CHECK~!  1로 초기화
    V_NUM       NUMBER;
BEGIN
    FOR V_NUM IN 1.. B LOOP
        V_RESULT := V_RESULT * A;       -- V_RESULT *= A; (자바에서)
    END LOOP;
    
    --최종 결과값 확인
     RETURN V_RESULT;
END;

 

■ 실습2

--○ TBL_INSA 테이블의 급여 계산 전용 함수를 정의한다.
--   급여는 『(기본금*12)+수당』 기반으로 연산을 수행한다.
--   함수명 : FN_PAY(기본급, 수당)

-- 제가 해보았구용
CREATE OR REPLACE FUNCTION FN_PAY(V_BASIC_PAY NUMBER, V_SUDANG NUMBER)
RETURN NUMBER
IS
    V_RESULT    NUMBER := 1;
BEGIN
    V_RESULT := V_BASIC_PAY *12 + V_SUDANG;
    
    RETURN V_RESULT;
END;

-- 같이 해보겠습니당~!  이 문제에서는 NULL 이 없어서 신경쓰지 못했지만 NULL 처리도 같이 해주자 TBL_EMP 테이블에는 NULL 값이 있는 데이터들도 있다!
CREATE OR REPLACE FUNCTION FN_PAY(VBASICPAY NUMBER, VSUDANG NUMBER)
RETURN NUMBER
IS
    -- 주요 변수 선어
    VRESULT NUMBER;
BEGIN
    -- 연산 및 처리
    VRESULT := (NVL(VBASICPAY, 0) * 12) + NVL(VSUDANG, 0);
    -- 최종 결과값 반환
    RETURN VRESULT;
END;

 

■ 실습3

--○ TBL_INSA 테이블에서
--   입사일을 기준으로 현재까지의 근무년수를 반환하는 함수를 정의한다.
--   단, 근무년수는 소수점 이하 한자리까지 계산한다.
--   함수명 : FN_WORKYEAR(입사일)

-- 나 혼자
CREATE OR REPLACE FUNCTION FN_WORKYEAR(V_IBSADATE DATE)
RETURN NUMBER
IS
    V_RESULT    NUMBER:=0;
BEGIN
    V_RESULT := TRUNC(MONTHS_BETWEEN(SYSDATE, V_IBSADATE)/12, 1);
    
    RETURN V_RESULT;
END;

-- 몇년 몇개월로 뽑았음
CREATE OR REPLACE FUNCTION FN_WORKYEARMONTH(V_IBSADATE DATE)
RETURN VARCHAR2
IS
    V_RESULT     VARCHAR(80);
    V_RESULT1    NUMBER:=0;
    V_RESULT2    NUMBER:=0;
BEGIN
    V_RESULT1 := TRUNC(MONTHS_BETWEEN(SYSDATE, V_IBSADATE)/12, 0);
    V_RESULT2 := TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, V_IBSADATE), 12), 0);
    
    IF 
        V_RESULT2 = 0
            THEN V_RESULT := V_RESULT1 || '년 ';
    ELSE
        V_RESULT := V_RESULT1 || '년 ' || V_RESULT2 ||'개월';
    END IF;    
    RETURN V_RESULT;
END;



-- 같이 
-- 1
SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('1998-10-11', 'YYYY-MM-DD')) / 12 "RESULT"
FROM DUAL;
--==>> 25.06313778748506571087216248506571087217

-- 2
SELECT TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('1998-10-11', 'YYYY-MM-DD'))/12) || '년' ||
       TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, TO_DATE('1998-10-11', 'YYYY-MM-DD')), 12)) || '개월'
FROM DUAL;
--==>> 25년0개월


CREATE OR REPLACE FUNCTION FN_YEAR(VIBSADATE DATE)
RETURN NUMBER
IS
    VRESULT NUMBER;
BEGIN
    VRESULT := TRUNC(MONTHS_BETWEEN(SYSDATE, VIBSADATE)/12, 1);
    
    RETURN VRESULT;
END;


-- 결과 확인
SELECT NUM, NAME, IBSADATE, FN_WORKYEAR(IBSADATE) "근무기간"
FROM TBL_INSA;

SELECT NUM, NAME, IBSADATE, FN_WORKYEARMONTH(IBSADATE) "입사년개월수"
FROM TBL_INSA;