我需要撰寫一個以 ID 陣列作為引數的 oracle 程式。然后我將回傳一個包含 select(1) 結果的游標。
(1) - 從表中選擇 * 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;
你有其他想法嗎？
uj5u.com熱心網友回復：
您可以創建用戶定義的集合型別：
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<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/478848.html