① SELECT문

먼저 SELECT문에 대한 EXECUTE IMMEDIATE문을 적용해 보자.

입력

    BEGIN
      EXECUTE IMMEDIATE 'SELECT employee_id, emp_name, job_id
                   FROM employees WHERE job_id = ''AD_ASST'' ';
    END;

결과

    익명 블록이 완료되었습니다.

오류 메시지가 나오지 않은 것을 보니 성공적으로 실행되었음을 알 수 있다. 위 문장을 자세히 살펴보면, SQL 문장 자체를 따옴표로 묶어 문자열을 만든 후 EXECUTE IMMEDIATE문을 사용했다. 특히 눈여겨 볼 부분은 WHERE 조건이다. JOB_ID가 ‘AD_ASST’인 건을 조회하는데, 여러분도 알다시피 JOB_ID 컬럼 값은 문자형이다. 동적 SQL문은 그 자체가 문자열 형태이므로 작은 따옴표로 감쌌는데, 그 문자열 안에 또 다른 문자값이 들어가 있으므로 이를 구분하기 위해 작은 따옴표를 2개씩 사용한 것이다.

위 문장이 제대로 실행됐는지 확인하려면 SELECT한 결과를 출력해 봐야 한다. 보통 PL/SQL 블록에서는 SELECT INTO를 사용해 결과 값을 변수에 할당했었는데, EXECUTE IMMEDIATE문에서는 다음과 같이 INTO를 사용한다.

입력

    DECLARE
      --출력 변수 선언
      vn_emp_id employees.employee_id%TYPE;
      vs_emp_name employees.emp_name%TYPE;
      vs_job_id employees.job_id%TYPE;
    BEGIN
      EXECUTE IMMEDIATE 'SELECT employee_id, emp_name, job_id
                           FROM employees WHERE job_id = ''AD_ASST'' '
                             INTO vn_emp_id, vs_emp_name, vs_job_id;

      DBMS_OUTPUT.PUT_LINE( 'emp_id : '   || vn_emp_id );
      DBMS_OUTPUT.PUT_LINE( 'emp_name : ' || vs_emp_name );
      DBMS_OUTPUT.PUT_LINE( 'job_id : '   || vs_job_id );
    END;

입력

    emp_id : 200
    emp_name : Jennifer Whalen
    job_id : AD_ASST

위 예제에서는 EXECUTE IMMEDIATE 문에 직접 동적 SQL 문장을 연결했지만, 실제 현장에서는 다음과 같이 SQL문 자체를 문자열 변수에 담아 처리하는 경우가 대부분이다.

입력

    DECLARE
      --출력 변수 선언
      vn_emp_id    employees.employee_id%TYPE;
      vs_emp_name  employees.emp_name%TYPE;
      vs_job_id    employees.job_id%TYPE;

      vs_sql VARCHAR2(1000);
    BEGIN
      -- SQL문을 변수에 담는다.
      vs_sql := 'SELECT employee_id, emp_name, job_id
                    FROM employees WHERE job_id = ''AD_ASST'' ';

    EXECUTE IMMEDIATE vs_sql INTO vn_emp_id, vs_emp_name, vs_job_id;

    DBMS_OUTPUT.PUT_LINE( 'emp_id : '   || vn_emp_id );
    DBMS_OUTPUT.PUT_LINE( 'emp_name : ' || vs_emp_name );
    DBMS_OUTPUT.PUT_LINE( 'job_id : '   || vs_job_id );
    END;

결과

    emp_id : 200
    emp_name : Jennifer Whalen
    job_id : AD_ASST

vs_sql이란 변수를 선언하고 SQL문을 그대로 옮긴 다음, 문자열 형태의 기존 SQL문을 vs_sql 변수에 담아 실행했다. 사실 동적 SQL문을 작성하면서 가장 까다롭고 실수가 많은 부분이 문자열 형태로 SQL문을 만드는 작업이다. 위 예제에서는 SELECT 항목이 3개, WHERE 조건은 1개 뿐이지만 훨씬 더 복잡한 SQL문을 사용하는 경우가 대부분이다. 앞에서 언급했듯이 10개의 조회 조건 중에서 조회할 때마다 선택하는 조건의 수와 해당 조건에 따라 비교할 컬럼, 그리고 입력 값이 매번 달라지므로 사용자가 선택한 조회 조건이 3개든 4개든, 개발자는 10가지 조회 조건 모두에 값이 입력됐을 때를 가정해서 문자열 형태로 SQL문을 작성해야 한다. 이처럼 동적 SQL문은 문자열이 연결된 형태이므로 정적 SQL처럼 한눈에 들어오지 않는다는 단점이 있다. 또한 정적 SQL은 PL/SQL 블록을 컴파일함과 동시에 해당 SQL문의 구문 오류를 검사하지만 동적 SQL은 런타임, 즉 실행 시점에 구문을 검사하고 실행하므로 사전에 오류를 파악하기가 힘들다. 따라서 동적 SQL문을 작성할 때는 위 예제처럼 별도로 분리해서 작성해 변수에 할당하는 것이 집중력도 높일 수 있고 가독성 측면에서도 좋은 방법이다.