Batch binding (forall)

1. batch binding means that executing a single SQL operation can transmit data of all set elements, which can greatly speed up data processing and improve program performance, it is completed using the bulk collect clause and forall clause.

2. forall: This clause is only applicable to batch DML operations. Only one statement can be modified. It includes three syntaxes:

A. forall index in lower_bound .. upper_boundsql_statement; for example: 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 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; (like the iterator, traversing the Set) for example: 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 -- where index_table is 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; (traverse the values in the Set) for example: 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 -- the value in index_table is 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: This clause is used to obtain batch data and can only be used in select, fetch, and DML return clauses.

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;