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);
...
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(幽灵) 等对我的帮助