create sequence BPO.SEQ_ZJSJ_ID
minvalue 1
maxvalue 9999999999999999
start with 341
increment by 1
cache 20;
create or replace procedure bpo.pro_zjsj(p_login_log_id in
number,p_ywfp_id in varchar2) is
v_id number(16);
v_sjzt char(1):='3';
--v_login_log_id number(10);
v_lrrq date;
v_xgzt varchar2(10):='0';
v_xgnr varchar2(200):='';
begin
select seq_zjsj_id.nextval into v_id from dual;
select sysdate into v_lrrq from dual;
for c in (
select ywfp_id,param1, param2, param3, param4,
param5, param6, param7, param8, param9,
param10, ywzb_id, tpmc
from
(
select ywfp_id,param1, param2, param3, param4, param5, param6, param7,
param8, param9, param10,
ywzb_id, tpmc from ylsj where
ywfp_id=p_ywfp_id and sjzt='1'
intersect
select ywfp_id,param1, param2, param3, param4, param5, param6, param7,
param8, param9, param10, ywzb_id, tpmc from elsj
where ywfp_id=p_ywfp_id and sjzt='1'
) lin
) loop
insert into zjsj
(id, sjzt, login_log_id,
ywfp_id, param1, param2, param3, param4, param5, param6, param7,
param8, param9, param10, lrrq, tpmc, xgzt, xgnr, ywzb_id)
values
(v_id, v_sjzt, p_login_log_id,
c.ywfp_id, c.param1, c.param2, c.param3, c.param4, c.param5,
c.param6, c.param7, c.param8, c.param9,
c.param10, v_lrrq, c.tpmc, v_xgzt, v_xgnr,
c.ywzb_id);
update ylsj set sjzt='3'
where ywzb_id=c.ywzb_id;
update elsj set sjzt='3'
where ywzb_id=c.ywzb_id;
end loop;
commit;
end pro_zjsj;