oracle 如何在 PL/SQL 函数中使用传递数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6338721/
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
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-10 03:20:37 来源:igfitidea点击:
How to use pass an array in PL/SQL function
提问by micheal marquiz
I am a Java developer with limited knowledge of Oracle PL/SQL. Please let me know how to pass an array to a PL/SQL function in the following example and how to invoke it.
我是一名 Java 开发人员，对 Oracle PL/SQL 的了解有限。请告诉我如何将数组传递给以下示例中的 PL/SQL 函数以及如何调用它。
CREATE OR REPLACE FUNCTION get_employees (pUserId NUMBER)
RETURN VARCHAR2
IS
l_text VARCHAR2(32767) := NULL;
BEGIN
FOR cur_rec IN (SELECT grp.NAME GROUP_NAME FROM UserGroupRole ugr, Group_ grp WHERE ugr.groupid=grp.groupid and USERID = pUserId) LOOP
l_text := l_text || ',' || cur_rec.GROUP_NAME;
END LOOP;
RETURN LTRIM(l_text, ',');
END;
/
SELECT get_employees(414091) FROM DUAL;
回答by eaolson
You can create a collection type and pass the parameter as an instance of that type.
您可以创建一个集合类型并将参数作为该类型的实例传递。
SQL> create type num_array as table of number;
2 /
Type created.
SQL> create or replace function myfun ( arr_in num_array ) return varchar2 is
2 txt varchar2(1000);
3 begin
4 for i in 1..arr_in.count loop
5 txt := txt || to_char( arr_in(i) ) || ',';
6 end loop;
7 return txt;
8 end;
9 /
Function created.
SQL> declare
2 myarray num_array;
3 mytext varchar2(1000);
4 begin
5 myarray := num_array();
6 myarray.extend(3);
7 myarray(1) := 1;
8 myarray(2) := 5;
9 myarray(3) := 9;
10 dbms_output.put_line( myfun( myarray ));
11 end;
12 /
1,5,9,
PL/SQL procedure successfully completed.
