我有一个接受数组的PLsql oracle函数：
CREATE OR REPLACE FUNCTION CM.give_me_an_arrays (p_array IN num_array)
RETURN VARCHAR2
IS
x VARCHAR2 (512);
BEGIN
x := '';
FOR i IN 1 .. p_array.COUNT
LOOP
DBMS_OUTPUT.put_line (p_array (i));
END LOOP;
RETURN x;
END;
/
我想这样做：
select CM.give_me_an_arrays(select COM.COM_ID
from CM.XLP_SE_COMPONENT com
where rownum < 10)
from dual
有任何想法吗？
提前致谢.
解决方法
你可以这样做,只要数组是一个sql对象(在11gR2上测试,应该在10g上工作)：
sql> create or replace type num_array is table of number; 2 / Type created. sql> CREATE OR REPLACE FUNCTION give_me_an_arrays (p_array IN num_array) 2 RETURN VARCHAR2 3 IS 4 x VARCHAR2 (512); 5 BEGIN 6 x := ''; 7 FOR i IN 1 .. p_array.COUNT 8 LOOP 9 DBMS_OUTPUT.put_line (p_array (i)); 10 END LOOP; 11 12 RETURN x; 13 END; 14 / Function created.
sql> SELECT give_me_an_arrays((SELECT cast(collect(rownum) AS num_array) value 2 FROM dual 3 CONNECT BY level <= 10)) arr 4 FROM dual; ARR -------------------------------------------------------------------------------- 1 2 [..] 10
在9i(甚至可能是8i,现在无法测试),COLLECT不存在,但您可以使用MULTISET代替：
sql> SELECT give_me_an_arrays(cast(MULTISET(SELECT rownum value 2 FROM dual 3 CONNECT BY level <= 10) AS num_array) 4 ) arr 5 FROM dual; ARR -------------------------------------------------------------------------------- 1 2 [..] 10
