카테고리 없음

오라클 함수(Function) 관리 Best Practice - 실무 완벽 가이드

shaprimanDev 2025. 12. 23. 20:31
반응형

오라클 함수(Function)는 특정 값을 반환하는 PL/SQL 서브프로그램으로, SQL 쿼리 내에서 직접 호출할 수 있다는 점에서 프로시저와 차별화됩니다. 이 가이드에서는 함수의 조회, 관리, 최적화까지 실무에서 필요한 모든 내용을 다룹니다.

목차

  1. 함수 기본 정보 조회
  2. 함수 파라미터 및 반환값 조회
  3. 함수 소스 코드 조회
  4. 함수 성능 분석
  5. DETERMINISTIC 함수 관리
  6. 함수 의존성 및 영향도 분석
  7. 함수 작성 Best Practices
  8. 함수 vs 프로시저 선택 가이드

1. 함수 기본 정보 조회

함수 존재 여부 및 상태 확인

SELECT
    OBJECT_NAME,
    OBJECT_TYPE,
    STATUS,
    CREATED,
    LAST_DDL_TIME
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'FUNCTION'
  AND OBJECT_NAME = UPPER('함수명');

주요 컬럼:

  • STATUS: VALID(정상) / INVALID(오류)
  • CREATED: 최초 생성일
  • LAST_DDL_TIME: 마지막 변경일

전체 함수 목록 조회

SELECT
    OBJECT_NAME AS "함수명",
    STATUS AS "상태",
    TO_CHAR(CREATED, 'YYYY-MM-DD') AS "생성일",
    TO_CHAR(LAST_DDL_TIME, 'YYYY-MM-DD HH24:MI:SS') AS "최종수정일"
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'FUNCTION'
ORDER BY OBJECT_NAME;

스키마별 함수 통계

SELECT
    OWNER AS "스키마",
    COUNT(*) AS "함수개수",
    SUM(CASE WHEN STATUS = 'VALID' THEN 1 ELSE 0 END) AS "정상",
    SUM(CASE WHEN STATUS = 'INVALID' THEN 1 ELSE 0 END) AS "오류"
FROM ALL_OBJECTS
WHERE OBJECT_TYPE = 'FUNCTION'
GROUP BY OWNER
ORDER BY COUNT(*) DESC;

2. 함수 파라미터 및 반환값 조회

함수 시그니처 확인

SELECT
    ARGUMENT_NAME,
    POSITION,
    DATA_TYPE,
    IN_OUT,
    DATA_LENGTH,
    DATA_PRECISION,
    DATA_SCALE,
    DEFAULT_VALUE
FROM ALL_ARGUMENTS
WHERE OBJECT_NAME = UPPER('함수명')
  AND PACKAGE_NAME IS NULL  -- 패키지 함수 제외
ORDER BY POSITION;

반환값 식별:

  • POSITION = 0: 함수의 반환값
  • ARGUMENT_NAME IS NULL: 반환값 (일부 케이스)

패키지 내 함수 파라미터 조회

SELECT
    OBJECT_NAME AS "패키지명",
    SUBPROGRAM_ID AS "서브프로그램ID",
    ARGUMENT_NAME AS "파라미터명",
    POSITION,
    DATA_TYPE,
    IN_OUT
FROM ALL_ARGUMENTS
WHERE PACKAGE_NAME = UPPER('패키지명')
  AND OBJECT_NAME = UPPER('함수명')
ORDER BY POSITION;

함수 상세 정보 (통합 뷰)

SELECT
    p.OBJECT_NAME AS "함수명",
    p.PROCEDURE_NAME,
    a.ARGUMENT_NAME AS "파라미터명",
    a.POSITION AS "순서",
    a.DATA_TYPE AS "데이터타입",
    a.IN_OUT AS "입출력",
    a.DEFAULT_VALUE AS "기본값"
FROM ALL_PROCEDURES p
LEFT JOIN ALL_ARGUMENTS a
    ON p.OBJECT_NAME = a.OBJECT_NAME
WHERE p.OBJECT_TYPE = 'FUNCTION'
  AND p.OBJECT_NAME = UPPER('함수명')
ORDER BY a.POSITION;

3. 함수 소스 코드 조회

전체 소스 코드 보기

SELECT
    LINE,
    TEXT
FROM ALL_SOURCE
WHERE NAME = UPPER('함수명')
  AND TYPE = 'FUNCTION'
ORDER BY LINE;

소스 코드를 하나의 문자열로 조회

SELECT
    LISTAGG(TEXT, '') WITHIN GROUP (ORDER BY LINE) AS SOURCE_CODE
FROM USER_SOURCE
WHERE NAME = UPPER('함수명')
  AND TYPE = 'FUNCTION';

특정 키워드를 포함한 함수 찾기

SELECT DISTINCT
    NAME AS "함수명",
    COUNT(*) AS "매칭라인수"
FROM ALL_SOURCE
WHERE TYPE = 'FUNCTION'
  AND UPPER(TEXT) LIKE '%키워드%'
GROUP BY NAME
ORDER BY COUNT(*) DESC;

실무 활용:

  • 특정 테이블을 사용하는 함수 찾기
  • 특정 함수를 호출하는 다른 함수 찾기
  • 하드코딩된 값 찾기

4. 함수 성능 분석

SQL 내에서 호출된 함수 성능 확인

SELECT
    sql_text,
    executions AS "실행횟수",
    ROUND(elapsed_time/1000000, 2) AS "총소요시간(초)",
    ROUND(elapsed_time/executions/1000000, 4) AS "평균소요시간(초)",
    ROUND(cpu_time/1000000, 2) AS "CPU시간(초)"
FROM v$sql
WHERE UPPER(sql_text) LIKE '%함수명%'
  AND executions > 0
ORDER BY elapsed_time DESC;

함수 호출 빈도 분석

-- SQL 문장에서 함수 호출 빈도
SELECT
    sql_text,
    executions,
    (LENGTH(sql_text) - LENGTH(REPLACE(UPPER(sql_text), UPPER('함수명'), ''))) 
    / LENGTH('함수명') AS "함수호출추정횟수"
FROM v$sql
WHERE UPPER(sql_text) LIKE '%함수명%'
ORDER BY executions DESC;

느린 함수 식별

-- 소스 코드 길이로 복잡도 추정
SELECT
    NAME AS "함수명",
    COUNT(*) AS "코드라인수",
    MAX(LINE) AS "최대라인번호"
FROM USER_SOURCE
WHERE TYPE = 'FUNCTION'
GROUP BY NAME
HAVING COUNT(*) > 100  -- 100줄 이상
ORDER BY COUNT(*) DESC;

5. DETERMINISTIC 함수 관리

DETERMINISTIC 속성 확인

DETERMINISTIC 함수는 같은 입력에 대해 항상 같은 결과를 반환하므로 오라클이 결과를 캐싱할 수 있습니다.

-- 소스에서 DETERMINISTIC 키워드 확인
SELECT
    NAME AS "함수명",
    CASE 
        WHEN MAX(CASE WHEN UPPER(TEXT) LIKE '%DETERMINISTIC%' THEN 1 ELSE 0 END) = 1 
        THEN 'YES' 
        ELSE 'NO' 
    END AS "DETERMINISTIC"
FROM USER_SOURCE
WHERE TYPE = 'FUNCTION'
GROUP BY NAME
ORDER BY NAME;

DETERMINISTIC 함수 작성 예시

-- ✅ 좋은 예: 순수 계산 함수
CREATE OR REPLACE FUNCTION calculate_tax(
    p_amount IN NUMBER
) RETURN NUMBER
DETERMINISTIC
IS
BEGIN
    RETURN p_amount * 0.1;  -- 항상 같은 결과
END;
/

-- ❌ 나쁜 예: 시간 의존적 함수
CREATE OR REPLACE FUNCTION get_current_discount(
    p_amount IN NUMBER
) RETURN NUMBER
DETERMINISTIC  -- 잘못된 사용!
IS
BEGIN
    IF SYSDATE BETWEEN '01-JAN-23' AND '31-DEC-23' THEN
        RETURN p_amount * 0.2;
    ELSE
        RETURN p_amount * 0.1;
    END IF;
END;
/

DETERMINISTIC 사용 조건:

  • 입력 파라미터만으로 결과가 결정
  • 데이터베이스 조회 없음
  • 시스템 함수(SYSDATE, USER 등) 사용 없음
  • 시퀀스 사용 없음

6. 함수 의존성 및 영향도 분석

함수가 참조하는 객체 확인

SELECT
    NAME AS "함수명",
    TYPE AS "타입",
    REFERENCED_OWNER AS "참조스키마",
    REFERENCED_NAME AS "참조객체",
    REFERENCED_TYPE AS "참조타입",
    REFERENCED_LINK_NAME AS "DB링크"
FROM USER_DEPENDENCIES
WHERE NAME = UPPER('함수명')
  AND TYPE = 'FUNCTION'
ORDER BY REFERENCED_TYPE, REFERENCED_NAME;

함수를 참조하는 객체 확인 (역방향)

SELECT
    NAME AS "참조하는객체",
    TYPE AS "객체타입",
    REFERENCED_NAME AS "함수명"
FROM USER_DEPENDENCIES
WHERE REFERENCED_NAME = UPPER('함수명')
  AND REFERENCED_TYPE = 'FUNCTION'
ORDER BY TYPE, NAME;

테이블 변경 시 영향받는 함수 찾기

SELECT DISTINCT
    d.NAME AS "영향받는함수",
    d.TYPE AS "객체타입",
    d.REFERENCED_NAME AS "의존테이블"
FROM USER_DEPENDENCIES d
WHERE d.REFERENCED_NAME = UPPER('테이블명')
  AND d.REFERENCED_TYPE = 'TABLE'
  AND d.TYPE = 'FUNCTION'
ORDER BY d.NAME;

함수 의존성 체인 분석

-- 3단계 의존성까지 추적
WITH dep_tree AS (
    -- Level 1: 직접 의존성
    SELECT 
        NAME AS func_name,
        REFERENCED_NAME AS depends_on,
        1 AS depth
    FROM USER_DEPENDENCIES
    WHERE NAME = UPPER('함수명')
      AND TYPE = 'FUNCTION'

    UNION ALL

    -- Level 2: 간접 의존성
    SELECT 
        dt.func_name,
        d.REFERENCED_NAME,
        2
    FROM dep_tree dt
    JOIN USER_DEPENDENCIES d
        ON dt.depends_on = d.NAME
    WHERE dt.depth = 1
      AND d.TYPE = 'FUNCTION'

    UNION ALL

    -- Level 3
    SELECT 
        dt.func_name,
        d.REFERENCED_NAME,
        3
    FROM dep_tree dt
    JOIN USER_DEPENDENCIES d
        ON dt.depends_on = d.NAME
    WHERE dt.depth = 2
      AND d.TYPE = 'FUNCTION'
)
SELECT DISTINCT
    func_name AS "함수명",
    depends_on AS "의존객체",
    depth AS "의존깊이"
FROM dep_tree
ORDER BY depth, depends_on;

7. 함수 작성 Best Practices

7.1 명명 규칙

-- 권장 명명 규칙
-- FN_[동사]_[명사] 또는 GET_[명사], CALC_[명사]
-- 예시:
-- - FN_GET_EMPLOYEE_NAME
-- - FN_CALC_TAX
-- - FN_CHECK_VALID_DATE
-- - GET_DEPARTMENT_TOTAL

7.2 RESULT_CACHE 활용

Oracle 11g 이상에서는 RESULT_CACHE를 사용하여 함수 결과를 메모리에 캐싱할 수 있습니다.

-- ✅ 좋은 예: 자주 호출되고 변경이 드문 데이터
CREATE OR REPLACE FUNCTION get_department_name(
    p_dept_id IN NUMBER
) RETURN VARCHAR2
RESULT_CACHE
IS
    v_dept_name VARCHAR2(100);
BEGIN
    SELECT department_name
    INTO v_dept_name
    FROM departments
    WHERE department_id = p_dept_id;

    RETURN v_dept_name;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END;
/

RESULT_CACHE 사용 조건:

  • 함수가 자주 호출됨
  • 입력값 조합이 제한적
  • 참조하는 데이터가 자주 변경되지 않음
  • SELECT 문만 있고 DML이 없음

7.3 예외 처리 패턴

-- ✅ 좋은 예: 명확한 예외 처리
CREATE OR REPLACE FUNCTION get_employee_salary(
    p_emp_id IN NUMBER
) RETURN NUMBER
IS
    v_salary NUMBER;
BEGIN
    SELECT salary
    INTO v_salary
    FROM employees
    WHERE employee_id = p_emp_id;

    RETURN v_salary;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- 로그 기록 또는 기본값 반환
        RETURN 0;
    WHEN TOO_MANY_ROWS THEN
        -- 데이터 정합성 문제
        RAISE_APPLICATION_ERROR(-20001, 
            '직원 ID ' || p_emp_id || '에 대한 중복 데이터 존재');
    WHEN OTHERS THEN
        -- 예상치 못한 오류
        RAISE_APPLICATION_ERROR(-20002, 
            '함수 실행 오류: ' || SQLERRM);
END;
/

7.4 NULL 처리

-- ✅ 좋은 예: NULL 안전 함수
CREATE OR REPLACE FUNCTION calculate_discount(
    p_amount IN NUMBER,
    p_rate IN NUMBER DEFAULT 0
) RETURN NUMBER
IS
BEGIN
    -- NULL 체크
    IF p_amount IS NULL THEN
        RETURN NULL;
    END IF;

    IF p_rate IS NULL OR p_rate < 0 THEN
        RETURN p_amount;  -- 할인 없음
    END IF;

    RETURN p_amount * (1 - p_rate);
END;
/

7.5 함수 순수성 유지 (Purity)

-- ✅ 좋은 예: 순수 함수 (사이드 이펙트 없음)
CREATE OR REPLACE FUNCTION format_phone_number(
    p_phone IN VARCHAR2
) RETURN VARCHAR2
DETERMINISTIC
IS
BEGIN
    RETURN SUBSTR(p_phone, 1, 3) || '-' || 
           SUBSTR(p_phone, 4, 4) || '-' || 
           SUBSTR(p_phone, 8, 4);
END;
/

-- ❌ 나쁜 예: 사이드 이펙트 있음
CREATE OR REPLACE FUNCTION log_and_return(
    p_value IN NUMBER
) RETURN NUMBER
IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    -- 함수에서 데이터 변경 (안티패턴)
    INSERT INTO audit_log VALUES (SYSDATE, p_value);
    COMMIT;

    RETURN p_value;
END;
/

7.6 성능을 위한 SQL 최적화

-- ❌ 나쁜 예: 함수 내에서 반복 쿼리
CREATE OR REPLACE FUNCTION get_total_orders_bad(
    p_customer_id IN NUMBER
) RETURN NUMBER
IS
    v_total NUMBER := 0;
BEGIN
    FOR rec IN (SELECT order_id FROM orders WHERE customer_id = p_customer_id) LOOP
        SELECT amount INTO v_total
        FROM order_details
        WHERE order_id = rec.order_id;
    END LOOP;

    RETURN v_total;
END;
/

-- ✅ 좋은 예: 단일 쿼리로 처리
CREATE OR REPLACE FUNCTION get_total_orders_good(
    p_customer_id IN NUMBER
) RETURN NUMBER
IS
    v_total NUMBER;
BEGIN
    SELECT SUM(od.amount)
    INTO v_total
    FROM orders o
    JOIN order_details od ON o.order_id = od.order_id
    WHERE o.customer_id = p_customer_id;

    RETURN NVL(v_total, 0);
END;
/

8. 함수 vs 프로시저 선택 가이드

언제 함수를 사용할까?

상황 함수 프로시저
SQL 쿼리 내에서 호출 ✅ ❌
단일 값 반환 ✅ △ (OUT 파라미터)
SELECT 문에서 사용 ✅ ❌
DML 수행 (INSERT/UPDATE/DELETE) ❌ 권장하지 않음 ✅
복잡한 비즈니스 로직 △ ✅
트랜잭션 제어 (COMMIT/ROLLBACK) ❌ ✅

함수 사용이 적합한 경우

-- ✅ Case 1: SQL에서 직접 사용
SELECT 
    employee_id,
    first_name,
    FN_GET_DEPARTMENT_NAME(department_id) AS dept_name,
    FN_CALC_TAX(salary) AS tax_amount
FROM employees;

-- ✅ Case 2: 재사용 가능한 계산 로직
SELECT 
    product_id,
    price,
    FN_APPLY_DISCOUNT(price, discount_rate) AS discounted_price
FROM products;

-- ✅ Case 3: 데이터 변환/포맷팅
SELECT 
    order_id,
    FN_FORMAT_DATE(order_date, 'YYYY-MM-DD') AS formatted_date
FROM orders;

프로시저 사용이 적합한 경우

-- ✅ Case 1: 복잡한 비즈니스 로직
BEGIN
    SP_PROCESS_MONTHLY_SALARY(
        p_year => 2024,
        p_month => 12
    );
END;
/

-- ✅ Case 2: 다중 DML 작업
BEGIN
    SP_TRANSFER_EMPLOYEE(
        p_emp_id => 100,
        p_new_dept_id => 50,
        p_effective_date => SYSDATE
    );
END;
/

9. 함수 성능 최적화 체크리스트

9.1 PARALLEL_ENABLE 사용

대량 데이터 처리 시 병렬 실행을 허용합니다.

CREATE OR REPLACE FUNCTION calculate_bonus(
    p_salary IN NUMBER
) RETURN NUMBER
PARALLEL_ENABLE
IS
BEGIN
    RETURN p_salary * 0.1;
END;
/

-- 병렬 실행 가능
SELECT 
    employee_id,
    calculate_bonus(salary) AS bonus
FROM employees;

9.2 함수 기반 인덱스 (FBI)

자주 사용되는 함수에 대해 인덱스를 생성합니다.

-- 함수 생성
CREATE OR REPLACE FUNCTION get_upper_name(
    p_name IN VARCHAR2
) RETURN VARCHAR2
DETERMINISTIC
IS
BEGIN
    RETURN UPPER(p_name);
END;
/

-- 함수 기반 인덱스 생성
CREATE INDEX idx_upper_name 
ON employees(get_upper_name(first_name));

-- 인덱스 활용 쿼리
SELECT * 
FROM employees
WHERE get_upper_name(first_name) = 'JOHN';

9.3 불필요한 함수 호출 제거

-- ❌ 나쁜 예: 같은 함수를 여러 번 호출
SELECT 
    employee_id,
    FN_GET_DEPT_NAME(department_id),
    FN_GET_DEPT_LOCATION(department_id),
    FN_GET_DEPT_MANAGER(department_id)
FROM employees;

-- ✅ 좋은 예: JOIN으로 한 번에 조회
SELECT 
    e.employee_id,
    d.department_name,
    d.location,
    d.manager_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

10. 함수 모니터링 및 관리

INVALID 함수 찾기 및 재컴파일

-- INVALID 함수 목록
SELECT
    OBJECT_NAME AS "함수명",
    STATUS AS "상태",
    LAST_DDL_TIME AS "최종수정일"
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'FUNCTION'
  AND STATUS = 'INVALID'
ORDER BY LAST_DDL_TIME DESC;

-- 컴파일 오류 확인
SELECT
    LINE,
    POSITION,
    TEXT AS "오류내용"
FROM USER_ERRORS
WHERE NAME = UPPER('함수명')
  AND TYPE = 'FUNCTION'
ORDER BY SEQUENCE;

-- 재컴파일 스크립트 생성
SELECT 'ALTER FUNCTION ' || OBJECT_NAME || ' COMPILE;'
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'FUNCTION'
  AND STATUS = 'INVALID';

함수 사용 현황 모니터링

-- 어떤 프로그램에서 함수를 사용하는지 확인
SELECT DISTINCT
    d.NAME AS "호출하는객체",
    d.TYPE AS "객체타입",
    o.STATUS AS "상태"
FROM USER_DEPENDENCIES d
JOIN USER_OBJECTS o ON d.NAME = o.OBJECT_NAME
WHERE d.REFERENCED_NAME = UPPER('함수명')
  AND d.REFERENCED_TYPE = 'FUNCTION'
ORDER BY d.TYPE, d.NAME;

함수 백업 (DDL 추출)

-- 개별 함수 DDL
SELECT DBMS_METADATA.GET_DDL('FUNCTION', '함수명') AS ddl
FROM DUAL;

-- 모든 함수 DDL 추출
SELECT DBMS_METADATA.GET_DDL('FUNCTION', OBJECT_NAME) AS ddl
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'FUNCTION'
ORDER BY OBJECT_NAME;

11. 함수 테스트 및 디버깅

단위 테스트 템플릿

-- 테스트 케이스 테이블
CREATE TABLE function_test_cases (
    test_id NUMBER PRIMARY KEY,
    function_name VARCHAR2(100),
    test_case VARCHAR2(500),
    input_params VARCHAR2(4000),
    expected_output VARCHAR2(4000),
    actual_output VARCHAR2(4000),
    test_result VARCHAR2(10),
    test_date DATE
);

-- 테스트 실행 예시
DECLARE
    v_result NUMBER;
    v_expected NUMBER := 1000;
BEGIN
    -- 함수 실행
    v_result := FN_CALC_TAX(10000);

    -- 결과 검증
    IF v_result = v_expected THEN
        DBMS_OUTPUT.PUT_LINE('✅ 테스트 성공');
    ELSE
        DBMS_OUTPUT.PUT_LINE('❌ 테스트 실패: 예상=' || v_expected || 
                             ', 실제=' || v_result);
    END IF;
END;
/

디버깅을 위한 로깅

-- 로그 테이블 생성
CREATE TABLE function_debug_log (
    log_id NUMBER PRIMARY KEY,
    function_name VARCHAR2(100),
    log_level VARCHAR2(20),
    log_message CLOB,
    log_date TIMESTAMP DEFAULT SYSTIMESTAMP
);

CREATE SEQUENCE seq_debug_log START WITH 1;

-- 로깅이 포함된 함수 예시
CREATE OR REPLACE FUNCTION calculate_with_log(
    p_amount IN NUMBER
) RETURN NUMBER
IS
    v_result NUMBER;
    v_debug_enabled BOOLEAN := TRUE;  -- 개발 시에만 TRUE
BEGIN
    IF v_debug_enabled THEN
        INSERT INTO function_debug_log VALUES (
            seq_debug_log.NEXTVAL,
            'CALCULATE_WITH_LOG',
            'INFO',
            'Input: ' || p_amount,
            SYSTIMESTAMP
        );
    END IF;

    -- 비즈니스 로직
    v_result := p_amount * 1.1;

    IF v_debug_enabled THEN
        INSERT INTO function_debug_log VALUES (
            seq_debug_log.NEXTVAL,
            'CALCULATE_WITH_LOG',
            'INFO',
            'Output: ' || v_result,
            SYSTIMESTAMP
        );
        COMMIT;  -- 로그는 별도 커밋
    END IF;

    RETURN v_result;
END;
/

12. 실무 패턴 모음

패턴 1: Safe Division (0으로 나누기 방지)

CREATE OR REPLACE FUNCTION safe_divide(
    p_numerator IN NUMBER,
    p_denominator IN NUMBER,
    p_default IN NUMBER DEFAULT NULL
) RETURN NUMBER
DETERMINISTIC
IS
BEGIN
    IF p_denominator = 0 OR p_denominator IS NULL THEN
        RETURN p_default;
    END IF;

    RETURN p_numerator / p_denominator;
END;
/

-- 사용 예
SELECT 
    product_id,
    safe_divide(total_sales, total_orders, 0) AS avg_order_value
FROM products;

패턴 2: String Aggregation

CREATE OR REPLACE FUNCTION get_employee_skills(
    p_emp_id IN NUMBER
) RETURN VARCHAR2
IS
    v_skills VARCHAR2(4000);
BEGIN
    SELECT LISTAGG(skill_name, ', ') WITHIN GROUP (ORDER BY skill_name)
    INTO v_skills
    FROM employee_skills
    WHERE employee_id = p_emp_id;

    RETURN v_skills;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END;
/

패턴 3: Date Range Validation

CREATE OR REPLACE FUNCTION is_date_in_range(
    p_check_date IN DATE,
    p_start_date IN DATE,
    p_end_date IN DATE
) RETURN VARCHAR2
DETERMINISTIC
IS
BEGIN
    IF p_check_date BETWEEN p_start_date AND p_end_date THEN
        RETURN 'Y';
    ELSE
        RETURN 'N';
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        RETURN 'N';
END;
/

패턴 4: Hierarchy Path Builder

CREATE OR REPLACE FUNCTION get_org_path(
    p_emp_id IN NUMBER
) RETURN VARCHAR2
IS
    v_path VARCHAR2(4000);
BEGIN
    SELECT LTRIM(SYS_CONNECT_BY_PATH(department_name, ' > '), ' > ')
    INTO v_path
    FROM departments
    START WITH department_id = (
        SELECT department_id 
        FROM employees 
        WHERE employee_id = p_emp_id
    )
    CONNECT BY PRIOR parent_department_id = department_id;

    RETURN v_path;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END;
/

13. 엑셀 추출용 종합 리포트

-- 함수 관리 리포트 (문서화용)
SELECT
    ROWNUM AS "NO",
    o.OBJECT_NAME AS "함수명",
    o.STATUS AS "상태",
    TO_CHAR(o.CREATED, 'YYYY-MM-DD') AS "생성일",
    TO_CHAR(o.LAST_DDL_TIME, 'YYYY-MM-DD') AS "수정일",
    (SELECT COUNT(*) 
     FROM USER_SOURCE s 
     WHERE s.NAME = o.OBJECT_NAME 
       AND s.TYPE = 'FUNCTION') AS "코드라인수",
    (SELECT COUNT(*) 
     FROM USER_DEPENDENCIES d 
     WHERE d.NAME = o.OBJECT_NAME 
       AND d.TYPE = 'FUNCTION') AS "의존성수",
    CASE 
        WHEN EXISTS (
            SELECT 1 FROM USER_SOURCE s 
            WHERE s.NAME = o.OBJECT_NAME 
              AND UPPER(s.TEXT) LIKE '%DETERMINISTIC%'
        ) THEN 'Y' 
        ELSE 'N' 
    END AS "DETERMINISTIC",
    CASE 
        WHEN EXISTS (
            SELECT 1 FROM USER_SOURCE s 
            WHERE s.NAME = o.OBJECT_NAME 
              AND UPPER(s.TEXT) LIKE '%RESULT_CACHE%'
        ) THEN 'Y' 
        ELSE 'N' 
    END AS "RESULT_CACHE"
FROM USER_OBJECTS o
WHERE o.OBJECT_TYPE = 'FUNCTION'
ORDER BY o.OBJECT_NAME;

14. 마치며

오라클 함수는 SQL과 PL/SQL의 강력한 결합을 가능하게 하는 핵심 기능입니다. 이 가이드의 내용을 활용하면:

  • ✅ 함수를 체계적으로 관리하고 모니터링할 수 있습니다
  • ✅ 성능 최적화를 위한 명확한 지침을 얻을 수 있습니다
  • ✅ 함수와 프로시저 중 적절한 선택을 할 수 있습니다
  • ✅ 의존성 관리를 통해 시스템 안정성을 높일 수 있습니다

핵심 권장사항:

  1. 함수는 순수하게 (사이드 이펙트 없이) 작성
  2. DETERMINISTIC, RESULT_CACHE를 적절히 활용
  3. SQL 내 함수 호출을 최소화 (JOIN 우선 고려)
  4. 정기적인 성능 모니터링과 리팩토링
  5. 명확한 명명 규칙과 문서화

 

반응형
저작자표시 비영리 변경금지 (새창열림)

'카테고리 없음'의 다른글

  • 현재글오라클 함수(Function) 관리 Best Practice - 실무 완벽 가이드

티스토리툴바