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.
41 comentarios:
Hola, acabo de volver de vacaciones y justamente necesitaba esto.
Gracias de nuevo por tus artículos.
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?
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;
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.
Gracias Pepelu por tu respuesta.
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;
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
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;
Buen articulo, me ha servido, y buen blog, he conseguido buena info de plsql aqui
salu2
esa es la manera 'primitiva' ahora se usa sys_refcursor. suerte , buen blog !!!
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.
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
Hola Lukaz, en este caso me has pillado porque no tengo ninguna experiencia con Visual Basic conectado a una base de datos Oracle.
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
Ulises, echa un vistazo a esta página:
http://forums.oracle.com/forums/thread.jspa?threadID=841752
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--
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.
Saludos,
Viejo me sirvio de gran ayuda. Te lo agradezco. Pregunta, no tienes tutoriales sobre OracleForms y plsql??
Gracias por tu ayuda!
No, no tengo ningún tutorial de Oracle Forms. Con este de PL/SQL ya tengo trabajo más que suficiente.
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???
Marcos, efectivamente tienes que definir la referencia al cursor.
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)
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.
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.
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.
excelente blog me fue muy util.. gracias
Tengo una duda es posible retornar el cursos desde el procedimiento sin necesidad de crear el paquete.
agradezco la colaboracion
Hola Jairo, yo no conozco ninguna otra forma de hacerlo. El cursor de salida debe estar definido en un paquete PL/SQL.
gracias , muchas gracias x tu ayuda, no sabes lo mucho q me va a servir esto
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;
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.
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
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.
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!!!
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
Si llamo a in procedimiento almacenado q me devuelve un cursor con un order by, El Programa llamante puede modificar ese orden?
Hola
Cómo llamo el procedimiento almacenado que devuelve el cursor?
Saludos..
Hola Unknown, ya respondí a esta pregunta en un comentario anterior. Revisa los comentarios del 7 de agosto de 2009.
Hola con todos, como se podría controlar en el caso que no devuelva nada, en el else, si no necesito q se abra el cursor
Hola, se puede controlar que si tengo un if antes del cursor que no me deja abrir por la respuesta del if es falso, ce cierre mi cursor si no lo utilizo
Hola Carpat, puedes utilizar el atributo %ISOPEN.
IF c_emp%ISOPEN THEN
CLOSE c_emp;
END IF;
Publicar un comentario