Oracle 过程 'out' 'table of varchar2' 类型参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6470122/
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
Oracle procedure 'out' 'table of varchar2' type parameter
提问by help
I want to get a list that is assigned in one procedure(b) and return it as an OUT param to a variable of type 'table of varchar2' in procedure(a). How do I define the OUT param type?
我想获得一个在一个过程（b）中分配的列表，并将其作为 OUT 参数返回到过程（a）中类型为“varchar2 表”的变量。如何定义 OUT 参数类型？
ex.)
前任。）
procedure_a()
type t_list IS TABLE OF VARCHAR2(10)
l_list t_list;
procedure_b(l_list);
end procedure_a;
procedure_b(out_list OUT ????)
what would the OUT type be?
OUT 类型是什么？
Thanks in advance.
提前致谢。
回答by Justin Cave
The type would need to be declared somewhere that both A and B could reference. One option would be to declare the type in a package, i.e.
该类型需要在 A 和 B 都可以引用的地方声明。一种选择是在包中声明类型，即
SQL> create package some_pkg
2 as
3 type t_list is table of varchar2(10);
4
5 procedure procedureA;
6 procedure procedureB( p_list OUT t_list );
7 end;
8
9 /
Package created.
SQL> create or replace package body some_pkg
2 as
3 procedure procedureA
4 as
5 l_list t_list;
6 begin
7 procedureB( l_list );
8 dbms_output.put_line( l_list.count );
9 end;
10
11 procedure procedureB
12 ( p_list OUT t_list )
13 as
14 begin
15 select ename
16 bulk collect into p_list
17 from emp;
18 end;
19 end;
20 /
Package body created.
SQL> exec some_pkg.procedureA;
16
PL/SQL procedure successfully completed.
Another option would be to declare the type in SQL, i.e.
另一种选择是在 SQL 中声明类型，即
SQL> create type t_list
2 as table of varchar2(10);
3 /
Type created.
SQL> ed
Wrote file afiedt.buf
1 create or replace procedure procedureB( p_list OUT t_list )
2 as
3 begin
4 select ename
5 bulk collect into p_list
6 from emp;
7* end;
SQL> /
Procedure created.
SQL> create or replace procedure procedureA
2 as
3 l_list t_list;
4 begin
5 procedureB( l_list );
6 dbms_output.put_line( l_list.count );
7 end;
8 /
Procedure created.
SQL> exec procedureA;
16
PL/SQL procedure successfully completed.
