[C# PL/SQL] 1.오라클 프로시저 기본 개념과 작성법, PL SQL 기본 문법, 프로시저 생성, 매개변수 사용 등.

오라클 데이터베이스는 PL/SQL(Procedural Language/SQL)이라는 프로그래밍 언어를 통해 데이터베이스 작업을 자동화할 수 있는 여러 기능을 제공합니다. 그 중 하나가 바로 프로시저입니다. 이 글에서는 오라클 프로시저의 기본 개념, PL/SQL 기본 문법, 프로시저 생성 방법, 매개변수의 사용에 대해 설명하겠습니다.

1. 오라클 프로시저란?

프로시저는 특정 작업을 수행하기 위해 작성된 PL/SQL 블록입니다. 일반적으로 자주 사용하는 SQL 쿼리 및 PL/SQL 로직을 재사용 가능하게 묶어두어, 데이터베이스에서 직접 실행할 수 있습니다. 이를 통해 코드의 유지보수성을 높이고, 성능을 향상시키며, 복잡한 쿼리를 간단하게 호출할 수 있습니다.

프로시저의 장점

  • 코드 재사용성: 동일한 코드를 여러 번 작성할 필요 없이 호출하여 사용할 수 있습니다.
  • 성능 향상: 데이터베이스 서버에서 직접 실행되므로 클라이언트와 서버 간의 데이터 전송 비용을 줄일 수 있습니다.
  • 보안: 특정 사용자에게 프로시저 실행 권한만 부여하여 데이터의 무결성을 보호할 수 있습니다.
  • 유지보수 용이성: 변경이 필요한 부분을 프로시저 안에서만 수정하면 되어 코드 관리가 용이합니다.

2. PL/SQL 기본 문법

PL/SQL은 SQL을 확장한 프로그래밍 언어로, 변수 선언, 제어문, 예외 처리 등의 기능을 제공합니다. PL/SQL 블록은 다음과 같은 구조로 이루어집니다:

    
    DECLARE
        -- 변수 선언
    BEGIN
        -- 실행할 SQL 명령어와 PL/SQL 명령어
    EXCEPTION
        -- 예외 처리
    END;

변수 선언

변수를 선언할 때는 DECLARE 블록 내에서 사용합니다. 예를 들어:

    
    DECLARE
        v_employee_name VARCHAR2(100);
        v_employee_id NUMBER;
    BEGIN
        -- 변수 사용
    END;

제어문

PL/SQL에서는 제어문을 사용하여 코드를 제어할 수 있습니다. 조건문은 IF, 반복문은 LOOP 등을 이용합니다. 예를 들어:

    
    IF v_employee_id IS NOT NULL THEN
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id);
    END IF;

예외 처리

프로시저 내에서 발생할 수 있는 오류를 처리하기 위해 EXCEPTION 블록을 사용합니다:

    
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('No data found.');
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
    END;

3. 프로시저 생성

프로시저를 생성하려면 CREATE PROCEDURE 구문을 사용합니다. 기본 문법은 다음과 같습니다:

    
    CREATE OR REPLACE PROCEDURE procedure_name
    IS
        -- 변수 선언
    BEGIN
        -- 실행할 SQL 명령어와 PL/SQL 명령어
    END procedure_name;

프로시저 예제

다음은 직원의 급여를 업데이트하는 간단한 프로시저 예제입니다:

    
    CREATE OR REPLACE PROCEDURE update_employee_salary(employee_id IN NUMBER, new_salary IN NUMBER)
    AS
    BEGIN
        UPDATE employees
        SET salary = new_salary
        WHERE id = employee_id;

        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            DBMS_OUTPUT.PUT_LINE('Error updating salary: ' || SQLERRM);
    END update_employee_salary;

4. 매개변수 사용

프로시저는 매개변수를 통해 외부에서 값을 전달받을 수 있습니다. 매개변수는 IN, OUT, IN OUT으로 설정할 수 있습니다:

  • IN: 기본적으로 값이 전달되며, 프로시저 내에서만 사용할 수 있습니다.
  • OUT: 프로시저 내에서 계산된 결과를 호출자에게 반환할 수 있습니다.
  • IN OUT: 입력값을 받고, 프로시저 내에서 값을 변경하여 호출자에게 반환할 수 있습니다.

매개변수 예제

아래는 IN OUT 매개변수를 사용하는 예제입니다:

    
    CREATE OR REPLACE PROCEDURE adjust_salary(employee_id IN NUMBER, adjustment IN OUT NUMBER)
    AS
    BEGIN
        UPDATE employees
        SET salary = salary + adjustment
        WHERE id = employee_id;

        adjustment := adjustment + 1000; -- 조정된 금액 업데이트
    END adjust_salary;

5. 프로시저 실행

프로시저를 실행하려면 EXECUTE 또는 CALL 명령을 사용합니다. 예를 들어:

    
    EXECUTE update_employee_salary(101, 50000);

결론

오라클 프로시저는 PL/SQL을 사용하여 데이터베이스 내에서 복잡한 작업을 자동화하고 재사용할 수 있는 강력한 도구입니다. 이를 통해 코드의 유지보수성과 효율성을 높일 수 있습니다. 이번 글에서 다룬 프로시저의 기본 개념과 작성법, PL/SQL 문법 등을 활용하여 실제 데이터베이스 작업에 적용해 보십시오.