Oracle PL/SQL 의 사용자 정의함수

함수생성

SQL 함수는 오라클에서 제공하는 내장함수이고 여기에서 말하는 함수는 사용자가 직접 로직을 구현하는 사용자 정의 함수를 말한다.

 

SQL 함수편에서 배웠듯이 함수란 매개변수를 받아 뭔가를 처리해 그 결과를 번환하는 데이터베이스 객체이며 사용자 정의 함수도 마찬가다. 오라클에서 제공하는 빌트인SQL 함수가 많긴 하지만, 프로젝트를 진행하다보면 프로젝트 성격에 맞게 다양한 함수를 직접 구현해 사용해야 할 때가 많다. 먼저 함수의 구문부터 살펴보자.

 

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

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

 

매개변수: 함수로 전달되는 매개변수로 '매개변수명 데이터 타입' 형태로 명시한다. 매개변수는 생략할 수 있다.

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;

두개의 매개변수를 받아 나눗셈을 한 후 나머지를 반환 하는데, 동일한 기능을 수행하는 MY_MOD를 만들었다.

 

함수호출

 

매개변수의 존재 유무에 따라 함수명과 매개변수를 명시하기도 하고, 함수명만 명시하기도 한다. 그리고 함수는 반환값이 있으므로 SELECT 문장에서 사용할 수도 있고 PL/SQL 블록 내에서도 사용할 수 있다.

 

<매개변수가 없는 함수 호출>
함수명 혹은 함수명()
<매개변수가 있는 함수 호출>
함수명(배개변수1,....)

SELECT MY_MOD(14,3) REMINDER
FROM DUAL;

14/3을 하면 몫이 4이고 나머지가 2이며 MY_MOD 함수도 정확히 2를 반환한다.

 

CREATE OR REPLACE FUNCTION FN_GET_COUNTRY_NAME (P_COUNTRY_ID NUMBER)
      RETURN VARCHAR2 -- 국기명을 반환하므로 데이터 타입은 VARCHAR2
      IS
        VS_COUNTRY_NAME COUNTRIES.COUNTRY_NAME%TYPE;
        BEGIN
           SELECT COUNTRY_NAME
           INTO VS_COUNTRY_NAME
           FROM COUNTRIES
           WHERE COUNTRY_ID = P_COUNTRY_ID;
           
           RETURN VS_COUNTRY_NAME;

이 함수는 매개변수로 NUMBER 타입의 국가번호를 받아 COUNTRIES 테이블에서 해당 국가명을 가져와 VS_COUNTRY_NAME 변수에 받아 이를 반환하고 있다 이 함수를 호출해 보자.

 

SELECT FN_COUNTRY_NAME(52777) COUN1, FN_COUNTRY_NAME(52777) COUN2
FROM DUAL;

국가번호 52777는 덴마크지만 ,10000 번호를 가진 국가는 국가 테이블에 존재하지 않아 NULL이 반환되었다.

 

 

 

CREATE OR REPLACE FUNCTION FN_GET_COUNTRY_NAME (P_COUNTRY_ID NUMBER)
      RETURN VARCHAR2 -- 국기명을 반환하므로 데이터 타입은 VARCHAR2
      IS
        VS_COUNTRY_NAME COUNTRIES.COUNTRY_NAME%TYPE;
        VS_COUNT NUMBER := 0;
        BEGIN
           SELECT COUNT(*)
           INTO VS_COUNT
           FROM COUNTRIES
           WHERE COUNTRY_ID = P_COUNTRY_ID;
           
          IF VN_COUNTRY = 0 THEN
            VS_COUNTRY_NAME := '해당국가 없음';
            ELSE
              SELECT COUNTRY_NAME
              INTO VS_COUNTRY_NAME
              FROM COUNTRIES
              WHERE COUNTRY_ID = P_COUNTRY_ID;
              
              END IF;
              
              RETURN VS_COUNTRY_NAME;
      

매개변수로 들어오는 국가가 있는지 체크하는데 COUNT 수가 0이면 '해당국가 없음' 이란 문자열 값을 할당했고, 0이 아니면 다시 국가명을 선택해 VS_COUNTRY_NAME 변수에 할당해 반환했다. 

위 함수를 호출하게 되면 NULL 값에 해당국가 없음이라고 확인된다.