不明白为啥一条一条插入,明明一条语句就能完成,给你写了个demo,未测试 declare cursor a is select b.procinst_id from ws_worksheet_instance_t a, bb_bus_info_t b where a.register_number = b.register_number and a.WORKSHEET_TYPE in(99,803); vn_proc_instance_id bb_bus_info_t.procinst_id%type; begin open a; loop fetch a into vn_proc_instance_id; exit when a %notfound; insert into BB_T2T_UPLOAD_T(XXX) select XXX from rt_actinst where proc_instance_id = vn_proc_instance_id; commit; end loop; close a; end;
我怎么感觉一条SQL就能OK呢? insert into BB_T2T_UPLOAD_T (XXX) select XXX from rt_actinst where proc_instance_id in (select b.procinst_id from ws_worksheet_instance_t a, bb_bus_info_t b where a.register_number = b.register_number and a.WORKSHEET_TYPE in (99, 803));
一条语句的话还是直接连接快一点 insert into BB_T2T_UPLOAD_T (XXX) select c.XXX from ws_worksheet_instance_t a,bb_bus_info_t b, rt_actinst c where a.register_number = b.register_number and b.proc_instance_id=c.procinst_id and a.WORKSHEET_TYPE in (99, 803));
忙看一下这句是什么意思select * into vn_worksheet_instance_info from ws_worksheet_instance_t where register_number = vn_register_no; if vn_worksheet_instance_info.worksheet_type = 99 or vn_worksheet_instance_info.worksheet_type = 803 then select procinst_id into vn_proc_id from bb_bus_info_t where register_number = vs_register_no; INSERT INTO BB_T2T_UPLOAD_T XXX ----替换
select * into vn_worksheet_instance_info from ws_worksheet_instance_t where register_number = vn_register_no; if vn_worksheet_instance_info.worksheet_type = 99 or vn_worksheet_instance_info.worksheet_type = 803 then select procinst_id into vn_proc_id from bb_bus_info_t where register_number = vs_register_no; INSERT INTO BB_T2T_UPLOAD_T XXX ----替换关键第一条select 的register_number 有多条啊,多条数据可以直接into到vn_worksheet_instance_info ???
表 ws_worksheet_instance_t的pick_status为3时(条件worksheet_type in (99,803)),同时记录一张表BB_T2T_UPLOAD_T。表中数据,以rt_actinst表为准,需要注意的是,CURRENT_STATE记录成99,其余字段,都已 rt_actinst为准即可。具体取法如下:select register_number into vn_register_number from ws_worksheet_instance_t select procinst_id into vn_proc_instance_id from bb_bus_info_t where register_number = vn_register_number insert into BB_T2T_UPLOAD_T(XXX) select XXX from rt_actinst where proc_instance_id = vn_proc_instance_id我所修改的过程中根本没有vn_register_numbe和vn_proc_instance_id,我的理解就是最后得到的vn_proc_instance_id去插入数据。烦帮忙看下如何实现。谢谢
declare
cursor a is select b.procinst_id from ws_worksheet_instance_t a, bb_bus_info_t b where a.register_number = b.register_number and a.WORKSHEET_TYPE in(99,803);
vn_proc_instance_id bb_bus_info_t.procinst_id%type;
begin
open a;
loop
fetch a into vn_proc_instance_id;
exit when a %notfound;
insert into BB_T2T_UPLOAD_T(XXX)
select XXX from rt_actinst where proc_instance_id = vn_proc_instance_id;
commit;
end loop;
close a;
end;
insert into BB_T2T_UPLOAD_T
(XXX)
select XXX
from rt_actinst
where proc_instance_id in
(select b.procinst_id
from ws_worksheet_instance_t a, bb_bus_info_t b
where a.register_number = b.register_number
and a.WORKSHEET_TYPE in (99, 803));
insert into BB_T2T_UPLOAD_T (XXX)
select c.XXX from ws_worksheet_instance_t a,bb_bus_info_t b, rt_actinst c
where a.register_number = b.register_number
and b.proc_instance_id=c.procinst_id
and a.WORKSHEET_TYPE in (99, 803));
忙看一下这句是什么意思select * into vn_worksheet_instance_info from ws_worksheet_instance_t where register_number = vn_register_no;
if vn_worksheet_instance_info.worksheet_type = 99 or vn_worksheet_instance_info.worksheet_type = 803 then
select procinst_id into vn_proc_id from bb_bus_info_t where register_number = vs_register_no; INSERT INTO BB_T2T_UPLOAD_T
XXX ----替换
select * into vn_worksheet_instance_info from ws_worksheet_instance_t where register_number = vn_register_no;
if vn_worksheet_instance_info.worksheet_type = 99 or vn_worksheet_instance_info.worksheet_type = 803 then
select procinst_id into vn_proc_id from bb_bus_info_t where register_number = vs_register_no; INSERT INTO BB_T2T_UPLOAD_T
XXX ----替换关键第一条select 的register_number 有多条啊,多条数据可以直接into到vn_worksheet_instance_info ???
表 ws_worksheet_instance_t的pick_status为3时(条件worksheet_type in (99,803)),同时记录一张表BB_T2T_UPLOAD_T。表中数据,以rt_actinst表为准,需要注意的是,CURRENT_STATE记录成99,其余字段,都已 rt_actinst为准即可。具体取法如下:select register_number into vn_register_number from ws_worksheet_instance_t select procinst_id into vn_proc_instance_id from bb_bus_info_t where register_number = vn_register_number insert into BB_T2T_UPLOAD_T(XXX)
select XXX from rt_actinst where proc_instance_id = vn_proc_instance_id我所修改的过程中根本没有vn_register_numbe和vn_proc_instance_id,我的理解就是最后得到的vn_proc_instance_id去插入数据。烦帮忙看下如何实现。谢谢