Stored Procedure en MySQL (parte 2) [iterate, cursores y handlers]


  
    Stored Procedure en MySQL (parte 1) [inicio]
    Stored Procedure en MySQL (parte 2) [iterate, cursores y handlers]
    Stored procedure en MySQL (parte 3) [respaldo y recuperación]
    Stored Procedure en MySQL (parte 4) [validaciones]

Continuando con el post anterior donde se hacia un repaso de los stored procedure en MySQL, señalando las ventajas que nos deja hacer uso de ellos y recalcando desde lo mas básico en adelante.

ITERATE etiqueta

Habíamos visto entre las estructuras de control, el Loop (Etiqueta : Loop) que se ejecuta hasta que encuentre la sentencia LEAVE Etiqueta. Hay otra sentencia que podemos usar (dentro de cualquier bucle) y cuyo nombre es ITERATE, su función es parecida al CONTINUE de PHP, indicar que debe saltarse el resto del procedimiento (dentro del bucle) y continuar con la siguiente iteración.

DROP PROCEDURE IF EXISTS ejemplo //
CREATE PROCEDURE ejemplo()
BEGIN
   DECLARE var INT;
   SET var = 0; -- inicializamos la variable "var"
   loop_label: LOOP -- inicio del ciclo
       SET var = var + 1; -- contador
      IF var = 3 THEN
         ITERATE loop_label; -- se salta el resto del procedimiento e itera de nuevo
      END IF;
      -- el siguiente texto no se imprimira si var es igual a 3
      SELECT ' numero: ',var;
      IF var >= 4 THEN
         LEAVE loop_label; -- informa que debe salir del loop
      END IF;
   END LOOP; -- fin del loop
END; //

Ejecutando el código:

mysql> DROP PROCEDURE IF EXISTS ejemplo //
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE ejemplo()
    -> BEGIN
    ->    DECLARE var INT;
    ->    SET var = 0; -- inicializamos la variable "var"
    ->    loop_label: LOOP -- inicio del ciclo
    -> 
    ->       SET var = var + 1; -- contador
    ->       IF var = 3 THEN
    ->          ITERATE loop_label; -- se salta el resto del procedimiento e itera de nuevo
    ->       END IF;
    ->       -- el siguiente texto no se imprimira si var es igual a 3
    ->       SELECT var;
    ->       IF var >= 4 THEN
    ->          LEAVE loop_label; -- informa que debe salir del loop
    ->       END IF;
    ->    END LOOP; -- fin del loop
    -> END; //
Query OK, 0 rows affected (0.00 sec)

mysql> call ejemplo//
+------+
| var  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

+------+
| var  |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

+------+
| var  |
+------+
|    4 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

MySQL también soporta la instrucción GOTO, que al igual que en lenguajes como Pascal, C, entre otros, permite hacer un salto desde un punto dado hasta la etiqueta indicada:

   -- parte del codigo
   LABEL Nombre_de_la_etiqueta;
   -- parte del codigo
   GOTO Nombre_de_la_etiqueta; -- saltara a la etiqueta indicada
   -- resto del codigo

Cursores ...

Una forma de recorrer registros provenientes de una consulta, son los cursores, permiten acceder a determinados datos de manera secuencial (un registro luego de otro), se deben declarar antes de cualquier handler (se ve mas adelante), su sintaxis es la siguiente:

-- se declara un cursor

DECLARE cursor1 CURSOR FOR SQL;

cursor1 será en este ejemplo el nombre del cursor y SQL lo sustituimos por la consulta correspondiente, ejemplo:

DECLARE cursor1 CURSOR FOR SELECT id,nombres,apellidos FROM usuarios WHERE id=6;

Dicha consulta NO puede usar la instrucción INTO.

Para trabajar con un cursor antes hay que abrirlo y luego de trabajar con el mismo, hay que cerrarlo, eso lo hacemos con las sentencias OPEN y CLOSE.

OPEN cursor1;
   ...
CLOSE cursor1;

Es posible trabajar con mas de un cursor en el mismo procedimiento, es por eso que indicamos su nombre al abrirlo o cerrarlo.

Para trabajar con los datos del registro usamos la sentencia FETCH, ésta se encarga de moverse entre los registros hasta que no encuentre más.

FETCH permite asignar a determinadas variables, el valor de los campos del registro donde se encuentre el cursor en ese preciso momento y así poder trabajar con esos valores.

uso:

FETCH cursor1 INTO a, b;

Se está especificando que almacene temporalmente el resultado de los dos campos consultados, en las variables a y b previamente declaradas.

Ejemplo:

CREATE PROCEDURE ejemplo()
BEGIN -- abrimos el procedure

   -- se declaran las variables a usar
   DECLARE a INT;
   DECLARE b CHAR(6);

   -- se declara un cursor que navegara por el resultado de la consulta 
   DECLARE cursor1 CURSOR FOR SELECT id,nombre FROM usuarios;


  OPEN cursor1; -- se abre el cursor

    -- ... parte del procedimiento
    REPEAT -- comenzamos las iteraciones para movernos entre los registros

     -- almacenamos el id y el nombre del registro actual, en las variables a y b
     FETCH cursor1 INTO a, b;

    UNTIL expresion END REPEAT;-- salimos del ciclo
         -- ... resto del codigo, el que hacer con esas dos variables (a y b)

  -- cerramos el cursor 
  CLOSE cursor1;

-- cerramos el procedure
END//

Los Handlers (manejadores)

Cuando se produce un error en MySQL el servidor devuelve la descripción y dos códigos diferentes para el error:
1.- MySQL error: un código de error (numérico) que es exclusivo de ese manejador de base de datos.
2.- SQLSTATE: una cadena de cinco caracteres que está estandarizada, es decir, es independiente del manejador de base de datos, si estamos trabajando con Oracle, SQL Server, etc. obtendremos el mismo valor para determinado error.

No todos los códigos de error MySQL tienen su equivalente en código SQLSTATE. Cada error de Mysql está relacionado con un código de error SQLState, pero no siempre esta relación es uno a uno. HY000 es un código de error SQLSTATE para propositos generales que devuelve MySQL cuando su código de error no tiene asociado un código SQLSTATE.

Puedes encontrar una tabla con las equivalencias de códigos de errores MySQl y SQLSTATE en devshed.com.

Se puede presentar el caso de que se necesite controlar determinados errores debido a excepciones dentro del procedure, los handlers permiten controlar esos sucesos ejecutando un comando especificado luego de una o varias condiciones.

Los handlers se declaran luego de las variables y los cursores, su sintaxis es la siguiente:

DECLARE tipo_de_handler HANDLER FOR condicion_del_handler

Donde tipo_de_handler (que hacer en caso de que la condición se cumpla) puede ser:

- CONTINUE: indica que la ejecución de la rutina debe seguir.
- EXIT: indica que la ejecución de la rutina debe culminar.

En condicion_del_handler podemos usar alguno de los siguientes valores:

- SQLSTATE: códigos de errores independiente de la plataforma.
- MySQL error: códigos de errores exclusivos de MySQL.
- SQLWARNING: abreviación para los códigos SQLSTATE que comienzan con 01.
- NOT FOUND: abreviación para los códigos SQLSTATE que comienzan con 02.
- SQLEXCEPTION: abreviación para el resto de códigos SQLSTATE.
- nombre de una condición declarada previamente.

Por ejemplo:
- la función FETCH se encarga de moverse entre los registros hasta que no encuentre más y en ese caso se produce la condición SQLSTATE 02000.

DROP PROCEDURE IF EXISTS ejemplo//
CREATE PROCEDURE ejemplo()
BEGIN
  -- declaramos las variables antes de los cursores
  DECLARE bContinuar BOOLEAN DEFAULT true;-- true por defecto
  DECLARE a CHAR(20);
  -- declaramos un cursor con su respectiva consulta
  DECLARE cursor1 CURSOR FOR SELECT nombre FROM usuarios;
 
  -- declaramos un manejador,
  -- bContinuar sera false cuando se produzca la condicion SQLSTATE 02000,
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET bContinuar = false;
 
  OPEN cursor1;-- se abre el cursor declarado
    REPEAT -- comenzamos las iteraciones
 
      -- asignamos el nombre actual a la variable actual
      FETCH cursor1 INTO a;
 
      -- mientras no lleguemos el final de los registros, continuamos
      IF bContinuar THEN
 
        -- aqui lo que tengas que hacer ..
 
      END IF;

    UNTIL NOT bContinuar END REPEAT;-- salimos del ciclo
 
  CLOSE cursor1; -- cerramos el cursor
END//

- cuando intentamos insertar un registro cuyo ID ya se encuentra agregado (si es Primary Key), se produce el MySQL error 1062 y el SQLSTATE 23000:

INSERT INTO usuarios (idusuario,nombre,apellido,fecha_nacimiento) VALUES (1,'nombre','apellido','2000-01-01');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

Podemos controlarlo dentro del procedimiento de la siguiente manera:

-- declaramos una variable boobleana que seŕa true por defecto
DECLARE bContinuar BOOLEAN DEFAULT true;
-- indicamos que NO debe continuar si se produce el MySQL error 1062 
DECLARE CONTINUE HANDLER FOR 1062 SET bContinuar = false;

-- si se produce la condicion MySQL error 1062, la variable bContinuar sera false 
-- y de esa manera condicionamos la siguiente instrucción

IF bContinuar THEN
  -- aqui seguimos con los procedimientos correspondientes
END IF;

Tambien pudimos haber usado:

DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET bContinuar = 1;

ya que son equivalentes SQLSTATE '23000' y MySQL error 1062.

fuentes:
mysql.com
devshed.com

Publicar un comentario

2Comentarios

- Los comentarios están siendo moderados y serán publicados en la brevedad posible.

Diariotec4/6/11, 14:36
Hola,
sabes de un manual oficial de stores en mysql?

Ando buscando informacion acerca de parametros de salida (output) para devolver valores.

C.
ResponderEliminar
Respuestas
Reinaldo IC.4/6/11, 17:56
hola Diariotec, acá tienes la documentación oficial, aparte puedes consultar algunos ejemplos que deje en este post.
ResponderEliminar
Respuestas

Añadir comentario

Stored Procedure en MySQL (parte 2) [iterate, cursores y handlers]

Entradas relacionadas:

Publicar un comentario

PHP y los archivos dBase (dbf)

Sobre el autor

Contact form