Anuncios en tutorial de programación PLSQL

lunes, 4 de febrero de 2019

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

Cursores PLSQL usados como parámetro de salida de un procedure o procedimientoEn 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.

35 comentarios:

Lom dijo...

Hola, acabo de volver de vacaciones y justamente necesitaba esto.
Gracias de nuevo por tus artículos.

Lom dijo...

Tengo una pregunta al respecto:
En el caso de que quiera que el cursor que me devuelve la función no contenga todas las columnas de la table empleados, como tendría que definir el PACKAGE empleados_pkg?
Supongamos que quiero que el cursor contenga únicamente los campos apellidos y salario.
CREATE OR REPLACE PACKAGE empleados_pkg
IS
/* Definición del REF CURSOR type */
TYPE empleados_type IS REF CURSOR
RETURN (empleados.apellidos%TYPE, empleados.salario%TYPE);
END empleados_pkg;

¿Sería correcta esta sintaxis?

Pepelu dijo...

Hola Lom, sería como sigue:

CREATE OR REPLACE PACKAGE empleados_pkg IS

CURSOR c_emp IS
SELECT apellidos, salario
FROM empleados;

TYPE empleados_type IS REF CURSOR
RETURN c_emp%ROWTYPE;

END empleados_pkg;

Daniel Patino dijo...

Pregunta, en el primer caso, luego de correr el procedimiento, como utilizo el cursor creado por fuera del mismo?
Es decir, puedo en la misma sesiion hacer un select * from cursor1;
Gracias.

Lom dijo...

Gracias Pepelu por tu respuesta.

Pepelu dijo...

Daniel, en el procedimiento PL/SQL abro el cursor, sólo se necesita llamar al procedimiento y utilizar el comando FETCH para recoger los valores devueltos por el cursor. Te adjunto un ejemplo:

DECLARE
c_emp empleados_pkg.empleados_type;
c_emp_rec c_emp%ROWTYPE;

BEGIN
datos_empleados ('Pepe', 'Alegre', c_emp);
LOOP
FETCH c_emp INTO c_emp_rec;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (c_emp_rec.salario);
END LOOP;
CLOSE c_emp;
END;

kraftcreme dijo...

Hola, tengo una tabla como sigue:
DNI NOMBRE EDAD
--- ------------ ----
11 Juan Álvarez 18

Cómo puedo hacer lo siguiente:
crear un bloque PL/SQL, que pida por teclado el dni de la persona y que posteriormente se muestre el nombre y la edad de la persona correspondiente.

Gracias
Saludos
Iván

Pepelu dijo...

Hola Kraftcreme,

Tu pregunta no tiene mucho que ver con lo que se cuenta en este artículo y es algo bastante básico. De todas formas te adjunto un ejemplo de como debería ser el script PL/SQL que me pides:

DECLARE
  -- Cursor para obtener el nombre y la edad
  CURSOR obtener_nombre IS
    SELECT
      nombre,
      edad
    FROM
      tabla_nombres
    WHERE
      dni = &1;

BEGIN
  -- Obteniendo nombres con mismo DNI
  FOR obtener_nombre_rec IN obtener_nombre LOOP
    dbms_output.put_line ('Nombre '||obtener_nombre_rec.nombre);
    dbms_output.put_line ('Edad '||obtener_nombre_rec.edad);
  END LOOP;
END;

_84kur10_ dijo...

Buen articulo, me ha servido, y buen blog, he conseguido buena info de plsql aqui


salu2

Marcos Vera dijo...

esa es la manera 'primitiva' ahora se usa sys_refcursor. suerte , buen blog !!!

Pepelu dijo...

Marcos, tu comentario puede crear confusión. SYS_REFCURSOR se trata de un tipo REF CURSOR que viene predefinido desde la versión 9i de las bases de datos Oracle. Es cierto que se puede utilizar para lo que he explicado, pero tampoco abrevia demasiado el proceso.

.:. Luk@z .:. dijo...

pepelu, es excelente la informacion posteada aca,
ahora te pregunto, no se como invocar el store procedure desde visual basic 6 para poder utilizar justamente el cursor.
vos podras pasarme esa informacion?
gracias

Pepelu dijo...

Hola Lukaz, en este caso me has pillado porque no tengo ninguna experiencia con Visual Basic conectado a una base de datos Oracle.

Ulises dijo...

Hola Pepelepu, pues tengo un pequeño problema, tenemos algunos Package en nuestra base de datos, pero los queremos llamar desde un ADF que esta echo en Jdeveloper, me podrias ayudar con esto, los procedimientos tambien nos devuelven cursores asi como los que posteaste, pero no sabemos como invocarlos desde Jdeveloper ADF

Pepelu dijo...

Ulises, echa un vistazo a esta página:

http://forums.oracle.com/forums/thread.jspa?threadID=841752

Oscar Vargas dijo...

Saludos,
Casi que no encuentro info de este tema. Me ha sido de gran ayuda. Tengo dos inconvenientes por ahora.
1) Al ejecutarlo en el Developer me genera el siguiente error:

"PLS-00382: el tipo de la expresión no es correcto"

Solo tengo dos parametros de entrada el cursor y otra llamada P_Dano IN NUMBER

No entiendo el motivo del error. No creo q' tenga mucho q' ver el numero de digitos como he llamado el cursor o el parametro de entrada.

La otra pregunta ...
2) Necesito imprimir en pantalla lo que me trae el SP. Tendria que generar otro cursor en el Bloque anonimo cuando llame el SP??? Si es cierto, como realizo el recorrido de este nuevo cursor??

Gracias por tu colaboración--

Pepelu dijo...

Hola Oscar,

1) El error PLS-00382 indica que estás utilizando en algún lugar del código un tipo de dato incorrecto, pero no te puedo decir nada más.

2) En un comentario de esta misma entrada tienes un ejemplo que hace exactamente lo que me estás preguntando.

Oscar Vargas dijo...

Saludos,
Viejo me sirvio de gran ayuda. Te lo agradezco. Pregunta, no tienes tutoriales sobre OracleForms y plsql??

Gracias por tu ayuda!

Pepelu dijo...

No, no tengo ningún tutorial de Oracle Forms. Con este de PL/SQL ya tengo trabajo más que suficiente.

Marcos dijo...

Otra ves yo!
Saben siento que he aprendi mucho y que he avanzado enormemente, pero tengo una consulta mas:
resulta que es realidad lo que hemos aprendido es a enviar referencias de cursores( no cursores como tal) me dicen si me equivoco! en realidad no se puede enviar cursores sino mas bien solo referencias.
Mi consulta es para enviar una referencia de un cursor es necesario definirla en un paquete??? es decir si no la defino en un paquete no puedo enviarla como parametros???

Pepelu dijo...

Marcos, efectivamente tienes que definir la referencia al cursor.

Adaias dijo...

Ok, de maravilla la información, pero tengo una pregunta.

¿puedo utilizar un cursor definido en el package como return de una funcion?.

desconozco como funciona oracle, pero lo que quiero hacer es: desde mi aplicacion llamar un SP o funcion, pero no se si al hacer eso me devuelvan los datos en el formato que lo devuelve directamente un SELECT * FROM USER por ejemplo(lo cual devuelve una tabla en memoria).

¿si ejecuto una funcion de tipo cursor, al llamarlo en mi aplicacion me regresara algo como si fuera un select? o tengo que recorrerlo con un fetch...etc. aunque en realidad yo no quiero mostrar los datos en la consola de oracle(dbms_output.put_line)

Ulises dijo...

Adais, tendrias que definir una funcion que te regrese un tipo syscursor y recibirlo en una variable igual y esa variable vaciarlo en una tabla echa por ti que contenga el mismo numero de cambios asi como sus tipos.

Pepelu dijo...

Adaias, creo que un ejemplo que escribí el 7 de agosto de 2009 en un comentario de este mismo artículo es la respuesta a tu pregunta. Pero basicamente te diré que se necesita un FETCH para recorrer los cursores devueltos por una función o procedimiento.

Adaias dijo...

Bueno muchas gracias por las respuestas, creo que ya voy captando la idea, de todos modos seguire investigando y preguntando por aqui algunas dudas mas.

Drauguebz dijo...

excelente blog me fue muy util.. gracias

Jairo Plazas Leon dijo...

Tengo una duda es posible retornar el cursos desde el procedimiento sin necesidad de crear el paquete.

agradezco la colaboracion

Pepelu dijo...

Hola Jairo, yo no conozco ninguna otra forma de hacerlo. El cursor de salida debe estar definido en un paquete PL/SQL.

Matryx dijo...

gracias , muchas gracias x tu ayuda, no sabes lo mucho q me va a servir esto

Rafael dijo...

Hola. estoy siguiendo los pasos tal cual indicaste, pero en el procedimiento almacenado cuando llamo al cursor me envía el siguiente mensaje:
El cursor no se puede utilizar en una sentencia OPEN de SQL dinámico

el cursor lo estoy usando de la siguiente manera:

v_Query2:= 'selec nombre, perfil from Perfiles';

OPEN cur_OUT FOR v_Query2;

Pepelu dijo...

Rafael, tu pregunta no tiene mucho que ver con este artículo pero lo que veo a simple vista es que a la sentencia SELECT le falta la "T" final.

Percy Espinoza dijo...

Hay algo q no me va bien en esto... los cursores, luego de usarse se quedan abiertos y dado que no se pueden cerrar ya que no retornarian resultados, como se haria un procedimiento adicional para que sea llamado y cierre el cursor cuando ya no se necesite? hay otra forma de retornar resultados sin cursor? gracias

José Luis Pérez dijo...

Hola Percy, el cursor lo puedes cerrar en el procedimiento que lo utiliza como entrada. Es decir, si nos basamos en el ejemplo del artículo, el cursor se cerraría en el procedimiento o función que llame al procedimiento datos_empleados.

YonRico dijo...

Hola amigo interesante el tema,
una consulta...!!
quiero utilizar el cursor
cursor1 OUT empleados_pkg.empleados_type

para cualquier tabla..! es decir llamarla desde cualquier procedimiento almacenado como podría hacerlo ?.. me apoyas porfavor?

cursor1 OUT pqt_pkg.CualquierTabla_type

gracias!!!

Ingrid Rojas dijo...

Buenas estoy tratando de aplicar lo que explicas pero cuando estoy haciendo el procedimiento como salida el cursos que ya tengo definido en el paquete me dice pls-00302 Commponet 'b_s_pkg' must be declared