cursor c_GetServiceURLType(v_subclass varchar2)
is select ClassName from url_class where DictType = 2 and classFlag <> 0 and subclassid in (v_SubClass); ...
open c_GetServiceURLType(v_subclasslist);
...
is select ClassName from url_class where DictType = 2 and classFlag <> 0 and subclassid in (v_SubClass); ...
open c_GetServiceURLType(v_subclasslist);
...
解决方案 »
- alter index PARALLEL 起什么作用的?
- 9i登陆客户端登陆不进去了?
- http服务启动不了
- SQL往表里插入数据命令,请高手指教
- 【新手求助】如何让PL/SQL 和 ob9连接到本地的ORACLE数据库
- 高分求一个组合问题
- 关于Oracle 里面的sql 语句问题,有谁能帮我一下??
- 请教高手:oracle集合变量不能放在字符串语句中执行?!
- 我的Oracle8i安装在win2000server上,我不想通过控制面板中的服务(已经设置为手动)启动,如何在sqlplus中启动?
- 请大伙推荐一个优秀的建库工具
- 大数据量,两表比较操作性能!
- 下面的过程编译时出错,不知是什么原因,请帮帮忙,谢谢!
create or replace function D_Func_GetSpecServiceURLType(i_SpecServiceID varchar2) return varchar2 is
Result varchar2(500) ;
--变量声明
v_SubClassList varchar2(200) default '0';
v_ClassName varchar2(20);
cursor c_GetServiceURLType(v_subclass varchar2)
is select ClassName from url_class where DictType = 2 and classFlag <> 0 and subclassid in (v_subclass);
begin
select nvl(subclasslist,'') into v_SubClassList from security_templet inner join
service_info on security_templet.securityclassid=service_info.maxonlinetime2
where securitytype=3 and serviceid=i_SpecServiceID;
if(length(v_SubClassList)<3) then Result:='';
else
dbms_output.put_line(v_SubClassList);
v_SubClassList:=LTRIM(v_SubClassList,',');
v_SubClassList:=RTRIM(v_SubClassList,',');
if(LENGTH(v_SubClassList)=0)
then
Result:='';
else
dbms_output.put_line(v_SubClassList);
open c_GetServiceURLType(v_SubClassList);
IF c_GetServiceURLType%ISOPEN THEN
LOOP
FETCH c_GetServiceURLType INTO v_ClassName;
Result:=Result+','+v_ClassName;
EXIT WHEN c_GetServiceURLType%NOTFOUND;
END LOOP;
close c_GetServiceURLType;
Result:=RTRIM(Result,',');
end if;
end if;
end if;
return(Result);
end D_Func_GetSpecServiceURLType;
出错了 select ClassName from url_class where DictType = 2 and classFlag <> 0 and subclassid in (521,523,524,541)确实没有问题
你这样用in是不对的
open c_name for 'select classname from ...
where ... and subclassid in('||v_SubClassList||')';
type v_cursor is ref cursor;
v_cur v_cursor;
create or replace procedure p_test is
v_processedcount varchar2(100);
v_str varchar2(400);
v_pcount varchar2(4);
v_upcount varchar2(5);
type v_cursor is ref cursor;
c_name v_cursor;
begin
v_processedcount:='2,5,7';
v_str:='select processedcount,unprocessedcount from aaaa where processedcount in ('||v_processedcount||')';
dbms_output.put_line(v_str);
open c_name for v_str;
loop
fetch c_name into v_pcount,v_upcount;
exit when c_name%notfound;
dbms_output.put_line(v_pcount);
end loop;
close c_name;
end p_test;
select nvl(subclasslist,'') into v_SubClassList from security_templet inner join
service_info on security_templet.securityclassid=service_info.maxonlinetime2
where securitytype=3 and serviceid=i_SpecServiceID; //这行没有数据的时候好像也不行在oracle 中 select 语句要有为表中数据才行。
所以一般采取 select count(*) into v_count from security_templet inner join
service_info on security_templet.securityclassid=service_info.maxonlinetime2
where securitytype=3 and serviceid=i_SpecServiceID;
if v_count>0 then
select nvl(subclasslist,'') into v_SubClassList from security_templet inner join
service_info on security_templet.securityclassid=service_info.maxonlinetime2
where securitytype=3 and serviceid=i_SpecServiceID;
end if;
按大家的建议,现在改为:create or replace function D_Func_GetSpecServiceURLType(i_SpecServiceID varchar2) return varchar2 is
Result varchar2(500) ;
--变量声明
v_SubClassList varchar2(200) default '';
v_ClassName varchar2(20);
-- cursor c_GetServiceURLType(v_subclass varchar2)
-- is select ClassName from url_class where DictType = 2 and classFlag <> 0 and subclassid in (v_subclass);
type v_Cursor is ref cursor;
c_GetServiceURLType v_Cursor;
v_Str varchar2(100);
v_Count int;
begin
select count(subclasslist) into v_Count from security_templet inner join
service_info on security_templet.securityclassid=service_info.maxonlinetime2
where securitytype=3 and serviceid=i_SpecServiceID;
if(v_Count=0) then Result:='';
else
select nvl(subclasslist,'') into v_SubClassList from security_templet inner join
service_info on security_templet.securityclassid=service_info.maxonlinetime2
where securitytype=3 and serviceid=i_SpecServiceID;
end if;
if(length(v_SubClassList)<3) then Result:='';
else
v_SubClassList:=LTRIM(v_SubClassList,',');
v_SubClassList:=RTRIM(v_SubClassList,',');
if(LENGTH(v_SubClassList)=0)
then
Result:='';
else
v_Str:='select ClassName from url_class where DictType = 2 and classFlag <> 0 and subclassid in ( ';
v_Str:=v_Str||v_SubClassList;--就在这里出现堆栈错误
v_Str:=v_Str||' )';
open c_GetServiceURLType for v_Str;
IF c_GetServiceURLType%ISOPEN THEN
LOOP
FETCH c_GetServiceURLType INTO v_ClassName;
Result:=Result+','+v_ClassName;
EXIT WHEN c_GetServiceURLType%NOTFOUND;
END LOOP;
close c_GetServiceURLType;
Result:=RTRIM(Result,',');
end if;
end if;
end if;
return(Result);
end D_Func_GetSpecServiceURLType;堆栈错误:
ORA-06502:PL/SQL:numeric or value error
ORA_06512:at"D_Func_GetSpecServiceURLType" at line34
ORA_06512:at line 3
v_Str:=v_Str||v_SubClassList;--就在这里出现堆栈错误
v_Str:=v_Str||' )';
就出现了堆栈异常,我用pl/SQL Developer调试的
v_Str:=v_Str||v_SubClassList;
v_Str:=v_Str||' )';
改写成了select v_Str||v_SubClassList into v_Str from dual调试通过了,
并且上面的还有一个写错的地方; Result:=Result+','+v_ClassName;
改为 Result:=Result||','||v_ClassName;结贴,谢谢 bzszp(www.bzszp.533.net), superlcj(幽灵) 等对我的帮助