ref 游标:动态关联结果集的临时对象.即在运行的时候动态决定执行查询
如何使用REF游标:
①声明REF 游标类型,确定REF 游标类型;
⑴强类型REF游标:指定retrun type,REF 游标变量的类型必须和return type一致。
语法:Type REF游标名 IS Ref Cursor Return 结果集返回记录类型;
⑵弱类型REF游标:不指定return type,能和任何类型的CURSOR变量匹配,用于获取任何结果集。
语法:Type REF游标名 IS Ref Cursor;
管道:
例子:
create or replace function parallel_pipelined(l_cursor in sys_refcursor)
return t2_tab_type pipelined
parallel_enable (partition l_cursor by any)
is
l_session_id number;
TYPE type_t1_data IS TABLE OF t1%ROWTYPE INDEX BY PLS_INTEGER;
l_t1 type_t1_data;
begin
select sid into l_session_id
from v$mystat
where rownum=1;
//游标隐式打开
loop
fetch l_cursor bulk collect into l_t1;--用bulk collect来一次性获取数据
exit when l_t1.count=0;
for i in 1 .. l_t1.count loop
pipe row(t2_type(l_t1(i).id,l_t1(i).text,l_session_id));
end loop;
null;
end loop;
close l_cursor; //游标显示关闭
return;
end;
CREATE OR REPLACE PACKAGE BODY parallel_ptf_api AS
FUNCTION test_ptf (p_cursor IN t_parallel_test_ref_cursor)
RETURN t_parallel_test_tab PIPELINED
PARALLEL_ENABLE(PARTITION p_cursor BY HASH (id))
IS
l_row t_parallel_test_row;
BEGIN
LOOP
FETCH p_cursor
INTO l_row.id,
l_row.description;
EXIT WHEN p_cursor%NOTFOUND;
SELECT sid
INTO l_row.sid
FROM v$mystat
WHERE rownum = 1;
PIPE ROW (l_row);
END LOOP;
RETURN;
END test_ptf;
END parallel_ptf_api;