Oracle PL/SQL 프로시저 작성법: 기본 개념과 실습 예제

프로시저란?

프로시저(Procedure)는 특정 작업을 수행하기 위해 미리 정의해두고, 필요할 때마다 호출하여 실행할 수 있는 서브 프로그램입니다. 데이터베이스에서 자주 사용되는 작업들을 자동화하고, 반복적인 코드 작성을 방지할 수 있어 효율적인 관리가 가능합니다.

 

프로시저의 주요 특징

1. 입력 파라미터와 출력 파라미터를 사용하여 외부 데이터를 받아서 처리합니다.
2. 반복적인 작업을 함수처럼 자동화할 수 있습니다.
3. 여러 SQL 문장을 하나의 실행 단위로 묶을 수 있어 코드 유지보수성이 좋아집니다.

이번 포스팅에서는 Oracle PL/SQL에서 프로시저를 작성하는 방법과, 이를 활용한 실습 예제를 살펴보겠습니다.

 

---

 

1. 기본적인 프로시저 작성법

프로시저는 CREATE PROCEDURE 구문을 사용하여 생성합니다. 기본적인 구문은 아래와 같습니다.

기본 구문

CREATE OR REPLACE PROCEDURE PROCEDURE_NAME (
    PARAMETER1 IN TYPE,  -- 파라미터 정의
    PARAMETER2 OUT TYPE -- 출력 파라미터 정의
)
IS
BEGIN
    -- 프로시저 내 로직 작성
END PROCEDURE_NAME;

• IN: 프로시저에 전달되는 값.
• OUT: 프로시저에서 값을 반환하는 경우 사용.
• IS: 프로시저의 본문이 시작되는 부분.

 

간단한 예제: 인사 정보 업데이트

부서명과 설명을 받아 부서 정보를 업데이트하는 프로시저를 작성해보겠습니다.

CREATE OR REPLACE PROCEDURE UPDATE_DEPT_INFO (
    P_DEPT_ID IN INT,              -- 부서 ID
    P_NEW_DEPT_NAME IN VARCHAR2,   -- 새로운 부서명
    P_NEW_DEPT_DESC IN VARCHAR2 DEFAULT 'No description provided'  -- 부서 설명 (기본값)
)
IS
    V_DEPT_NAME VARCHAR2(50);
    V_DEPT_DESC VARCHAR2(100);
BEGIN
    -- 변수에 파라미터 값 할당
    V_DEPT_NAME := P_NEW_DEPT_NAME;
    V_DEPT_DESC := P_NEW_DEPT_DESC;

    -- 부서 정보 업데이트
    UPDATE DEPT
    SET DEPT_NAME = V_DEPT_NAME,
        DEPT_DESC = V_DEPT_DESC
    WHERE DEPT_ID = P_DEPT_ID;

    -- 결과 출력
    IF SQL%ROWCOUNT > 0 THEN
        DBMS_OUTPUT.PUT_LINE('부서 정보가 성공적으로 업데이트되었습니다.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('부서 ID ' || P_DEPT_ID || '를 찾을 수 없습니다.');
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('에러 발생: ' || SQLERRM);
END UPDATE_DEPT_INFO;

 

설명

• P_DEPT_ID, P_NEW_DEPT_NAME, P_NEW_DEPT_DESC는 입력 파라미터입니다.
• 부서명과 설명을 업데이트하고, SQL%ROWCOUNT를 사용하여 업데이트된 행 수를 확인합니다.
• 오류 발생 시 DBMS_OUTPUT를 통해 에러 메시지를 출력합니다.

 

---

 

2. 프로시저 예제 2: 직원 정보 추가

직원 이름, 부서 ID, 급여를 받아 직원 정보를 추가하는 프로시저를 작성해보겠습니다.

CREATE OR REPLACE PROCEDURE INSERT_EMP (
    P_EMP_NAME IN VARCHAR2,          -- 직원 이름
    P_DEPT_ID IN INT,                -- 부서 ID
    P_SALARY IN DECIMAL(10, 2)       -- 급여
)
IS
    V_EMP_ID INT;
BEGIN
    -- 직원 ID는 시퀀스로 자동 생성
    SELECT EMP_SEQ.NEXTVAL INTO V_EMP_ID FROM DUAL;

    -- 직원 정보 INSERT
    INSERT INTO EMP (EMP_ID, EMP_NAME, DEPT_ID, SALARY)
    VALUES (V_EMP_ID, P_EMP_NAME, P_DEPT_ID, P_SALARY);

    -- 결과 출력
    DBMS_OUTPUT.PUT_LINE('직원 정보가 성공적으로 추가되었습니다. 직원 ID: ' || V_EMP_ID);

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('에러 발생: ' || SQLERRM);
END INSERT_EMP;

 

설명

• 직원 ID는 시퀀스를 사용하여 자동으로 생성됩니다.
• INSERT INTO EMP를 통해 직원 정보를 데이터베이스에 추가하고, DBMS_OUTPUT.PUT_LINE을 통해 결과를 출력합니다.
• 예외 처리 구문을 통해 에러 발생 시 메시지를 출력합니다.

 

---

 

3. 프로시저 예제 3: 부서별 급여 인상

특정 부서의 모든 직원 급여를 인상하는 프로시저 예제를 살펴보겠습니다.

CREATE OR REPLACE PROCEDURE INCREASE_SALARY_BY_DEPT (
    P_DEPT_ID IN INT,              -- 부서 ID
    P_INCREASE_PERCENT IN DECIMAL  -- 급여 인상 비율
)
IS
    V_SALARY EMP.SALARY%TYPE;
BEGIN
    -- 부서별로 급여 인상
    FOR EMP_REC IN (SELECT EMP_ID, SALARY FROM EMP WHERE DEPT_ID = P_DEPT_ID) LOOP
        V_SALARY := EMP_REC.SALARY * (1 + P_INCREASE_PERCENT / 100);

        -- 급여 업데이트
        UPDATE EMP
        SET SALARY = V_SALARY
        WHERE EMP_ID = EMP_REC.EMP_ID;

        DBMS_OUTPUT.PUT_LINE('직원 ID ' || EMP_REC.EMP_ID || '의 급여가 ' || V_SALARY || '로 업데이트되었습니다.');
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('부서 ID ' || P_DEPT_ID || '의 모든 직원 급여가 인상되었습니다.');

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('에러 발생: ' || SQLERRM);
END INCREASE_SALARY_BY_DEPT;

 

설명

• 부서 ID와 급여 인상 비율을 받아 해당 부서의 모든 직원 급여를 인상합니다.
• FOR 문을 사용하여 해당 부서의 모든 직원을 순차적으로 조회하고, 급여를 인상합니다.
• 인상된 급여를 UPDATE문을 통해 반영하고, DBMS_OUTPUT.PUT_LINE으로 결과를 출력합니다.

 

---

 

Oracle PL/SQL에서 프로시저는 여러 SQL 작업을 하나의 실행 단위로 묶어서 효율적으로 처리할 수 있는 중요한 도구입니다. 이를 통해 반복적인 작업을 줄이고, 코드 재사용성을 높일 수 있습니다. 또한, 입력 파라미터와 출력 파라미터를 활용하여 다양한 데이터를 처리할 수 있습니다.

위에서 제공한 예시들처럼, UPDATE, INSERT, SELECT 문을 프로시저 내에서 자유롭게 활용하여 데이터베이스 작업을 효율적으로 자동화할 수 있습니다. 프로시저 작성 시에는 반드시 예외 처리와 결과 출력을 고려하여 안정적인 시스템을 구축하는 것이 중요합니다.