ch17_dyquery 테이블의 첫 번째 컬럼에는 프로그램명, 두 번째 컬럼에는 동적 쿼리 본문이 들어갈 것이다. ch17_dynamic_test 프로시저를 수정해 ch17_dyquery 테이블에 쿼리 텍스트를 넣는 루틴을 추가해 보자.

입력

    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
      -- 동적 쿼리 생성
      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);

      -- 기존 데이터를 모두 삭제한다
      DELETE ch17_dyquery;

      -- 쿼리 구문을 ch17_dyquery에 넣는다.
      INSERT INTO ch17_dyquery (program_name, query_text)
      VALUES ( 'ch17_dynamic_test', vs_query);

      COMMIT;
    END;

결과

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

프로시저를 실행해 보자.

입력

    EXEC ch17_dynamic_test (NULL, 'Jon', 'SA_REP' );

결과

    결과건수 : 1