[DB] Oracle에서 Procedure 작성 규칙과 실무 활용법

Oracle Procedure 작성 규칙과 실무 활용법

Oracle PL/SQL Procedure는 반복적인 데이터베이스 작업을 자동화하고 복잡한 비즈니스 로직을 구현하는 강력한 도구입니다. 한 번 작성하여 컴파일하면 데이터베이스에 저장되어, 필요할 때마다 빠르고 효율적으로 실행할 수 있습니다.

실무에서 Procedure를 제대로 작성하지 못하면 성능 문제, 유지보수 어려움, 예상치 못한 에러가 발생할 수 있습니다. 특히 대용량 데이터를 처리하거나 트랜잭션이 복잡한 경우, 올바른 작성 규칙과 최적화 기법을 적용하는 것이 매우 중요합니다.

이 글에서는 Oracle Procedure의 기본 문법부터 매개변수 사용법, 예외 처리, 성능 최적화, 디버깅 기법까지 실무에서 바로 적용할 수 있는 모든 내용을 다룹니다. 초보 DBA부터 경험 많은 개발자까지 모두에게 유용한 가이드가 될 것입니다.

목차

1. Oracle Procedure 개념과 장점

2. Procedure 기본 문법과 구조

3. 매개변수와 예외 처리

4. 성능 최적화와 코딩 표준

5. 자주 묻는 질문 (FAQ)

---

#1. Oracle Procedure 개념과 장점

Oracle Procedure는 특정 작업을 수행하기 위해 이름이 부여된 PL/SQL 블록입니다. 한 번 작성하고 컴파일하면 데이터베이스 딕셔너리에 저장되어 필요할 때마다 호출할 수 있습니다.

1) Procedure의 정의

Procedure는 저장 프로시저(Stored Procedure)라고도 불리며, 데이터베이스 서버에 저장되는 실행 가능한 코드입니다. 일반 SQL 문과 달리 조건문, 반복문, 예외 처리 등 프로그래밍 언어의 기능을 사용할 수 있습니다.

(1) Procedure vs Function

구분	Procedure	Function
반환값	반환하지 않거나 OUT 매개변수로 여러 값 반환	반드시 하나의 값을 RETURN
호출 방식	독립적으로 EXECUTE 또는 CALL로 실행	SELECT 문이나 표현식 내에서 사용 가능
DML 사용	INSERT, UPDATE, DELETE 자유롭게 사용	제한적 (READ ONLY 권장)
트랜잭션 제어	COMMIT, ROLLBACK 가능	일반적으로 사용 불가

. . . . .

2) Procedure 사용의 주요 장점

(1) 성능 향상

① 사전 컴파일: Procedure는 생성 시 한 번 컴파일되어 실행 계획이 캐싱됩니다. 매번 SQL 문을 파싱하고 최적화하는 비용이 절약됩니다.
② 네트워크 트래픽 감소: 여러 SQL 문을 하나의 Procedure 호출로 실행하므로, 클라이언트와 서버 간 통신 횟수가 줄어듭니다.
③ 서버 측 처리: 데이터베이스 서버에서 직접 로직을 실행하므로 대량의 데이터를 클라이언트로 전송할 필요가 없습니다.

(2) 코드 재사용성과 모듈화

① 중앙 집중식 관리: 동일한 로직을 여러 애플리케이션에서 공유할 수 있어 코드 중복을 방지합니다.
② 모듈식 설계: 복잡한 작업을 작은 단위의 Procedure로 분할하여 관리가 용이합니다.
③ 유지보수 효율: 로직 변경 시 Procedure만 수정하면 되므로 애플리케이션 재배포가 불필요합니다.

(3) 보안 강화

① 접근 제어: 테이블에 직접 접근 권한 없이 Procedure 실행 권한만 부여할 수 있습니다.
② SQL 인젝션 방지: 매개변수를 통한 입력으로 악의적인 SQL 삽입을 차단합니다.
③ 비즈니스 로직 은닉: 테이블 구조를 직접 노출하지 않고 Procedure를 통해서만 접근하도록 제한할 수 있습니다.

(4) 트랜잭션 관리

여러 DML 작업을 하나의 논리적 단위로 묶어 원자성(Atomicity)을 보장할 수 있습니다. 모든 작업이 성공하면 COMMIT, 하나라도 실패하면 ROLLBACK하여 데이터 일관성을 유지합니다.

---

#2. Procedure 기본 문법과 구조

Oracle Procedure는 명확한 구조를 가지고 있으며, 각 구성 요소의 역할을 이해하는 것이 중요합니다.

1) 기본 구문 형식

CREATE [OR REPLACE] PROCEDURE 프로시저_이름
(
    매개변수1 [IN | OUT | IN OUT] 데이터타입 [DEFAULT 기본값],
    매개변수2 [IN | OUT | IN OUT] 데이터타입,
    ...
)
[AUTHID {DEFINER | CURRENT_USER}]
[ACCESSIBLE BY (프로그램_단위_목록)]
IS | AS
    -- 선언부: 지역 변수, 커서, 예외 등 선언
    v_변수명 데이터타입;
BEGIN
    -- 실행부: 실제 처리 로직
    SQL 문 및 PL/SQL 블록;
[EXCEPTION
    -- 예외 처리부
    WHEN 예외명 THEN
        예외 처리 코드;]
END [프로시저_이름];
/

. . . . .

2) 주요 구성 요소 설명

(1) CREATE OR REPLACE

CREATE OR REPLACE는 Procedure가 이미 존재하면 덮어쓰고, 없으면 새로 생성합니다. 개발 단계에서는 OR REPLACE를 사용하여 반복 수정이 용이하지만, 운영 환경에서는 신중하게 사용해야 합니다.

OR REPLACE 없이 CREATE만 사용하면 동일한 이름의 Procedure가 이미 존재할 때 ORA-00955: name is already used by an existing object 에러가 발생합니다.

(2) IS vs AS

IS와 AS는 기능적으로 동일하며, 둘 중 어느 것을 사용해도 상관없습니다. 일반적으로 독립 실행형 Procedure에서는 IS를, Package 내부 Procedure에서는 AS를 사용하는 관례가 있지만 필수는 아닙니다.

(3) AUTHID 절

Procedure의 실행 권한을 지정합니다.

옵션	설명	사용 시나리오
DEFINER (기본값)	Procedure 생성자의 권한으로 실행	호출자에게 직접 테이블 권한을 주지 않고 작업 수행
CURRENT_USER	현재 사용자(호출자)의 권한으로 실행	호출자별로 다른 데이터에 접근해야 할 때

(4) 기본 예제

-- 간단한 Procedure 예제
CREATE OR REPLACE PROCEDURE update_employee_salary(
    p_emp_id IN NUMBER,
    p_percentage IN NUMBER DEFAULT 10
)
IS
    v_current_salary NUMBER;
BEGIN
    -- 현재 급여 조회
    SELECT salary INTO v_current_salary
    FROM employees
    WHERE employee_id = p_emp_id;

    -- 급여 인상
    UPDATE employees
    SET salary = salary * (1 + p_percentage/100)
    WHERE employee_id = p_emp_id;

    COMMIT;

    DBMS_OUTPUT.PUT_LINE('급여 인상 완료: ' || v_current_salary ||
        ' → ' || (v_current_salary * (1 + p_percentage/100)));
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('직원 ID ' || p_emp_id || '을(를) 찾을 수 없습니다.');
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('에러 발생: ' || SQLERRM);
END update_employee_salary;
/

-- Procedure 실행
EXEC update_employee_salary(100, 15);
-- 또는
BEGIN
    update_employee_salary(p_emp_id => 100, p_percentage => 15);
END;
/

---

#3. 매개변수와 예외 처리

매개변수의 올바른 사용과 철저한 예외 처리는 안정적인 Procedure 작성의 핵심입니다.

1) 매개변수 모드

Oracle Procedure는 세 가지 매개변수 모드를 지원합니다. 각 모드의 특성을 정확히 이해하고 적절히 사용해야 합니다.

(1) IN 매개변수 (입력 전용)

IN 매개변수는 Procedure에 값을 전달하는 용도로만 사용됩니다. 가장 일반적으로 사용되며, 명시하지 않으면 기본값은 IN입니다.

특징:
① Procedure 내에서 값을 변경할 수 없습니다 (읽기 전용)
② 기본값(DEFAULT) 설정이 가능합니다
③ 리터럴, 상수, 변수 모두 전달 가능합니다
④ 값이 복사되어 전달됩니다 (Call by Value)

CREATE OR REPLACE PROCEDURE calculate_bonus(
    p_emp_id IN NUMBER,
    p_bonus_rate IN NUMBER DEFAULT 0.1
)
IS
    v_salary NUMBER;
    v_bonus NUMBER;
BEGIN
    SELECT salary INTO v_salary
    FROM employees
    WHERE employee_id = p_emp_id;

    v_bonus := v_salary * p_bonus_rate;
    -- p_bonus_rate := 0.2; 에러! IN 매개변수는 수정 불가

    DBMS_OUTPUT.PUT_LINE('보너스 금액: ' || v_bonus);
END;
/

(2) OUT 매개변수 (출력 전용)

OUT 매개변수는 Procedure에서 호출자에게 값을 반환하는 용도입니다. 여러 값을 반환해야 할 때 유용합니다.

특징:
① Procedure 시작 시 값이 NULL로 초기화됩니다
② Procedure 내에서 반드시 값을 할당해야 합니다
③ 호출 시 반드시 변수를 전달해야 합니다 (리터럴 불가)
④ Procedure 종료 시 값이 호출자의 변수에 복사됩니다

CREATE OR REPLACE PROCEDURE get_employee_info(
    p_emp_id IN NUMBER,
    p_name OUT VARCHAR2,
    p_salary OUT NUMBER,
    p_dept_name OUT VARCHAR2
)
IS
BEGIN
    SELECT
        e.first_name || ' ' || e.last_name,
        e.salary,
        d.department_name
    INTO p_name, p_salary, p_dept_name
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    WHERE e.employee_id = p_emp_id;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_name := NULL;
        p_salary := NULL;
        p_dept_name := NULL;
END;
/

-- 사용 예제
DECLARE
    v_name VARCHAR2(100);
    v_salary NUMBER;
    v_dept VARCHAR2(50);
BEGIN
    get_employee_info(100, v_name, v_salary, v_dept);
    DBMS_OUTPUT.PUT_LINE('이름: ' || v_name);
    DBMS_OUTPUT.PUT_LINE('급여: ' || v_salary);
    DBMS_OUTPUT.PUT_LINE('부서: ' || v_dept);
END;
/

(3) IN OUT 매개변수 (입출력)

IN OUT 매개변수는 값을 받아서 수정한 후 다시 반환합니다. 입력과 출력을 동시에 처리할 때 사용합니다.

CREATE OR REPLACE PROCEDURE format_phone_number(
    p_phone IN OUT VARCHAR2
)
IS
BEGIN
    -- 숫자만 추출
    p_phone := REGEXP_REPLACE(p_phone, '[^0-9]', '');

    -- 형식 지정 (예: 010-1234-5678)
    IF LENGTH(p_phone) = 11 THEN
        p_phone := SUBSTR(p_phone, 1, 3) || '-' ||
                   SUBSTR(p_phone, 4, 4) || '-' ||
                   SUBSTR(p_phone, 8);
    ELSIF LENGTH(p_phone) = 10 THEN
        p_phone := SUBSTR(p_phone, 1, 3) || '-' ||
                   SUBSTR(p_phone, 4, 3) || '-' ||
                   SUBSTR(p_phone, 7);
    END IF;
END;
/

-- 사용 예제
DECLARE
    v_phone VARCHAR2(20) := '01012345678';
BEGIN
    DBMS_OUTPUT.PUT_LINE('변환 전: ' || v_phone);
    format_phone_number(v_phone);
    DBMS_OUTPUT.PUT_LINE('변환 후: ' || v_phone);
END;
/

. . . . .

2) 예외 처리 (Exception Handling)

철저한 예외 처리는 안정적이고 신뢰할 수 있는 Procedure 작성의 핵심입니다. 예상치 못한 오류로 인한 데이터 손상을 방지하고, 명확한 오류 메시지를 제공합니다.

(1) 예외 처리 종류

예외 유형	설명	예시
사전 정의 예외 (Predefined)	Oracle이 미리 정의한 일반적인 예외	NO_DATA_FOUND, TOO_MANY_ROWS, DUP_VAL_ON_INDEX
비사전 정의 예외 (Non-Predefined)	Oracle 오류 코드는 있지만 이름이 없는 예외	PRAGMA EXCEPTION_INIT으로 연결
사용자 정의 예외 (User-Defined)	개발자가 직접 정의하는 예외	비즈니스 규칙 위반 시 RAISE

(2) 종합 예외 처리 예제

CREATE OR REPLACE PROCEDURE transfer_money(
    p_from_account IN NUMBER,
    p_to_account IN NUMBER,
    p_amount IN NUMBER
)
IS
    -- 사용자 정의 예외 선언
    insufficient_funds EXCEPTION;
    invalid_amount EXCEPTION;
    same_account EXCEPTION;

    v_from_balance NUMBER;
BEGIN
    -- 입력 검증
    IF p_amount <= 0 THEN
        RAISE invalid_amount;
    END IF;

    IF p_from_account = p_to_account THEN
        RAISE same_account;
    END IF;

    -- 출금 계좌 잔액 확인 (FOR UPDATE로 락 획득)
    SELECT balance INTO v_from_balance
    FROM accounts
    WHERE account_id = p_from_account
    FOR UPDATE;

    IF v_from_balance < p_amount THEN
        RAISE insufficient_funds;
    END IF;

    -- 출금
    UPDATE accounts
    SET balance = balance - p_amount
    WHERE account_id = p_from_account;

    -- 입금
    UPDATE accounts
    SET balance = balance + p_amount
    WHERE account_id = p_to_account;

    -- 거래 기록
    INSERT INTO transaction_log(from_acc, to_acc, amount, trans_date)
    VALUES(p_from_account, p_to_account, p_amount, SYSDATE);

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('이체 성공: ' || p_amount || '원');

EXCEPTION
    WHEN insufficient_funds THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20001,
            '잔액 부족: 현재 잔액 ' || v_from_balance || '원');

    WHEN invalid_amount THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20002, '이체 금액은 0보다 커야 합니다');

    WHEN same_account THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20003, '동일한 계좌로는 이체할 수 없습니다');

    WHEN NO_DATA_FOUND THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20004, '계좌를 찾을 수 없습니다');

    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20999,
            '시스템 오류: ' || SQLERRM);
END transfer_money;
/

---

#4. 성능 최적화와 코딩 표준

효율적인 Procedure는 성능 최적화 기법과 일관된 코딩 표준을 따라야 합니다.

1) 성능 최적화 기법

(1) 벌크 연산 (FORALL & BULK COLLECT)

대량의 데이터를 처리할 때 개별 처리 대신 벌크 연산을 사용하면 PL/SQL과 SQL 엔진 간 컨텍스트 스위칭을 크게 줄일 수 있습니다.

-- 비효율적인 방법 (Row-by-Row)
CREATE OR REPLACE PROCEDURE update_salaries_slow(
    p_dept_id IN NUMBER
)
IS
BEGIN
    FOR rec IN (SELECT employee_id FROM employees
                WHERE department_id = p_dept_id) LOOP
        UPDATE employees
        SET salary = salary * 1.1
        WHERE employee_id = rec.employee_id;
    END LOOP;
    COMMIT;
END;
/

-- 효율적인 방법 (Bulk Operation)
CREATE OR REPLACE PROCEDURE update_salaries_fast(
    p_dept_id IN NUMBER
)
IS
    TYPE emp_id_table IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    v_emp_ids emp_id_table;
BEGIN
    -- BULK COLLECT로 데이터 한번에 가져오기
    SELECT employee_id
    BULK COLLECT INTO v_emp_ids
    FROM employees
    WHERE department_id = p_dept_id;

    -- FORALL로 한번에 업데이트
    FORALL i IN 1..v_emp_ids.COUNT
        UPDATE employees
        SET salary = salary * 1.1
        WHERE employee_id = v_emp_ids(i);

    COMMIT;
END;
/

벌크 연산은 수천 건 이상의 데이터 처리 시 10배 이상의 성능 향상을 가져올 수 있습니다.

(2) 바인드 변수 활용

동적 SQL을 사용할 때는 리터럴 대신 바인드 변수를 사용하여 하드 파싱을 방지해야 합니다.

-- 나쁜 예: 하드 파싱 발생
v_sql := 'SELECT * FROM employees WHERE department_id = ' || p_dept_id;
EXECUTE IMMEDIATE v_sql;

-- 좋은 예: 바인드 변수 사용
v_sql := 'SELECT * FROM employees WHERE department_id = :dept_id';
EXECUTE IMMEDIATE v_sql USING p_dept_id;

(3) 커서 최적화

① 암시적 커서 사용: 단순 조회는 SELECT INTO 사용
② 커서 FOR 루프: 자동으로 OPEN, FETCH, CLOSE 처리
③ LIMIT 절 활용: BULK COLLECT 시 메모리 관리

. . . . .

2) 코딩 표준 및 명명 규칙

(1) 명명 규칙

구분	접두사	예시
매개변수	p_	p_employee_id, p_salary
지역 변수	v_	v_count, v_total_amount
상수	c_	c_max_retry, c_tax_rate
커서	cur_	cur_employees, cur_orders
예외	e_	e_invalid_data, e_timeout

(2) 주석 작성 규칙

CREATE OR REPLACE PROCEDURE calculate_bonus(
    p_emp_id IN NUMBER,
    p_bonus_pct IN NUMBER,
    p_bonus_amt OUT NUMBER
)
IS
    v_salary NUMBER;
BEGIN
    /*******************************************
    * Procedure: calculate_bonus
    * 목적: 직원의 보너스 금액 계산
    * 작성자: 홍길동
    * 작성일: 2024-01-15
    * 수정내역:
    * 2024-02-10 - 최소 보너스 금액 기능 추가
    * 2024-03-05 - 성과급 비율 차등 적용
    *******************************************/

    -- 직원 급여 조회
    SELECT salary INTO v_salary
    FROM employees
    WHERE employee_id = p_emp_id;

    -- 보너스 계산 (최소 100,000원 보장)
    p_bonus_amt := GREATEST(v_salary * (p_bonus_pct / 100), 100000);
END;
/

---

#5. 자주 묻는 질문 (FAQ)

1) Q: Procedure 실행 시 "PLS-00201: identifier must be declared" 에러가 발생합니다

A: 이 에러는 참조하는 객체(테이블, 뷰, 다른 Procedure 등)를 찾을 수 없을 때 발생합니다. 주요 원인은 다음과 같습니다. ① 객체 이름 오타: 테이블이나 열 이름의 철자를 확인하세요. ② 스키마 권한 부족: 참조하는 객체에 대한 SELECT, INSERT 등의 권한이 있는지 확인하세요. ③ 스키마 명시 필요: 다른 스키마의 객체를 참조할 때는 스키마명을 명시해야 합니다 (예: HR.EMPLOYEES). 권한 확인은 "SELECT * FROM USER_TAB_PRIVS WHERE TABLE_NAME = '테이블명';" 쿼리로 가능합니다.

. . . . .

2) Q: Procedure 내에서 COMMIT을 사용해야 하나요, 아니면 호출하는 곳에서 해야 하나요?

A: 이는 상황에 따라 다릅니다. 자율 트랜잭션(Autonomous Transaction) Procedure라면 내부에서 COMMIT을 명시적으로 수행해야 합니다. 반면 일반 Procedure는 호출자가 트랜잭션을 관리하도록 하는 것이 더 유연합니다. 일반적인 가이드라인은 다음과 같습니다. ① 단일 작업 Procedure: 내부에서 COMMIT (예: 로그 기록, 독립적인 업데이트) ② 복합 작업의 일부: 호출자가 COMMIT (예: 여러 Procedure를 연속 호출하는 경우) ③ 배치 작업: 일정 개수마다 중간 COMMIT으로 롤백 세그먼트 관리. 명확한 정책이 없다면 Procedure 내부에서 COMMIT하지 않고 호출자에게 맡기는 것이 더 안전합니다.

. . . . .

3) Q: OUT 매개변수와 함수의 RETURN 중 어떤 것을 사용해야 하나요?

A: 반환해야 할 값의 개수와 용도에 따라 결정하세요. 하나의 값만 반환하고 SELECT 문에서 사용할 가능성이 있다면 함수를 사용하세요. 예를 들어 급여 계산, 문자열 변환, 날짜 포맷팅 등은 함수가 적합합니다. 반면 여러 값을 반환하거나 복잡한 DML 작업을 수행한다면 OUT 매개변수를 가진 Procedure를 사용하세요. 또한 트랜잭션 제어(COMMIT, ROLLBACK)가 필요한 경우에도 Procedure가 더 적합합니다. 함수는 주로 계산과 변환에 사용하고, Procedure는 데이터 처리와 비즈니스 로직 실행에 사용하는 것이 일반적입니다.

. . . . .

4) Q: AUTHID DEFINER와 CURRENT_USER의 실무적 차이는 무엇인가요?

A: 보안과 권한 관리 방식의 차이입니다. DEFINER (기본값)는 Procedure 생성자의 권한으로 실행되므로, 사용자에게 직접 테이블 권한을 주지 않고도 작업을 수행할 수 있습니다. 예를 들어 급여 테이블에 직접 접근 권한을 주지 않고, 급여 인상 Procedure 실행 권한만 부여하는 경우입니다. 이는 보안이 강화되지만, 모든 사용자가 동일한 권한으로 실행됩니다. 반면 CURRENT_USER는 호출자의 권한으로 실행되므로, 사용자별로 접근할 수 있는 데이터가 달라집니다. VPD(Virtual Private Database)나 멀티 테넌트 환경에서 유용합니다. 일반적으로 보안이 중요한 경우 DEFINER를, 사용자별 데이터 분리가 필요한 경우 CURRENT_USER를 선택합니다.

. . . . .

5) Q: Procedure 성능이 느립니다. 어떻게 개선해야 하나요?

A: 성능 문제는 여러 원인이 있을 수 있습니다. 먼저 실행 계획 분석부터 시작하세요. "SET SERVEROUTPUT ON; SET TIMING ON;" 명령으로 각 단계의 실행 시간을 측정하고, EXPLAIN PLAN이나 SQL Trace를 사용하여 병목 지점을 파악하세요. 주요 개선 방법은 다음과 같습니다. ① 인덱스 확인: WHERE 절의 열에 적절한 인덱스가 있는지 확인 ② 벌크 연산 사용: 루프 대신 FORALL과 BULK COLLECT 활용 ③ 불필요한 커밋 제거: 루프 내부의 COMMIT은 성능을 크게 저하시킵니다 ④ 통계 갱신: DBMS_STATS.GATHER_TABLE_STATS로 테이블 통계 갱신 ⑤ 파티셔닝 고려: 대용량 테이블은 파티셔닝으로 쿼리 성능 향상. AWR 리포트나 Statspack을 활용하면 더 정확한 성능 분석이 가능합니다.

. . . . .

6) Q: Procedure를 디버깅하는 가장 좋은 방법은 무엇인가요?

A: Oracle에는 여러 디버깅 방법이 있습니다. ① DBMS_OUTPUT.PUT_LINE: 가장 기본적인 방법으로 중요 지점에 로그를 출력합니다. "SET SERVEROUTPUT ON" 설정 필수입니다. ② 로그 테이블 활용: 독립적인 로그 테이블에 INSERT하여 실행 흐름과 변수 값을 추적합니다. 자율 트랜잭션(PRAGMA AUTONOMOUS_TRANSACTION)을 사용하면 롤백되어도 로그가 유지됩니다. ③ SQL Developer 디버거: 브레이크포인트 설정, 단계별 실행, 변수 감시 등 강력한 디버깅 기능을 제공합니다. ④ 예외 스택 추적: DBMS_UTILITY.FORMAT_ERROR_STACK과 DBMS_UTILITY.FORMAT_ERROR_BACKTRACE로 오류 발생 위치 파악. 실무에서는 로그 테이블 방식이 가장 효과적입니다.

. . . . .

7) Q: 동적 SQL을 사용할 때 주의사항은 무엇인가요?

A: 동적 SQL(EXECUTE IMMEDIATE)은 편리하지만 보안과 성능 측면에서 주의가 필요합니다. ① SQL 인젝션 방지: 사용자 입력을 직접 연결하지 말고 반드시 바인드 변수를 사용하세요. 나쁜 예: "v_sql := 'DELETE FROM emp WHERE id=' || p_id;", 좋은 예: "v_sql := 'DELETE FROM emp WHERE id=:id'; EXECUTE IMMEDIATE v_sql USING p_id;" ② 하드 파싱 최소화: 바인드 변수 사용으로 실행 계획 재사용 ③ 입력 검증: 테이블명이나 열명을 동적으로 받을 때는 화이트리스트 검증 필수 ④ 에러 처리 강화: 동적 SQL은 컴파일 시점에 오류를 잡을 수 없으므로 철저한 런타임 예외 처리가 필요합니다. 가능하면 정적 SQL을 사용하고, 불가피한 경우에만 동적 SQL을 사용하세요.

. . . . .

8) Q: Package와 독립 Procedure 중 어떤 것을 사용해야 하나요?

A: 규모와 관련성에 따라 선택하세요. Package는 관련된 Procedure, Function, 변수, 커서를 논리적으로 그룹화하는 컨테이너입니다. 장점은 ① 모듈화: 관련 기능을 한 곳에 모아 관리 ② 캡슐화: Private 멤버로 내부 구현 숨김 ③ 성능: Package가 최초 호출될 때 한 번만 로드되어 메모리에 상주 ④ 전역 변수: Package 수준의 변수를 여러 Procedure가 공유 가능. 반면 독립 Procedure는 단순하고 다른 것과 관련 없는 작업에 적합합니다. 일반적으로 3개 이상의 관련 Procedure가 있거나, 공통 변수/타입을 공유한다면 Package 사용을 권장합니다. 대규모 프로젝트에서는 기능 영역별로 Package를 구성하는 것이 표준입니다.

. . . . .

9) Q: Procedure에서 DDL 문을 실행할 수 있나요?

A: 네, EXECUTE IMMEDIATE를 사용하여 DDL 실행이 가능합니다. 하지만 주의사항이 있습니다. DDL 문은 암시적 COMMIT을 발생시키므로, 실행 전의 모든 DML 작업이 자동으로 커밋됩니다. 이는 트랜잭션 제어를 어렵게 만듭니다. 예를 들어: "EXECUTE IMMEDIATE 'CREATE TABLE temp_table (id NUMBER)';". 이 방식은 주로 ① 동적 파티션 생성 ② 임시 테이블 생성/삭제 ③ 인덱스 재구성 ④ 통계 수집 등의 관리 작업에 사용됩니다. 일반적인 비즈니스 로직 Procedure에서는 DDL 사용을 피하는 것이 좋습니다. DDL이 필요한 경우 별도의 관리용 Procedure로 분리하고, 충분한 권한 검증과 예외 처리를 구현하세요.

. . . . .

10) Q: Procedure 실행 권한을 다른 사용자에게 주려면 어떻게 해야 하나요?

A: GRANT 명령을 사용합니다. 기본 문법은 "GRANT EXECUTE ON 프로시저명 TO 사용자명;"입니다. 예를 들어 HR 스키마의 update_salary Procedure를 SCOTT 사용자에게 권한 부여하려면: "GRANT EXECUTE ON HR.update_salary TO SCOTT;". 여러 사용자에게 한 번에 권한을 주려면 역할(Role)을 활용하세요. ① 역할 생성: "CREATE ROLE emp_admin_role;" ② Procedure 권한 부여: "GRANT EXECUTE ON update_salary TO emp_admin_role;" ③ 사용자에게 역할 부여: "GRANT emp_admin_role TO scott, jones;". 권한 확인은 "SELECT * FROM USER_TAB_PRIVS WHERE TABLE_NAME = 'UPDATE_SALARY';"로 가능합니다. 권한 회수는 "REVOKE EXECUTE ON procedure_name FROM user_name;"을 사용합니다. PUBLIC에 권한을 주면 모든 사용자가 실행 가능하므로 보안에 주의하세요.

---

마무리

Oracle PL/SQL Procedure는 데이터베이스 개발에서 핵심적인 역할을 합니다. 올바른 작성 규칙과 최적화 기법을 적용하면 성능, 보안, 유지보수성이 뛰어난 시스템을 구축할 수 있습니다.

이 글에서 다룬 핵심 내용을 요약하면 다음과 같습니다.

① 명확한 구조 이해: 선언부, 실행부, 예외 처리부로 구성되며 각 부분의 역할을 명확히 이해해야 합니다.
② 매개변수 적절한 사용: IN, OUT, IN OUT 매개변수의 특성을 이해하고 상황에 맞게 선택합니다.
③ 철저한 예외 처리: 사전 정의 예외와 사용자 정의 예외를 적절히 활용하여 안정성을 높입니다.
④ 성능 최적화: 벌크 연산, 바인드 변수, 커서 최적화로 대용량 데이터 처리 성능을 향상시킵니다.
⑤ 일관된 코딩 표준: 명명 규칙, 주석, 들여쓰기 등 표준을 준수하여 가독성과 유지보수성을 높입니다.

특히 실무에서는 트랜잭션 관리, 보안, 성능이 가장 중요합니다. COMMIT/ROLLBACK을 적절히 배치하고, 권한을 최소화하며, 벌크 연산으로 성능을 최적화하세요.

Procedure 작성은 단순히 문법을 아는 것을 넘어서, 비즈니스 로직을 효율적으로 구현하는 예술입니다. 이 가이드를 참고하여 더욱 견고하고 효율적인 Oracle Procedure를 작성하시기 바랍니다.

긴 글 읽어주셔서 감사합니다.

끝.