오라클 함수의 작성법과 다양한 유형의 예제

오라클 데이터베이스에서 함수(Function)는 특정 입력값을 받아 처리한 뒤 결과값을 반환하는 PL/SQL 블록입니다. 함수는 SQL 및 PL/SQL 환경에서 재사용 가능하며, 데이터 처리, 변환, 계산 등의 반복 작업을 효율적으로 수행하는 데 사용됩니다.

이 글에서는 오라클 함수의 작성법, 함수 유형, 다양한 예제를 자세히 다룹니다.

---

1. 오라클 함수란?

오라클 함수는 입력값을 받아 특정 작업을 수행한 후 결과값을 반환하는 PL/SQL 프로그램 단위입니다. 함수는 보통 계산 작업이나 데이터를 변환하는 데 사용되며, SELECT, INSERT, UPDATE, DELETE 문 등에서 호출할 수 있습니다.

1.1. 함수와 프로시저의 차이점

구분	함수(Function)	프로시저(Procedure)
반환값	항상 반환값이 있음 (RETURN 키워드 사용)	반환값이 없어도 됨
호출 가능 위치	SQL 문과 PL/SQL 모두에서 호출 가능	주로 PL/SQL 블록에서 호출
사용 목적	단일 값을 반환하거나 계산에 사용	특정 작업이나 프로세스를 수행

---

2. 함수 작성법

오라클 함수는 다음의 구조로 작성됩니다:

2.1. 함수 기본 구조

CREATE [OR REPLACE] FUNCTION 함수명
   (매개변수1 데이터타입, 매개변수2 데이터타입, ...)
RETURN 반환타입 IS
   -- 지역 변수 선언
BEGIN
   -- 함수 로직 작성
   RETURN 반환값;
END 함수명;

2.2. 주요 구성 요소

• CREATE [OR REPLACE]: 함수를 새로 생성하거나 기존 함수를 대체합니다.
• FUNCTION 함수명: 함수의 이름을 정의합니다.
• 매개변수: 입력값을 정의하며, 데이터 타입을 지정해야 합니다.
• RETURN 반환타입: 함수의 반환값 데이터 타입을 지정합니다.
• IS 또는 AS: 함수 본문을 시작합니다.
• BEGIN ... END: 함수의 실행 블록입니다.
• RETURN: 함수 결과값을 반환합니다.

---

3. 기본 예제: 오라클 함수 작성 및 호출

3.1. 간단한 함수 작성

CREATE OR REPLACE FUNCTION get_employee_salary(emp_id NUMBER)
RETURN NUMBER IS
   v_salary NUMBER;
BEGIN
   SELECT salary INTO v_salary
   FROM employees
   WHERE employee_id = emp_id;

   RETURN v_salary;
END get_employee_salary;
/

3.2. 함수 호출

• PL/SQL 블록에서 호출:
• DECLARE salary NUMBER; BEGIN salary := get_employee_salary(101); DBMS_OUTPUT.PUT_LINE('직원의 급여: ' || salary); END; /
• SQL 문에서 호출:
• SELECT employee_id, get_employee_salary(employee_id) AS salary FROM employees WHERE department_id = 10;

---

4. 매개변수 모드

함수에서 매개변수는 보통 IN 모드로 사용되며, 이는 함수에 값을 전달하는 데 사용됩니다. 하지만 오라클 프로시저에서는 OUT 또는 IN OUT 모드도 사용할 수 있습니다.

4.1. 매개변수 모드 예제

CREATE OR REPLACE FUNCTION calculate_bonus(salary NUMBER, bonus_rate NUMBER)
RETURN NUMBER IS
   bonus NUMBER;
BEGIN
   bonus := salary * bonus_rate;
   RETURN bonus;
END calculate_bonus;
/

호출 예제

DECLARE
   result NUMBER;
BEGIN
   result := calculate_bonus(5000, 0.1);
   DBMS_OUTPUT.PUT_LINE('보너스 금액: ' || result);
END;
/

---

5. 다양한 유형의 함수 예제

5.1. 단일 값 반환 함수

CREATE OR REPLACE FUNCTION square_number(n NUMBER)
RETURN NUMBER IS
BEGIN
   RETURN n * n;
END square_number;
/

호출:

SELECT square_number(5) FROM dual;

5.2. 문자열 반환 함수

CREATE OR REPLACE FUNCTION greet_user(name VARCHAR2)
RETURN VARCHAR2 IS
BEGIN
   RETURN 'Hello, ' || name || '!';
END greet_user;
/

호출:

SELECT greet_user('Alice') FROM dual;

5.3. 날짜 계산 함수

CREATE OR REPLACE FUNCTION add_days_to_date(start_date DATE, num_days NUMBER)
RETURN DATE IS
BEGIN
   RETURN start_date + num_days;
END add_days_to_date;
/

호출:

SELECT add_days_to_date(SYSDATE, 30) FROM dual;

5.4. 테이블 기반 값 반환 함수

CREATE OR REPLACE FUNCTION get_department_name(dept_id NUMBER)
RETURN VARCHAR2 IS
   dept_name VARCHAR2(100);
BEGIN
   SELECT department_name INTO dept_name
   FROM departments
   WHERE department_id = dept_id;

   RETURN dept_name;
END get_department_name;
/

호출:

SELECT get_department_name(10) FROM dual;

---

6. 동적 SQL을 사용하는 함수

동적 SQL을 사용하여 실행 시 쿼리를 구성할 수도 있습니다.

CREATE OR REPLACE FUNCTION execute_dynamic_query(query VARCHAR2)
RETURN VARCHAR2 IS
   result VARCHAR2(100);
BEGIN
   EXECUTE IMMEDIATE query INTO result;
   RETURN result;
END execute_dynamic_query;
/

호출:

DECLARE
   query_result VARCHAR2(100);
BEGIN
   query_result := execute_dynamic_query('SELECT COUNT(*) FROM employees');
   DBMS_OUTPUT.PUT_LINE('결과: ' || query_result);
END;
/

---

7. 에러 처리와 예외 처리

오라클 함수에서 에러가 발생하면 예외 처리를 통해 이를 처리할 수 있습니다.

7.1. 예외 처리 예제

CREATE OR REPLACE FUNCTION safe_divide(numerator NUMBER, denominator NUMBER)
RETURN NUMBER IS
BEGIN
   IF denominator = 0 THEN
      RETURN NULL; -- 0으로 나눌 수 없으므로 NULL 반환
   ELSE
      RETURN numerator / denominator;
   END IF;
EXCEPTION
   WHEN OTHERS THEN
      RETURN NULL; -- 기타 에러 발생 시 NULL 반환
END safe_divide;
/

호출:

SELECT safe_divide(10, 2) FROM dual; -- 결과: 5
SELECT safe_divide(10, 0) FROM dual; -- 결과: NULL

---

8. 함수의 사용 시 주의사항

1. DDL 문 금지
   함수에서는 CREATE, DROP 등의 DDL 문을 사용할 수 없습니다.
2. SIDE EFFECTS 회피
   • 함수는 외부 상태를 변경하지 않는 것이 이상적입니다.
   • INSERT, UPDATE, DELETE 같은 DML 문을 사용하는 경우 주의해야 합니다.
3. SQL 문에서의 호출 제한
   • SQL 문에서 호출되는 함수는 PRAGMA AUTONOMOUS_TRANSACTION을 사용할 수 없습니다.

---

9. 성능 최적화

1. 결과 캐싱
   • 자주 호출되는 함수는 결과를 캐싱하여 성능을 향상시킬 수 있습니다.

     CREATE OR REPLACE FUNCTION cached_function(param NUMBER)
     RETURN NUMBER
     RESULT_CACHE IS
     BEGIN
      RETURN param * param;
     END cached_function;
     /

2. 복잡한 로직 분리
   • 함수 내부 로직이 복잡하다면 적절히 분리하여 가독성과 성능을 개선합니다.

---

10. 결론

오라클 함수는 데이터 처리와 로직 재사용에 강력한 도구입니다. 이 글에서는 함수의 기본 개념부터 다양한 유형의 작성법, 그리고 실무에서 활용할 수 있는 예제까지 살펴보았습니다. 함수 사용 시 올바른 설계와 적절한 예외 처리를 통해 안정적이고 효율적인 데이터베이스 프로그래밍을 구현해 보세요!