Apuntes sobre programas MySQL

Creación de procedimientos, funciones y disparadores en lenguaje SQL para la base de datos MySQL

Importar archivos SQL

Desde phpMyAdmin

Selecciónar la base de datos, ir a la pestaña Importar, luego pulsar el botón Examinar y finalmente el botón Continuar

Desde línea de comandos del terminal

$ mysql -u usuario -p < archivo.sql

Desde comandos de mysql

$ mysql -u usuario -p
mysql> use database;
mysql> source ruta/archivo.sql

Comentarios

Hasta fin de línea

Guión, guión y espacio en blanco

-- Comentario…

De varias líneas

/* 
  Comentario…
*/

Tipos habituales de parámetros y variables

Coincide con los tipos de datos de los campos

INTNúmero entero
DECIMAL(8,2)Número decimal de 6 cifras enteras y 2 decimales
FLOATNúmero en notación científica
VARCHAR(128)Texto de una línea
TEXTTexto con frases
DATETIMEFecha y hora
DATEFecha
TIMEHora
BOOLEANValor lógico: TRUE o FALSE
ENUM(valor1, valor2, …)Enumeración

Elementos

  • Entre 'comillas simples' los textos literales
  • Entre "comillas dobles" los alias de campos. Es obligatorio si tienen espacios.
  • Entre `comillas voladas` los campos y tablas. Es obligatorio si tienen espacios o coincide con una palabra clave.
  • Booleano: TRUE (1), FALSE (0)

Rutinas 🔗

Características:

  • READS SQL DATA cuando la rutina lea datos: SELECT FROM
  • MODIFIES SQL DATA cuando modifique datos: INSERT, UPDATE, DELETE
  • NO SQL cuando no tiene sentencias SQL
  • DETERMINISTIC cuando los valores de entrada identifican unívocamente la salida

Procedimiento

Un procedimiento ejecuta un código. Puede retornar valores por parámetro o puede retornar un conjunto de resultados mediante una consulta

DELIMITER $$
DROP PROCEDURE IF EXISTS nombreProc$$
CREATE PROCEDURE nombreProc(
  IN inParam1 TIPO, 
  OUT outParam2 TIPO,
  INOUT inoutParam3 TIPO,
  …
) READS|MODIFIES SQL DATA
BEGIN
  DECLARE myVar1 tipo;
  …
END$$
DELIMITER ;

Retornar un SELECT en un procedimiento

Hacer un SELECT dentro de un PROCEDURE es como si fuese una vista con parámetros

Tipos de parámetros de un procedimiento

INEntrada de datos
OUTSalida de datos
INOUTEntrada y salida de datos

Llamada a un procedimiento

CALL nombreProc(valor1, valor2, …);

Si hay algún parámetro de salida o de entrada y salida se requiere una variable para recuperar el valor de salida:

SET @salida = 0;
CALL nombreProc(…, @salida, …);
SELECT @salida;

Función

Una función siempre retorna un valor que puede ser usada en cualquier expresión

DELIMITER $$
DROP FUNCTION IF EXISTS nombreFunc$$
CREATE FUNCTION nombreFunc(
  inParam1 TIPO, 
  …
) RETURNS tipo
  READS|MODIFIES SQL DATA
BEGIN
  DECLARE myVar1 TIPO;
  …
  RETURN valor;
END$$
DELIMITER ;

El valor de retorno se indica con un RETURN que finaliza la función

Llamada desde SELECT

SELECT nombreFunc(valor1, valor2, …);

Variables

Declaración

DECLARE myVar1, myVar2, … TIPO;

Indicar un valor por omisión:

DECLARE myVar1, myVar2, … TIPO DEFAULT valor;

Asignación SET

SET myVar1 := expresión;

Si se asigna desde un SELECT éste debe encontrarse entre paréntesis y retorna una sóla fila:

SET myVar1 := (SELECT … LIMIT 1);

Asignación INTO

SELECT campo1, campo2, … INTO myVar1, myVar2, …
  FROM … ;

Condicionales

Condicional IF

IF condición THEN
  …
ELSEIF condición2 THEN
  …
ELSE 
  …
END IF;

Condicional CASE (multicondicional)

CASE
  WHEN condicion1 THEN
    …
  WHEN condición2 THEN 
    …
  …
  ELSE
    …
END CASE;

Condicional CASE (variable)

CASE variable
  WHEN valor1 THEN
    …
  WHEN valor2 THEN 
    …
  …
  ELSE
    …
END CASE;

¡Cuidado! el ELSE del CASE es opcional pero si se omite y no se para en ningún WHEN dará un error

Función IF

IF(condición, valor_verdadero, valor_falso)

Función ELT

ELT(índice, valorIndice1, valoIndicer2, …)

Bucles

El bucle WHILE

WHILE condición DO
  …
END WHILE;

Bucle contador

DECLARE myCount INT DEFAULT 1;
WHILE myCount <= 10 DO
  …
  SET myCount := myCount + 1;
END WHILE;

El bucle LOOP

loop1: LOOP
  …
  IF condicion THEN 
    LEAVE|ITERATE loop1; 
  END IF;
  …
END LOOP;

El bucle REPEAT

REPEAT
  …
  UNTIL condicion
END REPEAT;

Sentencias SQL

Inserción y actualización UPSET

INSERT INTO tabla
  VALUES (valor1, valor2, …)
  ON DUPLICATE KEY UPDATE
    campo1 = valor1,
    campo2 = valor2,
    …;

Ignorar valores duplicados al insertar o actualizar

INSERT IGNORE INTO …
UPDATE IGNORE

Crear una sentencia SQL con texto

SET @sql = CONCAT('SELECT …', …);
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Crear una tabla temporal

CREATE TEMPORARY TABLE IF NOT EXISTS tmp_tabla (campo1 TIPO, …);
DELETE FROM tmp_tabla;

Crear una vista

CREATE OR REPLACE VIEW vista AS SELECT …;
DROP VIEW IF EXISTS vista;

Último id insertado

Si la instrucción INSERT tuvo éxito retorna el nuevo ID

LAST_INSERT_ID() 🔗

Número de cambios efectuados

ROW_COUNT() 🔗

Después de INSERT … ON DUPLICATE KEY UPDATE … retorna:

  1. No hubo cambios
  2. Si hubo inserción
  3. Si hubo actualización

El valor NULL

Evitar valor nulo

Toma el primer valor que no sea nulo

COALESCE(valor1, variable2, …)

Obtener valor nulo

Retorna valor1 a no ser que sea igual al valor2 en cuyo caso da nulo

NULLIF(valor1, valor2)

Cursores

Un cursor

Acceder a los datos de una consulta registro a registro

DECLARE myDone1 BOOLEAN DEFAULT FALSE;
DECLARE myCursor1 CURSOR FOR
  SELECT campo1, campo2, …
  FROM …;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET myDone1 := TRUE;
…
OPEN myCursor1;
loop1: LOOP
  FETCH myCursor1 INTO myVar1, myVar2, …
  IF myDone1 THEN
    CLOSE myCursor1;
    LEAVE loop1;
  END IF;
  …
END LOOP;

Cursores anidados

block1: BEGIN
  DECLARE myDone1 BOOLEAN DEFAULT FALSE;
  DECLARE myCursor1 CURSOR FOR
    SELECT campo11, campo12, …
    FROM …;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET myDone1 := TRUE;
  …
  OPEN myCursor1;
  loop1: LOOP
    FETCH myCursor1 INTO myVar11, myVar12, …
    IF myDone1 THEN
      CLOSE myCursor1;
      LEAVE loop1;
    END IF;
    …
    block2: BEGIN
      DECLARE myDone2 BOOLEAN DEFAULT FALSE;
      DECLARE myCursor2 CURSOR FOR
        SELECT campo21, campo22, …
        FROM …;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET myDone2 := TRUE;
      …
      OPEN myCursor2;
      loop2: LOOP
        FETCH myCursor2 INTO myVar21, myVar22, …
        IF myDone2 THEN
          CLOSE myCursor2;
          LEAVE loop2;
        END IF;
        …
      END LOOP;
    END;
    …
  END LOOP;
END;

Disparadores

Código que se ejecuta en respuesta a un cambio en una tabla

DELIMITER $$
DROP TRIGGER IF EXISTS disparador$$
CREATE TRIGGER disparador
  BEFORE|AFTER 
  INSERT|UPDATE|DELETE 
  ON tabla 
  FOR EACH ROW
BEGINEND$$
DELIMITER ;

Campos OLD y NEW

La sintaxis es OLD.campo y NEW.campo

Al insertar, actualizar o borrar

INSERTAcceso a NEW
UPDATEAcceso a OLD y NEW
DELETEAcceso a OLD

Antes o después de realizar los cambios

BEFORESe puede cambiar campos NEW porque aun no están guardados
AFTERSe pueden cambiar registros relacionados en tablas externas porque los campos NEW ya están guardados

Lanzar una excepción

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'mensaje';

Eventos 🔗

Código que se ejecuta a intervalos regulares

DELIMITER $$
DROP EVENT IF EXISTS evento$$
CREATE EVENT evento
  ON SCHEDULE
  EVERY n DAY|HOUR|MINUTE
  STARTS CURRENT_TIMESTAMP
  DO 
BEGINEND$$
DELIMITER ;

Iniciar a las 5 A.M.

  …
  STARTS DATE_ADD(CURRENT_DATE(), INTERVAL 5 HOUR)
  …

Configuración

Temporalmente

$ mysql -u usuario -p
mysql> SET GLOBAL event_scheduler = ON;
mysql> SHOW VARIABLES LIKE 'e%';
mysql> SHOW PROCESSLIST;
mysql> SHOW EVENTS;

Permanentemente en /etc/mysql/my.cnf

…
[mysql]
…
event-scheduler = DISABLED|ON
…

Comentarios

Proinf.net, ©2003-2018 ci 3.1.8 (CC) Esta obra está bajo una licencia de Creative Commons Este software está sujeto a la CC-GNU GPL