批量绑定

/—————–批量绑定——————————————/

create table en (
id number primary key,
name varchar2(100)

);

declare
type id_table_type is table of number
index by binary_integer;
type name_table_type is table of varchar2(100)
index by binary_integer;
v_id id_table_type;
v_name name_table_type ;
start_time number(10);
end_time number(10);
begin
for i in 5001..10000 loop
v_id (i) := i;
v_name (i) :=’name’|| i ;
end loop;
start_time := to_number(dbms_utility.get_time);
for i in 5001..10000 loop
insert into en (id ,name) values(v_id (i),v_name(i));
end loop;
end_time := to_number(dbms_utility.get_time);
dbms_output.put_line(‘总用时为 ’ || (end_time-start_time )/100);

end;

declare
type id_table_type is table of number
index by binary_integer;
type name_table_type is table of varchar2(100)
index by binary_integer;
v_id id_table_type;
v_name name_table_type ;
start_time number(10);
end_time number(10);
begin
for i in 1..5000 loop
v_id (i) := i;
v_name (i) :=’name’|| i ;
end loop;
start_time := to_number(dbms_utility.get_time);
Forall i in 1..v_id.count
insert into en (id ,name) values(v_id (i),v_name(i));
end_time := to_number(dbms_utility.get_time);
dbms_output.put_line(‘总用时为 ’ || (end_time-start_time )/100);
end;

select * from en ;

declare
v_id number(10):=50;
begin
for i in 1..10 loop
dbms_output.put_line( i ||’ ’ );
end loop;
end ;

/indices of 跳过空的 取集合的基数/
–删除1,3
declare
type id_table_type is table of number(10);
v_id id_table_type := id_table_type(1,null,3);
begin
forall i in 1..3
delete from en where id = v_id(i) ;
end;

declare
type id_table_type is table of number(10);
v_id id_table_type := id_table_type(1,null,3);
begin
forall i in indices of v_id —和上面一样 取基数（复数）
delete from en where id = v_id(i) ;
end;

/values of 取集合的下标 /

declare
type id_table_type is table of binary_integer;
v_id id_table_type := id_table_type(2,5,8);
begin
forall i in values of v_id —和上面一样 取集合下标（复数）
delete from en where id = v_id(i) ;
end;

/bulk collect 将查询结果放入集合中 /

declare
type v_table_type is table of en%rowtype;
v_table v_table_type ;
begin
select * bulk collect into v_table from en;

for i in 1..v_table.count loop —和上面一样 取集合下标（复数）
dbms_output.put_line(v_table(i).name || ’ ‘|| v_table(i).id);
end loop;
end;

/* sql%bulk_rowcount */
– 专门为forall 提供的函数，返回第i个元素所影响的行数；