Oracle 데이터베이스를 다루는 개발자라면 반드시 알아야 할 기능 중 하나가 바로 프로시저(Procedure)입니다. 오라클 프로시저는 반복적인 작업을 효율적으로 처리하고, 로직을 데이터베이스 레벨에서 구현할 수 있게 해 줍니다. 이번 포스트에서는 Oracle 프로시저의 기본 개념부터 생성, 실행, 조회, 수정에 이르는 전반적인 내용을 실제 예제와 함께 자세히 알아보겠습니다. 🙂

 

1. Oracle 프로시저란?

프로시저(Procedure)는 PL/SQL 구문으로 작성된 명령문들의 집합으로, 데이터베이스에 저장되어 필요할 때마다 호출하여 실행할 수 있는 서브프로그램입니다.

PL/SQL은 “Procedural Language extension to SQL”의 약자로, Oracle에서 SQL에 절차적 프로그래밍 기능을 확장한 언어입니다. 이를 통해 단순 쿼리 이상의 복잡한 데이터 처리와 비즈니스 로직을 구현할 수 있습니다.

프로시저의 주요 특징은 다음과 같습니다:

• 데이터베이스에 컴파일된 형태로 저장되어 재사용이 가능합니다.
• IN, OUT, INOUT 매개변수를 통해 값을 주고받을 수 있습니다.
• 복잡한 비즈니스 로직과 데이터 처리를 캡슐화할 수 있습니다.
• 네트워크 트래픽을 줄이고 성능을 향상시킬 수 있습니다.
• 다중 SQL 문장을 한 번에 실행할 수 있어 데이터베이스 작업의 효율성을 높여줍니다.

 

2. 프로시저의 기본 구조

Oracle 프로시저는 크게 다음과 같은 구조로 이루어져 있습니다:

CREATE [OR REPLACE] PROCEDURE 프로시저명 
    [(매개변수1 [IN | OUT | IN OUT] 데이터타입,
      매개변수2 [IN | OUT | IN OUT] 데이터타입, ...)]
IS | AS
    -- 선언부: 변수, 상수, 커서 등 선언
BEGIN
    -- 실행부: SQL문과 PL/SQL 문장
    -- 비즈니스 로직 구현
EXCEPTION
    -- 예외처리부: 오류 처리
END [프로시저명];
/

각 부분의 역할은 다음과 같습니다:

1. 헤더부: 프로시저의 이름과 매개변수를 정의합니다.
2. 선언부: 프로시저 내에서 사용할 변수, 상수, 커서 등을 선언합니다.
3. 실행부: 실제 실행할 코드를 작성하는 부분으로 BEGIN으로 시작하여 END로 끝납니다.
4. 예외처리부: 실행 중 발생할 수 있는 예외 상황을 처리하는 부분입니다.

 

3. 프로시저 생성하기

Oracle에서 프로시저를 생성하는 가장 기본적인 방법을 살펴보겠습니다. 다음은 가장 간단한 형태의 프로시저 생성 예제입니다.

3.1 기본 프로시저 생성

CREATE OR REPLACE PROCEDURE hello_world
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello, World!');
END hello_world;
/

위 예제는 단순히 ‘Hello, World!’라는 메시지를 출력하는 프로시저입니다. CREATE OR REPLACE는 같은 이름의 프로시저가 이미 존재할 경우 덮어쓰기를 가능하게 합니다.

3.2 매개변수가 있는 프로시저 생성

다음은 매개변수를 받아 처리하는 프로시저의 예입니다:

CREATE OR REPLACE PROCEDURE welcome_msg 
    (p_name IN VARCHAR2)
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Welcome ' || p_name);
END welcome_msg;
/

이 프로시저는 p_name이라는 매개변수를 입력받아 환영 메시지를 출력합니다.

3.3 IN, OUT, IN OUT 매개변수 사용

프로시저는 매개변수를 통해 값을 주고받을 수 있는데, 세 가지 유형의 매개변수를 사용할 수 있습니다:

• IN: 프로시저에 값을 전달할 때 사용 (기본값)
• OUT: 프로시저에서 결과값을 반환할 때 사용
• IN OUT: 값을 전달하고 변경된 값을 반환할 때 사용

다음은 세 가지 유형의 매개변수를 모두 사용하는 예제입니다:

CREATE OR REPLACE PROCEDURE calc_bonus
    (p_empno IN NUMBER,
     p_bonus OUT NUMBER,
     p_sal IN OUT NUMBER)
IS
    v_comm NUMBER;
BEGIN
    -- 사원 번호로 커미션 조회
    SELECT comm INTO v_comm
    FROM emp
    WHERE empno = p_empno;
    
    -- 보너스 계산 (급여의 20%)
    p_bonus := p_sal * 0.2;
    
    -- 급여에 보너스와 커미션 추가
    p_sal := p_sal + p_bonus + NVL(v_comm, 0);
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('사원 정보를 찾을 수 없습니다.');
        p_bonus := 0;
END calc_bonus;
/

위 프로시저는 사원 번호(IN), 급여(IN OUT) 값을 받아 보너스(OUT)를 계산하고, 최종 급여(급여+보너스+커미션)를 반환합니다.

 

4. 프로시저 실행하기

생성한 프로시저를 실행하는 방법에는 여러 가지가 있습니다.

4.1 EXECUTE 명령어 사용

SQL*Plus나 SQL Developer에서 다음과 같이 EXECUTE 또는 줄여서 EXEC 명령을 사용할 수 있습니다:

EXECUTE welcome_msg('John');
-- 또는
EXEC welcome_msg('John');

4.2 PL/SQL 블록 내에서 호출

익명 블록이나 다른 프로시저 내에서 프로시저를 호출할 수 있습니다:

BEGIN
    welcome_msg('John');
END;
/

4.3 OUT 매개변수 사용 예제

OUT 매개변수가 있는 프로시저를 호출할 때는 변수를 선언하여 결과를 받아야 합니다:

DECLARE
    v_bonus NUMBER;
    v_sal NUMBER := 5000;
BEGIN
    calc_bonus(7839, v_bonus, v_sal);
    DBMS_OUTPUT.PUT_LINE('보너스: ' || v_bonus);
    DBMS_OUTPUT.PUT_LINE('최종 급여: ' || v_sal);
END;
/

 

5. 프로시저 조회하기

Oracle에서는 데이터 딕셔너리 뷰를 통해 저장된 프로시저 정보를 조회할 수 있습니다.

5.1 사용자가 소유한 모든 프로시저 조회

SELECT object_name, status, created, last_ddl_time
FROM user_objects
WHERE object_type = 'PROCEDURE'
ORDER BY object_name;

5.2 특정 프로시저의 소스 코드 조회

SELECT line, text
FROM user_source
WHERE name = '프로시저명'
AND type = 'PROCEDURE'
ORDER BY line;

5.3 프로시저 매개변수 정보 확인

SELECT argument_name, position, data_type, in_out
FROM user_arguments
WHERE object_name = '프로시저명'
ORDER BY position;

5.4 SQL Developer에서 프로시저 확인

SQL Developer를 사용하면 GUI 환경에서 쉽게 프로시저를 확인할 수 있습니다.

1. 왼쪽 탐색 창에서 Procedures 폴더를 확장합니다.
2. 프로시저를 선택하고 마우스 오른쪽 버튼을 클릭합니다.
3. “편집” 또는 “소스 보기”를 선택하면 프로시저 코드를 확인할 수 있습니다.

 

6. 프로시저 수정하기

이미 생성된 프로시저를 수정하는 방법은 다음과 같습니다.

6.1 OR REPLACE 구문 사용

가장 일반적인 방법은 CREATE OR REPLACE 구문을 사용하여 프로시저를 재정의하는 것입니다:

CREATE OR REPLACE PROCEDURE welcome_msg 
    (p_name IN VARCHAR2)
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello and welcome ' || p_name || '!');
    -- 메시지 변경
END welcome_msg;
/

6.2 프로시저 삭제 후 재생성

프로시저를 삭제한 후 새로 생성할 수도 있습니다:

DROP PROCEDURE welcome_msg;

CREATE PROCEDURE welcome_msg 
    (p_name IN VARCHAR2)
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello and welcome ' || p_name || '!');
END welcome_msg;
/

6.3 프로시저 컴파일

프로시저가 무효화된 경우(예: 참조하는 테이블 변경) 다음 명령으로 재컴파일할 수 있습니다:

ALTER PROCEDURE welcome_msg COMPILE;

 

7. 프로시저 예외 처리

예외 처리는 프로시저에서 발생할 수 있는 오류를 관리하는 중요한 부분입니다. PL/SQL에서는 다양한 유형의 예외를 처리할 수 있습니다.

7.1 기본 예외 처리 구조

CREATE OR REPLACE PROCEDURE update_salary
    (p_empno IN NUMBER, p_increase IN NUMBER)
IS
    v_sal emp.sal%TYPE;
BEGIN
    -- 급여 업데이트
    UPDATE emp
    SET sal = sal + p_increase
    WHERE empno = p_empno;
    
    -- 영향받은 행이 없으면 예외 발생
    IF SQL%ROWCOUNT = 0 THEN
        RAISE NO_DATA_FOUND;
    END IF;
    
    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('사원 번호 ' || p_empno || '에 해당하는 사원이 없습니다.');
        ROLLBACK;
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('오류 발생: ' || SQLERRM);
        ROLLBACK;
END update_salary;
/

7.2 자세한 오류 정보 얻기

SQLCODE와 SQLERRM 함수를 사용하여 발생한 예외에 대한 자세한 정보를 얻을 수 있습니다:

CREATE OR REPLACE PROCEDURE exception_handler
IS
    v_result NUMBER;
BEGIN
    v_result := 100 / 0;  -- 0으로 나누기 시도
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('오류 코드: ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('오류 메시지: ' || SQLERRM);
        -- 추가 정보를 위한 백트레이스
        DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END exception_handler;
/

7.3 사용자 정의 예외

사용자 정의 예외를 선언하고 사용할 수도 있습니다:

CREATE OR REPLACE PROCEDURE check_salary
    (p_empno IN NUMBER, p_new_sal IN NUMBER)
IS
    e_salary_too_high EXCEPTION;
    v_max_sal NUMBER := 10000;
BEGIN
    IF p_new_sal > v_max_sal THEN
        RAISE e_salary_too_high;
    END IF;
    
    -- 급여 업데이트 로직
    UPDATE emp
    SET sal = p_new_sal
    WHERE empno = p_empno;
    
    COMMIT;
EXCEPTION
    WHEN e_salary_too_high THEN
        DBMS_OUTPUT.PUT_LINE('급여가 최대 한도(' || v_max_sal || ')를 초과했습니다.');
        ROLLBACK;
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('오류 발생: ' || SQLERRM);
        ROLLBACK;
END check_salary;
/

7.4 RAISE_APPLICATION_ERROR 사용

RAISE_APPLICATION_ERROR 프로시저를 사용하여 사용자 정의 오류 메시지와 코드를 생성할 수 있습니다:

CREATE OR REPLACE PROCEDURE validate_employee
    (p_empno IN NUMBER)
IS
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM emp
    WHERE empno = p_empno;
    
    IF v_count = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, '사원 번호 ' || p_empno || '에 해당하는 사원이 없습니다.');
    END IF;
    
    -- 유효한 사원일 경우 추가 로직
    DBMS_OUTPUT.PUT_LINE('사원 유효성 검사 통과');
END validate_employee;
/

사용자 정의 오류 코드는 -20000에서 -20999 사이의 값을 사용해야 합니다.

 

8. 프로시저 성능 최적화

프로시저 성능을 최적화하기 위한 몇 가지 팁을 알아보겠습니다.

8.1 바인드 변수 사용

리터럴 값 대신 바인드 변수를 사용하면 하드 파싱을 줄이고 성능을 향상시킬 수 있습니다:

-- 좋지 않은 방법
CREATE OR REPLACE PROCEDURE bad_practice
IS
BEGIN
    FOR i IN 1..1000 LOOP
        EXECUTE IMMEDIATE 'UPDATE emp SET sal = sal * 1.01 WHERE empno = ' || i;
    END LOOP;
END;
/

-- 더 나은 방법
CREATE OR REPLACE PROCEDURE better_practice
IS
BEGIN
    FOR i IN 1..1000 LOOP
        EXECUTE IMMEDIATE 'UPDATE emp SET sal = sal * 1.01 WHERE empno = :num' 
        USING i;
    END LOOP;
END;
/

8.2 벌크 수집 사용

개별 처리 대신 벌크 수집을 사용하여 성능을 향상시킬 수 있습니다:

CREATE OR REPLACE PROCEDURE bulk_update
IS
    TYPE emp_id_table IS TABLE OF emp.empno%TYPE;
    TYPE emp_sal_table IS TABLE OF emp.sal%TYPE;
    
    v_emp_ids emp_id_table;
    v_salaries emp_sal_table;
BEGIN
    -- 데이터 벌크 수집
    SELECT empno, sal BULK COLLECT INTO v_emp_ids, v_salaries
    FROM emp
    WHERE deptno = 10;
    
    -- 벌크 업데이트
    FORALL i IN 1..v_emp_ids.COUNT
        UPDATE emp
        SET sal = v_salaries(i) * 1.1
        WHERE empno = v_emp_ids(i);
    
    COMMIT;
END bulk_update;
/

8.3 커서 FOR 루프 활용

명시적 커서와 FOR 루프를 함께 사용하면 코드를 간결하게 작성하고 성능을 향상시킬 수 있습니다:

CREATE OR REPLACE PROCEDURE process_employees
IS
BEGIN
    FOR emp_rec IN (SELECT empno, ename, sal FROM emp) LOOP
        -- 각 사원 처리
        DBMS_OUTPUT.PUT_LINE('사원: ' || emp_rec.ename || ', 급여: ' || emp_rec.sal);
        
        -- 급여가 3000 이상인 경우 추가 처리
        IF emp_rec.sal >= 3000 THEN
            UPDATE emp
            SET comm = NVL(comm, 0) + 500
            WHERE empno = emp_rec.empno;
        END IF;
    END LOOP;
    
    COMMIT;
END process_employees;
/

 

9. Oracle 프로시저 실사용 예제

다양한 상황에서 사용할 수 있는 실전 프로시저 예제를 살펴보겠습니다.

9.1 부서별 급여 통계 프로시저

CREATE OR REPLACE PROCEDURE get_dept_salary_stats
    (p_deptno IN NUMBER,
     p_avg_sal OUT NUMBER,
     p_min_sal OUT NUMBER,
     p_max_sal OUT NUMBER,
     p_count OUT NUMBER)
IS
BEGIN
    SELECT AVG(sal), MIN(sal), MAX(sal), COUNT(*)
    INTO p_avg_sal, p_min_sal, p_max_sal, p_count
    FROM emp
    WHERE deptno = p_deptno;
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_avg_sal := 0;
        p_min_sal := 0;
        p_max_sal := 0;
        p_count := 0;
        DBMS_OUTPUT.PUT_LINE('부서 번호 ' || p_deptno || '에 해당하는 사원이 없습니다.');
END get_dept_salary_stats;
/

호출 예제:

DECLARE
    v_avg_sal NUMBER;
    v_min_sal NUMBER;
    v_max_sal NUMBER;
    v_count NUMBER;
    v_deptno NUMBER := 10;
BEGIN
    get_dept_salary_stats(v_deptno, v_avg_sal, v_min_sal, v_max_sal, v_count);
    
    DBMS_OUTPUT.PUT_LINE('부서 ' || v_deptno || ' 급여 통계:');
    DBMS_OUTPUT.PUT_LINE('평균 급여: ' || v_avg_sal);
    DBMS_OUTPUT.PUT_LINE('최소 급여: ' || v_min_sal);
    DBMS_OUTPUT.PUT_LINE('최대 급여: ' || v_max_sal);
    DBMS_OUTPUT.PUT_LINE('사원 수: ' || v_count);
END;
/

9.2 트랜잭션 로그 프로시저

다음은 테이블 변경 사항을 기록하는 로그 프로시저의 예입니다:

CREATE OR REPLACE PROCEDURE log_emp_changes
    (p_empno IN NUMBER,
     p_action IN VARCHAR2,
     p_old_sal IN NUMBER DEFAULT NULL,
     p_new_sal IN NUMBER DEFAULT NULL)
IS
BEGIN
    -- 로그 테이블에 변경 사항 기록
    INSERT INTO emp_log
        (log_date, user_name, action, empno, old_sal, new_sal)
    VALUES
        (SYSDATE, USER, p_action, p_empno, p_old_sal, p_new_sal);
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('로그 기록 중 오류 발생: ' || SQLERRM);
        -- 로그 기록 실패 시에도 메인 트랜잭션은 영향 없음
END log_emp_changes;
/

이 프로시저는 다른 프로시저나 트리거에서 호출하여 사원 정보 변경 내역을 기록할 수 있습니다.

9.3 동적 SQL을 사용한 프로시저

동적 SQL을 사용하여 유연한 프로시저를 작성할 수 있습니다:

CREATE OR REPLACE PROCEDURE dynamic_query
    (p_table_name IN VARCHAR2,
     p_column_name IN VARCHAR2,
     p_condition IN VARCHAR2)
IS
    v_sql VARCHAR2(4000);
    v_cursor SYS_REFCURSOR;
    v_value VARCHAR2(4000);
BEGIN
    -- 동적 SQL 문 생성
    v_sql := 'SELECT ' || p_column_name || ' FROM ' || p_table_name;
    
    IF p_condition IS NOT NULL THEN
        v_sql := v_sql || ' WHERE ' || p_condition;
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('실행할 쿼리: ' || v_sql);
    
    -- 동적 SQL 실행
    OPEN v_cursor FOR v_sql;
    
    LOOP
        FETCH v_cursor INTO v_value;
        EXIT WHEN v_cursor%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE(p_column_name || ': ' || v_value);
    END LOOP;
    
    CLOSE v_cursor;
EXCEPTION
    WHEN OTHERS THEN
        IF v_cursor%ISOPEN THEN
            CLOSE v_cursor;
        END IF;
        DBMS_OUTPUT.PUT_LINE('오류 발생: ' || SQLERRM);
END dynamic_query;
/

호출 예제:

BEGIN
    dynamic_query('emp', 'ename', 'deptno = 10');
END;
/

 

10. Oracle 23c의 새로운 프로시저 기능

Oracle 23c(Oracle Database 23ai)에서는 프로시저 관련 몇 가지 새로운 기능들이 추가되었습니다.

10.1 SQL Transpiler – PL/SQL to SQL 자동 변환

Oracle 23c에서는 PL/SQL 함수가 자동으로 SQL 표현식으로 변환되는 Transpiler 기능이 추가되었습니다. 이는 성능 향상에 도움이 될 수 있습니다.

10.2 IF EXISTS와 IF NOT EXISTS 구문

23c에서는 객체의 CREATE, ALTER, DROP 문에서 IF EXISTS와 IF NOT EXISTS 구문을 사용할 수 있게 되었습니다. 이를 통해 프로시저나 함수 생성 스크립트를 더 안전하게 작성할 수 있습니다.

CREATE OR REPLACE PROCEDURE IF NOT EXISTS my_procedure
IS
BEGIN
    -- 프로시저 내용
END;
/

10.3 새로운 PL/SQL 패키지

Oracle 23c에는 다음과 같은 새로운 PL/SQL 패키지들이 추가되었습니다:

1. DBMS_HCHECK: 데이터베이스 딕셔너리 불일치를 식별하는 패키지
2. DBMS_SEARCH: 여러 객체에 대한 검색 기능 제공
3. DBMS_SQL_FIREWALL: SQL 인젝션 공격 방지를 위한 패키지

 

11. 자주 발생하는 문제와 해결 방법

프로시저 개발 시 자주 발생하는 문제와 해결 방법을 알아보겠습니다.

11.1 컴파일 오류

컴파일 오류가 발생하면 다음 명령으로 자세한 오류 정보를 확인할 수 있습니다:

SHOW ERRORS PROCEDURE 프로시저명;

또는 데이터 딕셔너리 뷰에서 조회:

SELECT line, position, text
FROM user_errors
WHERE name = '프로시저명'
AND type = 'PROCEDURE'
ORDER BY sequence;

11.2 매개변수 관련 오류

IN 매개변수에 값을 할당하려고 할 때 발생하는 오류:

-- 오류 발생 코드
CREATE OR REPLACE PROCEDURE wrong_param_usage
    (p_value IN NUMBER)
IS
BEGIN
    p_value := 10;  -- 오류: IN 매개변수에 값 할당 불가
END;
/

해결 방법: IN 매개변수는 읽기 전용이므로 값을 변경하려면 IN OUT 또는 OUT 매개변수를 사용해야 합니다.

11.3 권한 관련 문제

프로시저 실행 시 권한 오류가 발생할 수 있습니다. 이 경우 다음과 같이 권한을 부여해야 합니다:

-- 프로시저 실행 권한 부여
GRANT EXECUTE ON 프로시저명 TO 사용자명;

또한 프로시저 내에서 다른 스키마의 객체에 접근할 때는 해당 객체에 대한 권한이 필요합니다:

-- 테이블 접근 권한 부여
GRANT SELECT, UPDATE ON 테이블명 TO 프로시저_소유자;

11.4 재귀 호출 제한

Oracle에서는 프로시저의 재귀 호출 깊이에 제한이 있습니다. 기본값은 보통 50 레벨입니다. 다음과 같이 조정할 수 있습니다:

ALTER SYSTEM SET plsql_optimize_level=3;

 

이번 포스팅에서는 Oracle 프로시저의 생성, 실행, 조회, 수정 방법에 대해 자세히 알아보았습니다. 프로시저는 데이터베이스 내에서 비즈니스 로직을 구현하고 재사용 가능한 코드를 작성하는 강력한 도구입니다. 여러분들도 Oracle 프로시저를 통해 더 효율적이고 안정적인 데이터베이스 애플리케이션을 개발해보시길 추천드립니다. 그럼 이만 슝~! 🙂