DBMS_SQL 응용 활용
① BULK INSERT & UPDATE
INSERT 문을 실행하는 예제에서 바인드 변수를 사용해 한 번에 한 로우씩 INSERT를 수행했는데 그 대신 여러 개의 로우를 한 번에 입력할 수 있는 방법이 있다. 어떻게 이런 일이 가능하냐면 입력할 값을 일반 변수가 아닌 배열 형태의 변수, 즉 컬렉션 변수에 담아 놓고 이 변수를 바인드 변수로 연결한 다음 DBMS_SQL 패키지로 해당 INSERT문을 실행하면 된다. DBMS_SQL 패키지의 BIND_VARIABLE 대신 BIND_ARRAY 프로시저를 사용하는 점만 제외하면 나머지는 같은 방식으로 처리할 수 있다. 다시 ch13_physicist 테이블을 깨끗이 비우고 4건의 데이터를 한 번에 입력해 보자.
입력
TRUNCATE TABLE ch13_physicist;
결과
table CH13_PHYSICIST이(가) 잘렸습니다.
입력
DECLARE
-- DBMS_SQL 패키지에서 제공하는 컬렉션 타입 변수 선언
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);
-- DBMS_SQL 패키지 관련 변수
vn_cur_id NUMBER := DBMS_SQL.OPEN_CURSOR(); -- 1.커서를 연다
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');
-- INSERT문 작성
vs_sql := 'INSERT INTO ch13_physicist VALUES (:a, :b, :c)';
-- 2. 파싱
DBMS_SQL.PARSE (vn_cur_id, vs_sql, DBMS_SQL.NATIVE);
-- 3. 바인드 변수 연결 (BIND_VARIABLE 대신 BIND_ARRAY 사용)
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);
-- 4. 쿼리 실행
vn_return := DBMS_SQL.EXECUTE (vn_cur_id);
-- 5. 커서 닫기
DBMS_SQL.CLOSE_CURSOR (vn_cur_id);
--결과 건수 출력
DBMS_OUTPUT.PUT_LINE('결과건수: ' || vn_return);
COMMIT;
END;
결과
결과건수: 4
위 예제소스를 차근차근 분석해 보자. 먼저 컬렉션 변수를 선언했는데 각각의 타입이 DBMS_SQL.VARCHAR2_TABLE, DBMS_SQL.NUMBER_TABLE, DBMS_SQL.DATE_TABLE이다. 이들 모두 DBMS_SQL 패키지에 속한 컬렉션, 정확히 말해 연관 배열 사용자 정의 타입이며, 이 외에도 DBMS_SQL 패키지에는 다양한 컬렉션 타입과 레코드 타입을 지원하고 있다. 그리고 나서 각 컬렉션 변수에 총 4개씩 값을 할당한 다음, BIND_ARRAY 프로시저로 컬렉션 변수와 INSERT문의 바인드 변수를 연결한 뒤 실행했다. 바인드 변수 자체가 배열 형태이므로 한 번만 실행했는데도 실제로는 총 4개의 행이 입력된 것이다.
INSERT문 뿐만 아니라 UPDATE와 DELETE 문에서도 BIND_ARRAY 프로시저로 한 번에 여러 개의 데이터를 처리할 수 있다.
입력
DECLARE
-- DBMS_SQL 패키지에서 제공하는 컬렉션 타입 변수 선언
vn_ids_array DBMS_SQL.NUMBER_TABLE;
vs_name_array DBMS_SQL.VARCHAR2_TABLE;
vs_sql VARCHAR2(1000);
-- DBMS_SQL 패키지 관련 변수
vn_cur_id NUMBER := DBMS_SQL.OPEN_CURSOR(); -- 1. 커서를 연다
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';
-- UPDATE문 작성
vs_sql := 'UPDATE ch13_physicist SET names = :a WHERE ids = :b';
-- 2. 파싱
DBMS_SQL.PARSE (vn_cur_id, vs_sql, DBMS_SQL.NATIVE);
-- 3. 바인드 변수 연결(BIND_VARIABLE 대신 BIND_ARRAY 사용)
DBMS_SQL.BIND_ARRAY ( vn_cur_id, ':a', vs_name_array);
DBMS_SQL.BIND_ARRAY ( vn_cur_id, ':b', vn_ids_array);
-- 4. 쿼리 실행
vn_return := DBMS_SQL.EXECUTE (vn_cur_id);
-- 5. 커서 닫기
DBMS_SQL.CLOSE_CURSOR (vn_cur_id);
--결과 건수 출력
DBMS_OUTPUT.PUT_LINE('결과건수: ' || vn_return);
COMMIT;
END;
결과
결과건수: 4
이번 예제에서는 ch13_physicist 테이블에서 ids 값에 따른 names 값을 변경하는 UPDATE문을 실행하고 있는데, SET과 WHERE 절에 각각 이름과 번호를 가진 컬렉션 변수를 연결했다. 해당 테이블을 선택해 보면 ids와 names 값이 컬렉션 변수에 담은 순서와 쌍에 맞춰 변경됐음을 알 수 있다.
입력
SELECT ids, names
FROM ch13_physicist;
결과
IDS NAMES
--- -------------------
1 Albert Einstein
2 Galileo Galilei
3 Isaac Newton
4 Max Plank