create or replace function getChannelNum(in_channleid2 varchar) return int
IS
resultStr int;
sqltp varchar2(6000);
begin
sqltp:= 'select count(*) into resultStr from dic_channel where stopflag=0 and channelid in (' || in_channleid2 ||')';
--例如 in_channleid2 ='209,207'
--select count(*) into resultStr from dic_channel where stopflag=0 and channelid in (in_channleid2);
execute immediate sqltp;
commit;
return resultStr;
Exception
when others then
begin
return 0;
end;
end getChannelNum;
写了一个 方法 ,传过来字符传209,207,203
执行上述功能方法 老是提示错误
求解决这道
oracle9.0 版本
IS
resultStr int;
sqltp varchar2(6000);
begin
sqltp:= 'select count(*) into resultStr from dic_channel where stopflag=0 and channelid in (' || in_channleid2 ||')';
--例如 in_channleid2 ='209,207'
--select count(*) into resultStr from dic_channel where stopflag=0 and channelid in (in_channleid2);
execute immediate sqltp;
commit;
return resultStr;
Exception
when others then
begin
return 0;
end;
end getChannelNum;
写了一个 方法 ,传过来字符传209,207,203
执行上述功能方法 老是提示错误
求解决这道
oracle9.0 版本
create or replace function getChannelNum(in_channleid2 varchar) return int
IS
resultStr int;
sqltp varchar2(6000);
begin
sqltp:= 'select count(*) from dic_channel where stopflag=0 and channelid in (' || in_channleid2 ||')';
--例如 in_channleid2 ='209,207'
--select count(*) from dic_channel where stopflag=0 and channelid in (in_channleid2);
execute immediate sqltp into resultStr; --把into 放到这里
-- commit; 这个不要
return resultStr;
Exception
when others then
begin
return 0;
end;
end getChannelNum;
execute immediate sqltp 直接跳到 Exception
execute immediate sqltp into resultStr; --把into 放到这里
这样的写法也是一样直接跳到 Exception
进入异常操作了
create or replace function getChannelNum(in_channleid2 varchar2) return int
IS
resultStr int;
sqltp varchar2(6000);
begin
sqltp:= 'select count(*) from emp where empno in (' || in_channleid2 ||')';
execute immediate sqltp into resultStr;
--commit;
return resultStr;
Exception
when others then
begin
return 0;
end;
end getChannelNum;select getchannelnum('7788,8899') from dual;GETCHANNELNUM('7788,8899')
--------------------------
1