有2张表table1和table2
table1字段a1,b1,c1
table2字段a2,b2,c2
现在要写一个存储过程,将table1的数据插入到table2里面。有个条件,如果a1=a2那么就更新table2里面b2字段(b2=b1),如果(a1!=a2)执行插入操作create or replace procedure kkk(e_flag out varchar2)
is
cursor cur1 is select a1,b1 from table1;
cursor cur2 is select a2,b2 from table2;
begin
for rec1 in cur1 loop
e_flag:='OK';
for rec2 in cur2 loop
if rec1.a1 = rec2.a2 then
update table2 set b2=rec1.b1 where a2=rec2.a2;
commit;
e_flag:='update';
else
insert into table2(b2) values(rec1.b1);
commit;
e_flag:='insert';
end if;
end loop;--结束cur2循环
end loop;--结束cur1的循环
exception
when others then
e_flag := 'Error(kkk):[操作失败]';
end;这是我写的代码。有问题,不知道问题出在那里。能帮我修改一下吗?
table1字段a1,b1,c1
table2字段a2,b2,c2
现在要写一个存储过程,将table1的数据插入到table2里面。有个条件,如果a1=a2那么就更新table2里面b2字段(b2=b1),如果(a1!=a2)执行插入操作create or replace procedure kkk(e_flag out varchar2)
is
cursor cur1 is select a1,b1 from table1;
cursor cur2 is select a2,b2 from table2;
begin
for rec1 in cur1 loop
e_flag:='OK';
for rec2 in cur2 loop
if rec1.a1 = rec2.a2 then
update table2 set b2=rec1.b1 where a2=rec2.a2;
commit;
e_flag:='update';
else
insert into table2(b2) values(rec1.b1);
commit;
e_flag:='insert';
end if;
end loop;--结束cur2循环
end loop;--结束cur1的循环
exception
when others then
e_flag := 'Error(kkk):[操作失败]';
end;这是我写的代码。有问题,不知道问题出在那里。能帮我修改一下吗?
MERGE INTO table2 B
USING table1 A
ON (A.a1 = B.a2)
WHEN MATCHED THEN
UPDATE SET B.b2 = A.b1
WHEN NOT MATCHED THEN
INSERT (B.b2)
VALUES (A.b1)
不知道你哪里出错,是不会调用带out型参数的过程吗?也可以用dbms_output.put_line()
而且e_flag也显示的'Error(kkk):[操作失败]'
SQL> select * from test1; A1 B1 C1
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
SQL> select * from test2; A2 B2 C2
---------- ---------- ----------
1 11 1
3 33 3
4 44 4
CREATE OR REPLACE procedure kkk(e_flag out varchar2)
is
v_cnt number;
cursor cur1 is select a1,b1 from test1;
begin
for rec1 in cur1 loop
e_flag:='OK';
select count(*) into v_cnt from test2
where a2=rec1.a1; if v_cnt>0 then
update test2 set b2=rec1.b1 where a2=rec1.a1;
commit;
e_flag:='update';
else
insert into test2(b2) values(rec1.b1);
commit;
e_flag:='insert';
end if;
end loop;
exception
when others then
e_flag := 'Error(kkk):[操作失败]';
end;
/SQL> var aaa varchar2(100)SQL> exec kkk(:aaa)
PL/SQL 过程已成功完成。SQL> select * from test2; A2 B2 C2
---------- ---------- ----------
2
1 1 1
3 3 3
4 44 4
结贴