[Oracle] 사용자 정의 함수(Stored Function)

구문

CREATE OR REPLACE FUNCTION 함수이름

      RETURN 데이터타입

IS

BEGIN

     RETURN (값);

END;

 

EX) 입력받은 갑으로부터 10%의 세율을 얻는 함수 TAX()

CREATE OR REPLACE FUNCTION tax (p_value IN NUMBER)

                      RETURN NUMBER

IS

BEGIN

                     RETURN (p_value * 0.1);

END;

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

#사실 DB툴에서 함수정보를 확인하는게 더 간편하다.

 

 

생성된 함수는 데이터 사전(Data Dictionary)에 대문자로 등록되어 있습니다.

SELECT object_name, object_type
FROM user_objects
WHERE object_type = 'FUNCTION'
AND object_name = 'TAX';

작성된 함수의 소스코드 확인

SELECT text
FROM user_source
WHERE type = 'FUNCTION' AND name = 'TAX';

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT employee_id, last_name, salary, commission_pct, tax(salary + (commission_pct * 100)) AS tax
FROM employees;

 

질의의 결과를 보면 commission_pct 가 null 값을 가지는 사원의 경우, 세금 또한 null 값이 된다.

그럴경우 급여(salary)만으로 세금을 얻어낼 수 있도록 TAX()를 수정

 

CREATE OR REPLACE FUNCTION tax
(
p_sal IN employees.salary%TYPE,
p_bonus employees.commission_pct%TYPE
)

RETURN NUMBER
IS
BEGIN
            RETURN((p_sal + NVL(p_bonus, 0) * 100) * 0.1);
END;

 

SELECT employee_id, last_name, salary, commission_pct, tax(salary, commission_pct) AS tax
FROM employees;

 

급여(보너스 포함) 월 $1,000보다 적으면 세율을 5% 적용하며, $1,000 이상 $2,000 이하이면 10%, $2,000을 초과하면 20%를 적용하도록 수정

 

CREATE OR REPLACE FUNCTION tax
(p_sal IN employees.salary%TYPE, p_bonus employees.commission_pct%TYPE)
RETURN NUMBER
IS

l_sum NUMBER;
l_tax NUMBER;

BEGIN

            l_sum := p_sal + NVL(p_bonus, 0) * 100;
            
            IF l_sum < 1000 THEN
                          l_tax := l_sum * 0.05;
           ELSIF l_sum < 2000 THEN
                         l_tax := l_sum * 0.1;
           ELSE
                        l_tax := l_sum * 0.2;
          END IF;

          RETURN (l_tax);
          
END;

작성한 Tax()함수 급여의 정보를 보여주는 info_salary SP에 적용해보기

 

CREATE OR REPLACE PROCEDURE info_salary (p_empid employees.employee_id%TYPE)
IS

           l_empname employees.last_name%TYPE;
           l_sal employees.salary%TYPE;
           l_rank CHAR(1);
           l_tax NUMBER;
           
BEGIN

          SELECT last_name, salary,
                        CASE WHEN salary < 1000 THEN 'C'
                                   WHEN salary BETWEEN 1000 AND 2000 THEN 'B'
                                   WHEN salary > 2000 THEN 'A'
                       END CASE,
                      tax(salary, commission_pct)
          INTO l_empname, l_sal, l_rank, l_tax
          FROM employees
          WHERE employee_id = p_empid;

--아래 주석 부분은 위 질의문과 같은 결과를 얻을 수 있으나, CASE문이 간결하므로 앞서 작성된 질의문을 권장

/*
      SELECT last_name, salary, tax(salary, commission_pct)
      INTO l_empname, l_sal, l_tax
      FROM employees
      WHERE employee_id = p_empid;

      IF l_sal < 1000 THEN
              l_rank := C;
      ELSIF l_sal BETWEEN 1000 AND 2000 THEN
             l_rank := 'B';
      ELSE
             l_rank := 'A';
      END IF;

*/

DBMS_OUTPUT.PUT_LINE (p_empid || ' ' || l_empname || ' ' || l_sal || ' ' || l_rank || ' ' || l_tax);

END;