[PL/SQL] DBMS_SQL (3) : 응용활용

DBMS_SQL (3) : 응용활용

BULK INSERT & UPDATE

입력할 값을 일반 변수가 아닌 배열 형태에 변수에 담아 놓고 이 변수를 바인드 변수로 연결한다음 DBMS_SQL 패키지로 INSERT문을 실행하면 여러 개의 로우를 한 번에 INSERT 할 수 있다.

TRUNCATE TABLE CH13_PHYSICIST;
 
DECLARE
    VN_IDS_ARRAY    DBMS_SQL.NUMBER_TABLE;
    VS_NAME_ARRAY   DBMS_SQL.VARCHAR2_TABLE;
    VD_DT_ARRAY     DBMS_SQL.DATE_TABLE;
    
    VS_SQL          VARCHAR2(1000);
    
    VN_CUR_ID       NUMBER := DBMS_SQL.OPEN_CURSOR();
    VN_RETURN       NUMBER;
BEGIN
    VN_IDS_ARRAY(1) := 1;
    VS_NAME_ARRAY(1):= 'Galileo Galilei';
    VD_DT_ARRAY(1)  := TO_DATE('1564-02-15', 'YYYY-MM-DD');
    
    VN_IDS_ARRAY(2) := 2;
    VS_NAME_ARRAY(2):= 'Isaac Newton';
    VD_DT_ARRAY(2)  := TO_DATE('1643-01-04', 'YYYY-MM-DD');
    
    VN_IDS_ARRAY(3) := 3;
    VS_NAME_ARRAY(3):= 'Max Plank';
    VD_DT_ARRAY(3)  := TO_DATE('1858-04-23', 'YYYY-MM-DD');
    
    VN_IDS_ARRAY(4) := 4;
    VS_NAME_ARRAY(4):= 'Albert Einstein';
    VD_DT_ARRAY(4)  := TO_DATE('1879-03-14', 'YYYY-MM-DD');    
    
    VS_SQL := 'INSERT INTO CH13_PHYSICIST VALUES (:A, :B, :C)';
    
    DBMS_SQL.PARSE(VN_CUR_ID, VS_SQL, DBMS_SQL.NATIVE);
    
    DBMS_SQL.BIND_ARRAY(VN_CUR_ID, ':A', VN_IDS_ARRAY);
    DBMS_SQL.BIND_ARRAY(VN_CUR_ID, ':B', VS_NAME_ARRAY);
    DBMS_SQL.BIND_ARRAY(VN_CUR_ID, ':C', VD_DT_ARRAY);
    
    VN_RETURN := DBMS_SQL.EXECUTE(VN_CUR_ID);
    
    DBMS_SQL.CLOSE_CURSOR(VN_CUR_ID);
    
    DBMS_OUTPUT.PUT_LINE('결과건수 : ' || VN_RETURN);
    COMMIT;
END;

컬렉션 변수 모두가 DBMS_SQL 패키지에 속한 연관 배열 사용자 정의 타입이다.

바인드 변수 자체가 배열형태이므로 한 번만 실행해도 4개의 행이 입력된것이다.

DECLARE
    VN_IDS_ARRAY    DBMS_SQL.NUMBER_TABLE;
    VS_NAME_ARRAY   DBMS_SQL.VARCHAR2_TABLE;
    
    VS_SQL          VARCHAR2(1000);
    
    VN_CUR_ID       NUMBER := DBMS_SQL.OPEN_CURSOR();
    VN_RETURN       NUMBER;
BEGIN
    VN_IDS_ARRAY(1) := 1;
    VS_NAME_ARRAY(1):= 'Albert Einstein';
 
    VN_IDS_ARRAY(2) := 2;
    VS_NAME_ARRAY(2):= 'Galileo Galilei';
    
    VN_IDS_ARRAY(3) := 3;
    VS_NAME_ARRAY(3):= 'Isaac Newton';
    
    VN_IDS_ARRAY(4) := 4;
    VS_NAME_ARRAY(4):= 'Max Plank';
 
    VS_SQL := 'UPDATE CH13_PHYSICIST SET NAMES = :A WHERE IDS = :B';
    
    DBMS_SQL.PARSE(VN_CUR_ID, VS_SQL, DBMS_SQL.NATIVE);
    
    DBMS_SQL.BIND_ARRAY(VN_CUR_ID, ':A', VS_NAME_ARRAY);
    DBMS_SQL.BIND_ARRAY(VN_CUR_ID, ':B', VN_IDS_ARRAY);
    
    VN_RETURN := DBMS_SQL.EXECUTE(VN_CUR_ID);
    
    DBMS_SQL.CLOSE_CURSOR(VN_CUR_ID);
    DBMS_OUTPUT.PUT_LINE('결과건수 : ' || VN_RETURN);
    COMMIT;
END;

DBMS_SQL.TO_REFCURSOR 함수

DBMS_SQL 패키지로 SELECT 문을 실행해 결과를 받아오려면 각 컬럼을 정의한 뒤 루프를 돌며 패치한 뒤 이전에 정의했던 컬럼값을 변수에 할당 했었지만, TO_REFCURSOR를 사용하면 이 과정을 줄일 수 있다.

TO_REFCURSOR 함수는 파싱되고 실행된 커서를 약한 커서 타입으로 변환하는 함수로, 변환된 약한 커서 타입을 커서 변수로 받을 수 있다.

DBMS_SQL.TO_REFCURSOR
(
    CURSOR_NUMBER   IN OUT  INTEGER
)
RETURN SYS_REFCURSOR;

- CURSOR_NUMBER : 변환할 문장의 커서 ID

- 반환값 : 오라클에서 제공하는 SYS_REFCURSOR 타입

DECLARE
    VC_CUR          SYS_REFCURSOR;
    VA_EMP_ID       DBMS_SQL.NUMBER_TABLE;
    VA_EMP_NAME     DBMS_SQL.VARCHAR2_TABLE;
    
    VS_SQL          VARCHAR2(1000);
    
    VS_JOB          EMPLOYEES.JOB_ID%TYPE := 'SA_REP';
    VN_SAL          EMPLOYEES.SALARY%TYPE := 9000;
    VN_MANAGER      EMPLOYEES.MANAGER_ID%TYPE:= 148;
    
    VN_CUR_ID       NUMBER := DBMS_SQL.OPEN_CURSOR();
    VN_RETURN       NUMBER;
BEGIN
    VS_SQL := 'SELECT   EMPLOYEE_ID, EMP_NAME
               FROM     EMPLOYEES
               WHERE    JOB_ID = :A
               AND      SALARY < :B
               AND      MANAGER_ID = :C';
    
    DBMS_SQL.PARSE(VN_CUR_ID, VS_SQL, DBMS_SQL.NATIVE);
    
    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);
    
    VN_RETURN := DBMS_SQL.EXECUTE(VN_CUR_ID);
    
    VC_CUR := DBMS_SQL.TO_REFCURSOR(VN_CUR_ID);
    
    FETCH VC_CUR BULK COLLECT INTO VA_EMP_ID, VA_EMP_NAME;
    
    FOR IDX IN 1..VA_EMP_ID.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE(VA_EMP_ID(IDX) || ' - ' || VA_EMP_NAME(IDX));
    END LOOP;
    
    CLOSE VC_CUR;
END;

 주의할 점

맨 마지막 커서를 닫는 부분에서 기존에는 DBMS_SQL.CLOSE_CURSOR를 사용했지만 이미 커서 변수(VC_CUR)로 변환을 했기 때문에 닫아야할 커서는 VN_CUR_ID가 아니라 VC_CUR이다.

DBMS_SQL.TO_CURSOR_NUMBER 함수

동적 SQL에서는 NDS든 DBMS_SQL이든 내부적으로는 커서를 사용해 처리되는데 NDS는 커서 타입이 사용되며, DBMS_SQL은 숫자형태인 커서번호가 사용된다.

TO_REFCURSOR와는 반대로 TO_CURSOR_NUMBER는 커서 타입을 커서 번호로 변환한다.

DBMS_SQL.TO_CURSOR_NUMBER
(
    RC  IN OUT  SYS_REFCURSOR
)
RETURN INTEGER;