DBMS_SQL 기본 활용

이전 절에서 NDS를 사용해 처리했던 동적 SQL을 DBMS_SQL을 사용하는 방식으로 변환해 볼 것이다.

① SELECT문

먼저 사원 테이블에서 job_id, salary, manager_id 값을 매개변수로 넘겨 조회했던 동적 SQL을 DBMS_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);

      -- 바인드 변수 선언과 값 설정
      vs_job      employees.job_id%TYPE := 'SA_REP';
      vn_sal      employees.salary%TYPE := 7000;
      vn_manager  employees.manager_id%TYPE := 148;

      -- DBMS_SQL 패키지 관련 변수
      vn_cur_id   NUMBER := DBMS_SQL.OPEN_CURSOR(); -- 커서를 연다
      vn_return   NUMBER;
    BEGIN
      -- SQL문을 변수에 담기(바인드 변수 앞에 :를 붙인다).
      vs_sql := 'SELECT employee_id, emp_name, job_id
                   FROM employees
                  WHERE job_id = :a
                    AND salary < :b
                    AND manager_id = :c ';

      -- 2. 파싱
      DBMS_SQL.PARSE (vn_cur_id, vs_sql, DBMS_SQL.NATIVE);

      -- 3. 바인드 변수 연결(WHERE 절에 사용한 변수가 3개이므로 각 변수별로 총 3회 호출)
      DBMS_SQL.BIND_VARIABLE ( vn_cur_id, ':a', vs_job);
      DBMS_SQL.BIND_VARIABLE ( vn_cur_id, ':b', vn_sal);
      DBMS_SQL.BIND_VARIABLE ( vn_cur_id, ':c', vn_manager);

      -- 4. 결과 선택 컬럼 정의(사번, 사원명, job_id 총 3개의 컬럼을 선택했으므로 각각 순서대로 호출)
      -- SELECT 순서에 따라 순번을 맞추고 결과를 담을 변수와 연결
      DBMS_SQL.DEFINE_COLUMN ( vn_cur_id, 1, vn_emp_id);
      DBMS_SQL.DEFINE_COLUMN ( vn_cur_id, 2, vs_emp_name, 80); --문자형은 크기까지 지정
      DBMS_SQL.DEFINE_COLUMN ( vn_cur_id, 3, vs_job_id, 10);

      -- 5. 쿼리 실행
      vn_return := DBMS_SQL.EXECUTE (vn_cur_id);

      -- 6. 결과 패치
      LOOP
        -- 결과 건수가 없으면 루프를 빠져 나간다.
        IF DBMS_SQL.FETCH_ROWS (vn_cur_id) = 0 THEN
           EXIT;
        END IF;

        -- 7. 패치된 결과 값 받아오기
        DBMS_SQL.COLUMN_VALUE ( vn_cur_id, 1, vn_emp_id);
        DBMS_SQL.COLUMN_VALUE ( vn_cur_id, 2, vs_emp_name);
        DBMS_SQL.COLUMN_VALUE ( vn_cur_id, 3, 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 LOOP;

      -- 8. 커서 닫기
        DBMS_SQL.CLOSE_CURSOR (vn_cur_id);

    END;

결과

    emp_id : 173
    emp_name :Sundita Kumar
    job_id : SA_REP

NDS를 사용했을 때와 같은 결과가 나오긴 했지만 총 8단계를 거쳐 코드가 상당히 길어졌다. 이 예제를 보면 DBMS_SQL 보다는 NDS가 훨씬 편하다는 점을 알 수 있다.