create or replace procedure testts(listports in varchar2)
AS theSlot varchar2(50);
theRate varchar2(50); TYPE rc is REF CURSOR; v_rc rc;begin
open v_rc for select xtxx,xtbm from xtbm where xtbm in(listports);
--open v_rc for select xtxx,xtbm from xtbm where xtbm in('ZY0101','ZY0102'); loop
fetch v_rc into theSlot,theRate;
exit when v_rc%notfound;
dbms_output.put_line(theSlot || ' ***** ' || theRate);
end loop;
close v_rc;END testts;
AS theSlot varchar2(50);
theRate varchar2(50); TYPE rc is REF CURSOR; v_rc rc;begin
open v_rc for select xtxx,xtbm from xtbm where xtbm in(listports);
--open v_rc for select xtxx,xtbm from xtbm where xtbm in('ZY0101','ZY0102'); loop
fetch v_rc into theSlot,theRate;
exit when v_rc%notfound;
dbms_output.put_line(theSlot || ' ***** ' || theRate);
end loop;
close v_rc;END testts;
解决方案 »
- pl/sql连不上本机10g数据库,sqlplus可以,请问是怎么回事?
- oracle 10g ORA-01555
- 執行Oracle store Procedure時報錯:Object variable or With block variable not set
- oci 急!!
- 想输出数组的第一个数值,总报错误,谢谢大家给修改下。分不多下次补上
- 关于ORACLE DataGuard的问题
- “创建的触发器带有编译错误”是怎么回事?
- 请问哪里有Forms Builder的书下载阿?
- Oracle安装问题。!
- 关于表结构!请指教!
- fedora下面安装oracle xe的问题?
- 如何在游标for循环中排除不想要的记录
但如果静态指定open v_rc for select xtxx,xtbm from xtbm where xtbm in('ZY0101','ZY0102'); 就可以获取到值;
费解,希望高手指点迷津
open v_rc for select xtxx,xtbm from xtbm where xtbm in(' ''ZY0101'',''ZY0102'' ');所以在传入后你需要处理下试试下面这个:
open v_rc for select xtxx,xtbm from xtbm
where xtbm in (select substr( ','||listports, rownum, instr(','||listports,',',rownum+1) - instr(','||listports,',',rownum) - 1)
from dual connect by rownum <= length(listports) - length(replace(listports,',','')) + 1);
where xtbm in (select substr( ','||listports, instr(','||listports,',',rownum), instr(','||listports,',',rownum+1) - instr(','||listports,',',rownum) - 1)
from dual connect by rownum <= length(listports) - length(replace(listports,',','')) + 1);上面有点问题,这个应该可以了,家里没oracle没法测试
改为
rownum <
'''ZY0101'',''ZY0102'''