[DB] Oracle에서 Procedure 작성 규칙

Oracle PL/SQL Procedure 작성 완벽 가이드

안녕하세요
이번 포스팅은 Oracle 데이터베이스 개발자라면 반드시 알아야 할 PL/SQL Procedure 작성법에 대해 상세히 알아보겠습니다. 초보자부터 전문가까지 모두가 참고할 수 있는 실용적인 내용으로 구성했습니다.

목차

1. Oracle Procedure란?
2. Procedure 기본 구문
3. 매개변수 유형과 사용법
4. 예외 처리
5. 성능 최적화 기법
6. 디버깅 및 유지보수 팁
7. 실전 예제
8. 주요 명명 규칙과 코딩 표준
9. 자주 묻는 질문

 

#1. Oracle Procedure란?

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

Procedure의 주요 이점:

• 코드 재사용성: 반복적인 작업을 위한 코드를 한 번만 작성
• 모듈식 프로그래밍: 큰 작업을 작은 논리적 단위로 분할
• 보안 강화: 특정 작업에 대한 접근 제어 가능
• 네트워크 트래픽 감소: 여러 SQL문을 서버에서 한 번에 처리
• 유지보수 용이성: 중앙 집중식 코드 관리

 

#2. Procedure 기본 구문

Oracle Procedure의 기본 구문은 다음과 같습니다:

CREATE [OR REPLACE] PROCEDURE 프로시저_이름     [(매개변수1 [IN | OUT | IN OUT] 데이터타입,       매개변수2 [IN | OUT | IN OUT] 데이터타입, ...)] [AUTHID {DEFINER | CURRENT_USER}] [ACCESSIBLE BY (프로그램_단위_목록)] [PARALLEL_ENABLE] IS | AS     -- 지역 변수 선언부 BEGIN     -- 실행 로직     [EXCEPTION         -- 예외 처리 로직] END [프로시저_이름];

주요 구성요소 설명:

• CREATE OR REPLACE: 기존 프로시저가 있으면 덮어쓰기
• 매개변수 모드: IN(입력), OUT(출력), IN OUT(입출력)
• AUTHID: 실행 권한 지정(DEFINER: 생성자 권한, CURRENT_USER: 현재 사용자 권한)
• ACCESSIBLE BY: 프로시저에 접근할 수 있는 프로그램 단위 제한
• IS | AS: 선언부 시작을 나타냄
• BEGIN ~ END: 실행 블록 정의

 

#3. 매개변수 유형과 사용법

프로시저 매개변수는 세 가지 모드로 정의할 수 있습니다:

IN 매개변수 (기본값)

• 프로시저에 값을 전달하는 용도
• 프로시저 내에서 수정할 수 없음
• 기본값 설정 가능

CREATE OR REPLACE PROCEDURE update_salary(     p_emp_id IN NUMBER,     p_percentage IN NUMBER DEFAULT 10 ) IS BEGIN     UPDATE employees     SET salary = salary * (1 + p_percentage/100)     WHERE employee_id = p_emp_id;     COMMIT; END;

OUT 매개변수

• 프로시저에서 호출자에게 값을 반환하는 용도
• 프로시저 내에서 초기화 필요

CREATE OR REPLACE PROCEDURE get_employee_info(     p_emp_id IN NUMBER,     p_name OUT VARCHAR2,     p_salary OUT NUMBER ) IS BEGIN     SELECT first_name || ' ' || last_name, salary     INTO p_name, p_salary     FROM employees     WHERE employee_id = p_emp_id; EXCEPTION     WHEN NO_DATA_FOUND THEN         p_name := NULL;         p_salary := NULL; END;

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);     END IF; END;

 

#4. 예외 처리

효과적인 예외 처리는 강력한 프로시저 작성의 핵심입니다:

CREATE OR REPLACE PROCEDURE transfer_money(     p_from_account IN NUMBER,     p_to_account IN NUMBER,     p_amount IN NUMBER ) IS     insufficient_funds EXCEPTION;     PRAGMA EXCEPTION_INIT(insufficient_funds, -20001);     v_balance NUMBER; BEGIN     -- 출금 계좌 잔액 확인     SELECT balance INTO v_balance     FROM accounts     WHERE account_id = p_from_account     FOR UPDATE;     IF v_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;     COMMIT; EXCEPTION     WHEN insufficient_funds THEN         ROLLBACK;         RAISE_APPLICATION_ERROR(-20001, '잔액이 부족합니다.');     WHEN NO_DATA_FOUND THEN         ROLLBACK;         RAISE_APPLICATION_ERROR(-20002, '계좌를 찾을 수 없습니다.');     WHEN OTHERS THEN         ROLLBACK;         RAISE_APPLICATION_ERROR(-20003, '오류 발생: ' || SQLERRM); END;

주요 예외 처리 방법:

• 사전 정의 예외: NO_DATA_FOUND, TOO_MANY_ROWS 등
• 사용자 정의 예외: 직접 정의하여 특정 비즈니스 규칙 위반 처리
• PRAGMA EXCEPTION_INIT: Oracle 오류 코드와 예외 연결
• RAISE_APPLICATION_ERROR: 사용자 정의 오류 코드와 메시지 반환

 

#5. 성능 최적화 기법

1. 벌크 작업 사용

개별 처리 대신 FORALL 문을 사용하여 성능 향상:

CREATE OR REPLACE PROCEDURE bulk_update_salaries(     p_emp_ids IN emp_id_table,     p_percentages IN percentage_table ) IS BEGIN     FORALL i IN p_emp_ids.FIRST..p_emp_ids.LAST         UPDATE employees         SET salary = salary * (1 + p_percentages(i)/100)         WHERE employee_id = p_emp_ids(i);     COMMIT; END;

2. 바인드 변수 활용

리터럴 대신 바인드 변수를 사용하여 하드 파싱 방지:

CREATE OR REPLACE PROCEDURE get_dept_employees(     p_dept_id IN NUMBER,     p_result OUT SYS_REFCURSOR ) IS BEGIN     OPEN p_result FOR         SELECT employee_id, first_name, last_name         FROM employees         WHERE department_id = p_dept_id; END;

3. 불필요한 커서 FOR 루프 최적화

단순 집계나 존재 여부 확인 시 더 효율적인 방법 사용:

-- 비효율적인 방법 v_count NUMBER := 0; FOR rec IN (SELECT 1 FROM employees WHERE department_id = p_dept_id) LOOP     v_count := v_count + 1; END LOOP; -- 효율적인 방법 SELECT COUNT(*) INTO v_count FROM employees WHERE department_id = p_dept_id;

 

#6. 디버깅 및 유지보수 팁

로깅 구현

중요 지점에 로깅을 추가하여 디버깅 용이성 향상:

CREATE OR REPLACE PROCEDURE process_orders(p_date DATE) IS     v_count NUMBER; BEGIN     INSERT INTO log_table(log_time, message)     VALUES (SYSTIMESTAMP, '주문 처리 시작: ' || TO_CHAR(p_date, 'YYYY-MM-DD'));     -- 처리 로직     UPDATE orders     SET status = 'PROCESSED'     WHERE order_date = p_date     AND status = 'PENDING';     v_count := SQL%ROWCOUNT;     INSERT INTO log_table(log_time, message)     VALUES (SYSTIMESTAMP, '처리된 주문 수: ' || v_count);     COMMIT; EXCEPTION     WHEN OTHERS THEN         INSERT INTO log_table(log_time, message)         VALUES (SYSTIMESTAMP, '오류 발생: ' || SQLERRM);         RAISE; END;

모듈화

복잡한 프로시저는 작은 단위로 분할:

CREATE OR REPLACE PROCEDURE process_employee(p_emp_id NUMBER) IS BEGIN     validate_employee(p_emp_id);     update_salary(p_emp_id);     update_benefits(p_emp_id);     notify_changes(p_emp_id); EXCEPTION     WHEN OTHERS THEN         log_error('process_employee', p_emp_id, SQLERRM);         RAISE; END;

 

#7. 실전 예제

예제 1: 직원 정보 관리 프로시저

CREATE OR REPLACE PROCEDURE manage_employee(     p_action IN VARCHAR2,     p_emp_id IN NUMBER,     p_first_name IN VARCHAR2 DEFAULT NULL,     p_last_name IN VARCHAR2 DEFAULT NULL,     p_email IN VARCHAR2 DEFAULT NULL,     p_job_id IN VARCHAR2 DEFAULT NULL,     p_result OUT VARCHAR2 ) IS     v_count NUMBER; BEGIN     p_result := 'SUCCESS';     CASE UPPER(p_action)         WHEN 'INSERT' THEN             INSERT INTO employees(employee_id, first_name, last_name, email, job_id, hire_date)             VALUES(p_emp_id, p_first_name, p_last_name, p_email, p_job_id, SYSDATE);         WHEN 'UPDATE' THEN             UPDATE employees             SET first_name = NVL(p_first_name, first_name),                 last_name = NVL(p_last_name, last_name),                 email = NVL(p_email, email),                 job_id = NVL(p_job_id, job_id)             WHERE employee_id = p_emp_id;             IF SQL%ROWCOUNT = 0 THEN                 p_result := 'Employee not found';             END IF;         WHEN 'DELETE' THEN             DELETE FROM employees             WHERE employee_id = p_emp_id;             IF SQL%ROWCOUNT = 0 THEN                 p_result := 'Employee not found';             END IF;         ELSE             p_result := 'Invalid action';     END CASE;     COMMIT; EXCEPTION     WHEN DUP_VAL_ON_INDEX THEN         ROLLBACK;         p_result := 'Employee ID already exists';     WHEN OTHERS THEN         ROLLBACK;         p_result := 'Error: ' || SQLERRM; END; /

예제 2: 보고서 생성 프로시저

CREATE OR REPLACE PROCEDURE generate_sales_report(     p_year IN NUMBER,     p_month IN NUMBER,     p_dept_id IN NUMBER,     p_report_data OUT SYS_REFCURSOR ) IS     v_start_date DATE;     v_end_date DATE; BEGIN     -- 날짜 범위 계산     v_start_date := TO_DATE(p_year || '-' || p_month || '-01', 'YYYY-MM-DD');     v_end_date := LAST_DAY(v_start_date);     -- 로그 기록     INSERT INTO report_log(report_type, params, run_date)     VALUES('SALES', 'Year=' || p_year || ', Month=' || p_month || ', Dept=' || p_dept_id, SYSDATE);     -- 보고서 데이터 가져오기     OPEN p_report_data FOR         SELECT              e.employee_id,             e.first_name || ' ' || e.last_name AS employee_name,             p.product_name,             SUM(s.quantity) AS total_quantity,             SUM(s.quantity * s.unit_price) AS total_amount         FROM              sales s             JOIN employees e ON s.employee_id = e.employee_id             JOIN products p ON s.product_id = p.product_id         WHERE              s.sale_date BETWEEN v_start_date AND v_end_date             AND e.department_id = p_dept_id         GROUP BY              e.employee_id, e.first_name, e.last_name, p.product_name         ORDER BY              total_amount DESC;     COMMIT; EXCEPTION     WHEN OTHERS THEN         -- 오류 로그         INSERT INTO error_log(procedure_name, error_msg, error_date)         VALUES('generate_sales_report', SQLERRM, SYSDATE);         RAISE; END; /

 

#8. 주요 명명 규칙과 코딩 표준

효과적인 코드 관리를 위한 명명 규칙과 코딩 표준:

프로시저 명명 규칙

• 동사_명사 형태로 작성 (예: update_employee, calculate_salary)
• 의미 있고 설명적인 이름 사용
• 대소문자 일관성 유지 (보통 소문자 선호)

매개변수 명명 규칙

• 접두사로 매개변수 모드 표시:
  • p_ - 매개변수
  • v_ - 지역 변수
  • c_ - 상수
• 명확한 의미를 가진 이름 사용 (예: p_employee_id 대신 p_emp_id)

주석 처리

CREATE OR REPLACE PROCEDURE calculate_bonus(     p_emp_id IN NUMBER,     p_bonus_percentage IN NUMBER,     p_bonus_amount OUT NUMBER ) IS     v_salary NUMBER; BEGIN     /*     * 프로시저: calculate_bonus     * 목적: 직원의 보너스 계산     * 작성자: 홍길동     * 작성일: 2023-06-15     * 수정내역:     *   2023-07-10 - 최저 보너스 금액 기능 추가     */     -- 직원 급여 조회     SELECT salary INTO v_salary     FROM employees     WHERE employee_id = p_emp_id;     -- 보너스 계산 (최소 100,000원 보장)     p_bonus_amount := GREATEST(v_salary * (p_bonus_percentage / 100), 100000); END;

들여쓰기와 포맷팅

• 일관된 들여쓰기 사용 (보통 2-4 공백)
• 논리적 블록 사이에 빈 줄 추가
• 긴 SQL문은 여러 줄로 나누고 적절히 정렬

 

#9. 자주 묻는 질문

Q: 프로시저와 함수의 차이점은 무엇인가요?

A: 프로시저는 작업을 수행하는 독립적인 PL/SQL 블록으로, 값을 반환하지 않거나 OUT 매개변수를 통해 여러 값을 반환할 수 있습니다. 함수는 항상 단일 값을 반환하며, SELECT 문이나 다른 함수 내에서 사용할 수 있습니다.

Q: 프로시저 내에서 트랜잭션을 어떻게 관리해야 하나요?

A: 프로시저 내에서는 COMMIT과 ROLLBACK을 명시적으로 사용하여 트랜잭션을 관리할 수 있습니다. 일반적으로 모든 작업이 성공적으로 완료되면 COMMIT, 오류가 발생하면 EXCEPTION 블록에서 ROLLBACK을 수행합니다.

Q: 프로시저를 다른 스키마에서 실행할 수 있게 하려면 어떻게 해야 하나요?

A: 프로시저 생성 후 다른 스키마에 실행 권한을 부여해야 합니다:

GRANT EXECUTE ON schema_name.procedure_name TO other_schema;

Q: AUTHID DEFINER와 CURRENT_USER의 차이점은 무엇인가요?

A: AUTHID DEFINER(기본값)는 프로시저가 생성자의 권한으로 실행됩니다. AUTHID CURRENT_USER는 호출자의 권한으로 실행됩니다. 보안 요구사항에 따라 적절히 선택해야 합니다.

 

결론

이 글에서는 Oracle PL/SQL Procedure 작성에 관한 완벽한 가이드를 제공했습니다. 기본 구문부터 고급 최적화 기법, 코딩 표준까지 다양한 내용을 다루었습니다. 효과적인 프로시저 작성은 데이터베이스 성능을 향상시키고 코드의 재사용성과 유지보수성을 높이는 중요한 기술입니다.

이 가이드를 참고하여 더 효율적이고 강력한 Oracle 프로시저를 작성하시기 바랍니다!

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

끝.