我要问的问题,写在下面的触发器里了,请大家多多指教
create or replace trigger tg_kfs_res
after insert on t_kfsample
for each row
declare
v_asy_time date;
v_goodorbad number;
v_fe2o3 number;
v_sio2 number;
v_al2o3 number;
v_cao number;
v_mgo number;
v_na2o number;
v_k2o number;
v_s number;
v_p number;
v_as number;
v_b number;
v_cr number;
v_cu number;
v_ni number;
v_mo number;
v_co number;
v_v number;
v_sn number;
v_ba number;
v_hg number;
v_count number;
v_delegate varchar2(9); --委托快分码
v_s_fe2o3 number;
v_s_sio2 number;
v_s_al2o3 number;
v_s_cao number;
v_s_mgo number;
v_s_na2o number;
v_s_k2o number;
v_s_s number;
v_s_p number;
v_s_as number;
v_s_b number;
v_s_cr number;
v_s_cu number;
v_s_ni number;
v_s_mo number;
v_s_co number;
v_s_v number;
v_s_sn number;
v_s_ba number;
v_s_hg number;
v_s_count number;
v_s_asy_time date;
v_s_goodorbad number;
v_sample_id varchar2(11);
v_seq varchar2(2);
v_num number;
--myexception exception;
v_s_id varchar2(12);
begin
--首先取出t_kfsample表中的记录
v_s_id :=:new.fd_sample_id;
--为什么执行下面这个查询语句就跑到exception那个地方去了呢,v_s_id 是有值的,而且根据这个v_s_id也是可以查到数据的 ,但是为什么会跳到异常里了呢?请教大家
select count(*) into v_count from t_kfsample where fd_sample_id=v_s_id;
if(v_count>0) then
select fd_analy_time,fd_goodorbad ,fd_Fe2O3,fd_SiO2,fd_Al2O3,
fd_CaO,fd_MgO,fd_Na2O,fd_K2O,fd_S,fd_P,fd_As,fd_B,fd_Cr,
fd_Cu,fd_Ni,fd_Mo,fd_Co,fd_V,fd_Sn,fd_Ba,fd_Hg,fd_sample_id
into v_asy_time, v_goodorbad,v_fe2o3,v_sio2,v_al2o3,
v_cao,v_mgo ,v_na2o,v_k2o ,v_s , v_p, v_as,
v_b,v_cr,v_cu,v_ni,v_mo,v_co,v_v,v_sn,
v_ba,v_hg, v_delegate
from t_kfsample where FD_SAMPLE_ID=v_s_id;
if v_delegate is not null then
--取出t_sample 表中的记录
select count(*) into v_s_count from t_sample where fd_delegate=v_delegate;
if(v_s_count>0) then
select fd_sample_id,fd_analy_time,fd_goodorbad ,fd_Fe2O3,fd_SiO2,fd_Al2O3,
fd_CaO,fd_MgO,fd_Na2O,fd_K2O,fd_S,fd_P,fd_As,fd_B,fd_Cr,
fd_Cu,fd_Ni,fd_Mo,fd_Co,fd_V,fd_Sn,fd_Ba,fd_Hg,fd_seq
into v_sample_id,v_s_asy_time, v_s_goodorbad,v_s_fe2o3,v_s_sio2,v_s_al2o3,
v_s_cao,v_s_mgo ,v_s_na2o,v_s_k2o ,v_s_s , v_s_p, v_s_as,
v_s_b,v_s_cr,v_s_cu,v_s_ni,v_s_mo,v_s_co,v_s_v,v_s_sn,
v_s_ba,v_s_hg,v_seq from t_sample where fd_delegate=v_delegate;
end if;
end if;
else
dbms_output.put_line('没有找到该记录');
end if;
if v_delegate is not null then
v_num:=0;
select count(*)into v_num from t_send_buff where fd_sample_id=v_sample_id and fd_delegate=v_delegate;
if v_num<=0 then
insert into t_send_buff(fd_sample_id,fd_message_id, fd_delegate,fd_seq)
values(v_sample_id,'1B0F52',v_delegate,v_seq);
insert into t_send_buff(fd_sample_id,fd_message_id,fd_delegate,fd_seq)
values(v_sample_id,'1B0H51',v_delegate,v_seq);
end if;
end if;
commit ;
exception
when others then
dbms_output.put_line('错误信息为:'|| sqlcode || ':' || sqlerrm);
end tg_kfsample_insert;
create or replace trigger tg_kfs_res
after insert on t_kfsample
for each row
declare
v_asy_time date;
v_goodorbad number;
v_fe2o3 number;
v_sio2 number;
v_al2o3 number;
v_cao number;
v_mgo number;
v_na2o number;
v_k2o number;
v_s number;
v_p number;
v_as number;
v_b number;
v_cr number;
v_cu number;
v_ni number;
v_mo number;
v_co number;
v_v number;
v_sn number;
v_ba number;
v_hg number;
v_count number;
v_delegate varchar2(9); --委托快分码
v_s_fe2o3 number;
v_s_sio2 number;
v_s_al2o3 number;
v_s_cao number;
v_s_mgo number;
v_s_na2o number;
v_s_k2o number;
v_s_s number;
v_s_p number;
v_s_as number;
v_s_b number;
v_s_cr number;
v_s_cu number;
v_s_ni number;
v_s_mo number;
v_s_co number;
v_s_v number;
v_s_sn number;
v_s_ba number;
v_s_hg number;
v_s_count number;
v_s_asy_time date;
v_s_goodorbad number;
v_sample_id varchar2(11);
v_seq varchar2(2);
v_num number;
--myexception exception;
v_s_id varchar2(12);
begin
--首先取出t_kfsample表中的记录
v_s_id :=:new.fd_sample_id;
--为什么执行下面这个查询语句就跑到exception那个地方去了呢,v_s_id 是有值的,而且根据这个v_s_id也是可以查到数据的 ,但是为什么会跳到异常里了呢?请教大家
select count(*) into v_count from t_kfsample where fd_sample_id=v_s_id;
if(v_count>0) then
select fd_analy_time,fd_goodorbad ,fd_Fe2O3,fd_SiO2,fd_Al2O3,
fd_CaO,fd_MgO,fd_Na2O,fd_K2O,fd_S,fd_P,fd_As,fd_B,fd_Cr,
fd_Cu,fd_Ni,fd_Mo,fd_Co,fd_V,fd_Sn,fd_Ba,fd_Hg,fd_sample_id
into v_asy_time, v_goodorbad,v_fe2o3,v_sio2,v_al2o3,
v_cao,v_mgo ,v_na2o,v_k2o ,v_s , v_p, v_as,
v_b,v_cr,v_cu,v_ni,v_mo,v_co,v_v,v_sn,
v_ba,v_hg, v_delegate
from t_kfsample where FD_SAMPLE_ID=v_s_id;
if v_delegate is not null then
--取出t_sample 表中的记录
select count(*) into v_s_count from t_sample where fd_delegate=v_delegate;
if(v_s_count>0) then
select fd_sample_id,fd_analy_time,fd_goodorbad ,fd_Fe2O3,fd_SiO2,fd_Al2O3,
fd_CaO,fd_MgO,fd_Na2O,fd_K2O,fd_S,fd_P,fd_As,fd_B,fd_Cr,
fd_Cu,fd_Ni,fd_Mo,fd_Co,fd_V,fd_Sn,fd_Ba,fd_Hg,fd_seq
into v_sample_id,v_s_asy_time, v_s_goodorbad,v_s_fe2o3,v_s_sio2,v_s_al2o3,
v_s_cao,v_s_mgo ,v_s_na2o,v_s_k2o ,v_s_s , v_s_p, v_s_as,
v_s_b,v_s_cr,v_s_cu,v_s_ni,v_s_mo,v_s_co,v_s_v,v_s_sn,
v_s_ba,v_s_hg,v_seq from t_sample where fd_delegate=v_delegate;
end if;
end if;
else
dbms_output.put_line('没有找到该记录');
end if;
if v_delegate is not null then
v_num:=0;
select count(*)into v_num from t_send_buff where fd_sample_id=v_sample_id and fd_delegate=v_delegate;
if v_num<=0 then
insert into t_send_buff(fd_sample_id,fd_message_id, fd_delegate,fd_seq)
values(v_sample_id,'1B0F52',v_delegate,v_seq);
insert into t_send_buff(fd_sample_id,fd_message_id,fd_delegate,fd_seq)
values(v_sample_id,'1B0H51',v_delegate,v_seq);
end if;
end if;
commit ;
exception
when others then
dbms_output.put_line('错误信息为:'|| sqlcode || ':' || sqlerrm);
end tg_kfsample_insert;
解决方案 »
- 读取NCLOB字段内容
- 求教:用脚本实现plsql developer的export user objects功能
- 求oracle数据库合理索引的建立
- 关于在ORACLE中增加check()
- 卸载oracle软件的时候会不会把创建的数据库删除
- 请各位朋友帮我解决一下这个删除的问题吧。
- 怎样清空回退段?
- 请问,出现ORA-02074: cannot COMMIT or ROLLBACK in a distributed transaction错误怎样解决?
- 那位告诉我有哪里有Crystal Report的中文用户手册
- 在delphi中连接oracle
- 求高效存储过程或者视图
- db update 的 lock 问题
ORA-06512: 在 "BAOSIGHT.TG_KFS_RES", line 61
ORA-04088: 触发器 'BAOSIGHT.TG_KFS_RES' 执行过程中出错
这是错误信息,不知道大家有没有什么好办法