Oracle PL/SQL - procedure with array parameter

Question

我需要写一个oracle过程，它将有ID的数组作为参数。然后我将返回一个包含select(1)结果的游标。

(1) - select * from table where id in(id 's)

As an option we can pass a string param and then convert string to array.
DECLARE
info sys_refcursor ;  
error varchar(255); 
BEGIN
package.test_function('1,2,3',info ,error);// info will contain a result cursor for select(1)
END;

你有其他的想法吗?

Answer 1

您可以创建一个用户定义的集合类型:

CREATE TYPE int8_list IS TABLE OF NUMBER(8,0);

然后你的包:

CREATE PACKAGE pkg_name AS
PROCEDURE proc_name (
i_ids    IN  int8_list,
o_cursor OUT SYS_REFCURSOR
);
END;
/
CREATE PACKAGE BODY pkg_name AS
PROCEDURE proc_name (
i_ids    IN  int8_list,
o_cursor OUT SYS_REFCURSOR
)
IS
BEGIN
OPEN o_cursor FOR
SELECT * FROM table_name WHERE id MEMBER OF i_ids;
END;
END;
/

然后可以调用这个过程:

DECLARE
v_info  sys_refcursor ;  
v_id    TABLE_NAME.ID%TYPE;
v_value TABLE_NAME.VALUE%TYPE;
BEGIN
pkg_name.proc_name(int8_list(1,2,3), v_info);
LOOP
FETCH v_info INTO v_id, v_value;
EXIT WHEN v_info%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_id || ' ' || v_value);
END LOOP;
END;
/

对于样本数据:

CREATE TABLE table_name (id, value) AS
SELECT LEVEL, CHR(64+LEVEL) FROM DUAL CONNECT BY LEVEL <= 5;

输出:

1 A
2 B
3 C

db<此处小提琴>