oracle 預存程序

標籤：os   io   ar   amp   sql   type   res   oracle   

create or replace procedure p_insert_caz_lims as v_sqlcode TLIMSERRORINFO.ERROR_CODE%type; v_msg TLIMSERRORINFO.error_msg%type;begininsert into TLIMSERRORINFO (ERROR_ID, error_code, error_msg, error_date, error_program) values (SEQ_TLIMSERRORINFO.nextval, ‘0‘, ‘執行開始‘, sysdate, ‘p_insert_caz_lims‘); declare cursor lims_info is select zzdm, zzmc, cyd, ypmc, fxxm, r_units, qy, to_char(r_value) as r_value, to_char(r_text) as r_text, to_char(cysj) as rtime, to_char(rzsj) as dtime, batch_name, r_out_of_range, mv_range, grade from [email protected]_CAZ.REGRESS.RDBMS.DEV.US.ORACLE.COM where to_date(rzsj, ‘yyyy-mm-dd HH24:mi:ss‘) > trunc(sysdate)-2 and rzsj is not null; limsstop lims_info%rowtype; DATA_COUNT number(10); begin open lims_info; loop fetch lims_info into limsstop; exit when lims_info%NOTFOUND; begin select count(id) into DATA_COUNT from tlimsoriginaldata where rtime = to_date(limsstop.rtime,‘yyyy-mm-dd HH24:mi:ss‘) and dtime = to_date(limsstop.dtime,‘yyyy-mm-dd HH24:mi:ss‘) and locationid = limsstop.zzdm and locationname = limsstop.zzmc and samplingpoint = limsstop.cyd and samplename = limsstop.ypmc and rname = limsstop.fxxm and qy = ‘CAZ‘; if DATA_COUNT = 0 then insert into TLIMSORIGINALDATA (id, locationid, locationname, samplingpoint, samplename, rname, units, qy, rvalue, rtime, dtime, batch_name, rtext, r_out_of_range, mv_range, grade) values (SEQ_TLIMSORIGINALDATA.nextval, limsstop.zzdm, limsstop.zzmc, limsstop.cyd, limsstop.ypmc, limsstop.fxxm, limsstop.r_units, limsstop.qy, limsstop.r_value, to_date(limsstop.rtime, ‘yyyy-mm-dd hh24:mi:ss‘), to_date(limsstop.dtime, ‘yyyy-mm-dd hh24:mi:ss‘), limsstop.batch_name, limsstop.r_text, limsstop.r_out_of_range, limsstop.mv_range, limsstop.grade); end if; end; end loop; close lims_info; insert into TLIMSERRORINFO (ERROR_ID, error_code, error_msg, error_date, error_program) values (SEQ_TLIMSERRORINFO.nextval, ‘1‘, ‘執行成功‘, sysdate, ‘p_insert_caz_lims‘); exception when others then v_sqlcode := sqlcode; v_msg := sqlerrm; insert into TLIMSERRORINFO (ERROR_ID, error_code, error_msg, error_date, error_program) values (SEQ_TLIMSERRORINFO.nextval, v_sqlcode, v_msg, sysdate, ‘p_insert_caz_lims‘); end;end p_insert_caz_lims;