[Oracle] EXECUTE IMMEDIATE 동적 쿼리 주의사항

오라클 데이터베이스에서는 EXECUTE IMMEDIATE 문장을 통해 dynamic SQL, 즉 동적 쿼리를 사용하는 기능을 제공하고 있습니다. EXECUTE IMMEDIATE의 간단한 사용 예시와 함께 주의사항에 대해 알아보도록 합시다.

EXECUTE IMMEDIATE

동적 쿼리를 사용하는 기본 문장입니다. 사용 방법은 크게 3가지로 나누어 볼 수 있습니다.

EXECUTE IMMEDIATE dynamic string

동적 쿼리를 가장 기본적으로 사용하는 방법입니다. 주의사항은 모든 문자열이 변수가 아닐 때, 단일 인용부는 아포스트로피(')를 2번 입력하여야 합니다. 아포스트로피의 첫 번 째 부분은 이스케이프와 같이 작동합니다. 다음 예제는 인용부를 잘못 사용하여 [ ORA-00984: column not allowed here ] 오류가 발생하는 예제 자료입니다.

BEGIN
        EXECUTE IMMEDIATE 'INSERT INTO USER_NAME.TABLE_NAME (COLUMN_NAME) VALUES ('||'A'||') ';
     END;
     /
BEGIN

ERROR at line 1:
ORA-00984: column not allowed here
ORA-06512: at line 2

아포스트로피를 제대로 사용하여 오류를 해결한 예제자료입니다. VALUES 구문에 아포스트로피가 추가된 것을 확인할 수 있습니다.

  • ('||'A'||') => 오류
  • ('''||'A'||''') => 정상
BEGIN
        EXECUTE IMMEDIATE 'INSERT INTO USER_NAME.TABLE_NAME (COLUMN_NAME) VALUES ('''||'A'||''') ';
     END;
     /

PL/SQL procedure successfully completed.

EXECUTE IMMEDIATE INTO

EXECUTE IMMEDIATE INTO 구문은 SELECT INTO와 같이 동적쿼리의 결과를 변수에 설정하는 구문입니다. INTO를 사용할 때에 주의할 점은, SELECT INTO와 마찬가지로 데이터가 없거나 많은 수의 행을 추출하는 경우에 오류가 발생하는 것입니다.

주의하여 사용하지 않을 경우, 다음과 같은 두 가지 오류, [ ORA-01403: no data found ]와 [ ORA-01422: exact fetch returns more than requested number of rows ] 예외가 발생합니다. 해당 오류는 PL/SQL 사용 중에 자주 접할 수 있는 예외 중 하나이기도 합니다.

ORA-01403: no data found

데이터가 하나도 없는 경우에 발생하는 예외입니다.

DECLARE
    COL   VARCHAR2 (32767);
 BEGIN
    EXECUTE IMMEDIATE ' SELECT COLUMN_NAME FROM USER_NAME.TABLE_NAME WHERE 0 = 1 ' INTO COL;
 END;
 /

ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4

ORA-01422: exact fetch returns more than requested number of rows

조건이 단일 행을 보장하지 않는 경우에 발생하는 예외입니다. 물론, 다음의 예제에서는 행이 1개밖에 없었다면 오류가 발생하지는 않았을 것입니다. 하지만 그건 극도로 특이한 상황을 고려했다는 것을 알고 계시죠?

DECLARE
    COL   VARCHAR2 (32767);
 BEGIN
    EXECUTE IMMEDIATE ' SELECT COLUMN_NAME FROM USER_NAME.TABLE_NAME WHERE 0 = 0 ' INTO COL;
 END;
 /

ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4

EXECUTE IMMEDIATE USING

동적 쿼리는 USING 구문을 통해 바인딩을 할 수 있습니다. 바인딩 시에는 바인딩 컬럼 명과 관계없이 순서대로 바인딩이 되는 점을 주의해 주세요. 다음 예제는 바인딩과 컬럼 이름을 동일하게 하였으나, 실제 데이터는 순차적으로 입력이 됩니다.

  • 컬럼 순서 => COLUMN_NAME, COLUMN_NAME_2
  • 바인딩 순서 => :COLUMN_NAME_2, :COLUMN_NAME
  • 바인딩 변수는 명칭보다 순서가 중요합니다.
DECLARE
    TABLENAME   VARCHAR2 (32767);
 BEGIN
    TABLENAME := 'USER_NAME.TABLE_NAME';

    EXECUTE IMMEDIATE 'INSERT INTO  ' || TABLENAME || ' (COLUMN_NAME, COLUMN_NAME_2) VALUES (:COLUMN_NAME_2, :COLUMN_NAME) '
       USING '1', '2';
 END;
 /

PL/SQL procedure successfully completed.

조회를 하면 바인딩 변수 이름이 :COLUMN_NAME_2부터 시작하지만, 실제 값은 COLUMN_NAME 컬럼에 '1' 값이 들어갑니다. 그래서 보통 동적쿼리 바인딩은 :1, :2, ..., :N 과 같이 순차적으로 표시하는 경우가 많습니다. 반면, 보통 애플리케이션은 바인딩컬럼명과 일치하는 경우 위치가 다르더라도 문제가 없는 경우가 있습니다. 개발 환경과 언어를 교차하다 보면 발생하는 실수 중 하나입니다.

SELECT * FROM USER_NAME.TABLE_NAME

COLUMN_NAME COLUMN_NAME_2
-------------------------
1           2

EXECUTE IMMEDIATE INTO USING

동적쿼리는 INTO와 USING을 동시에 사용하여, 변수 할당과 바인딩을 동시에 할 수 있습니다. 이때 주의사항은, EXECUTE IMMEDIATE INTO 구문을 단독으로 사용할 때와, EXECUTE IMMEDIATE USING 구문을 단독으로 사용할 때에 주의하여야 할 점을 합친 것과 동일합니다.

EXECUTE IMMEDIATE RETURN

EXECUTE IMMEDIATE RETURN 구문을 통하여, SELECT INTO 뿐만 아니라 INSERT, UPDATE와 같은 DML과 함께 컬럼을 조회할 수 있습니다. 사용하는 것을 그리 많이 본 적은 없습니다만, 적어도 이러한 사용법이 있다는 것은 간단히 알아가면 도움이 많이 될 것입니다. 다음 예제는 :1과 :2를 바인딩하여 인서트 하고, :3과 :4 바인딩 값에 컬럼2, 컬럼1을 조회하도록 하였습니다. EXECUTE IMMEDIATE RETURN은 BULK COLLECT 구문과도 같이 사용할 수 있습니다.

SET SERVEROUTPUT ON

 DECLARE
    SQL_STMT   VARCHAR2 (1000);
    COL1       VARCHAR2 (1000) := '7';
    COL2       VARCHAR2 (1000);
    RTN2       VARCHAR2 (1000);
    RTN1       VARCHAR2 (1000);
 BEGIN
    COL2 := '9';
    SQL_STMT := 'INSERT INTO USER_NAME.TABLE_NAME(COLUMN_NAME, COLUMN_NAME_2) VALUES (:1, :2) RETURNING COLUMN_NAME_2, COLUMN_NAME INTO :3, :4';

    EXECUTE IMMEDIATE SQL_STMT USING COL1, COL2 RETURNING INTO RTN2, RTN1;

    DBMS_OUTPUT.PUT_LINE (RTN2 || ' / ' || RTN1);
 END;
 /

--DBMS_OUTPUT.PUT_LINE (RTN2 || ' / ' || RTN1);
9 / 7

PL/SQL procedure successfully completed.

댓글

댓글 쓰기