[ Oracle - Procedure BULK BIND(BULK COLLECT INTO문) ]
소스가 간단해지고, 처리속도가 빨라짐
커서를 사용한 예)
DECLARE
// 변수선언
TYPE RECTYPE IS RECORD (
CODE TAB1.CODE%TYPE,
NAME TAB1.NAME %TYPE);
TYPE DATATYPE IS TABLE OF RECTYPE;
DATA DATATYPE := DATATYPE(NULL);
I PLS_INTEGER := 1;
// 커서선언
CURSOR C1 IS SELECT CODE, NAME FROM TAB1;
BEGIN
FOR REC IN C1 LOOP
DATA.EXTEND;
DATA(I).CODE := REC.CODE;
DATA(I).NAME := REC.NAME;
I := I + 1;
END LOOP;
END;
/
경과시간 : 00:00:13.37
BULK COLLECT INTO문을 사용한 예)
DECLARE
// 변수선언
TYPE RECTYPE IS RECORD (
CODE TAB1.CODE%TYPE,
NAME TAB1.NAME %TYPE(;
TYPE DATATYPE IS TABLE OF RECTYPE;
DATA DATATYPE := DATATYPE(NULL);
BEGIN
SELECT CODE, NAME
BULK COLLECT INTO DATA
FROM TAB1;
END;
/
경과시간 : 00:00:01.54
|
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;
/
|
SELECTS문에서 한번에 대량의 레코들 취득 하는 경우, BULK COLLECT구를 사용하면
한번에 여러개의 레코드를 취득할수 있으므로 퍼포먼스 향상
Patten 1
-------------------------------------------------------------------------------
DECLARE
TYPE empno_tbl_type IS TABLE OF EMP.EMPNO%TYPE INDEX BY BINARY_INTEGER;
empno_tbl empno_tbl_type;
BEGIN
SELECT EMPNO BULK COLLECT INTO empno_tbl FROM EMP;
IF empno_tbl.COUNT > 0 THEN
FOR i IN empno_tbl.FIRST..empno_tbl.LAST LOOP
UPDATE EMP SET SAL = SAL * 1.05 WHERE EMPNO = empno_tbl( i );
END LOOP;
END IF;
END;
/
Patten 2
-------------------------------------------------------------------------------
DECLARE
TYPE emp_tbl_type IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER;
emp_tbl emp_tbl_type;
BEGIN
SELECT * BULK COLLECT INTO emp_tbl FROM EMP;
IF emp_tbl.COUNT > 0 THEN
FOR i IN emp_tbl.FIRST..emp_tbl.LAST LOOP
UPDATE EMP SET SAL = SAL * 1.05 WHERE EMPNO = emp_tbl( i ).EMPNO;
END LOOP;
END IF;
END;
/
Patten 3 커서 이용
-------------------------------------------------------------------------------
DECLARE
CURSOR emp_cur IS
SELECT * FROM EMP;
TYPE emp_tbl_type IS TABLE OF emp_cur%ROWTYPE INDEX BY BINARY_INTEGER;
emp_tbl emp_tbl_type;
BEGIN
OPEN emp_cur;
FETCH emp_cur BULK COLLECT INTO emp_tbl;
CLOSE emp_cur;
IF emp_tbl.COUNT > 0 THEN
FOR i IN emp_tbl.FIRST..emp_tbl.LAST LOOP
UPDATE EMP SET SAL = SAL * 1.05 WHERE EMPNO = emp_tbl( i ).EMPNO;
END LOOP;
END IF;
END;
/
즉,커서를 이용할시 취득할 데이터 수가 많을듯하면 Limit를 사용하여 일정 레코드 단위로
Fetch하는 것이 성능면에서 좋다.
FETCH emp_cur BULK COLLECT INTO emp_tbl LIMIT 100;
