--语法1:
FORALL 下标变量(只能当作下标被引用) IN 下限..上限
sql 语句; --只允许一条 sql 语句
--语法2:
FORALL 下标变量 IN INDICES OF(跳过没有赋值的元素,例如被 DELETE 的元素,NULL 也算值) 集合
[BETWEEN 下限 AND 上限]
sql 语句;
--语法3:
FORALL 下标变量 IN VALUES OF 集合(把该集合中的值当作下标,且该集合值的类型只能是 PLS_INTEGER BINARY_INTEGER)
sql 语句;
create table tb1(
id number(5),
name varchar2(50)
);
语法1演示:
--批量插入演示
declare
type tb_table_type is table of tb1%rowtype
index by binary_integer;
tb_table tb_table_type;
begin
for i in 1..10 loop
tb_table(i).id:=i;
tb_table(i).name:='NAME'||i;
end loop;
forall i in 1..tb_table.count
insert into tb1 values tb_table(i);
end;
--批量修改演示
declare
type tb_table_type is table of tb1%rowtype
index by binary_integer;
tb_table tb_table_type;
begin
for i in 1..10 loop
tb_table(i).id:=i;
tb_table(i).name:='NAMES'||i;
end loop;
forall i in 1..tb_table.count
update tb1 t set row = tb_table(i) where t.id = tb_table(i).id;
end;
--批量删除演示
declare
type tb_table_type is table of tb1%rowtype
index by binary_integer;
tb_table tb_table_type;
begin
for i in 1..10 loop
tb_table(i).id:=i;
tb_table(i).name:='NAMES'||i;
end loop;
forall i in 1..tb_table.count
delete tb1 where id = tb_table(i).id;
end;
语法2演示:
select * from tb1;
declare
type demo_table_type is table of demo%rowtype
index by binary_integer;
demo_table demo_table_type;
begin
for i in 1..10 loop
demo_table(i).id:=i;
demo_table(i).name:='NAME'||i;
end loop;
demo_table.delete(3);
demo_table.delete(6);
demo_table.delete(9);
forall i in indices of demo_table
insert into demo values demo_table(i);
end;
select * from demo;
1 1 NAME1
2 2 NAME2
3 4 NAME4
4 5 NAME5
5 7 NAME7
6 8 NAME8
7 10 NAME10
语法3演示:
declare
type index_poniter_type is table of pls_integer;
index_poniter index_poniter_type;
type demo_table_type is table of demo%rowtype
index by binary_integer;
demo_table demo_table_type;
begin
index_poniter:=index_poniter_type(1,3,5,7);
for i in 1..10 loop
demo_table(i).id:=i;
demo_table(i).name:='NAME'||i;
end loop;
forall i in values of index_poniter
insert into demo values demo_table(i);
end;
select * from demo;
1 1 NAME1
2 3 NAME3
3 5 NAME5
4 7 NAME7
-----------------------------------------------------------------------------------------------------------------------------------------
FOR i IN l_exploder.first .. l_exploder.last LOOP
l_exploder(i).supply_org_id := get_corss_orgid(p_plan_category => l_exploder(i)
.category_segment,
p_organization_id => l_exploder(i).oi,
p_item_id => l_exploder(i).cid);
--虚拟件引起上层
/* IF l_exploder(i)
.xnj_level > 0 AND l_exploder(i).ass_item_type = '虚拟件物料' THEN*/
IF l_exploder(i).xnj_level > 0
AND l_exploder(i).ass_item_type = g_ph THEN
--modified by sery 20170822
l_exploder(i).ac_parent_item_id := nvl(l_exploder(i)
.ac_parent_item_id,
l_exploder(i).pid);
ELSE
l_exploder(i).ac_parent_item_id := l_exploder(i).pid;
END IF;
IF nvl(l_exploder(i).supply_org_id, -1) <> l_exploder(i).oi THEN
BEGIN
SELECT ppc.category_segment
INTO l_exploder(i).category_segment
FROM ps_plan_category_v ppc
WHERE ppc.organization_id = l_exploder(i).supply_org_id
AND ppc.inventory_item_id = l_exploder(i).cid;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
--重新计算共享件标志
/* if instr(nvl(l_exploder(i).category_segment, 'XXXX'),
'跟单件',
1) > 0 OR l_exploder(i).item_type = '虚拟件物料' THEN*/
IF instr(nvl(l_exploder(i).category_segment, 'XXXX'), g_dm, 1) > 0
OR l_exploder(i).item_type = g_ph THEN
--modified by sery 20170822
l_exploder(i).gxj_flag := nvl(l_exploder(i).parent_gxj_flag,
'N');
ELSE
l_exploder(i).gxj_flag := 'Y';
END IF;
END IF;
--added by jimmy.xu at 2017.08.02 end
--added by jimmy.xu at 2017.09.04 begin
-- l_exploder(i).ac_parent_item_id
l_exploder(i).parent_relation := l_exploder(i)
.parent_relation || '-' || l_exploder(i).cid;
/*if cur_level =1 then
l_exploder(i).parent_relation:=l_exploder(i).ac_parent_item_id;
else
l_exploder(i).parent_relation:=l_exploder(i).parent_relation||'->'||l_exploder(i).ac_parent_item_id;
end if;*/
END LOOP;