----------------------------------------------
임시 테이블 생성
----------------------------------------------
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;
임시 테이블 생성
----------------------------------------------
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;
댓글 0
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
126 | test''' ' '' '' ''''""" '''''''' | 구퍼 | 2011.03.07 | 4314 |
125 | 테이블 열항목을 행항목으로 변경하는 팁 | 구퍼 | 2011.01.11 | 8675 |
124 | 오라클 DB 링크 만들기 | 구퍼 | 2011.01.06 | 6510 |
123 | select -> update 구문 샘플 | 구퍼 | 2010.01.21 | 5696 |
122 | Instant Client 사용법 | 구퍼 | 2009.12.30 | 5894 |
121 | row데이터 연결하여 조회 | 구퍼 | 2009.03.31 | 3663 |
120 | 데이타에 한글로 공백이 있는 경우 값을 가져오지 못하고 오류가 발생하는 경우 처리 | 구퍼 | 2009.03.23 | 5584 |
119 | mssql update예제(select ~ update) | 구퍼 | 2008.10.31 | 6115 |
118 | Update ~ Select 구문 예제 | 구퍼 | 2008.10.31 | 4432 |
117 | oracle 10g tnsnames.ora, listener.ora위치 | 구퍼 | 2008.09.29 | 4312 |
116 | Conditions | 구퍼 | 2008.09.17 | 5544 |
115 | Basic Delete Statements | 구퍼 | 2008.09.17 | 4557 |
114 | Basic Update Statements | 구퍼 | 2008.09.17 | 4527 |
113 | 문자열 연결, 날짜 변환, 오늘 구하기 | 구퍼 | 2008.09.10 | 4275 |
112 | 현재의 년월일시분초 구하기 | 구퍼 | 2008.09.10 | 5549 |
111 | 분산트랜젝션 가능여부 확인 방법 | 하늘과컴 | 2008.05.29 | 4960 |
110 | 순환구조에서 임시테이블에 하위 조직을 엮어서 넣는 프로시져 | 하늘과컴 | 2007.10.01 | 5204 |
109 | 최단거리찾기 혹은 멀리 떨어진 두지점간의 거리의 합구하기 | 박상현 | 2006.11.07 | 3608 |
» | PL/SQL에서 쿼리시 Array Processing 처리방법 | 박상현 | 2006.10.27 | 3469 |
107 | 시간선분(선분이력)의 합집합 구하기 | 박상현 | 2006.06.13 | 4931 |