求个oracle存储过程:
有一条查询语句,如:
select a,b,c,d,e,f,g
from temp
查询出的数据记录有很多,我想通过a是否为空值进行判断,
如果a是空值,那么我对temp1,temp2进行插入操作,如:
insert into temp1(a1,a2,a3) values(a,b,c)
insert into temp2(b1,b2,b3) values(a,f,g)
如果a不是空值,那么我对temp1,temp2进行修改操作,如:
update temp1 set a2 = b,a3 = c where a1 = a
update temp2 set b2 = f,b3 = g where b1 = a
请问大家这个该怎么写?
有一条查询语句,如:
select a,b,c,d,e,f,g
from temp
查询出的数据记录有很多,我想通过a是否为空值进行判断,
如果a是空值,那么我对temp1,temp2进行插入操作,如:
insert into temp1(a1,a2,a3) values(a,b,c)
insert into temp2(b1,b2,b3) values(a,f,g)
如果a不是空值,那么我对temp1,temp2进行修改操作,如:
update temp1 set a2 = b,a3 = c where a1 = a
update temp2 set b2 = f,b3 = g where b1 = a
请问大家这个该怎么写?
as
v1 temp.a%type;
v2 temp.b%type;
v3 temp.c%type;
v4 temp.d%type;
v5 temp.e%type;
v6 temp.f%type;
v7 temp.g%type;
cursor c is select a,b,c,d,e,f,g from temp;
begin
for c_rec in c loop
v1:=c_rec.a;
v2:=c_rec.b;
v3:=c_rec.c;
v4:=c_rec.d;
v5:=c_rec.e;
v6:=c_rec.f;
v7:=c_rec.g;
if v1='' then
insert into temp1(a1,a2,a3) values(v1,v2,v3);
insert into temp2(b1,b2,b3) values(v1,v6,v7);
commit;
else
update temp1 set a2 =v2,a3 =v3 where a1 =v1;
update temp2 set b2 =v6,b3 =v7 where b1 =v1;
commit;
end if;
end loop;
end;
BEGIN
INSERT ALL INTO temp1
(a1, a2, a3)
VALUES
(a, b, c) INTO temp2
(b1, b2, b3)
VALUES
(a, f, g)
SELECT a, b, c, d, e, f, g FROM temp WHERE a IS NULL; MERGE INTO temp1 a
USING temp b
ON (a.a1 = b.a)
WHEN MATCHED THEN
UPDATE SET a.a2 = b.b, a.a3 = b.c; MERGE INTO temp2 a
USING temp b
ON (a.b1 = b.a)
WHEN MATCHED THEN
UPDATE SET a.b2 = b.f, a.b3 = b.g;
END;
另外无特殊需求 不用手工commit
(老兵新手)
支持!insert all 和merge into两个方法太帅了