我是一名
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;
解决方法
您可以创建集合类型并将参数作为该类型的实例传递.
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.