oracle 如何将数字列表传递给存储过程？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5268699/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I pass a list of numbers into a stored procedure?
提问by ntsue
So I have the following stored procedure:
所以我有以下存储过程：
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
and, I can call it like this:
而且，我可以这样称呼它：
VARIABLE resultSet REFCURSOR
EXEC stored_p(80001, :resultSet);
PRINT :resultSet
but I want to be able to call it like this:
但我希望能够这样称呼它：
VARIABLE resultSet REFCURSOR
EXEC stored_p([80001,80002], :resultSet);
PRINT :resultSet
How should I modify my stored procedure accordingly? I am doing this so that I can display the results in a Crystal Report... (just in case that affects anything).. Thanks!!
我应该如何相应地修改我的存储过程？我这样做是为了可以在 Crystal Report 中显示结果......（以防万一会影响任何事情......谢谢！
回答by Justin Cave
The best option would be to pass a collection
最好的选择是传递一个集合
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
Unfortunately, Crystal Reports may not be able to pass a proper collection to a stored procedure. If that is the case, you'd have to pass in a comma-separated list of numbers. Your procedure would then have to parse that comma-separated string into a collection. You can use (or modify) Tom Kyte's in_listfunction for this
不幸的是，Crystal Reports 可能无法将正确的集合传递给存储过程。如果是这种情况，您必须传入一个以逗号分隔的数字列表。然后，您的过程必须将该逗号分隔的字符串解析为一个集合。您可以为此使用（或修改）Tom Kyte 的 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
回答by joelt
Newer versions might have different options. I work some with Oracle 9 and 10, and I will typically pass in a string of comma-separated values and dynamically build the SQL. There are some significant dangers with SQL injection to be aware of, though.
较新的版本可能有不同的选项。我使用了一些 Oracle 9 和 10，我通常会传入一串逗号分隔的值并动态构建 SQL。但是，需要注意 SQL 注入存在一些重大危险。
回答by isobar
You need to create a type..
你需要创建一个类型..
create or replace type NUMBER_ARRAY as table of number;
CREATE OR REPLACE PROCEDURE stored_p
(
ntype IN NUMBER_ARRAY ,
p_ResultSet OUT TYPES.cursorType
)
You can loop it using..
您可以使用循环它..
for i in 1 .. ntype.count
loop
dbms_output.put_line( ntype(i) );
end loop;
To test it,
为了测试它，
DECLARE
ntypetest NUMBER_ARRAY := NUMBER_ARRAY ();
BEGIN
FOR i IN 1 .. 5
LOOP
ntypetest.EXTEND;
ntypetest (i) := i;
END LOOP;
stored_p(ntypetest,..)
There may be some variation in syntax.
语法上可能会有一些变化。
Of course you can pass in comma separated values too but that will come in as a string. Your string should be something like 'val1','val2','val3'. You need to be careful when you have numbers as the whole string will look like in ('2343,3444,2222')which will be treated as one value instead of multiple numbers as in (2343,3444,2222)
当然，您也可以传入逗号分隔的值，但这将作为字符串传入。您的字符串应该类似于'val1','val2','val3'. 当你有数字时你需要小心，因为整个字符串看起来像in ('2343,3444,2222')一个值而不是多个数字in (2343,3444,2222)
