函数是
function getAppHoldNum(batchid number,
brandid number,
simfun number,
cityid varchar2,
queryDate varchar2) return AppHoldNum is
--------------------
2011-12-01
------------------------------------------------------
l_AppHoldNum AppHoldNum;
l_sql varchar2(3500);
l_select_sql varchar2(1500);
l_and_sql varchar2(500);
l_group_sql varchar2(500);
begin
if (queryDate is null) or (Length(queryDate) = 0) then
return l_AppHoldNum;
end if;
if (batchid = 0) or (brandid = 0) or (simfun = 0) then
return l_AppHoldNum;
end if;
l_select_sql := 'select v_menuname, sum(i_number),substr(v_statdate, 1, 6) as sdate
from t_statappholdingnum t
where t.i_batchid = ' || batchid || '
and t.n_brandid = ' || brandid || '
and t.i_simfunction = ' || simfun || '
and substr(v_statdate, 1, 6) in (' ||
queryDate || ')';
if (cityId <> '-1') then
l_and_sql := ' and t.v_cityid=' || cityId;
l_sql := l_sql || l_and_sql;
end if;
l_sql := l_sql || l_select_sql;
l_group_sql := ' group by v_menuname,substr(v_statdate, 1, 6) order by v_menuname';
l_sql := l_sql || l_group_sql;
open l_AppHoldNum for l_sql;
return l_AppHoldNum;
exception
when others then
return l_AppHoldNum;
end;
其中表里有数据如图
我访问用的例子是：
select pk_stat.getAppHoldNum(
11,
2,
7,
451,
'200801,200802,201103,200804,200805,200806,200807,200808,200809,200810,200811,200812')
from dual
执行结果是：
ORA-00604: error occurred at recursive SQL level 1
ORA-01003: no statement parsed
不知道是哪个原因，帮忙看一下。！
------解决方案--------------------
把建表的sql脚本贴一下吧，没有表结构，无法编译该函数，也就无法进行测试。
------解决方案--------------------
return AppHoldNum 这个类型oracle并不认识， 你如果是自定义的话，需要在plsql中定义。
------解决方案--------------------
--------------------
2011-12-01
------------------------------------------------------
这样的注释不对