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 ;