oracle学习笔记(第十十四章:批量绑定)

-- 使用批量绑定来执行 DELETE
DECLARE
TYPE NumList IS VARRAY(20) OF char(5) ;-- varray
v_emp_no NumList := NumList('00001', '00002', '00003'); -- department numbers
BEGIN
FOR i IN v_emp_no.FIRST..v_emp_no.LAST loop
DELETE FROM emp_d
WHERE emp_no = v_emp_no(i);
END LOOP;
END;
--
DECLARE
TYPE NumList IS VARRAY(20) OF VARCHAR2(20);-- varray
v_emp_no NumList := NumList('DNS', 'SAL', 'BOM'); -- department numbers
BEGIN
FOR i IN v_emp_no.FIRST..v_emp_no.LAST loop
DELETE FROM DEPT
WHERE dept_name = v_emp_no(i);
END LOOP;
END;
-- 使用批量绑定来执行 insert
DECLARE
TYPE v_type_1 IS TABLE OF test.col_1%TYPE INDEX BY BINARY_INTEGER;
TYPE v_type_2 IS TABLE OF test.col_2%TYPE INDEX BY BINARY_INTEGER;
v_tab_1 v_type_1;
v_tab_2 v_type_2;
BEGIN
FOR i IN 1..1000 LOOP—- 不使用批量绑定
v_tab_1(i) :=lpad(to_char(i),4,'0');
v_tab_2(i) :=dbms_random.string('l',5);
END LOOP;
FORALL i IN 1..1000-- 使用批量绑定
INSERT INTO test VALUES(v_tab_1(i),v_tab_2(i));
END;
-- 使用批量绑定的查询 select
DECLARE
TYPE v_type_1 IS TABLE OF test.col_1%TYPE INDEX BY BINARY_INTEGER;
TYPE v_type_2 IS TABLE OF test.col_2%TYPE INDEX BY BINARY_INTEGER;
v_tab_1 v_type_1;
v_tab_2 v_type_2;
BEGIN
SELECT col_1,col_2 BULK COLLECT INTO v_tab_1,v_tab_2 FROM test;
FOR i IN 1..v_tab_1.count LOOP
dbms_output.put_line(v_tab_1(i)||'--'||v_tab_2(i));
END LOOP;
END;
-- 使用批量绑定与 Cursor
DECLARE
TYPE v_type_1 IS TABLE OF test.col_1%TYPE INDEX BY BINARY_INTEGER;
TYPE v_type_2 IS TABLE OF test.col_2%TYPE INDEX BY BINARY_INTEGER;
v_tab_1 v_type_1;
v_tab_2 v_type_2;
CURSOR cur IS SELECT col_1,col_2 FROM test;
BEGIN
OPEN cur;
FETCH cur BULK COLLECT INTO v_tab_1,v_tab_2;
FOR i IN 1..v_tab_1.count LOOP
dbms_output.put_line(v_tab_1(i));
END LOOP;
END;
-- 使用批量绑定到 记录数组 version 9i
DECLARE
TYPE v_rec_tab IS TABLE OF styletype%ROWTYPE ;
--TYPE v_rec IS RECORD(col_1 CHAR(4),col_2 CHAR(5));
--TYPE v_rec_tab IS TABLE OF v_rec INDEX BY BINARY_INTEGER;
v_rectab v_rec_tab;
CURSOR cur IS SELECT fact_no, styletype_no, styletype_nm ,hkstyletype_no, hkstyletype_nm FROM styletype;
BEGIN
OPEN cur;
FETCH cur BULK COLLECT INTO v_rectab;
END ;
-- 限制返回的行数
DECLARE
TYPE v_type_1 IS TABLE OF test.col_1%TYPE INDEX BY BINARY_INTEGER;
TYPE v_type_2 IS TABLE OF test.col_2%TYPE INDEX BY BINARY_INTEGER;
v_tab_1 v_type_1;
v_tab_2 v_type_2;
CURSOR cur IS SELECT col_1,col_2 FROM test;
n NUMBER:=100;
BEGIN
OPEN cur;
FETCH cur BULK COLLECT INTO v_tab_1,v_tab_2 LIMIT n;
FOR i IN 1..v_tab_1.count LOOP
dbms_output.put_line(v_tab_1(i));
END LOOP;
END;
-- INSERT, UPDATE, or DELETE 的批量绑定 returning
DECLARE
TYPE v_type_1 IS TABLE OF test.col_1%TYPE INDEX BY BINARY_INTEGER;
TYPE v_type_2 IS TABLE OF test.col_2%TYPE INDEX BY BINARY_INTEGER;
v_tab_1 v_type_1;
v_tab_2 v_type_2;
CURSOR cur IS SELECT col_1,col_2 FROM test;
BEGIN
DELETE FROM test RETURNING col_1 BULK COLLECT INTO v_tab_1;
FOR i IN 1..v_tab_1.count LOOP
dbms_output.put_line(v_tab_1(i));
END LOOP;
END ;