[oracle강좌] 오라클 / PL-SQL 에서 쿼리시 Array Processing

PL/SQL에서 쿼리시 Array Processing 처리방법  

----------------------------------------------
임시 테이블 생성
----------------------------------------------

connect scott/tiger;

DROP TABLE bb;

CREATE TABLE bb
(
empno NUMBER,
empname VARCHAR2(10)
);



----------------------------------------------
풀이
----------------------------------------------

DECLARE
v_array_size CONSTANT INTEGER := 100;
v_empno DBMS_SQL.NUMBER_TABLE;
v_empname DBMS_SQL.VARCHAR2_TABLE;

v_cur_qry INTEGER;
v_rtn_qry INTEGER;
v_fetch_cnt INTEGER;
v_SelectStmt VARCHAR2(2000);
v_InsertStmt VARCHAR2(2000);

c NUMBER;
dummy NUMBER;


begin
v_cur_qry := DBMS_SQL.OPEN_CURSOR;

v_SelectStmt := 'select empno, ename from emp';

DBMS_SQL.PARSE(v_cur_qry, v_SelectStmt, DBMS_SQL.V7);

DBMS_SQL.DEFINE_ARRAY(v_cur_qry, 1, v_empno, v_array_size, 1);
DBMS_SQL.DEFINE_ARRAY(v_cur_qry, 2, v_empname, v_array_size, 1);

v_rtn_qry := DBMS_SQL.EXECUTE(v_cur_qry);

loop
v_fetch_cnt := DBMS_SQL.FETCH_ROWS(v_cur_qry);

DBMS_SQL.COLUMN_VALUE(v_cur_qry, 1, v_empno);
DBMS_SQL.COLUMN_VALUE(v_cur_qry, 2, v_empname);
if v_fetch_cnt = 0 or v_fetch_cnt < v_array_size then
exit;
end if;
end loop;


/********************INSERT 처리부분*************************************/

-- Bulk Insert 이용 !

v_InsertStmt := 'insert into bb values (:num_array, :name_array)';

c:= DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(c, v_InsertStmt, DBMS_SQL.native);

DBMS_SQL.BIND_ARRAY(c, ':num_array', v_empno);
DBMS_SQL.BIND_ARRAY(c, ':name_array', v_empname);

dummy := DBMS_SQL.EXECUTE(c);
DBMS_SQL.CLOSE_CURSOR(c);

/************************************************************************/

DBMS_SQL.CLOSE_CURSOR(v_cur_qry);
end sp_array;
/



----------------------------------------------
참고자료
----------------------------------------------

/* ------------------------------
벌크 바인딩 이용하기
------------------------------ */

1) Bulk Binding 이란?
PL/SQL의 벌크바인딩은 오라클8i의 새로운 기능이다.
벌크바인딩을 사용하면 모음의 항목에 따라 루프를 도는 PL/SQL 코드를 사용하지 않고도
모음에 있는 모든 항목에 작동하는 SQL 문을 작성할 수 있다.
SQL에서 PL/SQL로 전환하는 것(데이터를 페치해서 배열에 추가하는 것)을 "문맥전환
(context switch)"이라고 하며 이 과정에서 상당한 오버헤드가 소모된다.
하지만 벌크바인딩 기능을 사용하면 이런 오버헤드를 상당히 줄일수있다.

2) BULK COLLECT 사용하기 : select 문과 함께 사용

==> FETCH 문에 BULK COLLECT 예약어를 사용하여 커서에 의해 선택된 모든 데이터를 배열로
읽어들인다.
이 방법이 PL/SQL 루프를 사용해서 한번에 한행씩 페치하는 것보다 훨씬 빠르다.

DECLARE
CURSOR all_depts IS
SELECT deptno, dname
FROM dept
ORDER BY dname;

TYPE dept_id IS TABLE OF dept.deptno%TYPE;
TYPE dept_name IS TABLE OF dept.dname%TYPE;

dept_ids dept_id;
dept_names dept_name;
inx1 PLS_INTEGER;

v_InsertStmt VARCHAR2(2000);

BEGIN
OPEN all_depts;
FETCH all_depts BULK COLLECT INTO dept_ids, dept_names;
CLOSE all_depts;

/** Load한 데이터의 변형 **/

FOR inx1 IN 1..dept_ids.count LOOP
dept_names(inx1) := UPPER(dept_names(inx1) || '+');
DBMS_OUTPUT.PUT_LINE (dept_ids(inx1) || ' ' || dept_names(inx1));
END LOOP;

v_InsertStmt := 'UPDATE dept
SET dname = :1
WHERE deptno = :2';

FOR x IN dept_ids.first..dept_ids.last LOOP
EXECUTE IMMEDIATE v_InsertStmt USING IN dept_names(x), dept_ids(x);
END LOOP;

END;
/


3) FORALL 사용하기 : insert, update, delete 문과 사용

==> FORALL을 사용할 때, 문장은 모음의 각 항목에 대해 한번씩 실행된다.
하지만 PL/SQL에서 SQL로의 문맥전환은 오직 한번만 일어난다.
그 결과 PL/SQL에서 루프를 작성할 때보다 성능이 훨씬 빨라진다.

DECLARE
CURSOR all_depts IS
SELECT deptno, dname
FROM dept
ORDER BY dname;

TYPE dept_id IS TABLE OF dept.deptno%TYPE;
TYPE dept_name IS TABLE OF dept.dname%TYPE;

dept_ids dept_id;
dept_names dept_name;
inx1 PLS_INTEGER;

v_InsertStmt VARCHAR2(2000);

BEGIN
OPEN all_depts;
FETCH all_depts BULK COLLECT INTO dept_ids, dept_names;
CLOSE all_depts;

/** Load한 데이터의 변형 **/

FOR inx1 IN 1..dept_ids.count LOOP
dept_names(inx1) := UPPER(dept_names(inx1) || '+');
DBMS_OUTPUT.PUT_LINE (dept_ids(inx1) || ' ' || dept_names(inx1));
END LOOP;

FORALL x IN dept_ids.first..dept_ids.last
UPDATE dept
SET dname = dept_names(x)
WHERE deptno = dept_ids(x);

END;
/