oracle的批量处理操作梳理

好慕2820

　　Bulk Collect批量查询及FORALL批量维护（insert update delete)知识梳理
大批量数据操作时可以显著提高执行效率。
　　采用bulk collect可以将查询结果一次性地加载到collections中，而不是通过cursor一条一条地处理。---同时注意内存空间limit 256;-------  
　　在select into语句中使用bulk collect
在fetch into中使用bulk collect
在returning into中使用bulk collect
　　returning into语句的主要作用是：
delete操作：returning返回的是deleter之前的结果
insert操作:returning返回的是insert之后的结果
update操作:returning语句是返回update之后的结果
注意：
returning into语句不支持insert into select 语句和merge语句
　　forall指的是同时插入，如果使用for循环也可以插入三条记录，但默认returing只显示最后一条
　　DECLARE
    TYPE t_desc_tab IS TABLE OF t1.description%TYPE;//字段类型
    TYPE t_tab IS TABLE OF t1%ROWTYPE;//行类型  处理结果集
   
    /*
    TYPE enum_t IS TABLE OF employees.employee_id%TYPE; 
   TYPE dept_t IS TABLE OF employees.department_id%TYPE; 
    e_ids enum_t; 
    d_ids dept_t; 
    */
   
    l_desc_tab t_desc_tab := t_desc_tab('FIVE', 'SIX', 'SEVEN');//变量定义 进行类型数组的初始化
    l_tab t_tab;//变量定义
BEGIN
  //数组集合的遍历
    FORALL  i IN l_desc_tab.first .. l_desc_tab.last
    INSERT INTO t1
    VALUES (t1_seq.nextval, l_desc_tab(i))
   
    RETURNING id, description BULK COLLECT INTO l_tab;--以行为单位进行填充
    --RETURNING id, description BULK COLLECT INTO e_ids，d_ids;--以列为单位，进行填充
 
    FOR i IN l_tab.first .. l_tab.last LOOP
    DBMS_OUTPUT.put_line('INSERT ID=' || l_tab(i).id || ' DESC=' || l_tab(i).description);
    END LOOP;
    COMMIT;
END;
　　如果更新dml操作影响多条记录可以通过bulk collect into 来提取
使用%BULK_ROWCOUNT返回受影响的记录行数
CREATE TABLE emp_temp AS SELECT * FROM employees; 
DECLARE
   TYPE NumList IS TABLE OF NUMBER; 
   depts NumList := NumList(30, 50, 60); 
BEGIN
   FORALL j IN depts.FIRST..depts.LAST
      DELETE FROM emp_temp WHERE department_id = depts(j); 
-- How many rows were affected by each DELETE statement? 
   FOR i IN depts.FIRST..depts.LAST
   LOOP 
      DBMS_OUTPUT.PUT_LINE('Iteration #' || i || ' deleted ' || 
         SQL%BULK_ROWCOUNT(i) || ' rows.'); 
   END LOOP; 
END; 
　　
--自定义record 
TYPE  my_record  IS RECORD (  
   name varchar2(25), 
   mobile varchar2(20), 
   email  varchar2(50) 
  ）; 
type my_type is table of my_record;　--定义一个数组类型，相当于java中的List 
my_data_set my_type ; --声明我的个性化定义的数据类型。 
　　
集合方法
EXISTS(N)
如果第n个元素存在，返回TRUE
　　COUNT
该函数集合元素的数目
　　DELETE
DELETE(n)
DELETE(n,m)
删除集合元素
l         删除所有元素
l         删除第n个元素
l         删除n到m的元素
　　FIRST
返回集合第一个（最小的）元素索引号，如果集合为空，返回NULL
　　LAST
返回集合中最后一个（最大的）元素索引号，如果集合为空，返回NULL
　　NEXT(n)
返回集合当前元素的下n元素的索引号，如果它不存在就返回NULL
　　PRIOR(n)
返回集合当前元素的前n元素的索引号，如果它不存在就返回NULL
　　LIMIT
返回varray中创建元素的最大个数
　　
EXTEND
EXTEND(n)
EXTEND(n,m)
增加集合的大小。
l         添加一个，设为空
l         添加n个，设为空
l         添加n个，设为m
　　TRIM
TRIM(n)
从集合末尾处删除元素
l         删除一个
l         删除n个
　　
--语法1： 
FORALL 下标变量(只能当作下标被引用) IN 下限..上限 
  sql 语句;    --只允许一条 sql 语句 
 
 
--语法2： 
FORALL 下标变量 IN INDICES OF(跳过没有赋值的元素，例如被 DELETE 的元素，NULL 也算值) 集合 
  [BETWEEN 下限 AND 上限] 
  sql 语句; 
 
 
--语法3： 
FORALL 下标变量 IN VALUES OF 集合(把该集合中的值当作下标，且该集合值的类型只能是 PLS_INTEGER BINARY_INTEGER) 
  sql 语句;