④ 바인드 변수 처리 2

바인드 변수 처리는 그 이름에는 상관없이 순서와 개수, 타입이 중요하다고 했다. 하지만 모든 상황에서 그런 것은 아니다. 지금까지 배웠던 일반 SQL문에서는 맞는 말이지만, 동적 쿼리로 익명 블록이나 PL/SQL 함수, 프로시저를 호출할 때는 변수 이름도 맞춰줘야 한다..

먼저 입력 변수를 출력하는 간단한 프로시저를 하나 만들어 보자.

입력

    CREATE OR REPLACE PROCEDURE ch13_bind_proc1 ( pv_arg1 IN VARCHAR2,
                                                  pn_arg2 IN NUMBER,
                                                  pd_arg3 IN DATE )
    IS
    BEGIN
      DBMS_OUTPUT.PUT_LINE ('pv_arg1 = ' || pv_arg1);
      DBMS_OUTPUT.PUT_LINE ('pn_arg2 = ' || pn_arg2);
      DBMS_OUTPUT.PUT_LINE ('pd_arg3 = ' || pd_arg3);

    END;

결과

    PROCEDURE CH13_BIND_PROC1이(가) 컴파일되었습니다.

이제 프로시저를 통상적인 방법으로, 그리고 동적 SQL을 사용해서 실행해 보자.

입력

    DECLARE
      vs_data1 VARCHAR2(30) := 'Albert Einstein';
      vn_data2 NUMBER := 100;
      vd_data3 DATE   := TO_DATE('1879-03-14', 'YYYY-MM-DD');

      vs_sql   VARCHAR2(1000);
    BEGIN
      -- 프로시저 실행
      ch13_bind_proc1 ( vs_data1, vn_data2, vd_data3);

      DBMS_OUTPUT.PUT_LINE('-----------------------------------');
      -- 동적으로 프로시저 실행
      vs_sql := 'BEGIN ch13_bind_proc1 (:a, :b, :c); END;';

      EXECUTE IMMEDIATE vs_sql USING vs_data1, vn_data2, vd_data3;

    END;

결과

    pv_arg1 = Albert Einstein
    pn_arg2 = 100
    pd_arg3 = 1879-03-14
    -----------------------------------
    pv_arg1 = Albert Einstein
    pn_arg2 = 100
    pd_arg3 = 1879-03-14

일반적인 방법을 통해 실행한 것과 동적 SQL을 사용해 실행한 경우 모두 성공했다. 동적 SQL을 처리한 부분을 자세히 보면, 먼저 프로시저를 실행해야 하기 때문에 동적 SQL은 “BEGIN 프로시저명(:매개변수1, :매개변수2, …); END;’ 형태로 사용해야 한다. 여기서 프로시저의 매개변수를 바인드 변수 처리하는데, 일반 SQL문의 경우와는 달리 매개변수 개수, 매개변수명, 데이터 타입과 순서를 모두 맞춰 줘야 한다. 만약 바인드 변수명을 잘못 기재하면 오류가 발생한다.

입력

    ...
    ...
      -- 바인드 변수명을 잘못 기재한 경우
      vs_sql := 'BEGIN ch13_bind_proc1 (:a, :a, :c); END;';

      EXECUTE IMMEDIATE vs_sql USING vs_data1, vn_data2, vd_data3;
    ...
    ...

결과

    ORA-01006: 바인드 변수가 없습니다.
    ORA-06512: 11행

‘:a, :b, :c’가 아닌 ‘:a, :a, :c’로 기재했더니 바인드 변수가 없다는 오류가 발생했음을 알 수 있다. 일반 SQL문이 아닌 익명 블록이나 프로시저, 함수 등을 호출할 때는 바인드 변수의 이름, 개수, 타입, 순서를 모두 맞춰 줘야 한다는 점을 꼭 명심하도록 하자.

프로시저의 매개변수는 입력(IN), 출력(OUT), 입출력(IN OUT), 종류가 있었다. 입력 외에 출력과 입출력 매개변수는 프로시저를 실행하고 나서 해당 변수에 특정 값을 받아올 수 있는데, 동적 SQL로 프로시저를 호출했을 때에도 출력과 입출력 변수의 값을 받아올 수 있다. 먼저 출력 변수를 가진 프로시저를 하나 만들어 보자.

입력

    CREATE OR REPLACE PROCEDURE ch13_bind_proc2 ( pv_arg1 IN     VARCHAR2,
                                                  pv_arg2 OUT    VARCHAR2,
                                                  pv_arg3 IN OUT VARCHAR2 )

    IS
    BEGIN
      DBMS_OUTPUT.PUT_LINE ('pv_arg1 = ' || pv_arg1);

      pv_arg2 := '두 번째 OUT 변수';      -- 두 번째 출력 변수에 값 설정
      pv_arg3 := '세 번째 INOUT 변수';    -- 세 번째 입출력 변수에 값 설정
    END;

결과

    PROCEDURE CH13_BIND_PROC2이(가) 컴파일되었습니다.

이제 앞의 프로시저를 실행하는 익명 블록을 만들어 보자.

입력

    DECLARE
      vs_data1 VARCHAR2(30) := 'Albert Einstein';
      vs_data2 VARCHAR2(30);
      vs_data3 VARCHAR2(30);

      vs_sql   VARCHAR2(1000);
    BEGIN
      -- 바인드 변수
      vs_sql := 'BEGIN ch13_bind_proc2 (:a, :b, :c); END;';
      -- 출력, 입출력 변수를 받아오기 위해 OUT, IN OUT 명시
      EXECUTE IMMEDIATE vs_sql USING vs_data1, OUT vs_data2, IN OUT vs_data3;

      DBMS_OUTPUT.PUT_LINE ('vs_data2 = ' || vs_data2);
      DBMS_OUTPUT.PUT_LINE ('vs_data3 = ' || vs_data3);
    END;

결과

    pv_arg1 = Albert Einstein
    vs_data2 = 두 번째 OUT 변수
    vs_data3 = 세 번째 INOUT 변수

EXECUTE IMMEDIATE문에서 바인드 변수는 USING 뒤에 순서대로 명시하는데, 기본 값은 IN으로 생략이 가능하며 출력 및 입출력 변수는 각각 OUT과 IN OUT을 명시하면 된다. 따라서 위 블록의 경우 두, 세 번째 매개변수에 해당하는 vs_data2와 vs_data3에 각각ch13_bind_proc2 프로시저에서 설정한 값을 제대로 받아왔음을 알 수 있다.