oracle 存储过程
create or replace procedure p_insert_caz_lims as
v_sqlcode TLIMSERRORINFO.ERROR_CODE%type;
v_msg TLIMSERRORINFO.error_msg%type;
begin
insert 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 CAZ_LIMS_ALL_VIEW@DLINK_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;
