在使用时，
forall var in values of collection 的 collection定义需要为var的类型。
forall var inindices ofcollection 的collection定义可以使其他对象，这里的var相当于collection的下标
create or replace procedure p_product_price_ex(p_company varchar2) is
type t_product_id is table of product.product_id%type index by pls_integer;
type t_yyyy is table of product.yyyy%type index by pls_integer;
--type t_price is table of product.price%type index by pls_integer;
TYPE t_partions IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
v_product_id t_product_id;
v_yyyy t_yyyy;
--v_price t_price;
v_down_list t_partions;
v_up_list t_partions;
procedure p_retrive_datas is
v_sql varchar2(256);
begin
select p.product_id, p.yyyy bulk collect
into v_product_id, v_yyyy
from product_1 p
where p.company = p_company;
end;
procedure p_partition_datas is
begin
for i in v_product_id.first .. v_product_id.last loop
if mod(v_product_id(i), 2) = 0 then
v_down_list(i) := i;
else
v_up_list(i) := i;
end if;
end loop;
end;
procedure p_product_price_down is
begin
forall j in values of v_down_list
insert into product_down (product_id) values (v_product_id(j));
commit;
end;
procedure p_product_price_up is
begin
forall k in values of v_up_list
insert into product_up (product_id) values (v_product_id(k));
commit;
end;
begin
p_retrive_datas;
p_partition_datas;
p_product_price_down;
p_product_price_up;
end;
create or replace procedure p_product_price_ex2(p_company varchar2) is
type t_product_id is table of product.product_id%type index by pls_integer;
type t_yyyy is table of product.yyyy%type index by pls_integer;
v_product_id t_product_id;
v_yyyy t_yyyy;
TYPE guide_aat IS TABLE OF BOOLEAN INDEX BY PLS_INTEGER;
v_down_list guide_aat;
v_up_list guide_aat;
procedure p_retrive_datas is
v_sql varchar2(256);
begin
select p.product_id, p.yyyy bulk collect
into v_product_id, v_yyyy
from product_1 p
where p.company = p_company;
end;
procedure p_partition_datas is
begin
for i in v_product_id.first .. v_product_id.last loop
if mod(v_product_id(i), 2) = 0 then
v_down_list(i) := TRUE;
else
v_up_list(i) := TRUE;
end if;
end loop;
end;
procedure p_product_price_down is
begin
forall j in INDICES of v_down_list
insert into product_down (product_id) values (v_product_id(j));
commit;
end;
procedure p_product_price_up is
begin
forall k in INDICES of v_up_list
insert into product_u
p (product_id) values (v_product_id(k));
commit;
end;
begin
p_retrive_datas;
p_partition_datas;
p_product_price_down;
p_product_price_up;
end;下载本文
