触发器ORA-01858: 在要求输入数字处找到非数字字符
根据自己的错误和解决,总的归类出来啦:
1.我们在插入的类型不匹配,比如数据库里面是数字,我们用日期类型去填充.
2.我们忘数据库中插入的时候,为空的时候不要默认成null,这样数据库就不会出现这个问题啦.
3我花啦几个小时弄出来的问题
create or replace trigger CMS_TRG_MAT_STOR_PRICE
after insert or update
on cms_bdb_mat_erp
for each row
declare
v_mat_sid cms_bdb_mat_erp.sid%type;
v_mat_createdt cms_bdb_mat_erp.created_dt%type;
v_mat_createby cms_bdb_mat_erp.created_by%type;
v_mat_version cms_bdb_mat_erp.version%type;
v_mat_updatedt cms_bdb_mat_erp.updated_dt%type;
v_mat_updateby cms_bdb_mat_erp.updated_by%type;
v_mat_code cms_bdb_mat_erp.mat_code%type;
v_mat_desc cms_bdb_mat_erp.mat_desc%type;
v_mat_price cms_bdb_mat_erp.unit_price%type;
v_mat_count cms_bdb_mat_erp.real_num%type;
v_mat_cost_center_code cms_bdb_mat_erp.cost_center_code%type;
v_mat_cost_center_desc cms_bdb_mat_erp.cost_center_desc%type;
v_mat_code1 cms_bdb_mat_erp.mat_code%type;
v_mat_price1 cms_bdb_mat_erp.UNIT_PRICE%type;
v_mat_count1 cms_bdb_mat_erp.REAL_NUM%type;
begin
if inserting then
v_mat_sid :=:new.sid;
v_mat_createdt :=:new.CREATED_DT;
v_mat_createby :=:new.CREATED_BY;
v_mat_version :=:new.VERSION;
v_mat_updatedt :=:new.UPDATED_DT;
v_mat_updateby :=:new.UPDATED_BY;
v_mat_code :=:new.mat_code;
v_mat_desc :=:new.mat_desc;
v_mat_price :=:new.unit_price;
v_mat_count :=:new.REAL_NUM;
v_mat_cost_center_code :=:new.cost_center_code;
v_mat_cost_center_desc :=:new.cost_center_desc;
merge into CMS_BDB_MAT_STOR t1
using (select v_mat_sid SID,v_mat_createdt CREATED_DT,v_mat_createby CREATED_BY,v_mat_version VERSION,v_mat_updatedt UPDATED_DT, v_mat_updateby UPDATED_BY, v_mat_code MAT_CODE,v_mat_desc MAT_DESC,v_mat_count STOR_COUNT,v_mat_price PRICE,v_mat_cost_center_code COST_CENTER_CODE,v_mat_cost_center_desc COST_CENTER_DESC from dual) t2
on (t1.mat_code = t2.mat_code)
when matched then
update set STOR_COUNT=t1.STOR_COUNT+v_mat_count,PRICE=(v_mat_price*v_mat_count+t1.STOR_COUNT*t1.PRICE)/(v_mat_count+t1.STOR_COUNT)
when not matched then
insert values(t2.sid,t2.created_dt,t2.created_by,t2.version,t2.updated_dt,t2.updated_by,t2.mat_code,t2.mat_desc,t2.stor_count,t2.price,t2.cost_center_code,t2.cost_center_desc);
elsif updating then
v_mat_code1 :=:old.mat_code;
v_mat_price1 :=:old.UNIT_PRICE;
v_mat_count1 :=:old.REAL_NUM;
v_mat_sid :=:new.sid;
v_mat_createdt :=:new.CREATED_DT;
v_mat_createby :=:new.CREATED_BY;
v_mat_version :=:new.VERSION;
v_mat_updatedt :=:new.UPDATED_DT;
v_mat_updateby :=:new.UPDATED_BY;
v_mat_code :=:new.mat_code;
v_mat_desc :=:new.mat_desc;
v_mat_price :=:new.UNIT_PRICE;
v_mat_count :=:new.REAL_NUM;
v_mat_cost_center_code :=:new.cost_center_code;
v_mat_cost_center_desc :=:new.cost_center_desc;
update CMS_BDB_MAT_STOR t3 set STOR_COUNT=t3.STOR_COUNT-v_mat_count1,PRICE=(t3.STOR_COUNT*t3.PRICE-v_mat_price1*v_mat_count1)/(t3.STOR_COUNT-v_mat_count1) where t3.mat_code=v_mat_code1 and ((t3.STOR_COUNT-v_mat_count1)<>0);
delete from CMS_BDB_MAT_STOR t3 where t3.mat_code=v_mat_code1 and ((t3.STOR_COUNT-v_mat_count1)=0);
merge into CMS_BDB_MAT_STOR t1
using (select v_mat_sid SID,v_mat_createdt CREATED_DT,v_mat_createby CREATED_BY,v_mat_version VERSION,v_mat_updatedt UPDATED_DT, v_mat_updateby UPDATED_BY, v_mat_code MAT_CODE,v_mat_desc MAT_DESC,v_mat_price PRICE,v_mat_count STOR_COUNT,v_mat_cost_center_code COST_CENTER_CODE,v_mat_cost_center_desc COST_CENTER_DESC from dual) t2
on (t1.mat_code = t2.mat_code)
when matched then
update set STOR_COUNT=t1.STOR_COUNT+v_mat_count,PRICE=(v_mat_price*v_mat_count+t1.STOR_COUNT*t1.PRICE)/(v_mat_count+t1.STOR_COUNT)
when not matched then
insert values(t2.sid,t2.created_dt,t2.created_by,t2.version,t2.updated_dt,t2.updated_by,t2.mat_code,t2.mat_desc,t2.stor_count,t2.price,t2.cost_center_code,t2.cost_center_desc);
end if;


end CMS_TRI_MAT_STOR_PRICE;
这个是因为原表数据项大于现在的数据项.