Cómo utilizar un cursor PL/SQL como parámetro de salida en un procedimiento

En este artículo o voy a proponer un ejercicio práctico bastante sencillo que puede resultar de bastante útilidad práctica. Supongamos que queremos crear un procedimiento PLSQL que utilice un par de parámetros de entrada, que podrán tomar el valor NULL, y con un parámetro de salida que será el cursor PL/SQL correspondiente a la siguiente sentencia SELECT:

SELECT * FROM empleados
WHERE nombre = parametro_1
  AND apellidos = parametro_2;

Lo primero que tendremos que hacer es crear un paquete PL/SQL que contenga la definición del cursor:

CREATE OR REPLACE PACKAGE empleados_pkg
IS
  /* Definición del REF CURSOR type */
  TYPE empleados_type IS REF CURSOR
  RETURN empleados%ROWTYPE;
END empleados_pkg;

Después sólo tendremos que crear el procedimiento PLSQL que devolverá como salida el cursor definido en el paso anterior, y que utilizará los dos parámetros de entrada indicados en el enunciado del problema que estamos intentando resolver.

CREATE OR REPLACE PROCEDURE datos_empleados
  (
  par1 IN VARCHAR2,
  par2 IN VARCHAR2,
  cursor1 OUT empleados_pkg.empleados_type
  )
AS
BEGIN
  OPEN cursor1 FOR
  SELECT * FROM empleados
  WHERE nombre = NVL(par1,nombre)
    AND apellidos = NVL(par2,apellidos);
END datos_empleados;

No obstante, la utilización de la función NVL (si par1 es NULL toma el valor nombre, si par2 es NULL toma el valor apellidos), podría no ser muy conveniente y derivar en problemas de rendimiento, por lo que es recomendable definir el procedimiento anterior como sigue:

CREATE OR REPLACE PROCEDURE datos_empleados
  (
  par1 IN VARCHAR2,
  par2 IN VARCHAR2,
  cursor1 OUT empleados_pkg.empleados_type
  )
AS
BEGIN

  /* Todos los parámetros tienen valor */
  IF par1 IS NOT NULL AND par2 IS NOT NULL
  THEN
    OPEN cursor1 FOR
    SELECT * FROM empleados
    WHERE nombre = par1
      AND apellidos = par2;

  /* Sólo tiene valor par1 */
  ELSIF par1 IS NOT NULL
  THEN
    OPEN cursor1 FOR
    SELECT * FROM empleados
    WHERE nombre = par1;

  /* Sólo tienen valor par2 */
  ELSIF par2 IS NOT NULL
  THEN
    OPEN cursor1 FOR
    SELECT * FROM empleados
    WHERE apellidos = par2;

  /* Ningún parámetro tiene valor */
  ELSE
  THEN
    OPEN cursor1 FOR
    SELECT * FROM empleados;

  END IF;

END datos_empleados;

Artículos relacionados: Cursores en PLSQL.