Question

oracle存储过程将表1的数据插入表2，俩张表的结构一样，要用到游标

表的结构如图
要求:
先取biz_src=‘091000’的 并且 update_time中时间最新的，如果关联不到party_id的，再取biz_src<>‘091000’的 并且 update_time中时间最新的,party_id是外键。

Answer 1

create procedure xxx
as
begin
execute immediate
'
insert into table2
select * from (
select * from (
select t.*,row_number() over(partition by t.name order by t.id desc) as order_id from test_insert t
where t.party_id not in (select party_id from &foreign_table)
and biz_src='091000'
)
where t.order_id =1
union all
select * from (
select t.*,row_number() over(partition by t.name order by t.id desc) as order_id from test_insert t
where t.party_id in (select party_id from &foreign_table)
and biz_src<>'091000'
)
where t.order_id =1
)
'
commit;
end;

&foreign_table,你要手动改下关联表的名字，我不知道

Answer 2

推荐楼主学习下merge

追问

谢谢！但领导要求这样的。。请各位大仙，，帮帮忙吧，这些之前没接触过！

追答

你这2部分关联到的和关联不到的都是插入吗？

追问

这个是只有这两种情况，一种是关联到的取biz_src=‘091000’的 并且 update_time中时间最新的需要插入，另一种如果关联不到party_id的，再取biz_src‘091000’的 并且 update_time中时间最新的,需要插入

追答

cursor cur_a as select t.*,row_number() over(partition by t.party_id order by t.udate_time desc) as order_id from a;
for row_a in cur_a
loop
select count(*) into n_cnt from a;
if (n_cnt > 0 and row_a.biz_src = '091000' and row_a.order_id = 1)
then insert
else if (n_cnt = 0 and row_a.biz_src '091000' and row_a.order_id = 1)
then insert
end if;

end loop
这部分伪代码供参考~

Answer 3

建议楼主学习一下Merge,给你个链接。
http://blog.csdn.net/nsj820/article/details/5755685

追问

嗯！谢谢！有机会我会学下的。。