oracle – 如何将数字列表传递给存储过程？

概述所以我有以下存储过程： CREATE OR REPLACE PROCEDURE stored_p( ntype IN NUMBER , p_ResultSet OUT TYPES.cursorType)ASBEGINOPEN p_ResultSet FORselect * from table where ttype in ntype;END stored_p 所以我有以下存储过程：

CREATE OR REPLACE PROCEDURE stored_p( ntype IN NUMBER,p_ResultSet OUT  TYPES.cursorType)ASBEGINOPEN p_ResultSet FORselect * from table where ttype in ntype;END stored_p

而且,我可以这样称呼它：

VARIABLE resultSet  REFCURSOREXEC stored_p(80001,:resultSet);PRINT :resultSet

但我希望能够像这样称呼它：

VARIABLE resultSet  REFCURSOREXEC stored_p([80001,80002],:resultSet);PRINT :resultSet

我应该如何相应地修改我的存储过程？我正在这样做,以便我可以在Crystal Report中显示结果…(以防万一会影响任何事情)..谢谢!!

解决方法 最好的选择是传递一个集合

sql> create type empno_tbl  2  is  3  table of number;  4  /Type created.sql> create or replace procedure stored_p  2  (  3    empnos in empno_tbl,4    p_rc  out sys_refcursor )  5  as  6  begin  7    open  8   p_rc for select * from emp where empno in (select * from table(empnos));  9  end; 10  /Procedure created.sql> var rc refcursor;sql> edWrote file afIEdt.buf  1  create or replace procedure stored_p  2  (  3    empnos in empno_tbl,4    p_rc  out sys_refcursor )  5  as  6  begin  7    open  8   p_rc for select * from emp where empno in (select * from table(empnos));  9* end;sql> begin  2    stored_p( new empno_tbl(7902,7934),:rc );  3  end;  4  /PL/sql procedure successfully completed.sql> print rc     EMPNO Ename      JOB              MGR HIREDATE         SAL       COMM---------- ---------- --------- ---------- --------- ---------- ----------    DEPTNO   FAKE_Col        FOO---------- ---------- ----------      7902 FORD       ANALYST         7566 03-DEC-81       3000        20          1      7934 MILLER     CLERK           7782 23-JAN-82       1300        10          1

遗憾的是,Crystal Reports可能无法将正确的集合传递给存储过程.如果是这种情况,则必须传入以逗号分隔的数字列表.然后,您的过程必须将逗号分隔的字符串解析为集合.您可以使用(或修改)Tom Kyte’s in_list功能

sql> edWrote file afIEdt.buf  1    create or replace function in_List(  2        p_string in varchar2  3    )  4      return empno_tbl  5    as  6        l_string        long default p_string || ',';  7        l_data          empno_tbl := empno_tbl();  8        n               number;  9    begin 10      loop 11          exit when l_string is null; 12          n := instr( l_string,',' ); 13          l_data.extend; 14          l_data(l_data.count) := 15                ltrim( rtrim( substr( l_string,1,n-1 ) ) ); 16          l_string := substr( l_string,n+1 ); 17      end loop; 18      return l_data; 19*   end;sql> /Function created.sql> edWrote file afIEdt.buf  1  create or replace procedure stored_p  2  (  3    empnos in varchar2,4    p_rc  out sys_refcursor )  5  as  6  begin  7    open p_rc  8     for select *  9           from emp 10          where empno in (select * 11                            from table(in_List(empnos))); 12* end;sql> /Procedure created.sql> edWrote file afIEdt.buf  1  begin  2    stored_p( '7902,7934',:rc );  3* end;sql> /PL/sql procedure successfully completed.sql> print rc     EMPNO Ename      JOB              MGR HIREDATE         SAL       COMM---------- ---------- --------- ---------- --------- ---------- ----------    DEPTNO   FAKE_Col        FOO---------- ---------- ----------      7902 FORD       ANALYST         7566 03-DEC-81       3000        20          1      7934 MILLER     CLERK           7782 23-JAN-82       1300        10          1

总结

以上是内存溢出为你收集整理的oracle – 如何将数字列表传递给存储过程？全部内容，希望文章能够帮你解决oracle – 如何将数字列表传递给存储过程？所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。