我能想到的办法大致如下了: DECLARE l_sqltext VARCHAR2(2000); type t_cur is ref cursor; v_ser t_cur; TYPE array_t IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER; rec_array array_t; BEGIN l_sqltext := 'select oh.header_id/*,oh.order_number,oh.ordered_date*/ from oe_order_headers_all oh where oh.header_id in(2536,2440,2498)';--可以是任意有效的查询sql文本 l_curid := dbms_sql.open_cursor(); open v_ser for l_sqltext; FETCH v_ser BULK COLLECT INTO rec_array; FOR i IN rec_array.FIRST .. rec_array.LAST LOOP dbms_output.put_line(rec_array(i)); END LOOP; END;
过程里定义一个prm_OUTRESULT OUT sys_refcursor返回参数,返回结果集就赋值给返回参就行了,过程最后OPEN prm_OUTRESULT for p_slq
as
begin
OPEN p_cursor FOR p_sql;
end;
DECLARE
l_sqltext VARCHAR2(2000);
type t_cur is ref cursor;
v_ser t_cur;
TYPE array_t IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
rec_array array_t;
BEGIN
l_sqltext := 'select oh.header_id/*,oh.order_number,oh.ordered_date*/ from oe_order_headers_all oh where oh.header_id in(2536,2440,2498)';--可以是任意有效的查询sql文本 l_curid := dbms_sql.open_cursor();
open v_ser for l_sqltext;
FETCH v_ser BULK COLLECT INTO rec_array;
FOR i IN rec_array.FIRST .. rec_array.LAST
LOOP
dbms_output.put_line(rec_array(i));
END LOOP;
END;