Dentro de un procedimiento almacenado se pueden usar cursores de forma parecida a como hemos visto en 4gl.
* Con FOREACH:
FOREACH
SELECT id, nombre
INTO idCliente, nombreCliente
FROM clientes
ORDER BY id
RETURN idCliente, nombreCliente WITH RESUME;
END FOREACH;
En este caso la query asociada al CURSOR debe ser especificada inmediatamente despues de la palabra FOREACH.
Es importante recalcar que debemos utilizar la clausula 'WITH RESUME' si queremos que el bucle continue y obtener los resultados de todas las filas proporcionadas por la query.
* De forma DINAMICA:
Pongo el mismo ejemplo que en la entrada anterior del blog para que podais ver que hay muy pocas diferencias.
LET myQuery = "SELECT pedidos.id_pedido, pedidos.fecha FROM pedidos";
--
--Incluyo la tabla de paises si se ha definido un filtro
--
IF filtro IS NOT NULL THEN
LET myQuery = TRIM(myQuery) || ", paises";
END IF;
LET myQuery = TRIM(myQuery) ||
" WHERE pedidos.fecha BETWEEN '01/01/2015' AND '31/12/2015';
--
-- Incluyo las condiciones de Join con la tabla de paises
--
IF filtro IS NOT NULL THEN
LET myQuery = TRIM(myQuery) ||
" AND paises.nombre = " || TRIM(filtro) ||
" AND pedidos.id_pais = paises.id_pais";
END IF;
Una vez montada la query en la variable de texto, declaramos el CURSOR:
--
-- Preparo la variable que contiene la query antes de declarar el cursor
--
PREPARE mySql FROM myQuery;
DECLARE myCursor CURSOR FOR mySql;
OPEN myCursor;
FETCH myCursor INTO idPedido, fechaPedido;
IF (SQLCODE = 0) THEN
WHILE 1=1
RETURN id_pedido, fechaPedido WITH RESUME;
FETCH myCursor INTO idPedido, fechaPedido;
IF (SQLCODE = 100) THEN
EXIT WHILE;
END IF;
END WHILE;
END IF; CLOSE myCursor;
FREE myCursor;
FREE mySql;
Si teneis cualquier duda me poneis un comentario y procuraré responderos lo antes posible.
Hasta pronto.
Si la query asociada a la declaración de un CURSOR puede variar en funcion de determinadas condiciones, no nos queda mas remedio que definirlo de manera dinámica a partir de una variable:
Por ejemplo, imaginemos que queremos seleccionar los pedidos del ultimo año, pero el usuario puede optar por filtrar su pais de origen o no.
Dependiendo de si existe un filtro o no deberemos incluir un JOIN en nuestra QUERY:
Montamos la query en una variable de texto:
DEFINE myQuery CHAR(300) ,
datosPedido LIKE pedidos.*,
filtro CHAR(30)
---
---
---
LET myQuery = "SELECT pedidos.* FROM pedidos"
##
## Incluyo la tabla de paises si se ha definido un filtro
##
IF filtro IS NOT NULL THEN
LET myQuery = myQuery CLIPPED, ", paises"
END IF
LET myQuery = myQuery CLIPPED,
" WHERE pedidos.fecha BETWEEN '01/01/2015' AND '31/12/2015'
##
## Incluyo las condiciones de Join con la tabla de paises
##
IF filtro IS NOT NULL THEN
LET myQuery = myQuery CLIPPED,
" AND paises.nombre = ", filtro,
" AND pedidos.id_pais = paises.id_pais"
END IF
Una vez montada la query en la variable de texto, declaramos el CURSOR:
##
## Preparo la variable que contiene la query antes de declarar el cursor
##
PREPARE mySql FROM myQuery
DECLARE myCursor CURSOR FOR mySql
FOREACH myCursor INTO datosPedido
----
----
----
END FOREACH
FREE myCursor
FREE mySql
Hay que destacar que tenemos que hacer un FREE tanto del cursor como de la instruccion preparada para liberar recursos.
Hasta la próxima.
Supongo que algún jefe impaciente os habrá pedido alguna vez un informe de forma urgente (a mi me pasó hace tiempo...).
Por SQL es posible obtener información muy valiosa pero muchas veces el formato en el que se almacena la información no es del todo inteligible para la mayoría de los mortales y os habrá tocado 'maquillarlo' antes de entregarlo con el trabajo que eso conlleva.
Bueno, pues el uso de la expresión CASE en una sentencia SQL me ha servido para librarme de algunas de esas labores tan tediosas.
Imaginemos la típica tabla de pedidos en la que tenemos una columna que indica la situación del mismo con estos valores:
- 10-Recibido
- 20-En fabricación
- 30-Terminado
- 40-Enviado
- 50-Entregado
En un informe normal obtenido por SQL unicamente podriamos obtener el código de la situación y tendríamos que explicar su significado.
Sin embargo si utilizamos la expresión CASE podemos obtener algo como esto:
SELECT
id_pedido PEDIDO,
CASE situacion
WHEN 10 THEN "RECIBIDO"
WHEN 20 THEN "EN FABRICACION"
WHEN 30 THEN "TERMINADO"
WHEN 40 THEN "ENVIADO"
WHEN 50 THEN "ENTREGADO"
ELSE "DESCONOCIDO"
END SITUACION,
fecha_pedido FECHA,
cliente CLIENTE
FROM PEDIDOS
Ya sé que el ejemplo es algo sencillo pero estoy seguro de que le vereis la forma de sacarle partido.
Lo que no estoy seguro es de si estas expresiones son estandares de SQL y se pueden utilizar con otros gestores de Bases de Datos.
Eso ya me lo comentais vosotros...
Muchas veces se nos da la situación de que un procedimiento debe hacer muchas operaciones que conviene englobar dentro de una transaccion.
Esto lo solucionamos incluyendo todas las instrucciones entre las etiquetas BEGIN WORK y COMMIT WORK.
El problema viene cuando este procedimiento puede ser llamado desde diferentes sitios, es decir, puedo intentar ejecutarlo desde una aplicación Java, PHP o incluso desde el propio dbaccess y no sabemos si cuando se hace la llamada la transacción ya se encuentra abierta o no.
Para solucionar este problema lo que suelo hacer es capturar el error -535 (Already in transaction), de forma que cuando intento abrir la transacción en el procedimiento salta esta excepcion asumo que quien lo está ejecutando ya ha abierto la transaccion previamente.
Si he detectado que existe una transaccion abierta previa, evito cerrar la transaccion dentro del procedimiento para que siga su curso.
Por ejemplo:
CREATE PROCEDURE sp_myProcedure ()
DEFINE transactionOpen SMALLINT;
ON EXCEPTION IN (-535)
LET transactionOpen = 1; -- Activo la variable de control
END EXCEPTION WITH RESUME;
LET transactionOpen = 0; -- Incia el procedimiento poniendo valor 0 a la variable de control
BEGIN WORK; -- En este momento saltaria la excepcion -535 y se activaria a 1 la variable de control
---
---
---
--- Solo cierro la transaccion si no se ha activado la variable transactionOpen
---
IF transactionOpen = 0 THEN
COMMIT WORK;
END IF;
END PROCEDURE
Espero que os sirva de utilidad.
