🌟 프로시저와 트리거, 왜 배워야 할까?

📚 PL/SQL 기본 개념부터 차근차근

PL/SQL이 뭐야?

PL/SQL 블록 구조 이해하기

DECLARE
    -- 선언부: 변수, 상수, 커서 등을 선언
    v_employee_name VARCHAR2(100);
    v_salary NUMBER;
BEGIN
    -- 실행부: 실제 로직이 들어가는 부분
    SELECT employee_name, salary 
    INTO v_employee_name, v_salary
    FROM employees 
    WHERE employee_id = 100;
    
    DBMS_OUTPUT.PUT_LINE('직원명: ' || v_employee_name);
EXCEPTION
    -- 예외처리부: 에러 발생 시 처리
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('해당 직원이 없습니다.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('오류 발생: ' || SQLERRM);
END;
/

🔧 프로시저(Procedure) 완전 정복

프로시저가 뭐길래?

프로시저 생성 기본 문법

CREATE OR REPLACE PROCEDURE 프로시저명
(
    파라미터1 IN 데이터타입,
    파라미터2 OUT 데이터타입,
    파라미터3 IN OUT 데이터타입
)
IS
    -- 변수 선언
    v_local_var VARCHAR2(100);
BEGIN
    -- 실행 로직
    실행문들;
EXCEPTION
    -- 예외 처리
    WHEN 예외명 THEN
        처리문;
END 프로시저명;
/

실전 예제 1: 직원 정보 조회 프로시저

CREATE OR REPLACE PROCEDURE get_employee_info
(
    p_emp_id IN NUMBER,
    p_emp_name OUT VARCHAR2,
    p_salary OUT NUMBER
)
IS
BEGIN
    SELECT employee_name, salary
    INTO p_emp_name, p_salary
    FROM employees
    WHERE employee_id = p_emp_id;
    
    DBMS_OUTPUT.PUT_LINE('조회 완료: ' || p_emp_name);
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_emp_name := '없음';
        p_salary := 0;
        DBMS_OUTPUT.PUT_LINE('해당 직원이 존재하지 않습니다.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('오류 발생: ' || SQLERRM);
        RAISE;
END get_employee_info;
/

DECLARE
    v_name VARCHAR2(100);
    v_sal NUMBER;
BEGIN
    get_employee_info(100, v_name, v_sal);
    DBMS_OUTPUT.PUT_LINE('직원명: ' || v_name || ', 급여: ' || v_sal);
END;
/

실전 예제 2: 급여 인상 프로시저

CREATE OR REPLACE PROCEDURE raise_salary_by_dept
(
    p_dept_id IN NUMBER,
    p_raise_percent IN NUMBER,
    p_affected_count OUT NUMBER
)
IS
    v_min_salary NUMBER := 3000000;  -- 최소 급여
    v_max_raise NUMBER := 20;        -- 최대 인상률 20%
BEGIN
    -- 인상률 검증
    IF p_raise_percent <= 0 OR p_raise_percent > v_max_raise THEN
        RAISE_APPLICATION_ERROR(-20001, 
            '인상률은 0보다 크고 ' || v_max_raise || '% 이하여야 합니다.');
    END IF;
    
    -- 급여 인상 실행
    UPDATE employees
    SET salary = salary * (1 + p_raise_percent / 100),
        last_update_date = SYSDATE
    WHERE department_id = p_dept_id
      AND salary >= v_min_salary;
    
    -- 영향받은 행 수 저장
    p_affected_count := SQL%ROWCOUNT;
    
    -- 커밋
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE(p_affected_count || '명의 급여가 인상되었습니다.');
    
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('급여 인상 실패: ' || SQLERRM);
        RAISE;
END raise_salary_by_dept;
/

실전 예제 3: 복잡한 비즈니스 로직 - 주문 처리 프로시저

CREATE OR REPLACE PROCEDURE process_order
(
    p_customer_id IN NUMBER,
    p_product_id IN NUMBER,
    p_quantity IN NUMBER,
    p_order_id OUT NUMBER,
    p_result_msg OUT VARCHAR2
)
IS
    v_stock_quantity NUMBER;
    v_product_price NUMBER;
    v_total_amount NUMBER;
    v_point_earned NUMBER;
    
    -- 사용자 정의 예외
    e_insufficient_stock EXCEPTION;
    e_invalid_quantity EXCEPTION;
BEGIN
    -- 1. 입력값 검증
    IF p_quantity <= 0 THEN
        RAISE e_invalid_quantity;
    END IF;
    
    -- 2. 재고 확인
    SELECT stock_quantity, price
    INTO v_stock_quantity, v_product_price
    FROM products
    WHERE product_id = p_product_id
    FOR UPDATE;  -- 동시성 제어를 위한 행 잠금
    
    IF v_stock_quantity < p_quantity THEN
        RAISE e_insufficient_stock;
    END IF;
    
    -- 3. 총 금액 계산
    v_total_amount := v_product_price * p_quantity;
    v_point_earned := TRUNC(v_total_amount * 0.01);  -- 1% 포인트 적립
    
    -- 4. 주문 생성
    INSERT INTO orders (order_id, customer_id, product_id, quantity, 
                        total_amount, order_date, status)
    VALUES (orders_seq.NEXTVAL, p_customer_id, p_product_id, p_quantity,
            v_total_amount, SYSDATE, 'COMPLETED')
    RETURNING order_id INTO p_order_id;
    
    -- 5. 재고 차감
    UPDATE products
    SET stock_quantity = stock_quantity - p_quantity,
        last_update_date = SYSDATE
    WHERE product_id = p_product_id;
    
    -- 6. 포인트 적립
    UPDATE customers
    SET point_balance = point_balance + v_point_earned,
        total_purchase_amount = total_purchase_amount + v_total_amount
    WHERE customer_id = p_customer_id;
    
    -- 7. 로그 기록
    INSERT INTO order_logs (log_id, order_id, action, action_date)
    VALUES (order_logs_seq.NEXTVAL, p_order_id, 'ORDER_COMPLETED', SYSDATE);
    
    COMMIT;
    
    p_result_msg := '주문 완료! 주문번호: ' || p_order_id || 
                    ', 적립 포인트: ' || v_point_earned;
    
EXCEPTION
    WHEN e_invalid_quantity THEN
        ROLLBACK;
        p_result_msg := '주문 수량은 1개 이상이어야 합니다.';
        p_order_id := -1;
        
    WHEN e_insufficient_stock THEN
        ROLLBACK;
        p_result_msg := '재고 부족! 현재 재고: ' || v_stock_quantity || '개';
        p_order_id := -1;
        
    WHEN NO_DATA_FOUND THEN
        ROLLBACK;
        p_result_msg := '존재하지 않는 상품이거나 고객입니다.';
        p_order_id := -1;
        
    WHEN OTHERS THEN
        ROLLBACK;
        p_result_msg := '주문 처리 실패: ' || SQLERRM;
        p_order_id := -1;
        -- 관리자에게 알림 (실제로는 별도 알림 프로시저 호출)
        INSERT INTO error_logs (error_id, error_msg, error_date)
        VALUES (error_logs_seq.NEXTVAL, SQLERRM, SYSDATE);
        COMMIT;  -- 에러 로그는 별도로 커밋
END process_order;
/

프로시저 디버깅과 모니터링

-- 1. DBMS_OUTPUT으로 디버깅 정보 출력
SET SERVEROUTPUT ON;  -- 먼저 이걸 실행해야 출력이 보여!

CREATE OR REPLACE PROCEDURE debug_example
IS
    v_step VARCHAR2(100);
BEGIN
    v_step := 'Step 1: 데이터 조회';
    DBMS_OUTPUT.PUT_LINE(v_step || ' 시작');
    -- 실제 로직
    DBMS_OUTPUT.PUT_LINE(v_step || ' 완료');
    
    v_step := 'Step 2: 데이터 처리';
    DBMS_OUTPUT.PUT_LINE(v_step || ' 시작');
    -- 실제 로직
    DBMS_OUTPUT.PUT_LINE(v_step || ' 완료');
END;
/

-- 2. 프로시저 실행 시간 측정
DECLARE
    v_start_time TIMESTAMP;
    v_end_time TIMESTAMP;
    v_result VARCHAR2(1000);
    v_order_id NUMBER;
BEGIN
    v_start_time := SYSTIMESTAMP;
    
    process_order(1001, 2001, 5, v_order_id, v_result);
    
    v_end_time := SYSTIMESTAMP;
    
    DBMS_OUTPUT.PUT_LINE('실행 시간: ' || 
        TO_CHAR((v_end_time - v_start_time) DAY(0) TO SECOND(3)));
    DBMS_OUTPUT.PUT_LINE('결과: ' || v_result);
END;
/

⚡ 트리거(Trigger) 완전 정복

트리거가 뭐야?

트리거 종류와 타이밍

트리거 기본 문법

CREATE OR REPLACE TRIGGER 트리거명
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON 테이블명
[FOR EACH ROW]
[WHEN (조건)]
DECLARE
    -- 변수 선언
BEGIN
    -- 실행 로직
    -- :NEW.컬럼명 - 새로운 값 (INSERT, UPDATE)
    -- :OLD.컬럼명 - 이전 값 (UPDATE, DELETE)
EXCEPTION
    -- 예외 처리
END;
/

실전 예제 1: 감사 로그 트리거

-- 먼저 감사 로그 테이블 생성
CREATE TABLE employee_audit_log (
    log_id NUMBER PRIMARY KEY,
    employee_id NUMBER,
    action_type VARCHAR2(10),
    old_name VARCHAR2(100),
    new_name VARCHAR2(100),
    old_salary NUMBER,
    new_salary NUMBER,
    changed_by VARCHAR2(100),
    changed_date TIMESTAMP
);

CREATE SEQUENCE audit_log_seq START WITH 1;

-- 감사 로그 트리거 생성
CREATE OR REPLACE TRIGGER trg_employee_audit
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
DECLARE
    v_action VARCHAR2(10);
    v_user VARCHAR2(100);
BEGIN
    -- 현재 사용자 정보 가져오기
    v_user := USER;
    
    -- 어떤 작업인지 판단
    IF INSERTING THEN
        v_action := 'INSERT';
        INSERT INTO employee_audit_log 
        VALUES (audit_log_seq.NEXTVAL, :NEW.employee_id, v_action,
                NULL, :NEW.employee_name, NULL, :NEW.salary,
                v_user, SYSTIMESTAMP);
                
    ELSIF UPDATING THEN
        v_action := 'UPDATE';
        INSERT INTO employee_audit_log 
        VALUES (audit_log_seq.NEXTVAL, :NEW.employee_id, v_action,
                :OLD.employee_name, :NEW.employee_name,
                :OLD.salary, :NEW.salary,
                v_user, SYSTIMESTAMP);
                
    ELSIF DELETING THEN
        v_action := 'DELETE';
        INSERT INTO employee_audit_log 
        VALUES (audit_log_seq.NEXTVAL, :OLD.employee_id, v_action,
                :OLD.employee_name, NULL, :OLD.salary, NULL,
                v_user, SYSTIMESTAMP);
    END IF;
    
EXCEPTION
    WHEN OTHERS THEN
        -- 로그 기록 실패해도 원래 작업은 계속 진행
        DBMS_OUTPUT.PUT_LINE('감사 로그 기록 실패: ' || SQLERRM);
END;
/

실전 예제 2: 데이터 검증 트리거

CREATE OR REPLACE TRIGGER trg_validate_employee
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
DECLARE
    v_age NUMBER;
    v_dept_exists NUMBER;
BEGIN
    -- 1. 급여 검증
    IF :NEW.salary < 0 THEN
        RAISE_APPLICATION_ERROR(-20001, '급여는 0보다 작을 수 없습니다.');
    END IF;
    
    IF :NEW.salary > 100000000 THEN
        RAISE_APPLICATION_ERROR(-20002, '급여가 너무 높습니다. 확인이 필요합니다.');
    END IF;
    
    -- 2. 나이 검증
    v_age := TRUNC(MONTHS_BETWEEN(SYSDATE, :NEW.birth_date) / 12);
    
    IF v_age < 18 THEN
        RAISE_APPLICATION_ERROR(-20003, '만 18세 미만은 채용할 수 없습니다.');
    END IF;
    
    IF v_age > 70 THEN
        RAISE_APPLICATION_ERROR(-20004, '정년을 초과했습니다.');
    END IF;
    
    -- 3. 부서 존재 여부 확인
    IF :NEW.department_id IS NOT NULL THEN
        SELECT COUNT(*)
        INTO v_dept_exists
        FROM departments
        WHERE department_id = :NEW.department_id;
        
        IF v_dept_exists = 0 THEN
            RAISE_APPLICATION_ERROR(-20005, '존재하지 않는 부서입니다.');
        END IF;
    END IF;
    
    -- 4. 이메일 형식 검증
    IF :NEW.email IS NOT NULL THEN
        IF NOT REGEXP_LIKE(:NEW.email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$') THEN
            RAISE_APPLICATION_ERROR(-20006, '올바른 이메일 형식이 아닙니다.');
        END IF;
    END IF;
    
    -- 5. 전화번호 형식 자동 정리
    IF :NEW.phone_number IS NOT NULL THEN
        :NEW.phone_number := REGEXP_REPLACE(:NEW.phone_number, '[^0-9]', '');
    END IF;
    
    -- 6. 자동으로 생성일시 설정 (INSERT 시에만)
    IF INSERTING THEN
        :NEW.created_date := SYSDATE;
        :NEW.created_by := USER;
    END IF;
    
    -- 7. 자동으로 수정일시 설정 (UPDATE 시에만)
    IF UPDATING THEN
        :NEW.last_update_date := SYSDATE;
        :NEW.last_update_by := USER;
    END IF;
    
END;
/

실전 예제 3: 자동 계산 및 통계 업데이트 트리거

CREATE OR REPLACE TRIGGER trg_update_customer_stats
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW
DECLARE
    v_customer_id NUMBER;
    v_amount_change NUMBER := 0;
    v_count_change NUMBER := 0;
BEGIN
    -- 어떤 작업인지에 따라 변경값 계산
    IF INSERTING THEN
        v_customer_id := :NEW.customer_id;
        v_amount_change := :NEW.total_amount;
        v_count_change := 1;
        
    ELSIF UPDATING THEN
        v_customer_id := :NEW.customer_id;
        v_amount_change := :NEW.total_amount - :OLD.total_amount;
        -- 주문 수는 변경 없음
        
    ELSIF DELETING THEN
        v_customer_id := :OLD.customer_id;
        v_amount_change := -:OLD.total_amount;
        v_count_change := -1;
    END IF;
    
    -- 고객 통계 업데이트
    UPDATE customers
    SET total_purchase_amount = total_purchase_amount + v_amount_change,
        total_order_count = total_order_count + v_count_change,
        last_order_date = CASE 
            WHEN INSERTING OR UPDATING THEN SYSDATE 
            ELSE last_order_date 
        END,
        customer_grade = CASE
            WHEN total_purchase_amount + v_amount_change >= 10000000 THEN 'VIP'
            WHEN total_purchase_amount + v_amount_change >= 5000000 THEN 'GOLD'
            WHEN total_purchase_amount + v_amount_change >= 1000000 THEN 'SILVER'
            ELSE 'BRONZE'
        END
    WHERE customer_id = v_customer_id;
    
EXCEPTION
    WHEN OTHERS THEN
        -- 통계 업데이트 실패 시 로그 기록
        INSERT INTO error_logs (error_id, error_msg, error_date)
        VALUES (error_logs_seq.NEXTVAL, 
                '고객 통계 업데이트 실패: ' || SQLERRM, SYSDATE);
        -- 원래 작업은 계속 진행 (예외를 다시 발생시키지 않음)
END;
/

실전 예제 4: 복합 이벤트 트리거 (COMPOUND TRIGGER)

CREATE OR REPLACE TRIGGER trg_employee_complex
FOR INSERT OR UPDATE OR DELETE ON employees
COMPOUND TRIGGER
    
    -- 패키지 레벨 변수 (모든 타이밍 포인트에서 공유)
    TYPE t_emp_ids IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    v_affected_emp_ids t_emp_ids;
    v_counter PLS_INTEGER := 0;
    
    -- BEFORE STATEMENT: DML 문 실행 전 (한 번만)
    BEFORE STATEMENT IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('=== 직원 데이터 변경 시작 ===');
        v_counter := 0;
    END BEFORE STATEMENT;
    
    -- BEFORE EACH ROW: 각 행 처리 전
    BEFORE EACH ROW IS
    BEGIN
        IF INSERTING THEN
            -- 신규 직원번호 자동 생성
            IF :NEW.employee_id IS NULL THEN
                :NEW.employee_id := employees_seq.NEXTVAL;
            END IF;
            -- 입사일 자동 설정
            IF :NEW.hire_date IS NULL THEN
                :NEW.hire_date := SYSDATE;
            END IF;
        END IF;
        
        IF UPDATING THEN
            -- 급여 인상률 제한 (한 번에 50% 이상 인상 불가)
            IF :NEW.salary > :OLD.salary * 1.5 THEN
                RAISE_APPLICATION_ERROR(-20010, 
                    '급여는 한 번에 50% 이상 인상할 수 없습니다.');
            END IF;
        END IF;
    END BEFORE EACH ROW;
    
    -- AFTER EACH ROW: 각 행 처리 후
    AFTER EACH ROW IS
    BEGIN
        v_counter := v_counter + 1;
        
        IF INSERTING OR UPDATING THEN
            v_affected_emp_ids(v_counter) := :NEW.employee_id;
        ELSIF DELETING THEN
            v_affected_emp_ids(v_counter) := :OLD.employee_id;
        END IF;
    END AFTER EACH ROW;
    
    -- AFTER STATEMENT: DML 문 실행 후 (한 번만)
    AFTER STATEMENT IS
        v_dept_id NUMBER;
    BEGIN
        DBMS_OUTPUT.PUT_LINE('총 ' || v_counter || '건의 데이터가 변경되었습니다.');
        
        -- 영향받은 부서의 통계 업데이트
        FOR i IN 1..v_counter LOOP
            SELECT department_id INTO v_dept_id
            FROM employees
            WHERE employee_id = v_affected_emp_ids(i);
            
            -- 부서별 통계 업데이트 (중복 제거는 별도 로직 필요)
            UPDATE departments
            SET employee_count = (
                SELECT COUNT(*) FROM employees 
                WHERE department_id = v_dept_id
            ),
            avg_salary = (
                SELECT AVG(salary) FROM employees 
                WHERE department_id = v_dept_id
            )
            WHERE department_id = v_dept_id;
        END LOOP;
        
        DBMS_OUTPUT.PUT_LINE('=== 직원 데이터 변경 완료 ===');
        
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('통계 업데이트 실패: ' || SQLERRM);
    END AFTER STATEMENT;
    
END trg_employee_complex;
/

트리거 사용 시 주의사항과 베스트 프랙티스

자율 트랜잭션 활용

CREATE OR REPLACE TRIGGER trg_error_logging
AFTER INSERT OR UPDATE OR DELETE ON critical_data
FOR EACH ROW
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;  -- 자율 트랜잭션 선언
BEGIN
    -- 이 트리거의 작업은 메인 트랜잭션과 독립적
    INSERT INTO audit_log (log_id, action, log_date)
    VALUES (audit_seq.NEXTVAL, 
            CASE 
                WHEN INSERTING THEN 'INSERT'
                WHEN UPDATING THEN 'UPDATE'
                WHEN DELETING THEN 'DELETE'
            END,
            SYSDATE);
    
    COMMIT;  -- 자율 트랜잭션이므로 COMMIT 가능!
    
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;  -- 로그 기록 실패해도 메인 트랜잭션에 영향 없음
END;
/

🎨 프로시저와 트리거 고급 활용 패턴

패턴 1: 프로시저에서 트리거 제어하기

CREATE OR REPLACE PROCEDURE bulk_update_employees
(
    p_dept_id IN NUMBER,
    p_raise_percent IN NUMBER
)
IS
BEGIN
    -- 트리거 비활성화
    EXECUTE IMMEDIATE 'ALTER TRIGGER trg_employee_audit DISABLE';
    
    -- 대량 업데이트 수행
    UPDATE employees
    SET salary = salary * (1 + p_raise_percent / 100)
    WHERE department_id = p_dept_id;
    
    COMMIT;
    
    -- 트리거 다시 활성화
    EXECUTE IMMEDIATE 'ALTER TRIGGER trg_employee_audit ENABLE';
    
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || '건 업데이트 완료');
    
EXCEPTION
    WHEN OTHERS THEN
        -- 에러 발생 시에도 트리거는 반드시 다시 활성화
        EXECUTE IMMEDIATE 'ALTER TRIGGER trg_employee_audit ENABLE';
        RAISE;
END;
/

패턴 2: 동적 SQL 활용

CREATE OR REPLACE PROCEDURE dynamic_table_backup
(
    p_table_name IN VARCHAR2,
    p_where_clause IN VARCHAR2 DEFAULT NULL
)
IS
    v_sql VARCHAR2(4000);
    v_backup_table VARCHAR2(100);
    v_count NUMBER;
BEGIN
    -- 백업 테이블명 생성
    v_backup_table := p_table_name || '_BACKUP_' || 
                      TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS');
    
    -- 1. 백업 테이블 생성
    v_sql := 'CREATE TABLE ' || v_backup_table || 
             ' AS SELECT * FROM ' || p_table_name;
    
    IF p_where_clause IS NOT NULL THEN
        v_sql := v_sql || ' WHERE ' || p_where_clause;
    ELSE
        v_sql := v_sql || ' WHERE 1=1';
    END IF;
    
    EXECUTE IMMEDIATE v_sql;
    
    -- 2. 백업된 행 수 확인
    v_sql := 'SELECT COUNT(*) FROM ' || v_backup_table;
    EXECUTE IMMEDIATE v_sql INTO v_count;
    
    DBMS_OUTPUT.PUT_LINE('백업 완료: ' || v_backup_table);
    DBMS_OUTPUT.PUT_LINE('백업된 행 수: ' || v_count);
    
    -- 3. 백업 로그 기록
    INSERT INTO backup_log (log_id, original_table, backup_table, 
                            row_count, backup_date)
    VALUES (backup_log_seq.NEXTVAL, p_table_name, v_backup_table,
            v_count, SYSDATE);
    
    COMMIT;
    
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('백업 실패: ' || SQLERRM);
        RAISE;
END;
/

-- 사용 예시
BEGIN
    dynamic_table_backup('EMPLOYEES', 'department_id = 10');
END;
/

패턴 3: 커서를 활용한 복잡한 데이터 처리

CREATE OR REPLACE PROCEDURE process_monthly_sales
(
    p_year IN NUMBER,
    p_month IN NUMBER
)
IS
    -- 커서 선언
    CURSOR c_sales IS
        SELECT customer_id, SUM(total_amount) as monthly_total
        FROM orders
        WHERE EXTRACT(YEAR FROM order_date) = p_year
          AND EXTRACT(MONTH FROM order_date) = p_month
        GROUP BY customer_id
        HAVING SUM(total_amount) > 0
        ORDER BY monthly_total DESC;
    
    v_rank NUMBER := 0;
    v_bonus_rate NUMBER;
    v_bonus_amount NUMBER;
    v_total_processed NUMBER := 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE('=== ' || p_year || '년 ' || p_month || '월 매출 처리 시작 ===');
    
    -- 커서 FOR LOOP (자동으로 OPEN, FETCH, CLOSE)
    FOR rec IN c_sales LOOP
        v_rank := v_rank + 1;
        
        -- 순위에 따른 보너스율 결정
        v_bonus_rate := CASE
            WHEN v_rank <= 10 THEN 0.05    -- 상위 10명: 5%
            WHEN v_rank <= 50 THEN 0.03    -- 상위 50명: 3%
            WHEN v_rank <= 100 THEN 0.01   -- 상위 100명: 1%
            ELSE 0
        END;
        
        v_bonus_amount := rec.monthly_total * v_bonus_rate;
        
        -- 보너스 포인트 지급
        IF v_bonus_amount > 0 THEN
            UPDATE customers
            SET point_balance = point_balance + v_bonus_amount,
                last_bonus_date = SYSDATE
            WHERE customer_id = rec.customer_id;
            
            -- 보너스 지급 이력 기록
            INSERT INTO bonus_history (bonus_id, customer_id, bonus_amount,
                                       bonus_type, bonus_date)
            VALUES (bonus_seq.NEXTVAL, rec.customer_id, v_bonus_amount,
                    'MONTHLY_SALES', SYSDATE);
            
            v_total_processed := v_total_processed + 1;
        END IF;
        
        -- 100명마다 중간 커밋 (대량 처리 시 성능 향상)
        IF MOD(v_rank, 100) = 0 THEN
            COMMIT;
            DBMS_OUTPUT.PUT_LINE(v_rank || '명 처리 완료...');
        END IF;
    END LOOP;
    
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE('=== 처리 완료 ===');
    DBMS_OUTPUT.PUT_LINE('총 처리 고객 수: ' || v_total_processed);
    
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('처리 실패: ' || SQLERRM);
        RAISE;
END;
/

패턴 4: BULK COLLECT로 성능 최적화

CREATE OR REPLACE PROCEDURE bulk_process_orders
(
    p_status IN VARCHAR2,
    p_batch_size IN NUMBER DEFAULT 1000
)
IS
    TYPE t_order_ids IS TABLE OF orders.order_id%TYPE;
    TYPE t_customer_ids IS TABLE OF orders.customer_id%TYPE;
    TYPE t_amounts IS TABLE OF orders.total_amount%TYPE;
    
    v_order_ids t_order_ids;
    v_customer_ids t_customer_ids;
    v_amounts t_amounts;
    
    CURSOR c_orders IS
        SELECT order_id, customer_id, total_amount
        FROM orders
        WHERE status = p_status;
    
    v_total_processed NUMBER := 0;
BEGIN
    OPEN c_orders;
    
    LOOP
        -- 한 번에 p_batch_size만큼 가져오기
        FETCH c_orders BULK COLLECT INTO 
            v_order_ids, v_customer_ids, v_amounts
        LIMIT p_batch_size;
        
        EXIT WHEN v_order_ids.COUNT = 0;
        
        -- FORALL로 일괄 처리 (개별 UPDATE보다 훨씬 빠름)
        FORALL i IN 1..v_order_ids.COUNT
            UPDATE orders
            SET status = 'PROCESSED',
                processed_date = SYSDATE
            WHERE order_id = v_order_ids(i);
        
        -- 고객 포인트 일괄 업데이트
        FORALL i IN 1..v_customer_ids.COUNT
            UPDATE customers
            SET point_balance = point_balance + (v_amounts(i) * 0.01)
            WHERE customer_id = v_customer_ids(i);
        
        v_total_processed := v_total_processed + v_order_ids.COUNT;
        
        COMMIT;  -- 배치마다 커밋
        
        DBMS_OUTPUT.PUT_LINE(v_total_processed || '건 처리 완료...');
    END LOOP;
    
    CLOSE c_orders;
    
    DBMS_OUTPUT.PUT_LINE('=== 전체 처리 완료: ' || v_total_processed || '건 ===');
    
EXCEPTION
    WHEN OTHERS THEN
        IF c_orders%ISOPEN THEN
            CLOSE c_orders;
        END IF;
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('처리 실패: ' || SQLERRM);
        RAISE;
END;
/

패턴 5: 에러 핸들링과 로깅 전략

-- 에러 로그 테이블
CREATE TABLE error_logs (
    error_id NUMBER PRIMARY KEY,
    procedure_name VARCHAR2(100),
    error_code NUMBER,
    error_message VARCHAR2(4000),
    error_backtrace CLOB,
    error_date TIMESTAMP,
    additional_info VARCHAR2(4000)
);

CREATE SEQUENCE error_logs_seq;

-- 에러 로깅 프로시저
CREATE OR REPLACE PROCEDURE log_error
(
    p_procedure_name IN VARCHAR2,
    p_additional_info IN VARCHAR2 DEFAULT NULL
)
IS
    PRAGMA AUTONOMOUS_TRANSACTION;  -- 독립 트랜잭션
BEGIN
    INSERT INTO error_logs (
        error_id, procedure_name, error_code, error_message,
        error_backtrace, error_date, additional_info
    ) VALUES (
        error_logs_seq.NEXTVAL,
        p_procedure_name,
        SQLCODE,
        SQLERRM,
        DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,
        SYSTIMESTAMP,
        p_additional_info
    );
    
    COMMIT;  -- 자율 트랜잭션이므로 가능
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        -- 로깅 실패는 조용히 무시 (무한 루프 방지)
        NULL;
END;
/

-- 에러 핸들링을 적용한 프로시저
CREATE OR REPLACE PROCEDURE safe_update_employee
(
    p_emp_id IN NUMBER,
    p_new_salary IN NUMBER
)
IS
    v_old_salary NUMBER;
    v_dept_id NUMBER;
BEGIN
    -- 1. 현재 정보 조회
    SELECT salary, department_id
    INTO v_old_salary, v_dept_id
    FROM employees
    WHERE employee_id = p_emp_id;
    
    -- 2. 비즈니스 규칙 검증
    IF p_new_salary < v_old_salary * 0.5 THEN
        RAISE_APPLICATION_ERROR(-20100, '급여를 50% 이상 삭감할 수 없습니다.');
    END IF;
    
    IF p_new_salary > v_old_salary * 2 THEN
        RAISE_APPLICATION_ERROR(-20101, '급여를 2배 이상 인상할 수 없습니다.');
    END IF;
    
    -- 3. 업데이트 수행
    UPDATE employees
    SET salary = p_new_salary,
        last_update_date = SYSDATE
    WHERE employee_id = p_emp_id;
    
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE('급여 업데이트 성공: ' || v_old_salary || ' → ' || p_new_salary);
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        log_error('safe_update_employee', 
                  '존재하지 않는 직원 ID: ' || p_emp_id);
        DBMS_OUTPUT.PUT_LINE('오류: 해당 직원이 존재하지 않습니다.');
        
    WHEN OTHERS THEN
        ROLLBACK;
        log_error('safe_update_employee',
                  'Employee ID: ' || p_emp_id || ', New Salary: ' || p_new_salary);
        DBMS_OUTPUT.PUT_LINE('오류 발생: ' || SQLERRM);
        RAISE;  -- 상위로 예외 전파
END;
/

🛠️ 실전 프로젝트: 쇼핑몰 시스템 구축

1단계: 테이블 구조 설계

-- 고객 테이블
CREATE TABLE customers (
    customer_id NUMBER PRIMARY KEY,
    customer_name VARCHAR2(100) NOT NULL,
    email VARCHAR2(200) UNIQUE,
    phone VARCHAR2(20),
    point_balance NUMBER DEFAULT 0,
    customer_grade VARCHAR2(20) DEFAULT 'BRONZE',
    total_purchase_amount NUMBER DEFAULT 0,
    total_order_count NUMBER DEFAULT 0,
    created_date DATE DEFAULT SYSDATE,
    last_order_date DATE
);

-- 상품 테이블
CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    product_name VARCHAR2(200) NOT NULL,
    price NUMBER NOT NULL,
    stock_quantity NUMBER DEFAULT 0,
    category VARCHAR2(50),
    is_active VARCHAR2(1) DEFAULT 'Y',
    created_date DATE DEFAULT SYSDATE
);

-- 주문 테이블
CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER REFERENCES customers(customer_id),
    product_id NUMBER REFERENCES products(product_id),
    quantity NUMBER NOT NULL,
    total_amount NUMBER NOT NULL,
    used_points NUMBER DEFAULT 0,
    final_amount NUMBER,
    order_date DATE DEFAULT SYSDATE,
    status VARCHAR2(20) DEFAULT 'PENDING'
);

-- 주문 이력 테이블
CREATE TABLE order_history (
    history_id NUMBER PRIMARY KEY,
    order_id NUMBER REFERENCES orders(order_id),
    old_status VARCHAR2(20),
    new_status VARCHAR2(20),
    changed_date TIMESTAMP DEFAULT SYSTIMESTAMP,
    changed_by VARCHAR2(100)
);

-- 시퀀스 생성
CREATE SEQUENCE customers_seq START WITH 1000;
CREATE SEQUENCE products_seq START WITH 2000;
CREATE SEQUENCE orders_seq START WITH 3000;
CREATE SEQUENCE order_history_seq START WITH 4000;

2단계: 핵심 프로시저 구현

CREATE OR REPLACE PROCEDURE create_order
(
    p_customer_id IN NUMBER,
    p_product_id IN NUMBER,
    p_quantity IN NUMBER,
    p_use_points IN NUMBER DEFAULT 0,
    p_order_id OUT NUMBER,
    p_result_code OUT NUMBER,
    p_result_msg OUT VARCHAR2
)
IS
    v_product_price NUMBER;
    v_stock_quantity NUMBER;
    v_customer_points NUMBER;
    v_total_amount NUMBER;
    v_final_amount NUMBER;
    v_earned_points NUMBER;
    
    e_insufficient_stock EXCEPTION;
    e_insufficient_points EXCEPTION;
    e_invalid_quantity EXCEPTION;
BEGIN
    -- 초기화
    p_result_code := 0;
    
    -- 1. 입력값 검증
    IF p_quantity <= 0 THEN
        RAISE e_invalid_quantity;
    END IF;
    
    -- 2. 상품 정보 조회 (재고 잠금)
    SELECT price, stock_quantity
    INTO v_product_price, v_stock_quantity
    FROM products
    WHERE product_id = p_product_id
      AND is_active = 'Y'
    FOR UPDATE;
    
    -- 3. 재고 확인
    IF v_stock_quantity < p_quantity THEN
        RAISE e_insufficient_stock;
    END IF;
    
    -- 4. 고객 포인트 조회
    SELECT point_balance
    INTO v_customer_points
    FROM customers
    WHERE customer_id = p_customer_id
    FOR UPDATE;
    
    -- 5. 포인트 사용 가능 여부 확인
    IF p_use_points > v_customer_points THEN
        RAISE e_insufficient_points;
    END IF;
    
    -- 6. 금액 계산
    v_total_amount := v_product_price * p_quantity;
    v_final_amount := v_total_amount - p_use_points;
    
    IF v_final_amount < 0 THEN
        v_final_amount := 0;
    END IF;
    
    -- 7. 적립 포인트 계산 (최종 결제금액의 1%)
    v_earned_points := TRUNC(v_final_amount * 0.01);
    
    -- 8. 주문 생성
    INSERT INTO orders (
        order_id, customer_id, product_id, quantity,
        total_amount, used_points, final_amount,
        order_date, status
    ) VALUES (
        orders_seq.NEXTVAL, p_customer_id, p_product_id, p_quantity,
        v_total_amount, p_use_points, v_final_amount,
        SYSDATE, 'COMPLETED'
    ) RETURNING order_id INTO p_order_id;
    
    -- 9. 재고 차감
    UPDATE products
    SET stock_quantity = stock_quantity - p_quantity
    WHERE product_id = p_product_id;
    
    -- 10. 고객 정보 업데이트
    UPDATE customers
    SET point_balance = point_balance - p_use_points + v_earned_points,
        total_purchase_amount = total_purchase_amount + v_final_amount,
        total_order_count = total_order_count + 1,
        last_order_date = SYSDATE,
        customer_grade = CASE
            WHEN total_purchase_amount + v_final_amount >= 10000000 THEN 'VIP'
            WHEN total_purchase_amount + v_final_amount >= 5000000 THEN 'GOLD'
            WHEN total_purchase_amount + v_final_amount >= 1000000 THEN 'SILVER'
            ELSE 'BRONZE'
        END
    WHERE customer_id = p_customer_id;
    
    COMMIT;
    
    p_result_msg := '주문 완료! 주문번호: ' || p_order_id || 
                    ', 사용 포인트: ' || p_use_points ||
                    ', 적립 포인트: ' || v_earned_points;
    
EXCEPTION
    WHEN e_invalid_quantity THEN
        ROLLBACK;
        p_result_code := -1;
        p_result_msg := '주문 수량은 1개 이상이어야 합니다.';
        
    WHEN e_insufficient_stock THEN
        ROLLBACK;
        p_result_code := -2;
        p_result_msg := '재고 부족! 현재 재고: ' || v_stock_quantity || '개';
        
    WHEN e_insufficient_points THEN
        ROLLBACK;
        p_result_code := -3;
        p_result_msg := '포인트 부족! 보유 포인트: ' || v_customer_points;
        
    WHEN NO_DATA_FOUND THEN
        ROLLBACK;
        p_result_code := -4;
        p_result_msg := '존재하지 않는 상품이거나 고객입니다.';
        
    WHEN OTHERS THEN
        ROLLBACK;
        p_result_code := -99;
        p_result_msg := '주문 처리 실패: ' || SQLERRM;
        log_error('create_order', 
                  'Customer: ' || p_customer_id || ', Product: ' || p_product_id);
END;
/

3단계: 트리거 구현

CREATE OR REPLACE TRIGGER trg_order_status_history
AFTER UPDATE OF status ON orders
FOR EACH ROW
WHEN (OLD.status != NEW.status)
BEGIN
    INSERT INTO order_history (
        history_id, order_id, old_status, new_status,
        changed_date, changed_by
    ) VALUES (
        order_history_seq.NEXTVAL,
        :NEW.order_id,
        :OLD.status,
        :NEW.status,
        SYSTIMESTAMP,
        USER
    );
END;
/

CREATE OR REPLACE TRIGGER trg_low_stock_alert
AFTER UPDATE OF stock_quantity ON products
FOR EACH ROW
WHEN (NEW.stock_quantity < 10 AND OLD.stock_quantity >= 10)
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    -- 재고 부족 알림 로그 기록
    INSERT INTO stock_alerts (
        alert_id, product_id, product_name,
        current_stock, alert_date
    ) VALUES (
        stock_alerts_seq.NEXTVAL,
        :NEW.product_id,
        :NEW.product_name,
        :NEW.stock_quantity,
        SYSDATE
    );
    
    COMMIT;
    
    -- 실제로는 여기서 관리자에게 이메일이나 SMS 발송
    DBMS_OUTPUT.PUT_LINE('⚠️ 재고 부족 알림: ' || :NEW.product_name || 
                         ' (재고: ' || :NEW.stock_quantity || '개)');
END;
/

4단계: 테스트 및 실행

-- 테스트 데이터 입력
INSERT INTO customers (customer_id, customer_name, email, phone)
VALUES (customers_seq.NEXTVAL, '홍길동', 'hong@example.com', '010-1234-5678');

INSERT INTO products (product_id, product_name, price, stock_quantity, category)
VALUES (products_seq.NEXTVAL, '노트북', 1500000, 50, '전자제품');

INSERT INTO products (product_id, product_name, price, stock_quantity, category)
VALUES (products_seq.NEXTVAL, '마우스', 30000, 8, '전자제품');

COMMIT;

-- 주문 테스트
DECLARE
    v_order_id NUMBER;
    v_result_code NUMBER;
    v_result_msg VARCHAR2(1000);
BEGIN
    create_order(
        p_customer_id => 1000,
        p_product_id => 2000,
        p_quantity => 2,
        p_use_points => 0,
        p_order_id => v_order_id,
        p_result_code => v_result_code,
        p_result_msg => v_result_msg
    );
    
    DBMS_OUTPUT.PUT_LINE('결과 코드: ' || v_result_code);
    DBMS_OUTPUT.PUT_LINE('결과 메시지: ' || v_result_msg);
END;
/

-- 고객 정보 확인
SELECT customer_name, point_balance, customer_grade, 
       total_purchase_amount, total_order_count
FROM customers
WHERE customer_id = 1000;

-- 재고 확인
SELECT product_name, stock_quantity
FROM products
WHERE product_id = 2000;

📊 성능 최적화와 모니터링

프로시저 성능 분석

-- 1. 실행 계획 확인
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 2. 프로시저 실행 통계 확인
SELECT name, type, executions, 
       ROUND(elapsed_time/1000000, 2) as elapsed_sec,
       ROUND(cpu_time/1000000, 2) as cpu_sec
FROM v$sqlarea
WHERE sql_text LIKE '%process_order%'
ORDER BY elapsed_time DESC;

-- 3. 프로파일링 (DBMS_PROFILER 사용)
BEGIN
    DBMS_PROFILER.START_PROFILER('order_process_profile');
    
    -- 테스트할 프로시저 실행
    FOR i IN 1..100 LOOP
        process_order(1001, 2001, 5, NULL, NULL);
    END LOOP;
    
    DBMS_PROFILER.STOP_PROFILER;
END;
/

-- 프로파일링 결과 조회
SELECT u.unit_name, d.line#, d.total_time,
       s.text
FROM dbms_profiler_units u
JOIN dbms_profiler_data d ON u.runid = d.runid 
                          AND u.unit_number = d.unit_number
JOIN user_source s ON u.unit_name = s.name 
                  AND d.line# = s.line
WHERE u.runid = (SELECT MAX(runid) FROM dbms_profiler_runs)
ORDER BY d.total_time DESC;

성능 최적화 팁

모니터링 대시보드 구축

-- 프로시저 실행 통계 뷰
CREATE OR REPLACE VIEW v_procedure_stats AS
SELECT 
    object_name,
    object_type,
    status,
    created,
    last_ddl_time
FROM user_objects
WHERE object_type IN ('PROCEDURE', 'FUNCTION', 'TRIGGER')
ORDER BY last_ddl_time DESC;

-- 에러 발생 통계 뷰
CREATE OR REPLACE VIEW v_error_summary AS
SELECT 
    procedure_name,
    error_code,
    COUNT(*) as error_count,
    MAX(error_date) as last_error_date
FROM error_logs
WHERE error_date >= SYSDATE - 7  -- 최근 7일
GROUP BY procedure_name, error_code
ORDER BY error_count DESC;

-- 성능 문제 프로시저 조회
SELECT * FROM v_procedure_stats WHERE status = 'INVALID';
SELECT * FROM v_error_summary WHERE error_count > 10;

🎓 실무 베스트 프랙티스

코딩 컨벤션

주석 템플릿 예시

/*******************************************************************************
* 프로시저명: process_monthly_salary
* 설명: 월별 급여를 계산하고 지급 처리를 수행합니다.
* 
* 파라미터:
*   p_year_month (IN)  : 처리할 년월 (YYYYMM 형식)
*   p_dept_id (IN)     : 부서 ID (NULL이면 전체 부서)
*   p_result_count (OUT): 처리된 직원 수
*   p_result_msg (OUT) : 처리 결과 메시지
*
* 작성자: 홍길동
* 작성일: 2024-01-15
* 수정이력:
*   2024-01-20 김철수 - 보너스 계산 로직 추가
*   2024-02-01 이영희 - 성능 최적화 (BULK COLLECT 적용)
*
* 사용예시:
*   DECLARE
*       v_count NUMBER;
*       v_msg VARCHAR2(1000);
*   BEGIN
*       process_monthly_salary('202401', NULL, v_count, v_msg);
*       DBMS_OUTPUT.PUT_LINE(v_msg);
*   END;
*******************************************************************************/
CREATE OR REPLACE PROCEDURE process_monthly_salary
(
    p_year_month IN VARCHAR2,
    p_dept_id IN NUMBER DEFAULT NULL,
    p_result_count OUT NUMBER,
    p_result_msg OUT VARCHAR2
)
IS
    -- 변수 선언
    v_base_date DATE;
    v_total_amount NUMBER := 0;
    
BEGIN
    -- 비즈니스 로직...
END process_monthly_salary;
/

버전 관리와 배포

🎯 마무리하며

마지막 조언