oracle 10g 当月天数,oracle 10g values of 和indices of 初次接触

values of 和indices of是oracle 10g的新特性。在批量处理中提高性能，适用于处理嵌套集合，稀疏集合时，以避免丢失记录异常和创建多余副本。

在使用时，

forall  var    invalues of collection 的 collection定义需要为var的类型。

forall var inindices of collection 的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_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;