PL/SQL 저장 프로시저(IN, OUT, IN OUT, 바인드 변수) (5)

오라클은 사용자가 만든 PL/SQL 문을 데이터베이스에 저장할 수 있도록 저장 프로시저라는 것을 제공한다.

저장프로시저를사용하면복잡한 DML 문을 필요할때마다 다시 입력할 필요 없이 간단하게 호출만해서 복잡한 DML 문의 실행 결과를 얻을 수 있다.

 

저장 프로시저를 생성하기 위한 CREATE PROCEDURE의 형식은 다음과 같다.

CREATE OR REPLACE PROCEDURE procedure_name
IS
   -- 변수, 상수, 커서 등을 선언합니다.
BEGIN
   -- 실행할 코드 블록을 작성합니다.
   -- 변수, 상수, 커서 등을 사용하여 로직을 구현합니다.
EXCEPTION
   -- 예외 처리를 수행합니다.
END;
/

저장 프로시저를 생성하면 CREATE  PROCEDURE 다음에 새롭게 생성하고자 하는 프로시저 이름을 기술한다.

(CREATE PROCEDURE 문은 해당 이름의 프로시저가 이미 존재하면 오류를 발생시킵니다. 반면에 CREATE OR REPLACE PROCEDURE 문은 해당 이름의 프로시저가 이미 존재하면 대체합니다.)

이렇게 해서 생선한 저장 프로시저는 여러 번 반보해서 호출해서 사용할 수 있다는 장점이 있다.

 

생성된 저장 프로시저를 제거하기 위해서는 DROP PROCEDURE 다음에 제거하고자 하는 프로시저 이름을 기술한다.

(한번에 모든 프로시저를 지우는 명령어는 없음)

 

1. 저장 프로시저 조회하기

저장 프로시저를 작성한 후, 사용자가 저장 프로시저가 생성되었는지 확인하려면 USER_SOURCE를 살펴보면 된다.

SELECT * FROM USER_PROCEDURES;

 

2. 저장 프로시저의 매개 변수

 

CREATE OR REPLACE PROCEDURE procedure_name (parameter1 datatype1, parameter2 datatype2, ...) IS
BEGIN
   -- 프로시저 내용 작성
END;
/

위의 구문에서 parameter는 매개 변수의 이름을 나타내며, datatype은 해당 매개 변수의 데이터 유형을 나타냅니다. 매개 변수는 필요한 만큼 많이 정의할 수 있습니다.

예를 들어, 매개 변수가 있는 저장 프로시저를 만들어 보겠습니다. 이 프로시저는 주어진 사원 번호에 해당하는 정보를 반환합니다.

 

CREATE OR REPLACE PROCEDURE get_employee_info (p_emp_id IN NUMBER) IS
   v_emp_name VARCHAR2(50);
   v_emp_dept VARCHAR2(50);
BEGIN
   -- 사원 이름과 부서 이름 조회
   SELECT employee_name, department_name INTO v_emp_name, v_emp_dept
   FROM employees e
   JOIN departments d ON e.department_id = d.department_id
   WHERE employee_id = p_emp_id;
   
   -- 조회된 결과 출력
   DBMS_OUTPUT.PUT_LINE('Employee name: ' || v_emp_name);
   DBMS_OUTPUT.PUT_LINE('Department name: ' || v_emp_dept);
END;
/

위의 예제에서 get_employee_info 프로시저는 p_emp_id라는 매개 변수를 사용하여 해당하는 사원의 이름과 부서 이름을 조회합니다. 이 프로시저는 IN 매개 변수를 사용하여 값을 입력하고, 조회된 결과를 DBMS_OUTPUT.PUT_LINE 함수를 사용하여 출력합니다.

 

3. IN, OUT, INOUT 매개변수

 

저장 프로시저(Stored Procedure)의 매개 변수(Parameter)는 프로시저를 호출할 때 전달되는 값이나 변수를 받아들이기 위한 변수입니다. 매개 변수는 IN, OUT 또는 IN OUT 타입으로 정의할 수 있습니다.

 

 

IN 매개 변수: 프로시저 내부에서 읽기만 가능한 매개 변수 (데이터 전달 받을 때 사용)

 

IN 매개 변수는 저장 프로시저의 매개 변수 유형 중 하나입니다. 이 유형의 매개 변수는 프로시저 내에서 입력으로 사용되며, 호출하는 코드에서 값을 전달하여 프로시저 내에서 사용됩니다. IN 매개 변수는 저장 프로시저가 호출되면 값을 읽기만 할 수 있으며 변경할 수 없습니다.

 

예시

아래는 IN 매개 변수를 사용한 예시입니다. 이 예시는 전달된 숫자 두 개를 더한 값을 출력하는 저장 프로시저를 보여줍니다.

CREATE OR REPLACE PROCEDURE add_numbers(
   num1 IN NUMBER,
   num2 IN NUMBER
)
IS
   total NUMBER;
BEGIN
   total := num1 + num2;
   DBMS_OUTPUT.PUT_LINE('The sum of ' || num1 || ' and ' || num2 || ' is ' || total);
END;
/

위의 코드에서 num1 IN NUMBER 및 num2 IN NUMBER은 IN 매개 변수를 정의하는 구문입니다. 이 프로시저는 두 개의 IN 매개 변수 num1과 num2를 정의합니다. 이 프로시저는 두 개의 숫자를 입력으로 받아들이고, 더한 값을 계산하여 결과를 출력합니다.

이 프로시저를 호출하려면 다음과 같이 하면 됩니다.

BEGIN
   add_numbers(10, 20);
END;
/

위의 코드에서 add_numbers는 저장 프로시저의 이름입니다. 10은 num1에 전달되는 값이고, 20은 num2에 전달되는 값입니다. 이 호출은 두 개의 값을 더한 후 결과를 출력합니다.

OUT 매개 변수: 프로시저 내부에서 쓰기만 가능한 매개 변수 (수행된 결과 받아갈 때 사용)

 

OUT 매개 변수는 저장 프로시저에서 결과 값을 반환할 때 사용됩니다. 즉, 프로시저가 실행되고 나서 OUT 매개 변수에 값을 할당하면, 해당 변수를 호출한 코드에서 그 값을 참조할 수 있습니다.

OUT 매개 변수는 프로시저 내에서 할당이 필요한 경우가 있으며, 이를 통해 저장 프로시저의 유연성을 높일 수 있습니다.

OUT 매개 변수는 매개 변수 선언 시에 OUT 키워드를 사용하여 정의합니다. 

 

예시

예를 들어, 다음은 OUT 매개 변수를 사용한 저장 프로시저의 예입니다.

CREATE OR REPLACE PROCEDURE out_parameter_example(
   p_in_parameter NUMBER,
   p_out_parameter OUT VARCHAR2
) AS
BEGIN
   p_out_parameter := 'Result: ' || TO_CHAR(p_in_parameter);
END;
/

 

위의 예시에서는 p_out_parameter라는 OUT 매개 변수를 사용하고 있습니다. 이 매개 변수는 저장 프로시저가 실행된 이후, 프로시저 내에서 결과 값을 할당하고 있습니다. 이제 이 저장 프로시저를 호출하면, p_in_parameter에 전달된 값에 대한 결과 문자열이 반환됩니다.

호출 방법은 다음과 같이 사용할 수 있습니다.

DECLARE
   l_result VARCHAR2(100);
BEGIN
   out_parameter_example(100, l_result);
   DBMS_OUTPUT.PUT_LINE(l_result);
END;
/

위의 예시에서는 out_parameter_example 저장 프로시저를 호출하고 있습니다. 이 호출문에서는 p_in_parameter에 100이라는 값을 전달하고, l_result라는 변수를 OUT 매개 변수로 사용하여 결과 값을 반환하고 있습니다. 실행 결과, 'Result: 100'이라는 문자열이 출력됩니다.

IN OUT 매개 변수: 프로시저 내부에서 읽고 쓰기가 모두 가능한 매개 변수 (둘 다)

 

IN OUT 매개 변수는 저장 프로시저에서 IN과 OUT 매개 변수의 특징을 모두 가지고 있는 변수입니다. 이러한 매개 변수는 저장 프로시저에서 입력 값을 받고, 프로시저의 실행 결과를 출력으로 반환할 때 사용됩니다.

 

IN OUT 매개 변수는 프로시저가 호출될 때 값이 전달되어 초기화되며, 프로시저 내에서 수정될 수 있습니다. 그리고 프로시저 실행이 끝난 후에는 수정된 값을 반환하여 호출한 쪽에서 사용할 수 있습니다.

예시

아래는 IN OUT 매개 변수가 사용된 PL/SQL 예시입니다.

 

CREATE OR REPLACE PROCEDURE update_employee_salary(
    p_employee_id   IN     NUMBER,
    p_new_salary    IN OUT NUMBER)
IS
BEGIN
    -- Increase employee salary by 10%
    p_new_salary := p_new_salary * 1.1;

    -- Update employee salary in the database
    UPDATE employees
    SET salary = p_new_salary
    WHERE employee_id = p_employee_id;
END;
/

 

이 예시에서 update_employee_salary 저장 프로시저는 두 개의 매개 변수를 가지고 있습니다. 첫 번째 매개 변수 p_employee_id는 IN 매개 변수로, 저장 프로시저 내부에서만 사용됩니다. 두 번째 매개 변수 p_new_salary는 IN OUT 매개 변수로, 저장 프로시저가 호출될 때 값이 전달되고, 프로시저 내부에서 수정됩니다. 마지막으로, 프로시저가 실행되고 난 후 수정된 p_new_salary 값이 반환됩니다.

 

* 바인드 변수

바인드 변수는 SQL 문장 또는 PL/SQL 블록안에 사용되는 변수로 값을 담을 수 있는 메모리 공간입니다.

이 변수는 SQL 문장이나 PL/SQL 블록이 실행 될 때마다 값을 바인딩하고 사용할 수 있다.

즉, 값을 동적으로 변경하며 SQL 문장을 실행할 수 있게 해준다.

 

바인드 변수는 콜론(:)으로 시작하는 변수 이름을 사용한다.

예를 들어, ':salary' 라는 바인드 변수를 사용하면 'SELECT * FROM employees WHERE salary > :salary'와 같이 sql 문에서 변수를 사용할 수 있다.

 

예시

예를 들어, 아래 SQL 문에서는 :dept_id 바인드 변수를 사용하여 부서 번호를 동적으로 지정합니다.

SELECT *
FROM employees
WHERE department_id = :dept_id;

이제 이 SQL 문을 실행할 때, :dept_id 값을 지정해주어야 합니다. 예를 들어, 아래와 같이 SQL Plus에서 실행하면 됩니다.

SQL> VARIABLE dept_id NUMBER;
SQL> EXECUTE :dept_id := 20;

PL/SQL procedure successfully completed.

SQL> SELECT *
  2  FROM employees
  3  WHERE department_id = :dept_id;

     EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE  JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
    ------------ -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
             201 Michael              Hartstein                 MHARTSTEIN                515.123.5555         17-FEB-04  MK_MAN          13000                           20
             114 Den                  Raphaely                  DRAPHEAL                  515.127.4561         07-DEC-02  PU_MAN          11000                           30
             115 Alexander            Khoo                      AKHOO                     515.127.4562         18-MAY-03  PU_CLERK         3100                           30
             118 Guy                  Himuro                    GHIMURO                   515.127.4564         15-NOV-02  PU_CLERK         2600                           30
             203 Susan                Mavris                    SMAVRIS                   515.123.7777         07-JUN-02  HR_REP           6500                           40

SQL>

위 예시에서는 SQL Plus의 VARIABLE 명령어를 사용하여 :dept_id 바인드 변수를 정의하고, EXECUTE 명령어를 사용하여 값을 할당한 뒤, SELECT 문에서 :dept_id 값을 사용하여 부서별 직원 정보를 조회하였습니다.

 

* 요약

 

IN, OUT, IN OUT 매개 변수와 바인드 변수는 모두 변수의 값을 저장하고 전달하기 위해 사용됩니다. 그러나 이들 간에는 몇 가지 중요한 차이점이 있습니다.

IN 매개 변수는 저장 프로시저에서 입력 값으로만 사용됩니다. 따라서 프로시저에서 이 값을 변경할 수 없습니다.

OUT 매개 변수는 저장 프로시저에서 출력 값으로만 사용됩니다. 따라서 프로시저가 완료된 후에는 변수에 저장된 값을 가져올 수 있습니다.

IN OUT 매개 변수는 저장 프로시저에서 입력 값으로 사용되고, 실행 후에 변수에 새 값을 저장하여 출력 값으로 사용됩니다.

반면에, 바인드 변수는 SQL 문에서 값을 전달하는 데 사용됩니다. SQL 문이 실행될 때 매번 값이 변경될 수 있습니다.

따라서 바인드 변수는 SQL 문에서 값을 전달하기 위해 사용되고, IN, OUT, IN OUT 매개 변수는 저장 프로시저에서 입력 및 출력 값으로 사용됩니다. 또한, 바인드 변수는 SQL 문이 실행될 때마다 값이 변경될 수 있지만, 매개 변수는 저장 프로시저가 실행될 때마다 값이 변경될 수 있습니다.

 

 

4. 저장 프로시저의 특징

 

1) 저장 프로시저의 처리 방식

오라클에서 지원하는 프로시저는 기본적으로 C나 다른언어에서 사용하는 함수와 개념과 동일하다.

다만, 프로시저는 클라이언트 응용 프로그램에서 반복적으로 같은 처리를 할때 매번 같은 SQL문을 서버에 보내는 대신에,

미리 그 정의를 서버에 저장해두고, 클라이언트에서는 단순히 프로시저를 적당한 매개 변수와 함께 호출해주면, 서버에서 프로시저의 정의를 읽어 곧바로 실행하게 된다.

그러므로 프로시저를 사용하면 클라이언트/서버 간 네트워크 트래픽이 줄어들며, 서버에서는 복잡한 쿼리를 모아 관리할 수 있어 그만큼 처리 속도가 빨라진다.

 

2) 프로시저의 장점

 

여러 클라이언트가 업무 규칙을 공유할 수 있다.

네트워크에서 오고가는 긴 SQL 문의 네트워크 트래픽을 줄일 수 있다.