03 | 동적 쿼리 디버깅

프로그램을 작성하다 보면 동적 쿼리를 꼭 사용해야 할 때가 종종 있는데, 이번 절에서는 동적 쿼리에 대한 디버깅 기법을 소개하고자 한다. 동적 쿼리라고 해서 지금까지 소개한 디버깅 기법을 사용할 수 없는 것도 아니고 방법상 차이가 있는 것도 아니다. DBMS_OUTPUT.PUT_LINE 프로시저, 소요 시간 출력, 로그 테이블을 이용하는 방법 모두 적용이 가능하다.

하지만 다른 점이 하나 있다. 동적 쿼리는 DML 문장이 문자열로 되어 있다는 점이다. 문장의 길이가 짧다면 문제가 없겠지만 한 문장의 길이가 수십 줄이 넘어간다면 해당 문장이 정확히 어떤 동작을 하는지 파악하기가 힘들어 진다. 게다가 WHERE절 조건이 입력 매개변수의 값에 따라 변경된다면 프로그램을 실행할 때마다 수행되는 문장이 달라지는데 매번 정확히 어떤 문장이 실행됐는지 찾아내기가 매우 힘들다. 지금 어떤 상황을 설명하고 있는지 간단한 예제를 통해 알아 보자.

입력

    CREATE OR REPLACE PROCEDURE ch17_dynamic_test ( p_emp_id   NUMBER,
                                                    p_emp_name VARCHAR2,
                                                    p_job_id   VARCHAR2
                                                  )
    IS
      vs_query    VARCHAR2(1000);
      vn_cnt      NUMBER := 0;
      vs_empname  employees.emp_name%TYPE := '%' || p_emp_name || '%';

    BEGIN
    -- 동적 쿼리 생성, CHR(13)은 줄 바꿈
      vs_query :=             'SELECT COUNT(*) ' || CHR(13);
      vs_query := vs_query || '  FROM employees ' || CHR(13);
      vs_query := vs_query || ' WHERE 1=1 ' || CHR(13);

      -- 사번이 NULL이 아니면 조건 추가
      IF p_emp_id IS NOT NULL THEN
         vs_query := vs_query || ' AND employee_id = ' || p_emp_id || CHR(13);
      END IF;

      -- 사원명이 NULL이 아니면 조건 추가
      IF p_emp_name IS NOT NULL THEN
         vs_query := vs_query || ' AND emp_name like ' || '''' || vs_empname || '''' || CHR(13);
      END IF;
      -- JOB_ID가 NULL이 아니면 조건 추가
      IF p_job_id IS NOT NULL THEN
        vs_query := vs_query || ' AND job_id = ' || '''' || p_job_id || '''' || CHR(13);
      END IF;
      -- 동적 쿼리 실행, 건수는 vn_cnt 변수에 담는다
      EXECUTE IMMEDIATE vs_query INTO vn_cnt;

      DBMS_OUTPUT.PUT_LINE('결과건수 : ' || vn_cnt);
      DBMS_OUTPUT.PUT_LINE(vs_query);

    END;

결과

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