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가 훨씬 편하다는 점을 알 수 있다.

    신간 소식 구독하기
    뉴스레터에 가입하시고 이메일로 신간 소식을 받아 보세요.