oracle中INTECER,ORACLE 序列 存储过程 SYSDATE 实例

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;