存储过程里面有声明 sqlscript这个varchar2(2000)变量, hname hcode harttypeid这几个是传进来的参数,有可能为空 harttypeid是number 其他两个是varchar2类型报
ORA-06502: PL/SQL: 数字或值错误 只要删掉 where 后面的条件就没问题了,大家帮忙看看,谢谢,搞了一天了,郁闷呀.sqlscript:='select a.id as id,t.name as arttypename,a.name as name,a.code as code,a.spec as spec,a.minstock as minstock,a.maxstock as maxstock,
u.name as inunitname,un.name as outunitname,a.unitconversion as unitconversion,a.content as content,a.release as release,un.id as outunitid
from article_article_data a
inner join article_units_data u on a.inwarehouseunitid=u.id
inner join article_units_data un on a.outwarehouseunitid =un.id
inner join article_article_type_data t on (t.id=a.arttypeid)
where nvl2('|| hname ||',a.name,1) like nvl('||'%'|| hname ||'%'||',''1'')
and nvl2('|| hcode ||',a.code,1) like nvl('||'%'||hcode ||'%'||',''1'')
and nvl2('|| harttypeid ||',a.arttypeid,1)=nvl('|| harttypeid ||',''1'');
order by a.id';
ORA-06502: PL/SQL: 数字或值错误 只要删掉 where 后面的条件就没问题了,大家帮忙看看,谢谢,搞了一天了,郁闷呀.sqlscript:='select a.id as id,t.name as arttypename,a.name as name,a.code as code,a.spec as spec,a.minstock as minstock,a.maxstock as maxstock,
u.name as inunitname,un.name as outunitname,a.unitconversion as unitconversion,a.content as content,a.release as release,un.id as outunitid
from article_article_data a
inner join article_units_data u on a.inwarehouseunitid=u.id
inner join article_units_data un on a.outwarehouseunitid =un.id
inner join article_article_type_data t on (t.id=a.arttypeid)
where nvl2('|| hname ||',a.name,1) like nvl('||'%'|| hname ||'%'||',''1'')
and nvl2('|| hcode ||',a.code,1) like nvl('||'%'||hcode ||'%'||',''1'')
and nvl2('|| harttypeid ||',a.arttypeid,1)=nvl('|| harttypeid ||',''1'');
order by a.id';
and nvl2('|| hcode ||',a.code,1) like nvl('||'%'||hcode ||'%'||',''1'')
and nvl2('|| harttypeid ||',a.arttypeid,1)=nvl('|| harttypeid ||',''1'');
换成
where nvl2('''|| hname ||''',a.name,1) like nvl('||'%'|| hname ||'%'||',''1'')
and nvl2('''|| hcode ||''',a.code,1) like nvl('||'%'||hcode ||'%'||',''1'')
and nvl2('''|| harttypeid ||''',a.arttypeid,1)=nvl('''|| harttypeid ||''',''1'');
where nvl2('''|| hname ||''',a.name,1) like nvl('''||'%'|| hname ||'%'||''',''1'')
and nvl2('''|| hcode ||''',a.code,1) like nvl('''||'%'||hcode ||'%'||''',''1'')
and nvl2('''|| harttypeid ||''',a.arttypeid,1)=nvl('''|| harttypeid ||''',''1'')
nvl2('|| hname ||',a.name,1) like nvl('||'''%''||'|| hname ||'||''%'''||',''1'')
and nvl2('|| hcode ||',a.code,1) like nvl('||'''%''||'||hcode ||'||''%'''||',''1'')
and nvl2('|| harttypeid ||',a.arttypeid,1)=nvl('|| harttypeid ||',''1'');
u.name as inunitname,un.name as outunitname,a.unitconversion as unitconversion,a.content as content,a.release as release,un.id as outunitid
from article_article_data a
inner join article_units_data u on a.inwarehouseunitid=u.id
inner join article_units_data un on a.outwarehouseunitid =un.id
inner join article_article_type_data t on (t.id=a.arttypeid)
where nvl2('''|| hname ||''',a.name,1) like nvl('''||'%'|| hname ||'%'||''',''1'')
and nvl2('''|| hcode ||''',a.code,1) like nvl('''||'%'||hcode ||'%'||''',''1'')
and nvl2('''|| harttypeid ||''',a.arttypeid,1)=nvl('''|| harttypeid ||''',1)
order by a.id';
还是一样的错误最后一行把1两个单引号引起来也没用
大家帮帮忙了.
只留下这个试试
a开头的是 in值 h开头的是定义的变量 不行,
我在上面
----判断条件,如果为0或空则不给值
if(aarttypeid<>0) then
harttypeid:=aarttypeid;
end if;
if(acode is not null) then
hcode:=acode;
end if;
if(aname is not null) then
hname:=aname;
end if;
and nvl2('|| hcode ||',a.code,1) like nvl('||'''%''||'||hcode ||'||''%'''||',''1'')
and nvl2('|| harttypeid ||',a.arttypeid,1)=nvl('|| harttypeid ||',''1'');
sqlscript:='select a.id as id,t.name as arttypename,a.name as name,a.code as code,a.spec as spec,a.minstock as minstock,a.maxstock as maxstock,
u.name as inunitname,un.name as outunitname,a.unitconversion as unitconversion,a.content as content,a.release as release,un.id as outunitid
from article_article_data a
inner join article_units_data u on a.inwarehouseunitid=u.id
inner join article_units_data un on a.outwarehouseunitid =un.id
inner join article_article_type_data t on (t.id=a.arttypeid)
where nvl2('''|| hname ||''',a.name,''1'') like nvl('''||'%'|| hname ||'%'||''',''1'')
and nvl2('''|| hcode ||''',a.code,''1'') like nvl('''||'%'||hcode ||'%'||''',''1'')
and nvl2('''|| harttypeid ||''',a.arttypeid,''1'')=nvl('''|| harttypeid ||''',''1'');
order by a.id';