批量绑定(FORALL)
来源:互联网 发布:中国最新cpi数据 编辑:程序博客网 时间:2024/05/22 12:09
1.批量绑定是指执行单次SQL操作能传递所有集合元素的数据,可以极大的加快数据处理速度,提高程序的性能,它是使用Bulk Collect子句和ForALL子句来完成的。
2.ForAll:该子句只适用于执行批量的DML操作,只能修饰一条语句,它包括3种语法:
a.ForAll index in lower_bound..upper_boundsql_statement;例如:create table my_demo( mid number(6) primary key, mname varchar2(50));declare type my_index_table1 is table of my_demo.mid%type index by binary_integer; type my_index_table2 is table of my_demo.mname%type index by binary_integer; my_id my_index_table1; my_name my_index_table2; start_time number(10); end_time number(10);begin for i in 1..1000 loop my_id(i):=i; my_name(i):=to_char(i)||'NAME'; end loop; start_time:=dbms_utility.get_time; forall i in 1..1000 insert into My_DEMO values(my_id(i),my_name(i)); end_time:=dbms_utility.get_time; dbms_output.put_line(end_time-start_time);end;b.ForAll index in indices of collection[Between lower_bound.and. upper.bound]sql_statement;(和迭代器一样,遍历集合)例如:declare type id_table_type is table of demo%rowtype index by binary_integer; id_table id_table_type; type index_table_type is table of Pls_Integer index by binary_integer; index_table index_table_type;begin select object_id,object_name bulk collect into id_table from all_objects where rownum<=10; index_table(1):=1; index_table(2):=2; index_table(4):=6; index_table(6):=8; index_table(8):=10; forall i in indices of index_table--其中index_table中是1,2,4,6,8 insert into demo(did) values(id_table(i).did);--id_table(1,2,4,6,8) end; c.ForAll index in values of index_collectionsql_statement;(遍历集合里面的值)例如:declare type id_table_type is table of demo%rowtype index by binary_integer; id_table id_table_type; type index_table_type is table of PLS_Integer index by binary_integer; index_table index_table_type;begin select object_id,object_name bulk collect into id_table from all_objects where rownum<=10; index_table(1):=1; index_table(2):=2; index_table(4):=6; index_table(6):=8; index_table(8):=10; forall i in values of index_table--其中index_table中的值是1,2,6,8,10 insert into demo(did) values(id_table(i).did);--id_table(1,2,6,8,10) end;
3.Bulk Collect:该子句用于取得批量数据,只能用于Select、Fetch和DML返回子句中。
declare type eaa is record( empno scott.emp.empno%type, ename scott.emp.ename%type, sal scott.emp.sal%type ); type emp_table_type is table of scott.emp%rowtype index by binary_integer; emp_table emp_table_type; type aa is table of scott.emp%rowtype; a aa; type bb is varray(1000) of scott.emp%rowtype; b bb; begin select * bulk collect into emp_table from scott.emp; for i in 1..emp_table.count loop dbms_output.put_line(emp_table(i).ename); end loop; dbms_output.new_line; select * bulk collect into b from scott.emp; for i in 1..b.count loop dbms_output.put_line(b(i).ename); end loop;end;
- 批量绑定(FORALL)
- ORACLE 批量绑定 FORALL 与 BULK COLLECT
- oracle10g批量绑定forall bulk collect
- ORACLE批量绑定FORALL与BULK COLLECT
- ORACLE批量绑定FORALL与BULK COLLECT
- ORACLE批量绑定FORALL与BULK COLLECT
- ORACLE批量绑定FORALL与BULK COLLECT
- ORACLE批量绑定FORALL与BULK COLLECT
- ORACLE批量绑定FORALL与BULK COLLECT
- ORACLE批量绑定FORALL与BULK COLLECT
- ORACLE批量绑定FORALL与BULK COLLECT
- oracle批量绑定 forall bulk collect用法以及测试案例
- Oracle10g批量绑定FORALL字句的增强功能
- oracle批量绑定 forall bulk collect用法以及测试案例
- ORACLE批量绑定FORALL与BULK COLLECT【转】
- oracle批量绑定 forall bulk collect用法以及测试案例
- Oracle批量绑定FORALL字句的增强功能
- 批量绑定(bulk binds):FOR循环与FORALL的性能比较
- 将button按钮绘制成圆形按钮,做状态灯指示(winform)
- Windows下Oracle 11G - ODAC 11.2 Release 3 安装配置
- android 实现电话录音(窃听)
- string迭代器入门
- 浅谈GridView
- 批量绑定(FORALL)
- C# 实现CRC校验代码
- 茅台成分红王 去年净赚87.6亿拟派现41.5亿红包
- 【Linux 驱动】第六章 高级字符驱动程序操作 ----ioctl
- 把dataset中的数据更新回 数据库
- beagleboard 启动 android 内核
- 基于RSA算法实现软件注册码原理初讨
- java 内存中 栈 和 堆的区别
- 函数内临时变量的释放时刻