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

以是我有以下存储进程：

CREATE OR REPLACE PROCEDURE stored_p
(
 ntype IN NUMBER,p_ResultSet OUT  TYPES.cursorType
)
AS
BEGIN
OPEN p_ResultSet FOR
select * from table where ttype in ntype;
END stored_p

并且,我可以这样称号它：

VARIABLE resultSet  REFCURSOR
EXEC stored_p(80001,:resultSet);
PRINT :resultSet

但我但愿可以或许像这样称号它：

VARIABLE resultSet  REFCURSOR
EXEC 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> ed
Wrote 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> ed
Wrote 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> ed
Wrote 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> ed
Wrote 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

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!