Como hacer una cursor de una tabla de tipo record? (PL/SQL)

Hola:

Todos alguna vez cuando programamos en PL/SQL nos topamos con el problema de retornar un cursor pero los campos se encuentran en distinas tablas y ademas debemos tener validaciones de x tipo, sumado a esto no podemos crear una GLOBAL TEMPORARY TABLE.

Paso 1. Creamos un record con los tipo de datos que deseamos utilizar, para una mejor practica recomiendo el uso de         esquema.nombretabla.campo%TYPE

 TYPE persona IS RECORD
(
nombre VARCHAR2(100),
edad NUMERIC(2)
);

Paso 2. Creamos una colección de datos con el tipo de dato persona

TYPE listaPersonas  IS  TABLE OF persona;

listPers listaPersonas := listaPersonas();

Es necesario aclarar que esta tabla solo estará disponible cuando se llame al paquete, función o procedimiento, para agregar elementos a la colección (TABLA) se llama a metodo extend (añade una instancia)

Ejemplo:

listPers.extend;

listPers(indice).nombre := 'Josue ';

listPers(indice).edad :=25;

indice es el subíndice de la tabla, este indica la posición en que hace referencia la instancia que estamos asignando

Paso 3. Necesitamos crear una función PIPELINED, esta función sirve para manejar volúmenes de información cuando no se desea guardar los datos físicamente en alguna tabla de la base de datos, en resumen la función de PIPELINED son útiles si hay una necesidad de una fuente de datos que no sea una tabla en una instrucción select.

(http://www.akadia.com/services/ora_pipe_functions.html)

 

FUNCTION getRows RETURN listaPersonas PIPELINED IS

BEGIN

FOR i IN listPers.first..listPers.last LOOP 

PIPE ROW ( listPers(i) );

END LOOP;  RETURN;

END getRows;

El codigo completo es el siguiente:

create or replace PACKAGE         PR_EJEMPLO_Pipelined  IS

TYPE persona IS RECORD        

(            

nombre VARCHAR2(100),      

edad NUMERIC(2)

);

TYPE listaPersonas  IS  TABLE OF persona;

listPers listaPersonas := listaPersonas();

PROCEDURE OBTPERSONAS;

FUNCTION getRows RETURN listaPersonas PIPELINED;

END;

/

create or replace PACKAGE BODY PR_EJEMPLO_Pipelined  IS

PROCEDURE OBTPERSONAS IS

IND NUMBER := 0; 

type personas_cursor is REF CURSOR;

BEGIN

listPers.delete;

WHILE(IND<20)       

LOOP               

IND := IND + 1;

listPers.extend;

listPers(IND).nombre := 'Josue '||IND;

listPers(IND).edad := IND;

END LOOP;

for x in listPers.first..listPers.last  loop

dbms_output.put_line(listPers(x).nombre||' - '||listPers(x).edad);

end loop;

for p in ( SELECT * from  TABLE(getRows) ) loop

dbms_output.put_line('tabla temporal '||p.nombre||' - '||p.edad);

end loop;

end;

FUNCTION getRows RETURN listaPersonas PIPELINED IS

BEGIN

FOR i IN listPers.first..listPers.last LOOP 

PIPE ROW ( listPers(i) );

END LOOP;  RETURN;

END getRows;

END PR_EJEMPLO_Pipelined ;

 /