因为没有选择到记录,所以v_bz为null,trim一个null的值会出现这样的问题。
begin
select BZ into v_bz from BFHSYSZSYS where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh;
exception when no_data_found then
null;
end;
v_bz:=nvl(v_bz,null,trim(v_bz);
begin
select BZ into v_bz from BFHSYSZSYS where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh;
exception when no_data_found then
null;
end;
v_bz:=nvl(v_bz,null,trim(v_bz);
(m_bgbh in varchar2,
m_ypph in number,
m_yybh in varchar2)
AS
qhsm number(7,2);
hsm number(7,2);
shsm number(7,2);
jhsm number(7,2);
qzsm number(7,2);
zsm number(7,2);
zhsm number(7,2);
heism number(7,2);
yysd1m number(7,2);
yysd2m number(7,2);
v_cwmc varchar2(7);
v_yxms varchar2(60);
v_bz varchar2(100);
v_count number(7,2);
v_jh varchar2(20);
v_yylb varchar2(10);
v_csrq date;
TI number(7,2);
v_TI varchar2(10);
begin
select QHS,HS,SHS,JHS,QZS,ZS,ZHS,HEIS,YYSD1,YYSD2,CWMC,YXMS,BZ,JH,YYLB,CSRQ into qhsm,hsm,shsm,jhsm,qzsm,zsm,zhsm,heism,yysd1m,yysd2m,v_cwmc,v_yxms,v_bz,v_jh,v_yylb,v_csrq
from BFHSYSZSYS where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh;
if qhsm is null then
Update BFHSYSZSYS set QHS=0 where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh;
end if;
if hsm is null then
Update BFHSYSZSYS set HS=0 where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh;
end if;
if shsm is null then
Update BFHSYSZSYS set SHS=0 where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh;
end if;
if jhsm is null then
Update BFHSYSZSYS set JHS=0 where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh;
end if;
if qzsm is null then
Update BFHSYSZSYS set QZS=0 where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh;
end if;
if zsm is null then
Update BFHSYSZSYS set ZS=0 where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh;
end if;
if zhsm is null then
Update BFHSYSZSYS set ZHS=0 where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh;
end if;
if heism is null then
Update BFHSYSZSYS set HEIS=0 where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh;
end if;
if qhsm=0 and hsm=0 and shsm=0 and jhsm=0 and qzsm=0 and zsm=0 and zhsm=0 and heism=0 then
Update BFHSYSZSYS set BZ='化石少' where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh;
else
Update BFHSYSZSYS set BZ=' ' where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh;(这一句是赋值BZ为空格,可以吗?各位)
end if; select trim(BZ) into v_bz from BFHSYSZSYS where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh;(请问这句话如果BZ为空格的话,那么去掉空格v_bz就为null吗?? /* v_bz:=trim(v_bz);*/
if length(v_bz)=0 or v_bz is null then
v_count:=qhsm+hsm+shsm+qzsm+zsm+zhsm+heism;
TI:=(qhsm+hsm*2+shsm*2.5+jhsm*3+qzsm*3.5+zsm*4+zhsm*5+heism*6)/v_count;
Update BFHSYSZSYS set HEIS=TI where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh;(这句赋值没有错误)
v_TI:=to_char(TI);(这句转化有问题)
Update BFHSYSZSYS set BZ=v_TI where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh; (赋值出错)
else
Update BFHSYSZSYS set BZ='woshishui' where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh;
end if;
insert into BFHSYSZS(BGBH,YPPH,YYBH,JH,YYLB,CSRQ,YYSD1,YYSD2,CWMC,YXMS) values(m_bgbh,m_ypph,m_yybh,v_jh,v_yylb,v_csrq,yysd1m,yysd2m,v_cwmc,v_yxms); if v_bz<>'化石少' or v_bz is null then(这句话的句法有问题吗??)
Update BFHSYSZS set YSZS=substr(v_TI,5,2) where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh;
end if;
Update BFHSYSZS set BZ=v_bz where BGBH=m_bgbh and YPPH=m_ypph and YYBH=m_yybh;
end;
1. 是的。trim(‘ ’)结果是null,注意oracle中null与‘’是统一的。
2。但是你的问题是在不能在存储过程中使用trim(),并在前端应用调用它(如果这个存储过程不在前端调用是没有问题的)。trim('')或者trim(null)是不会出错的。
3。trim()换成rtrim(ltrim(v_bz)),OK?
Cause: An unexpected end-of-file was processed on the communication channel. The problem could not be handled by the Net8 two-task software. This message could occur if the shadow two-task process associated with a Net8 connect has terminated abnormally, or if there is a physical failure of the interprocess communication vehicle, that is, the network or server machine went down