Oracle PLSQL: Utilización de un CURSOR como parte de un argumento en una una función

La expresión CURSOR permite recorrer los registros que devuelve una consulta SQL.

Operaciones como INSERT, UPDATE y DELETE, definen de antemano un cursor implícito.

Cada cursor consta de una serie de atributos como:

• %ROWCOUNT: Cantidad de registros
• %FOUND: Indica que hay datos (tipo booleano)
• %NOTFOUND: Indica que no hay datos (tipo booleano)
• %ISOPEN: Indica si el cursor está abierto (tipo booleano

Un CURSOR se abre implícitamente cuando se evalúa la expresión del cursor. Si la expresión del cursor aparece en una lista de selección, se abrirá un cursor por cada file obtenida por la consulta.

Veamos como se puede utilizar un CURSOR como un argumento en una función.

Vamos a crear una función que pueda aceptar un argumento de REF_CURSOR, que no requiere especificar el tipo de registro retornado por el cursor, lo que permite mayor flexibilidad, pero con el inconveniente que impide poder validar los tipos de datos en tiempo de compilación, lo cuál puede generar errores en tiempo de ejecución.

CREATE FUNCTION f(cur SYS_REFCURSOR, mgr_hiredate DATE) RETURN NUMBER IS emp_hiredate DATE; before number :=0; after number:=0; begin loop fetch cur into emp_hiredate; exit when cur%NOTFOUND; if emp_hiredate > mgr_hiredate then after:=after+1; else before:=before+1; end if; end loop; close cur; if before > after then return 1; else return 0; end if; end; /

En cuánto a la función, esta acepta un CURSOR y una fecha. La función espera que el cursor sea una consulta que devuelva un conjunto de fechas.

La siguiente consulta utiliza la función para encontrar esos administradores en la tabla de "employees", cuyos empleados fueron contratados antes que el administrador.

SQL> SELECT e1.last_name, e1.hire_date from employees e1

WHERE f(

CURSOR(SELECT e2.hire_date FROM employees e2

WHERE e1.employee_id = e2.manager_id),

e1.hire_date) = 1

ORDER BY last_name

/

LAST_NAME                 HIRE_DATE

------------------------- ---------

Cambrault                 15-OCT-07

Higgins                   07-JUN-02

Kochhar                   21-SEP-05

Mourgos                   16-NOV-07

Zlotkey                   29-JAN-08