Anuncios en tutorial de programación PLSQL

martes, 5 de diciembre de 2006

Cursores en PL/SQL

Programación PL/SQL en el WCLos cursores se utilizan en PL/SQL para manejar las sentencias SELECT. Un cursor esta formado por un conjunto de registros devueltos por una instrucción SQL del tipo SELECT. Desde un punto de visto interno a la base de datos Oracle, los cursores son segmentos de memoria utilizados para realizar operaciones con los registros devueltos tras ejecutar una sentencia SELECT.

Se pueden distinguir dos tipos de cursores:

- Cursores implícitos: Se utilizan cuando la sentencia SELECT devuelve un solo registro y su formato es como sigue:

     DECLARE
       lsalario empleados.salario%TYPE;
       ldni empleados.dni%TYPE;
     BEGIN
       SELECT salario, dni
       INTO lsalario, ldni
       FROM empleados
       WHERE nombre = 'Juan'
       AND apellidos = 'Rodrigo Comas';
       /* Resto de sentencias del bloque */
     END;

Nota: Mucha gente considera que las sentencias UPDATE, dentro de un bloque PLSQL, son también cursores implícitos, no obstante, yo prefiero no incluirlas dentro de este concepto.

- Cursores explícitos: Se utilizan cuando la sentencia SELECT puede devolver varios registros. También se pueden utilizar en consultas que devuelvan un solo registro por razones de eficiencia con respecto a los cursores implícitos, eficiencia que mejorará especialmente si el cursor explícito se tiene que ejecutar varias veces dentro del bloque de código PL/SQL.

Un cursor explícito tiene que ser definido previamente como cualquier otra variable PLSQL y debe serle asignado un nombre. Veamos un ejemplo que muestra el DNI y el salario de los trabajadores incluidos en la tabla empleados:

     DECLARE
       CURSOR cemp IS
       SELECT salario, dni
       FROM empleados;
       cepm_rec cemp%ROWTYPE;
     BEGIN
       FOR cemp_rec IN cemp
       LOOP
         DBMS_OUTPUT.PUT_LINE
           (cemp_rec.dni || ' ' || cemp_rec.salario);
       END LOOP;
     END;

Los cursores explícitos admiten el uso de parámetros. Los parámetros deben declararse junto con el cursor. Por ejemplo:

     DECLARE
       CURSOR cemp(pnombre IN VARCHAR2) IS
       SELECT salario, dni
       FROM empleados
       WHERE nombre = pnombre;
       cepm_rec cemp%ROWTYPE;
       vnombre VARCHAR2(20);
     BEGIN
       vnombre := 'Juan';
       DBMS_OUTPUT.PUT_LINE
         ('Sueldo de los empleados con nombre ' || vnombre);
       FOR cemp_rec IN cemp(vnombre)
       LOOP
         DBMS_OUTPUT.PUT_LINE
           (cemp_rec.dni || ' ' || cemp_rec.salario);
       END LOOP;
     END;

En los ejemplos anteriores los cursores se han controlado con la sentencia FOR pero también pueden controlarse mediante el uso de las sentencias OPEN, FETCH y CLOSE. La sentencia OPEN identifica el cursor que se tiene que utilizar. La sentencia FETCH pone, registro a registro, los valores devueltos por el cursor en las variables correspondientes, variables que pueden estar constituidas por una lista de variables o un registro PLSQL (este es el caso de los ejemplos que incluyo en este artículo). Por último, la sentencia CLOSE cierra el cursor y libera la memoria reservada. Veamos como quedaría nuestro ejemplo utilizando este tipo de sentencias en lugar de utilizar la sentencia FOR:

     DECLARE
       CURSOR cemp(pnombre IN VARCHAR2) IS
       SELECT salario, dni
       FROM empleados
       WHERE nombre = pnombre;
       cepm_rec cemp%ROWTYPE;
       vnombre VARCHAR2(20);
     BEGIN
       vnombre := 'Juan';
       DBMS_OUTPUT.PUT_LINE
         ('Sueldo de los empleados con nombre ' || vnombre);
       OPEN cemp(vnombre);
       LOOP
         FETCH cemp INTO cemp_rec;
         DBMS_OUTPUT.PUT_LINE
           (cemp_rec.dni || ' ' || cemp_rec.salario);
         EXIT WHEN cemp%NOTFOUND; -- Último registro.
       END LOOP;
       DBMS_OUTPUT.PUT_LINE
         ('Número de empleados procesados ' || cemp%ROWCOUNT);
       CLOSE cemp;
     END;

Sólo me queda señalar que existe una tercera opción para manejar cursores que a mí, particularmente, no me gusta utilizar pero que no quiero omitir:

     DECLARE
       TYPE ecursor IS REF CURSOR RETURN empleados%ROWTYPE;
       cemp ecursor;
       cepm_rec empleados%ROWTYPE;
     BEGIN
       OPEN cemp FOR SELECT * FROM empleados;
       FOR cemp_rec IN cemp
       LOOP
         DBMS_OUTPUT.PUT_LINE
           (cemp_rec.dni || ' ' || cemp_rec.salario);
       END LOOP;
     END;

Finalmente sólo mencionar que existen cuatro tipos de atributos que nos permiten controlar la ejecución de un cursor:

- %ISOPEN: Devuelve "true" si el cursor está abierto.
- %FOUND: Devuelve "true" si el registro fue satisfactoriamente procesado.
- %NOTFOUND: Devuelve "true" si el registro no pudo ser procesado. Normalmente esto ocurre cuando ya se han procesado todos los registros devueltos por el cursor.
- %ROWCOUNT: Devuelve el número de registros que han sido procesados hasta ese momento.

65 comentarios:

Lom dijo...

Hola,
Hata hoy siempre había manejado los cursores usando OPEN, FETCH y CLOSE con EXIT WHEN ..%NOTFOUND para salir del LOOP.
Me ha sorprendido la simplicidad de manejo explicada en:
DECLARE
CURSOR cemp IS
SELECT salario, dni
FROM empleados;
cepm_rec cemp%ROWTYPE;
BEGIN
FOR cemp_rec IN cemp
LOOP
DBMS_OUTPUT.PUT_LINE
(cemp_rec.dni || ' ' || cemp_rec.salario);
END LOOP;
END;

Gracias

Carlos Eduardo dijo...

Saludos, muy buena la explicacion, bueno tengo una preg.

Tengo hecho a base de cursores con query's dinamicos un procedimiento en oracle y pues la verdad tarda en los tiempos de respuesta quisiera saber como reducir estos tiempos, si acaso el uso de los cursores reduce estos tiempos o incrementa o cual de las formas que explicas es mas efectiva, bueno bueno esa es una preg :P, la otra es que para el caso de pruebas he corrido mi store y ayer termine el store sin finalizar este (ctrl+alt+delete :P) e intente de nuevo pero con unos cambios en el store (sin modificar parte del cursor o forma de uso) y lo deje asi toda la noche , y hoy revise y no ha terminado :S la pregunta es si el cursor se queda bloqueado o algo asi en la base de datos (ya que lo corri desde una terminal pues no tengo acceso a donde esta montada la base de datos) asi es que tengo esas 2 dudas, me gustaria saber si sabes algo al respecto te agradeceria, bueno si puedes pues postea algo o mandame un correo a char_corp@hot.... gracias.

Pepelu dijo...

Hola Carlos Eduardo,

Los dos motivos más usuales por los que un cursor tarda demasiado tiempo en ejecutarse son:

1) Un problema de rendimiento, es decir, que dentro del cursor tengas alguna consulta dinámica que tarde mucho tiempo en ejecutarse.

En este caso te recomiendo que pruebes las consultas dinámicas una a una para determinar cual es la que te está dando problemas.

2) Que realices alguna operación que necesite bloquear algún objeto de la base de datos y dicho objeto esté ya bloqueado y el cursor se quede experando hasta que el objeto quede liberado.

CRivera dijo...

si se cual cual es la que tarda mas tiempo pero pues es por el tipo de consulta algun consejo para realizar querys mas rapidos, quiero decir mira mi query es algo asi
(SELECT B.ACCOUNT_NO, SUM(B.BALANCE_DUE) FROM CMF_BALANCE@NAMESERVER01, CMF@NAMESERVER01C WHERE B.ACCOUNT_NO = C.ACCOUNT_NO AND C.ACCOUNT_STATUS >= -1 AND B.PPDD_DATE < SYSDATE GROUP BY B.ACCOUNT_NO )

donde NAMESERVER01 es el nombre de uno de los servidores de donde extraigo infomacion, (por eso la necesidad de executar querys dinamicos) y pues no se si al efectuar la operacion de SUM o por el tamannio de la bd (entre 590000 60000 registros) sea el problema, el caso es que si existe la manera de hacer algo como hilos o threads para ejecutar varios de estos querys o algun consejo pepelu.. ???

Pepelu dijo...

No veo nada extraño en esa consulta. Supongo que ambas o al menos una de las tablas tendrá un índice sobre ACCOUNT_NO.

Cuando se accede a bases de datos en otros servidores no se pueden utilizar los índices de las tablas remotas y esto suele generar problemas de rendimiento si se unen con tablas de la base de datos local. Pero en tu caso ambas tablas están en el mismo servidor y entiendo que esto no debería ser un problema.

Prueba a ejecutar la consulta de manera no remota para ver si de esa manera la consulta se ejecuta sin problemas.

DaEnVa dijo...

Hola Pepelu, tengo una pregunta siempre he manejado los cursores con FOR (así como tu lo haces en estos ejemplos), pero por ahí me pasaron un Tip para optimizar el tiempo de respuesta de un cursor, claro siempre teniendo en cuenta de como es que se crea la consulta respetando los indices y de mas, este es el ejemplo del FOR que estoy utilizando y quisieras que me dieras tu opinión al respecto.

DECLARE

BEGIN
FOR cemp_rec IN (SELECT salario sal, dni di
FROM empleados;)
LOOP
DBMS_OUTPUT.PUT_LINE
(cemp_rec.di || ' ' || cemp_rec.sal);
END LOOP;
END;

Saludos.

Pepelu dijo...

Hola DaEnVa,

Yo con las sentencias FOR siempre utilizo cursores, más que nada porque el código es más fácil de leer.

Sinceramente no creo que escribir directamente la consulta SELECT dentro del FOR mejore el rendimiento. Quizá puede que mejore algo el tiempo de parsing, pero la mejora no creo que sea significativa.

DaEnVa dijo...

Hola pepelu, gracias por tu opinión y para ser sincero ahora me quedo mas claro este método, ahora me surge otra duda, que sucede esto con la utilización de la memoria de parte del cliente, cual de todos los métodos expuestos aquí crees tú, que utiliza menos memoria en el computador del cliente, esta duda surge porque en donde trabajo, por motivos como siempre económicos, muchos computadores utilizan RAM de 256mb, se que es poco pero mi intención es optimizar esta parte también, de antemano gracias por tu ayuda.

Salu2.

Pepelu dijo...

DaEnVA,

No creo que haya gran diferencia en cuanto al consumo de memoria en los clientes.

Vania dijo...

Hola a todos:
Tengo una duda sobre cursores y necesito ayuda.
Tengo por ejemplo:
open c1 for
select *
from empleados
Estoy trabajando con REF CURSOR y necesito 2 cosas:
1. recorrer el cursor (c1) para obtener ciertos conjunto de datos
(todos los departementos asociados al conjunto de empleados)
2. enviar c1 de vuelta a un procedimiento almacenado.
Asi las soluciones posibles son:
A: duplicarlo antes de comenzar a recorrer c1:
c2 := c1,
Pero una vez recorrido c1, el cursor C2 queda tambien sin registros y por tanto no lo puedo enviar de vuelta para hacer el paso 2.
B: una vez recorrido c1, volverlo al punto de inicio (se puede hacer eso con el REF CURSOR?)
Estaria muy agradecida con su ayuda,
desde ya muchas gracias
Saludos

Pepelu dijo...

Hola Vania,

No entiendo muy bien lo que quieres hacer. Un cursor no se puede duplicar, una vez recorrido lo puedes volver a abrir.

Si quieres seguir preguntándome puedes hacerlo enviándome un email a oracleyplsql[arroba]gmail[punto]com.

Omar dijo...

Hola Pepelu,

Excelente tu blog, muy claro y directo al punto. Tengo unas dudas y me gustaría compartirlas para ver si las puedo despejar.

Actualmente estoy realizando un proyecto en mi trabajo que extrae información de una bd remota, que luego son almacenados en una bd local. Para hacer esto utilizo un cursor dentro un stored procedure, el problema esta en que se demora mucho para ejecutar la actualización. Aqui te va un ejemplo:


create or replace procedure opt_ag_blocking is

segid NUMBER;
bscid NUMBER;
agblocking NUMBER;

cursor vector is



SELECT
a.SEGMENT_ID SEGMENT_ID, a.int_id int_id,
DECODE(SUM(A.IMM_ASSGN_SENT+A.IMM_ASSGN_REJ),
0,
0,
100*(1-(SUM(A.DEL_IND_MSG_REC)/SUM(A.IMM_ASSGN_SENT+A.IMM_ASSGN_REJ)))
) ag_blocking

FROM P_NBSC_RES_ACCESS@BD_REMOTA A

WHERE
(A.PERIOD_START_TIME >= TRUNC(SYSDATE-1) AND A.PERIOD_START_TIME < TRUNC(SYSDATE))


GROUP BY segment_id, int_id;

begin

open vector;

loop
FETCH vector INTO segid, bscid, agblocking;
EXIT WHEN vector%NOTFOUND;



UPDATE OPTIMIZATION_PERFORM
SET OPTIMIZATION_PERFORM.ag_blocking = agblocking
WHERE OPTIMIZATION_PERFORM.segment_id = segid AND
OPTIMIZATION_PERFORM.int_id = bscid and
OPTIMIZATION_PERFORM.date_download = trunc(sysdate-1);



end loop;

COMMIT;

close vector;


EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('Elemento no encontrado');
end opt_ag_blocking;

/

-- fin sp

Este es uno de los menos complejos, los mas complejos duran siete minutos si ejecutas el query solamente y casi una hora si ejecutas el procedimiento, ademas deberia recolectar alrededor de 2 mil registros por dia.
Es la mejor manera de hacerlo? o existe una mejor solucion? Cualquier ayuda es valida

Gracias y saludos desde Venezuela

Pepelu dijo...

Hola Omar,

El acceso a tablas en bases de datos remotas puede ralentizar algunas queries o consultas cuando se mezclan con tablas locales ya que no se pueden utilizar los índices de las tablas remotas pero en tu caso la consulta sólo accede a una tabla remota por lo que esto no debería ser un problema.

Supongo que en la BD remota existirá un índice sobre PERIOD_START_TIME y, sobre todo, que los índices que utiliza el UPDATE son los correctos.

Yo creo que el código que utilizas es el más óptimo aunque dependiendo de los datos es posible que todo se pudiera hacer en un sólo UPDATE sin necesidad de utilizar un CURSOR.

Si tienes alguna pregunta adicional puedes enviarme un email a oracleyplsql[arroba]gmail[punto]com.

luis dijo...

Estoy interesado en saber como se hacen commits dentro de un cursor, por ejemplo tengo que actualizar el valor catastral de todos los los inmuebles de zaragoza. Hago un cursor que me devueelva las finca y para cada finca tengo que hacer un commit, para no tener un segmento de rollback enorme. ¿como lo hago?

Pepelu dijo...

Hola Luis,

Sólo tienes que incluir el comando COMMIT dentro del cursor.

Miguel dijo...

Hola Luis
Consulta cuando pones
CURSOR cemp(pnombre IN VARCHAR2) IS
SELECT salario, dni
FROM empleados
WHERE nombre = pnombre;

que sucede si este cursor es parte de un SP y el pnombre es un parametro de entrada de este SP?, tambien tengo que declarar el pnombre en CURSOR cemp(pnombre IN VARCHAR2)?... o simplemente puedo utilizar CURSOR cemp IS
SELECT salario, dni
FROM empleados
WHERE nombre = pnombre;
sabiendo que el pnombre viene como parametro de entrada del SP?...
Gracias de Antemano por tu respuesta

Pepelu dijo...

Hola Miguel, se puede hacer lo que comentas, incluso se pueden utilizar variables que no sean parámetros de entrada.

Claudishi dijo...

Hola, bueno mi duda es, tengo que llamar a un sp y su salida(output) es un cursor y quiero recorrerla ¿? bueno eso.. no tengo q llamarla de ningun lugar solo quiero una query q me escriba en pantalla el cursor..

Pepelu dijo...

Para salidas por pantalla debes utilizar la función DBMS_OUTPUT (pulsa en el enlace si quieres algo más de información sobre como utilizarla).

Algunos de los ejemplos de esta entrada la incluyen.

Julio dijo...

Hola, tengo un problema con un formato de fecha que recupero de un cursor, este es el codigo
CURSOR DATOS_PER(PAR_DNI NUMBER) IS SELECT PER_ID,
..
..
PER_FECHA_NACI,
FROM PERSONAS WHERE PER_DOCUMENTO = PAR_DNI;

y cuando recupero datos con este cursor, los grabo en un archivo de textos de la siguiente forma:

utl_file.put_line(vHandle_out, '00' || '|' ||
.
.
to_char(REGI_PER.PER_FECHA_NACI,'DD/MM/YYYY') || '|' ||
.
.
|| '|' , TRUE);

esto hace que la fecha se grabe con el formato 17/02/0059 y no encuentro la forma de que se grabe como 17/02/1959, desde ya muchas gracias por tu ayuda

Julio

Lom dijo...

Hola Julio,
¿Has probado a declarar el cursor de la siguiente manera:
CURSOR DATOS_PER(PAR_DNI NUMBER) IS SELECT PER_ID,
..
..
to_char(PER_FECHA_NACI,'dd/mm/yyyy'),
FROM PERSONAS ...

Pepelu dijo...

Hola Julio,

No sé muy bien por qué estás teniendo ese problema. Prueba con la sugerencia de Lom pero ambas opciones, la tuya y la de Lom, deberían funcionar igual.

Raton dijo...

Hola Pepelu:

Quisiera saber si tienes información sobre cursores de actualización que pudieras compartir.

Saludos

Pepelu dijo...

Hola Ratón,

Si no me equivoco un cursor de actualización no es más que un cursor normal con un UPDATE dentro, no creo que este tema se merezca una entrada en el blog.

knch dijo...

Hola no tengo mucha idea de oracle queria pedirles ayuda con lo siguiente tengo que remplazar el subquery por un cursor UPDATE

DETALLE_FACTURA D
SET D.PCL = L_PCL, D.ID_FLIA_PIEZA = L_FLIAPIEZA,
D.PNC = L_PNC, D.ID_ESTADO_ITEM_FACTURA = 2, D.ID_CAMPANIA = (SELECT DISTINCT F.ID_CAMAPANIA FROM FLIA_PIEZA_CAMPANIA F, CAMPANIA C WHERE F.ID_FLIA_PIEZA = L_FLIAPIEZA AND F.ID_CAMAPANIA = C.ID_CAMAPANIA AND C.BIMESTRE = D.BIMESTRE)
muchas gracias

Pepelu dijo...

Hola Knch, el código que has puesto esta incompleto y no sé muy bien como ayudarte. Mejor que contactes conmigo vía email (pepelublog[arroba]gmail[punto]com).

Raulinho dijo...

Hola tengo un cursor que me devuelve mas de 540757 registros y dentro del loop se hacen calculos cual seria la mejor manejar de paginar un cursor para que no consuma mucha memoria del servidor?

Gracias

Pepelu dijo...

Si realizas operaciones que gastan memoria dentro del cursor y quieres vaciarla cada cierto número de registros procesados (digamos X), lo más sencillo es crear un contador y hacer un COMMIT cada vez que se procesen X registros.

Raulinho dijo...

Hola pepelu gracias por responder , dime pero cuando la query del cursor trae muchos registros todos estos son cargados en memoria al momento de leer el cursor ? o es que segun lea cada registro recien es cargado en memoria?

Pepelu dijo...

Raulinho, la carga de registros es secuencial. Los registros se van leyendo uno a uno.

A lo mejor se cargan multiples registros en memoria, pero esta gestión la realizaría internamente Oracle y no es controlable desde el código.

Desde el punto de vista de la codificación sólo debes preocuparte si dentro del cursor ejecutas sentencias que utilizan memoria que no se libera hasta que se ejecuta un COMMIT (UPDATE, INSERT, DELETE, ...).

Edson dijo...

hola una consulta se puede hacer un cursor con la sentencia de select into?, porque hasta donde yo se con el select into solo retorna 1 registro, pero yo quiero mostrar todos los registros de mi tabla employees

Pepelu dijo...

Edson,

Los SELECT INTO son los cursores implícitos y, como tu dices, sólo admiten que el SELECT retorne un registro, pero con los cursores explícitos puedes seleccionar múltiples registros e irlos recorriendo de uno en uno.

Patolina dijo...

Hola necesito ayuda ... les cuento tengo un tabla con registros con importes y por otro lado otra con una tabla con brutos tengo que ir llenando un campo neto con lo que cada individuo cobrara restandole al bruto los registros que le corresponden de la otra tabla el tema es que cuando llegue a 0 deberia poner en un campo de cada registro el importe que no se pudo descontar.. lo pense con cursores pero tarda una enormidad .. alguien tiene idea como seria mas eficiente Pato

Pepelu dijo...

Hola Pato,

En este tipo de tareas lo más eficiente suele ser tratar de escribir el UPDATE en una sola sentencia PL/SQL. Yo creo que lo que necesitas hacer, aunque no tengo muy claro lo que realmente quieres conseguir, se podría hacer con una o dos sentencias UPDATE.

Si necesitas más ayuda puedes mandarme un correo a pepelublog[arroba]gmail.com.

Blog de ARR dijo...

Hola, tengo un SELECT para un cursor asi:
SELECT CODIGO_SUPER, NOMBRE_ENTIDAD
FROM TDOD_ENTIDAD
WHERE CODIGO_NIVEL1 = 1
AND CODIGO_NIVEL2 = 4
AND CODIGO_NIVEL3 = 0
AND CAPITAL = 'EX'
AND NATURALEZA = 'PR'
AND ESTADO_ENTIDAD = 1;

Que significa: devuelve todas las entidades del nivel jerarquico (1-4-0)
de capital 'EXtranjero' y naturaleza 'PRivada'

Pero cada uno de los valores del WHERE puede ir o no. Es decir, podria requerir las entidades del nivel jerarquico (1-4-0) sin que me interese la naturaleza y capital por lo tanto el query del cursor seria:

SELECT CODIGO_SUPER, NOMBRE_ENTIDAD
FROM TDOD_ENTIDAD
WHERE CODIGO_NIVEL1 = 1
AND CODIGO_NIVEL2 = 4
AND CODIGO_NIVEL3 = 0
AND ESTADO_ENTIDAD = 1;

O quisiera que mostrara todas las entidades pertenecientes al nivel jerarquico (1) por lo que el query del cursor seria:

SELECT CODIGO_SUPER, NOMBRE_ENTIDAD
FROM TDOD_ENTIDAD
WHERE CODIGO_NIVEL1 = 1
AND ESTADO_ENTIDAD = 1;

Resumiendo: el query del cursor cambia dependiendo de los parametros, cual el la mejor manera de hacer eso?

Actalmente realizo eso de esta manera:

SELECT CODIGO_SUPER, NOMBRE_ENTIDAD
FROM TDOD_ENTIDAD
WHERE CODIGO_NIVEL1 = NVL(PC_CODIGO_N1, CODIGO_NIVEL1)
AND CODIGO_NIVEL2 = NVL(PC_CODIGO_N2, CODIGO_NIVEL2)
AND CODIGO_NIVEL3 = NVL(PC_CODIGO_N3, CODIGO_NIVEL3)
AND CAPITAL = NVL(PC_CAPITAL, CAPITAL)
AND NATURALEZA = NVL(PC_NATURALEZA, NATURALEZA)
AND ESTADO_ENTIDAD = 1;

Si no quiero algun campo del WHERE mando el parametro en NULO, pero no se si es la mejor manera de hacerlo.

Gracias por sus comentarios.

Pepelu dijo...

Hola Blog de ARR, la forma que has empleado para dar solución a tu requerimiento es la más breve y económica en cuanto a número de líneas.

Sin embargo, dependiendo del valor que tomen los parámetros puedes encontrarte con problemas de rendimiento. Por lo que te sugiero pruebes el rendimiento de la consulta para los distintos valores que puedan tomar los mismos.

Si te encuentras con problemas de rendimiento, entonces tendrás que utilizar múltiples consultas dependiendo del valor que tomen los parámetros.

Los problemas de rendimiento se pueden producir porque el plan de ejecución de tu consulta puede ser bueno para determinados valores de los parámetros, pero puede ser totalmente erróneo cuando dichos parámetros toman otros valores.

DIOS COMO ME ABURRO dijo...

Buenas tardes,
primero felicitarte por la explicacion que hace tiempo me aclaro muchas dudas.
Ahora tengo otro problema, necesito crear un cursor que haga una query asi:
Select * from tabla where campo1 IN (variable)

Donde variable puede tomar los valores '01','03' o '02'

pero cuando le paso este parametro no retorna nada. Si sutituyo la variable por el literal la consulta retorna datos.

Un saludo y gracias por adelantado

Pepelu dijo...

Pues con ese tipo de problemas PLSQL no sé como es posible que te aburras. Bueno, ahora en serio, no sé por qué te esta dando problemas el tema de la variable, aunque tampoco entiendo por qué utilizas IN, en vez de directamente =.

Si quieres puedes mandarme un email a pepelublog[arroba]gmail[punto]com.

Pepelu dijo...

Sandra, o "Dios como me aburro", ya me ha explicado lo que realmente quiere hacer. Su idea es pasar a la consulta SELECT todos los valores del IN en un sólo parámetro. Esto es posible pero no en la forma que ella está utilizando.

Para aquellos que estéis interesados podéis leer este artículo:

SQL dinámico.

DIOS COMO ME ABURRO dijo...

Muchas gracias,
El lunes tranquilamente lo pondre en practica y ya te contaré que tal me ha ido. Un saludo

Raul dijo...

Hola, tengo el siguiente problemilla, quiero pasar como parametro de un procedimiento un ref_cursor, pero no se como acoplar los datos a este cursor.

Me explico un poco mas, estos datos no pertenecen a ninguna de mis tablas sino que son resultado del calculo de diversos datos.

Estos calculos me pueden dar de 1 a 3 registros con los datos de tipo, superficie y cuota (todos numericos) que son los que quiero pasar.

Un saludo.

Pepelu dijo...

Hola Raúl, a ver si esta entrada te sirve de ayuda: Cómo utilizar un cursor PL/SQL como parámetro de salida en un procedimiento.

Ivan dijo...

Buenas tardes Pepelu,

Mi problema es el siguiente, quería saber si dentro de una estructura de tipo registro se podria tener definido ademas de datos simples (number, char, etc) una referencia a un cursor.
Lo que yo quiero hacer es que un procedimiento me devuelva en un registro un cursor ademas de una seríe de datos simples.

Muchas gracias de antemano

Pepelu dijo...

Ivan, pueden hacer que el procedimiento te devuelva el cursor y tantos parámetros de salida como quieras.

CREATE OR REPLACE PROCEDURE eje (
parin1 IN NUMBER,
...
parinn IN VARCHAR2,
parout1 OUT NUMBER,
...
paroutn OUT VARCHAR2,
cursor1 OUT cursor.c_type )
AS

Marcos dijo...

Bien chicos, el tema esta super interesante, no se si me puedan ayudar, lo que pasa es que quiero crear una funcion que tenga como parametros de entrada un cursor, pero me envia error, lo he intentado de dos maneras:
function iscomplete(cur cursor.c_type ) return boolean IS...

y asi:

function iscomplete(cur cursor ) return boolean IS...

me envia error, espero que me puedan ayudar y gracias de antemano!

:)

Pepelu dijo...

Hola Marcos,

Echa un vistazo a esta entrada del blog:

Cursor PL/SQL como parámetro de salida en un procedimiento.

Espero que te sea de ayuda.

Marcos dijo...

Gracias Pepelu
Me ha servido de mucho

Omar dijo...

Hola Pepelu,

Estoy creando un cursor que realice lo siguiente:

CREATE ROLE ROLE_DBLINK
/

BEGIN

FOR CURSOR1 IN (SELECT decode(object_type,
'TABLE','GRANT SELECT ON '||'PSP'||'.',
'VIEW','GRANT SELECT ON '||'PSP'||'.',
'SEQUENCE','GRANT SELECT ON '||'PSP'||'.')||object_name||' TO ROLE_DBLINK' AS SENTENCIA
FROM user_objects
WHERE
OBJECT_TYPE IN ( 'TABLE', 'VIEW', 'SEQUENCE')
ORDER BY OBJECT_TYPE) LOOP

EXECUTE IMMEDIATE CURSOR1.SENTENCIA;

END LOOP;

END;
/

GRANT CREATE SESSION TO ROLE_DBLINK
/

GRANT CONNECT TO ROLE_DBLINK
/

CREATE USER PSP_DBLINK IDENTIFIED BY &clave
/

GRANT ROLE_DBLINK TO PSP_DBLINK
/

Y esto es lo que me arroja el archivo de salida que utilizo:

Role created.

BEGIN
*
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table
ORA-06512: at line 12



Grant succeeded.


Grant succeeded.

Enter value for clave: dblink#12
old 1: CREATE USER PSP_DBLINK IDENTIFIED BY &clave
new 1: CREATE USER PSP_DBLINK IDENTIFIED BY dblink#12

User created.


Grant succeeded.

Es un error a nivel del cursor? o simplemente es a nivel de lo que me traigo de la BD?

Cualquier ayuda sería de mucha utilidad...

Gracias,

Omar

Pepelu dijo...

Hola Omar, una pregunta interesante.

El problema surge a nivel de lo que estás trayendo desde la tabla USER_OBJECTS.

El cursor PL/SQL debería funcionarte correctamente para views y sequences pero hay alguna tabla de tipo IOT que te está generando el error "ORA-25191: cannot reference overflow table of an index-organized table".

Para objetos del tipo tabla debes utilizar USER_TABLES y limitar las tablas elegidas para que no sean de tipo IOT (Index Organized Tables).

Te dejo un ejemplo:

BEGIN

FOR C1 IN (
SELECT table_name
FROM user_tables
WHERE iot_type IS NULL) LOOP

EXECUTE IMMEDIATE 'GRANT SELECT ON '||C1.table_name||' TO ROLE_DBLINK';

END LOOP;

END;

Triotec dijo...

hola,
he sabido que cuando accedes a tablas de otros esquemas, lo mejor es hacerlas vistas, ya sea normal o materializada. y desde el esquema consultarla y hacer join con las otras tablas. A mi me ha funcionado con tablas enormes y las consultas son pesadas, son de estadisticas. Asi que prueba eso y me cuentas.

Slds,
Cristian Cortes A
ccortesalzamora@gmail.com

Escipion dijo...

Gracias Pepelu, me ha servido de mucho

harris jesus dijo...

hola muy buenas noches a todos mi tema es como puedo realizar un un trigger en pl/sql de tabla digamos cliente que me permita registrar en una tabla auditoria_cliente todos los clientes que son eliminados..... por favor quien me pueda ayudar se lo agradeceria mucho para mi es muy urgente

Pepelu dijo...

Hola Harris Jesís, te aconsejo que eches un vistazo a este artículo del blog: Triggers PLSQL.

Mauri dijo...

Hola, disculpen pero me veo obligado a hacer varias preguntontas, que en todos los articulos las dan por sabidas y son: Donde meto todo ese codigo? DECLARE

BEGIN
FOR ...
FROM ...
LOOP
...
END LOOP;
END;

en el sqlPLus por ejemplo? esto lo puedo poner en un .sql y correrlo como un script por ejemplo, y luego darle run desde la consola del sqlPlus? y desde el SQLNavigator como seria?
Y luego de declarado el cursor, como lo pruebo? tengo que hacer una aplicacion java que lo invoque?

Muchas gracias y perdón por la ignorancia

Pepelu dijo...

Hola Mauri,

En SQL*Plus puedes ir metiendo línea a línea en el interfaz y el script se ejecutará al meter la última línea del script. También puedes salvar el script en un fichero .sql y abrirlo desde SQL*Plus.

No conozco el funcionamiento del SQLNavigator pero imagino que podrás copiar el script y existirá algún botón "ejecutar" para que el script se ejecute.

No necesitas Java para invocar código PL/SQL.

Fabian dijo...

Hola Tengo un cursor que no sale cuando pasa por el ultimo registro. Me pueden ayudar...

declare
correlativo number;
cursor cursor_corre is Select codigo From dps_institucion where nombre not in ('BECH');
institucion cursor_corre%ROWTYPE;
begin
OPEN cursor_corre;
loop
FETCH cursor_corre INTO institucion;
Select nvl((max(foliodps)+1),1) into correlativo From SALUD.ifw_dps where CODIGO_INSTITUCION = institucion.codigo;
insert into DPS_CORRELATIVO (COD_INSTITUCION, COR_DPS)
values (institucion.codigo,correlativo);
commit;
EXIT WHEN cursor_corre%NOTFOUND;
end loop;
Close cursor_corre;
end;

La sentencia Select me devuelve 6 registro que almaceno en la variable institucion y lo recorro, al momento de pasar por el ultimo registro, el cursor no se termina y comienza de nuevo, el fin de archivo no se ejecuta correctamente. Me pueden decir que falta.

Gracias

Pepelu dijo...

Hola Fabian, el código PLSQL que aparece en tu comentario está correctamente escrito. no veo nada incorrecto y debería terminar al recorrer todos los registros del cursor.

Fabian dijo...

Si, esta correctamente escrito el codigo PLSQL, me pueden ayudar con otra sintaxis de hacer el cursor.

Gracias

Pepelu dijo...

Puedes usar la sentencia FOR. En el artículo tienes algunos ejemplos y no te debe resultar complicado pasar de utilizar OPEN, FETCH y CLOSE, a utilizar simplemente el FOR de PL/SQL.

darkone2k4 dijo...

Hola amigo.
Muy buena tu página.

Tengo una consulta, de novato tal vez.
La pregunta es la siguiente:
Es posible pasar el "resultado" de un cursor a otro dentro de un procedimiento??

Desde ya, muchas gracias.

José Luis Pérez dijo...

Aunque no sé si he entendido bien tu pregunta, creo que en este otros artículo encontrarás la respuesta:

Cursores PL/SQL como parámetro de salida en un procedimiento.

darkone2k4 dijo...

Gracias por la respuesta José Luis, te explico un poco.
Tengo un cursor que utilizo en un procedimiento el cual recorro para hacer algunas comprobaciones dentro del mismo procedimiento, devolviéndome unos mensajes que recibo en Delphi. Lo que necesito es ese cursor pasarlo a otro cursor y utilizarlo en otro procedimiento.

Este es parte del código:

CURSOR xCURSOR (pSEDE VARCHAR2,pAGNO VARCHAR2,pMES VARCHAR2,pTIPO VARCHAR2,pFOLIO VARCHAR2,pACT VARCHAR2 ) IS
SELECT
FLAG_ANA,
FLAG_DOC,
FLAG_NUM,
FLAG_PRO,
SEDE,
AGNO...

Después proceso el cursor de la siguiente manera:

rgCon xCURSOR%ROWTYPE;

Begin
OPEN xCURSOR(pSEDE,pAGNO,pMES,pTIPO,pFOLIO,pACT);
LOOP
FETCH xCURSOR INTO rgCon;
IF rgCon.Sede = '1' THEN
.....

Al usar de esa manera el cursor, no puedo declararlo como CURSOR de SALIDA.

José Luis Pérez dijo...

No entiendo que quieres decir con pasar un cursor a otro cursor. Te agradecería que utilices mi emal para continuar con esta consulta (pepelublog[arroba]gmail[punto]com).

Ellery Sammy Valest Torres dijo...

Como puedo devolver el resultado de una consulta dinamica en una variable tipo ref cursor.

La consulta se arma en tiempo de ejecucion.

Agradeceria su ayuda

José Luis Pérez dijo...

Hola Ellery,

Echa un vistazo a este artículo, creo que te servirá de ayuda:

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