Procedure no Oracle

[fsitjaParticipante]

Edit: continuando mensagem acima

1

CREATE OR REPLACE PROCEDURE
test_ref(cur_output OUT sys_refcursor) IS
BEGIN
OPEN cur_output FOR
WITH teste AS
(SELECT 1 num FROM dual)
SELECT num
FROM teste;
END;

2

CREATE OR REPLACE PACKAGE pk_type AS
TYPE t_tab IS TABLE OF v$version%rowtype;
END pk_type;

CREATE OR REPLACE FUNCTION sp_test return pk_type.t_tab
PIPELINED IS
v_saida pk_type.t_tab;
v_refcur SYS_REFCURSOR;
BEGIN
OPEN v_refcur FOR
SELECT * from v$version;
FETCH v_refcur BULK COLLECT
INTO v_saida;
close v_refcur;
for i in 1 .. v_saida.count
loop
PIPE ROW(v_saida(i));
end loop;
END sp_test;

select * from table(sp_test)