Oracle 실전 활용 팁 모음

Oracle 실전 활용 팁 모음

꿀벌의 노동

 

Oracle 데이터베이스를 다루면서 자주 접하는 문제를 해결하고, 효율적인 쿼리를 작성할 수 있는 방법들을 정리했습니다.

1. Oracle에서 만 19세 나이 산출 방법

만 나이를 계산하려면 MONTHS_BETWEEN 함수를 사용하여 생년월일과 현재 날짜 간 차이를 계산하고, 12로 나누어 연령을 산출할 수 있습니다.

sql

SELECT TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('2005-04-10', 'YYYY-MM-DD')) / 12) AS age
FROM dual;

만약 만 19세 이상인지 확인하려면 다음과 같은 쿼리를 활용할 수 있습니다:

sql

SELECT CASE 
    WHEN ADD_MONTHS(TO_DATE('2005-04-10', 'YYYY-MM-DD'), 12 * 19) <= SYSDATE 
    THEN '19세 이상' 
    ELSE '19세 미만' 
END AS age_status
FROM dual;

2. ORA-01847 오류 무시

날짜 형식 오류(ORA-01847)를 처리하기 위해 예외(Exception) 처리를 활용할 수 있습니다:

sql

BEGIN
    SELECT TO_DATE('2025-02-30', 'YYYY-MM-DD') FROM dual;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('오류 발생: 무시하고 계속 진행');
END;

입력 데이터가 정확한지 검증하는 것이 가장 효과적인 해결책입니다.

3. 사용자 정의 함수에서 오류 처리

프로시저 또는 함수에서 발생할 수 있는 쿼리 오류를 처리하려면 예외 처리를 포함하는 구조를 사용합니다:

sql

CREATE OR REPLACE FUNCTION get_employee_name(p_emp_id NUMBER)
RETURN VARCHAR2
IS
    v_name VARCHAR2(100);
BEGIN
    SELECT ename INTO v_name 
    FROM emp 
    WHERE empno = p_emp_id;

    RETURN v_name;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN '존재하지 않는 직원';
    WHEN TOO_MANY_ROWS THEN
        RETURN '중복된 데이터 오류';
    WHEN OTHERS THEN
        RETURN '예기치 않은 오류 발생';
END get_employee_name;
/

4. 프로시저의 쿼리 확인 방법

DBMS_METADATA 또는 USER_SOURCE를 활용하여 Oracle에서 프로시저의 소스 코드를 확인할 수 있습니다:

sql

SELECT DBMS_METADATA.GET_DDL('PROCEDURE', '프로시저_이름', '스키마_이름') 
FROM dual;

또는 USER_SOURCE를 사용하여 코드 확인:

sql

SELECT text 
FROM USER_SOURCE 
WHERE name = '프로시저_이름'
ORDER BY line;

5. PIVOT과 CONNECT BY를 활용한 동적 월별 합산

PIVOT을 활용하여 월별 거래 합계를 계산할 수 있습니다:

sql

WITH months AS (
    SELECT TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), LEVEL - 1), 'YYYY-MM') AS month_label
    FROM dual
    CONNECT BY LEVEL <= 12
),
transformed_data AS (
    SELECT TO_CHAR(transaction_date, 'YYYY-MM') AS month_label, amount
    FROM transactions
)
SELECT * 
FROM transformed_data
PIVOT (
    SUM(amount) FOR month_label IN ('2025-01' AS "JAN", '2025-02' AS "FEB", '2025-03' AS "MAR")
);

6. 반기별 거래 합산

CASE 문을 사용하여 반기별 거래 합계를 구합니다:

sql

SELECT 
    CASE 
        WHEN EXTRACT(MONTH FROM transaction_date) BETWEEN 1 AND 6 
        THEN TO_DATE(EXTRACT(YEAR FROM transaction_date) || '-01-01', 'YYYY-MM-DD')
        ELSE TO_DATE(EXTRACT(YEAR FROM transaction_date) || '-07-01', 'YYYY-MM-DD')
    END AS half_year_start,
    SUM(amount) AS total_amount
FROM transactions
GROUP BY 
    CASE 
        WHEN EXTRACT(MONTH FROM transaction_date) BETWEEN 1 AND 6 
        THEN TO_DATE(EXTRACT(YEAR FROM transaction_date) || '-01-01', 'YYYY-MM-DD')
        ELSE TO_DATE(EXTRACT(YEAR FROM transaction_date) || '-07-01', 'YYYY-MM-DD')
    END
ORDER BY half_year_start;

결론

위의 방법들을 활용하면 Oracle 데이터베이스를 효율적으로 사용할 수 있습니다. 각각의 쿼리는 특정한 문제를 해결하거나 데이터를 동적으로 관리하는 데 유용합니다. 여러분의 업무나 프로젝트에서 적극적으로 활용해보세요!