Sigueme por RSS! RSS

Stored Procedure en MySQL (parte 4) [validaciones]

MySQL dispone de varios operadores para comparación de cadenas, entre ellos, LIKE y REGEXP (y similares: RLIKE, NOT REGEXP y NOT RLIKE), el primero permite hacer comparaciones entre cadenas y patrones usando comodines (% y _), mientras que con el segundo se puede hacer lo mismo pero usando expresiones regulares en lugar de comodines.

Una expresión regular viene siendo un conjunto de caracteres (o cadenas de lo mismo) que forman un patrón, dichos patrones nos permiten hacer búsquedas personalizadas, bajo una sintaxis establecida.
<expresión> REGEXP <patrón>

La expresión bien podría ser una columna:
mysql> SELECT * FROM Pais WHERE nombre_pais REGEXP "^(M)[a-z]+(o)$";
+------+--------------+
| code | nombre_pais  |
+------+--------------+
| ma   | Morocco      |
| mc   | Monaco       |
| me   | Montenegro   |
| mo   | Macao        |
| mx   | Mexico       |
+------+--------------+

Podemos usar REGEXP para las validaciones dentro de nuestros stored procedures, ejemplo:
CREATE FUNCTION IsNumeric(Num INT) RETURNS CHAR(1)
BEGIN
   -- retornamos 1 si es numerico o 0 si no lo es 
   RETURN (Num REGEXP "[0-9]");
END//
DELIMITER ;

Esa función devolvería uno (1) si el valor pasado por parámetro es una cadena numérica y cero (0) de lo contrario,

Las cadenas numéricas consisten de un signo opcional, cualquier número de dígitos, una parte decimal opcional y una parte exponencial opcional. Por lo tanto, +0123.45e6 es un valor numérico válido. La notación hexadecimal (0xFF) es permitida también pero solo sin partes de signo, valor decimal y valor exponencial.

Ahora, si quisiéramos que solo permita números, por ejemplo, para validar una cédula de identidad, podríamos hacerlo así:
Clic aquí para mostrar/ocultar el código
CREATE FUNCTION IsCI(Num char(8)) RETURNS CHAR(1)
BEGIN
   DECLARE contador,Lng INT;
   DECLARE sCaracter CHAR(1);

   IF (Num = 0) THEN -- la CI no puede ser igual a 0 
      RETURN 0; 
   ELSE
      SET Lng = LENGTH(Num); -- almacenamos la longitud
   END IF;

   -- inicializamos las variables
   SET contador = 1;

   loop_label: LOOP -- inicio del ciclo

      -- extraemos cada caracter 1x1
      SET sCaracter = MID(Num,contador,1);

      -- si el caracter actual no es un numero ..
      IF (sCaracter REGEXP "[0-9]") = 0 THEN
         RETURN 0;
      END IF;
     
      IF contador = Lng THEN
         LEAVE loop_label; -- informa que debe salir del loop
      ELSE
         SET contador = contador + 1; -- contador++
      END IF;
   END LOOP;

   RETURN 1;
END//

Devolverá 1 si el número de cédula de identidad es válido, en caso de que se pase una cadena superior a 8 caracteres, se corta a esa longitud, tomamos en cuenta que un número de cédula (en Venezuela por ejemplo) no puede superar los 8 caracteres.

En el ejemplo se usaron las funciones LENGTH y MID, para calcular la longitud de una cadena y extraer un carácter, respectivamente, si lo deseas puedes leer un poco sobre funciones con cadenas en MySQL.

Lo siguiente será confeccionar una función que gestione el contenido de una tabla (personas), recibirá un conjunto de datos por parámetros, los validará y luego los, insertará, actualizará o eliminará de la tabla según sea la intención:
Clic aquí para mostrar/ocultar el código
DROP FUNCTION IF EXISTS GestPersonas;
DELIMITER //
CREATE FUNCTION GestPersonas
(
   sCI VARCHAR(8), -- nuestro PK (la cédula de identidad es unica por persona)
   sNombre VARCHAR(20),
   sApellido VARCHAR(20),
   sSexo CHAR(1), -- M ó F
   sFecha_Nac DATE,
   sModo char(3) 
      -- INS --> para Insertar el registro
      -- UPD --> para actualizar
      -- DEL --> para eliminar

) RETURNS VARCHAR(50) -- el mensaje
BEGIN
  DECLARE sMensaje VARCHAR(50);
  DECLARE bError BOOLEAN DEFAULT false; -- false por defecto
  DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET bError = true; -- insert
  DECLARE CONTINUE HANDLER FOR SQLSTATE '43000' SET bError = true; -- update
  SET sMensaje = '';
  SET sSexo = UPPER(sSexo);  -- lo pasamos a mayuscula
  SET sModo = UPPER(sModo);

  -- validaciones básicas
  -- validamos que la cedula de identidad sea correcta
  IF (NOT IsCI(sCI)) THEN
     RETURN 'Error: la cédula de identidad es inválida!';
  ELSEIF (sModo <> 'DEL') THEN
    IF (sSexo <> 'F' AND sSexo <> 'M') THEN
      RETURN 'Error: el campo SEXO solo acepta las letras F o M';
    ELSEIF (sNombre=''  OR sApellido='' OR sFecha_Nac='') THEN
      RETURN 'Error: debes llenar todos los campos';
    END IF;
  END IF;

  -- dependiendo del modo ..
  CASE sModo
    WHEN 'INS' THEN -- insertar
      INSERT INTO personas (CI,NOMBRE,APELLIDO,SEXO,FECHA_NAC) 
VALUES (sCI,sNombre,sApellido,sSexo,sFecha_Nac);
      SET sMensaje = 'Registro insertado satifactoriamente';

    WHEN 'UPD' THEN -- actualizar
      UPDATE personas SET NOMBRE=sNombre,APELLIDO='sApellido',
SEXO=sSexo,FECHA_NAC=sFecha_nac WHERE CI=sCI;
      SET sMensaje = 'Registro actualizado satifactoriamente';

    WHEN 'DEL' THEN -- eliminar
      DELETE FROM personas WHERE CI = sCI;
      SET sMensaje = 'Registro eliminado satifactoriamente';
  ELSE 
      SET sMensaje = 'Error: Modo inválido! debe ser: INS, UPD o DEL';
  END CASE;
  
  IF (bError) THEN
     RETURN CONCAT('Error: no se han podido (',IF(sModo='INS','Insertar',
IF(sModo='UPD','Actualizar','Eliminar')),') los datos!');
  ELSE
     RETURN sMensaje;
  END IF;
END//

Al momento de iniciar el proceso de validaciones, son muchas las que se te pueden ocurrir si quieres evitar la mayor posibilidad de error dentro de tus rutinas, a esta ultima función podríamos agregarle una validación extra para el número de cédula, hacer una búsqueda de ese número en la base de datos y así asegurarnos de que el registro exista y mostrar un mensaje personalizado si no es el caso.

Cabe señalar, que todas las funciones que creamos podemos llamarlas dentro de nuevas rutinas, así como, las funciones propias de MySQL, en este caso reutilizamos la función IsCI() y se usaron las funciones UPPER y CONCAT.

Las validaciones no acaban aquí, a este tema hay mucho que agregarle, pero será en otra oportunidad ;)


referencias:
mysql.com/regexp
wikipedia.org/expresiones_regulares

0 comentarios: Suscribete a los comentarios por RSS

Publicar un comentario

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