Apendice A

Procedimientos almacenados,funciones y disparadores

En este anexo, que presentamos a continuacion, vamos a mostrar como podremosalmacenar procedimientos, funciones y disparadores dentro de la base de datos.Comenzaremos hablando de los dos primeros y concluiremos con los disparadores otriggers.

A.1. Procedimientos almacenados y funciones

Los procedimientos almacenados y funciones son nuevas caractersticas de la ver-sion 5.0 de MySQL. Un procedimiento almacenado es un conjunto de comandos SQLque pueden guardarse en el servidor. Una vez que se hace, los clientes no necesitanrelanzar los comandos individuales, teniendo solamente que referirse ahora al pro-cedimiento almacenado.

Algunas situaciones en que los procedimientos almacenados pueden ser particu-larmente utiles:

Cuando multiples aplicaciones cliente se escriben en distintos lenguajes o fun-cionan en distintas plataformas, pero necesitan realizar la misma operacion enla base de datos.

Cuando la seguridad es muy importante. Los bancos, por ejemplo, usan proce-dimientos almacenados para todas las oparaciones comunes. Esto proporcionaun entorno seguro y consistente, y los procedimientos pueden asegurar quecada operacion se registra apropiadamente. En tal entorno, las aplicaciones ylos usuarios no obtendran ningun acceso directo a las tablas de la base dedatos, solo podran ejectuar algunos procedimientos.

1

Los procedimientos almacenados pueden mejorar el rendimiento ya que se nece-sita enviar menos informacion entre el servidor y el cliente. Ademas, son optimospara tareas que se ejecutan con una cierta frecuencia, ya que se precompilan en elservidor la primera vez, y ya no hay que volver a hacerlo. Cuando se ejecutan con-sultas individuales, cada una debe ser precompilada y procesada (Lo cual, requieremas tiempo). Sin embargo, a pesar del ahorro en tiempo que se obtienen con losprocedimientos almacenados, debemos tener cuidado con su uso. Un entorno con unservidor y varios clientes que ejecuten muchos procedimientos almacenados, aumen-tara la carga del servidor de la base de datos ya que la mayora del trabajo se realizaen la parte del mismo y no en el cliente. Considere esto si muchas maquinas cliente(como servidores Web) se sirven a solo uno o pocos servidores de bases de datos.

Asimismo, los procedimientos almacenados permiten tener bibliotecas o funcio-nes en el servidor de base de datos. Esta caracterstica es compartida por los len-guajes de programacion modernos que permiten este diseno interno, por ejemplo,usando clases. El uso de estas caractersticas del lenguaje de programacion clientees beneficioso para el programador, incluso fuera del entorno de la base de datos.

A.1.1. Procedimientos almacenados y la tabla de permisos

Los procedimientos almacenados requieren la tabla proc en la base de datosmysql. Esta tabla se crea durante la instalacion de MySQL 5.0. Si esta actualizandoa MySQL 5.0 desde una version anterior, asegurese de actualizar sus tablas de per-misos para asegurar que la tabla proc existe.

Desde MySQL 5.0.3, el sistema de permisos se ha modificado para tener en cuentalos procedimientos almacenados como sigue:

El permiso CREATE ROUTINE se necesita para crear procedimientos alma-cenados.

El permiso ALTER ROUTINE se necesita para alterar o borrar procedimientosalmacenados. Este permiso se da automaticamente al creador de una rutina.

El permiso EXECUTE se requiere para ejectuar procedimientos almacenados.Sin embargo, este permiso se da automaticamente al creador de la rutina.Tambien, la caracterstica SQL SECURITY por defecto para una rutina esDEFINER, lo que permite ejecutar la rutina asociada a los usuarios que tienenacceso a la base de datos.

2

A.1.2. Especificacion de la sintaxis

Los procedimientos almacenados y rutinas se crean con comandos CREATEPROCEDURE y CREATE FUNCTION. Una rutina es un procedimiento o unafuncion. Un procedimiento se invoca usando un comando CALL, y solo puede pasarvalores usando variables de salida. Una funcion puede llamarse desde dentro de uncomando como cualquier otra funcion (esto es, invocando el nombre de la funcion),y puede retornar un valor escalar. Las rutinas almacenadas pueden llamar otras ru-tinas almacenadas.

Desde MySQL 5.0.1, los procedimientos almacenados o funciones se asocian conuna base de datos. Esto tiene varias implicaciones:

Cuando se invoca la rutina, se realiza implcitamente USE db name (y sedeshace cuando acaba la rutina). Los comandos USE dentro de procedimientosalmacenados no se permiten.

Puede calificar los nombres de rutina con el nombre de la base de datos. Estopuede usarse para referirse a una rutina que no este en la base de datos ac-tual. Por ejemplo, para invocar procedimientos almacenados p o funciones fasociadas a la base de datos test, puede decir CALL test.p() o test.f().

Cuando se borra una base de datos, todos los procedimientos almacenadosasociados con ella tambien se borran.

(En MySQL 5.0.0, los procedimientos almacenados son globales y no asociadoscon una base de datos. Heredan la base de datos por defecto del que llama. Si seejecuta USE db name desde la rutina, la base de datos por defecto original se res-taura a la salida de la rutina.)

MySQL soporta la extension muy util que permite el uso de comandos regularesSELECT (esto es, sin usar cursores o variables locales) dentro de los procedimientosalmacenados. El conjunto de resultados de estas consultas se enva diractamenteal cliente. Comandos SELECT multiples generan varios conjuntos de resultados,as que el cliente debe usar una biblioteca cliente de MySQL que soporte conjuntosde resultados multiples. Esto significa que el cliente debe usar una biblioteca clientede MySQL como mnimos desde 4.1.

En las siguientes subsecciones describiremos la sintaxis usada para crear, alterar,borrar, y consultar procedimientos almacenados y funciones.

3

Creacion de procedimientos y funciones

CREATE PROCEDURE sp name ([parameter[,...]])[characteristic ...] routine body

CREATE FUNCTION sp name ([parameter[,...]])RETURNS type[characteristic ...] routine body

parameter:[ IN OUT INOUT ] param name type

type:Any valid MySQL data type

characteristic:LANGUAGE SQL| [NOT] DETERMINISTIC| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL

DATA }| SQL SECURITY { DEFINER | INVOKER }| COMMENT string

routine body:procedimientos almacenados o comandos SQL validos

Estos comandos crean una rutina almacenada. Desde MySQL 5.0.3, para crearuna rutina, es necesario tener el permiso CREATE ROUTINE, y los permisos AL-TER ROUTINE y EXECUTE se asignan automaticamente a su creador.

Por defecto, la rutina se asocia con la base de datos actual. Para asociar la rutinaexplcitamente con una base de datos, especifique el nombre como db name.sp nameal crearlo.

Si el nombre de rutina es el mismo que el nombre de una funcion de SQL, nece-sita usar un espacio entre el nombre y el siguiente parentesis al definir la rutina, ohabra un error de sintaxis. Esto tambien es cierto cuando se invoca la rutina poste-riormente.

La clausula RETURNS puede especificarse solo con FUNCTION, donde es obli-gatorio. Se usa para indicar el tipo de retorno de la funcion, y el cuerpo de la funcion

4

debe contener un comando RETURN value.

La lista de parametros entre parentesis debe estar siempre presente. Si no hayparametros, se debe usar una lista de parametros vaca (). Cada parametro es unparametro IN por defecto. Para especificar otro tipo de parametro, use la palabraclave OUT o INOUT antes del nombre del parametro.

Un procedimiento o funcion se considera determinista si siempre produce elmismo resultado para los mismos parametros de entrada, y no determinista encualquier otro caso. Si no se indica ni DETERMINISTIC ni NOT DETERMINIS-TIC, se tomara por defecto NOT DETERMINISTIC.

Varias caractersticas proporcionan informacion sobre la naturaleza de los datosusados por la rutina. CONTAINS SQL indica que la rutina no contiene comandosque leen o escriben datos. NO SQL indica que la rutina no contiene comandos SQL.READS SQL DATA indica que la rutina contiene comandos que leen datos, pero nocomandos que escriben datos. MODIFIES SQL DATA indica que la rutina contienecomandos que pueden escribir datos. CONTAINS SQL es el valor por defecto si nose dan explcitamente ninguna de estas caractersticas.

La caracterstica SQL SECURITY puede usarse para especificar si la rutina debeser ejecutada usando los permisos del usuario que crea la rutina o el usuario quela invoca. El valor por defecto es DEFINER. El creador o el invocador deben tenerpermisos para acceder a la base de datos con la que la rutina esta asociada. DesdeMySQL 5.0.3, es necesario tener el permiso EXECUTE para ser capaz de ejecutarla rutina.

MySQL almacena la variable de sistema sql mode, que comienza a tener su efec-to cuando se crea la rutina. Siempre ejecutara la rutina con esta inicializacion de lavariable.

La clausula COMMENT es una extension de MySQL, y puede usarse para des-cribir el procedimiento almacenado. Esta informacion se muestra con los comandosSHOW CREATE PROCEDURE y SHOW CREATE FUNCTION.

Por otro lado, cabe destacar que MySQL permite que las rutinas contengancomandos DDL (tales como CREATE y DROP) y comandos de transaccion SQL(como COMMIT). Sin embargo, los procedimientos almacenados no pueden usarLOAD DATA INFILE.

El siguiente es un ejemplo de un procedimiento almacenado que usa un parame-

5

tro OUT. El ejemplo usa el cliente mysql y el comando delimiter para cambiar eldelimitador del comando de ; a // mientras se define el procedimiento. Esto permitepasar el delimitador ; usado en el cuerpo del procedimiento a traves del servidor, enlugar de ser interpretado por el mismo mysql.

mysql> delimiter //

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)-> BEGIN-> SELECT COUNT(*) INTO param1 FROM t;-> END-> //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL simpleproc(@a);Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a;++| @a |++| 3 |++1 row in set (0.00 sec)

Al usar el comando delimiter, debe evitar el uso de la barra invertida (), ya quees el caracter de escape de MySQL.

El siguiente es un ejemplo de funcion que toma un parametro, realiza una ope-racion con una funcion SQL, y retorna el resultado:

mysql> delimiter //

mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)-> RETURN CONCAT(Hello, ,s, !);-> //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

6

mysql> SELECT hello(world);++| hello(world) |++| Hello, world! |++1 row in set (0.00 sec)

Si el comando RETURN, en un procedimiento almacenado, retorna un valor conun tipo distinto al especificado en la clausula RETURNS de la funcion, el valor deretorno se amolda al tipo apropiado. Por ejemplo, si una funcion retorna un valorENUM o SET, pero el comando RETURN retorna un entero, el valor retornado porla funcion es la cadena para el miembro de ENUM correspondiente de un conjuntode miembros SET.

Modificacion de procedimientos y funciones

ALTER {PROCEDURE | FUNCTION} sp name [characteristic ...]

characteristic:{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL

DATA }| SQL SECURITY { DEFINER | INVOKER }| COMMENT string

Este comando puede usarse para cambiar las caractersticas de un procedimientoo funcion. Debe tener el permiso ALTER ROUTINE para la rutina (desde MySQL5.0.3). El permiso se otorga automaticamente al creador de la rutina.

Borrado de procedimientos y funciones

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp name

Este comando se usa para borrar un procedimiento o funcion. Esto es, la rutinaespecificada se borra del servidor. Debe tener el permiso ALTER ROUTINE para lasrutinas (desde MySQL 5.0.3). Este permiso se otorga automaticamente al creadorde la rutina.

7

La clausula IF EXISTS es una extension de MySQL. Evita que ocurra un errorsi la funcion o procedimiento no existe. Con ella se generara una advertencia quepuede verse con SHOW WARNINGS.

Mostrar procedimientos y funciones creadas

SHOW CREATE {PROCEDURE | FUNCTION} sp name

Este comando es una extension de MySQL. Similar a SHOW CREATE TABLE,retorna la cadena exacta que puede usarse para recrear la rutina nombrada.

mysql> SHOW CREATE FUNCTION test.hello\G*************************** 1. row ***************************

Function: hellosql mode:

Create Function: CREATE FUNCTION test.hello(s CHAR(20)) RETURNS CHAR(50)RETURN CONCAT(Hello, ,s, !)

Mostrar estado de procedimientos y funciones

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE pattern]

Este comando es una extension de MySQL. Retorna caractersticas de rutinas,como el nombre de la base de datos, nombre, tipo, creador y fechas de creacion ymodificacion. Si no se especifica un patron, listara la informacion para todos losprocedimientos almacenados, en funcion del comando que use.

mysql> SHOW FUNCTION STATUS LIKE hello\G*************************** 1. row ***************************

Db: testName: helloType: FUNCTIONDefiner: testuser@localhostModified: 2008-03-29 15:29:37Created: 2008-03-29 15:29:37Security type: DEFINERComment:

Tambien puede obtener informacion de rutinas almacenadas de la tabla ROU-TINES en INFORMATION SCHEMA.

8

La sentencia CALL

CALL sp name([parameter[,...]])

El comando CALL invoca un procedimiento definido previamente con CREATEPROCEDURE.

CALL puede pasar valores al llamador usando parametros declarados como OUTo INOUT . Tambien retorna el numero de registros afectados, que con un programacliente puede obtenerse a nivel SQL llamando la funcion ROW COUNT().

Sentencia compuesta

[begin label:] BEGIN[statement list]

END [end label]

Los procedimientos almacenados pueden contener varios comandos, usando uncomando compuesto BEGIN ... END.

Un comando compuesto puede etiquetarse. end label no puede darse a no ser quetambien este presente begin label, y si ambos lo estan, deben ser el mismo.

Tenga en cuenta que la clausula opcional [NOT] ATOMIC no esta soportada.Esto significa que no hay un punto transaccional al inicio del bloque de instruccionesy la clausula BEGIN usada en este contexto no tiene efecto en la transaccion actual.

Usar multiples comandos requiere que el cliente sea capaz de enviar cadenasde consultas con el delimitador de comando ;. Esto se trata en el cliente de lneade comandos mysql con el comando delimiter. Cambiar el delimitador de final deconsulta ; end-of-query (por ejemplo, a //) permite usar ; en el cuerpo de la rutina.

La sentencia DECLARE

El comando DECLARE se usa para definir varios iconos locales de una rutina:las variables locales, handlers y cursores. Los comandos SIGNAL y RESIGNAL nose soportan en la actualidad.

DECLARE puede usarse solo dentro de comandos compuestos BEGIN ... ENDy deben ser su inicio, antes de cualquier otro comando.

9

Los cursores deben declararse antes de declarar los handlers, y las variables ycondiciones deben declararse antes de declarar los cursores o handlers.

Declarar variables locales con DECLARE

DECLARE var name[,...] type [DEFAULT value]

Este comando se usa para declarar variables locales. Para proporcionar un valorpor defecto para la variable, incluya una clausula DEFAULT. El valor puede espe-cificarse como expresion, no necesita ser una constante. Si la clausula DEFAULT noesta presente, el valor inicial es NULL.

La visibilidad de una variable local es dentro del bloque BEGIN ... END dondeesta declarado. Puede usarse en bloques anidados excepto aquellos que declaren unavariable con el mismo nombre.

Sentencia SET para variables

SET var name = expr [, var name = expr ...]

El comando SET en procedimientos almacenados es una version extendida delcomando general SET. Las variables referenciadas pueden ser las declaradas dentrode una rutina, o variables de servidor globales.

El comando SET en procedimientos almacenados se implemnta como parte dela sintaxis SET pre-existente. Esto permite una sintaxis extendida de SET a=x,b=y, ... donde distintos tipos de variables (variables declaradas local y globalmentey variables de sesion del servidor) pueden mezclarse. Esto permite combinacionesde variables locales y algunas opciones que tienen sentido solo para variables desistema; en tal caso, las opciones se reconocen pero se ignoran.

La sentencia SELECT ... INTO

SELECT col name[,...] INTO var name[,...] table expr

Esta sintaxis SELECT almacena columnas seleccionadas directamente en varia-bles. Por lo tanto, solo un registro puede retornarse.

SELECT id,data INTO x,y FROM test.t1 LIMIT 1;

10

Condiciones DECLARE

DECLARE condition name CONDITION FOR condition value

condition value:SQLSTATE [VALUE] sqlstate value| mysql error code

Este comando especifica condiciones que necesitan tratamiento especfico. Asociaun nombre con una condicion de error especfica. El nombre puede usarse subsecuen-temente en un comando DECLARE HANDLER.

Ademas de valores SQLSTATE , los codigos de error MySQL se soportan.

DECLARE handlers

DECLARE handler type HANDLER FOR condition value[,...] sp statement

handler type:CONTINUE| EXIT| UNDO

condition value:SQLSTATE [VALUE] sqlstate value| condition name| SQLWARNING| NOT FOUND| SQLEXCEPTION| mysql error code

Este comando especifica handlers que pueden tratar una o varias condiciones. Siuna de estas condiciones ocurren, el comando especificado se ejecuta.

Para un handler CONTINUE, continua la rutina actual tras la ejecucion delcomando del handler. Para un handler EXIT, termina la ejecucion del comandocompuesto BEGIN...END actual. El handler de tipo UNDO todava no se soporta.

SQLWARNING es una abreviacion para todos los codigos SQLSTATE que comien-zan con 01.

11

NOT FOUND es una abreviacion para todos los codigos SQLSTATE que comien-zan con 02.

SQLEXCEPTION es una abreviacion para todos los codigos SQLSTATE no tra-tados por SQLWARNING o NOT FOUND.

Ademas de los valores SQLSTATE , los codigos de error MySQL se soportan.

Por ejemplo:

mysql> CREATE TABLE test.t (s1 int,primary key (s1));Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //

mysql> CREATE PROCEDURE handlerdemo ()-> BEGIN-> DECLARE CONTINUE HANDLER FOR SQLSTATE 23000 SET @x2 =

1;-> SET @x = 1;-> INSERT INTO test.t VALUES (1);-> SET @x = 2;-> INSERT INTO test.t VALUES (1);-> SET @x = 3;-> END;-> //

Query OK, 0 rows affected (0.00 sec)

mysql> CALL handlerdemo()//Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x;++| @x |++| 3 |++1 row in set (0.00 sec)

Tenga en cuenta que @x es 3, lo que muestra que MySQL se ha ejecutado al finaldel procedimiento. Si la lnea DECLARE CONTINUE HANDLER FOR SQLSTATE

12

23000 SET @x2 = 1; no esta presente, MySQL habra tomado la ruta por defecto(EXIT) tras el segundo INSERT fallido debido a la restriccion PRIMARY KEY , ySELECT @x habra retornado 2.

Cursores

Estos nos ayudaran a obtener los valores de nuestras tablas de una forma facil yordenada.

Se soportan cursores simples dentro de procedimientos y funciones almacenadas.La sintaxis es la de SQL empotrado. Los cursores no son sensibles, son de solo lec-tura, y no permiten scrolling. No sensible significa que el servidor puede o no haceruna copia de su tabla de resultados.

Los cursores deben declararse antes de declarar los handlers, y las variables ycondiciones deben declararse antes de declarar cursores o handlers.

Por ejemplo:

CREATE PROCEDURE curdemo()BEGIN

DECLARE done INT DEFAULT 0;DECLARE a CHAR(16);DECLARE b,c INT;DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;DECLARE CONTINUE HANDLER FOR SQLSTATE 02000 SET done = 1;

OPEN cur1;OPEN cur2;

REPEATFETCH cur1 INTO a, b;FETCH cur2 INTO c;IF NOT done THENIF b < c THENINSERT INTO test.t3 VALUES (a,b);

ELSEINSERT INTO test.t3 VALUES (a,c);

END IF;END IF;

13

UNTIL done END REPEAT;

CLOSE cur1;CLOSE cur2;

END

Declarar cursores

DECLARE cursor name CURSOR FOR select statement

Este comando declara un cursor. Pueden definirse varios cursores en una rutina,pero cada cursor en un bloque debe tener un nombre unico.

El comando SELECT no puede tener una clausula INTO.

Sentencia OPEN del cursor

OPEN cursor name

Este comando abre un cursor declarado previamente.

Sentencia de cursor FETCH

FETCH cursor name INTO var name [, var name ...]

Este comando trata el siguiente registro (si existe) usando el cursor abierto es-pecificado, y avanza el puntero del cursor.

Sentencia de cursor CLOSE

CLOSE cursor name

Este comando cierra un cursor abierto previamente.

Si no se cierra explcitamente, un cursor se cierra al final del comando compuestoen que se declara.

Estructuras Selectivas e Iterativas

Los constructores IF, CASE, LOOP, WHILE, ITERATE, y LEAVE estan com-pletamente implementados.

14

Estos constructores pueden contener un comando simple, o un bloque de coman-dos usando el comando compuesto BEGIN ... END. Los constructores pueden estaranidados.

Los bucles FOR no estan soportados.

Sentencia IF

IF search condition THEN statement list[ELSEIF search condition THEN statement list] ...[ELSE statement list]

END IF

IF implementa un constructor condicional basico. Si search condition se evaluaa cierto, el comando SQL correspondiente listado se ejectua. Si no coincide ningunasearch condition se ejecuta el comando listado en la clausula ELSE. statement listpuede consistir en varios comandos.

Sentencia CASE

CASE case valueWHEN when value THEN statement list[WHEN when value THEN statement list] ...[ELSE statement list]

END CASE

O:

CASEWHEN search condition THEN statement list[WHEN search condition THEN statement list] ...[ELSE statement list]

END CASE

El comando CASE para procedimientos almacenados implementa un constructorcondicional complejo. Si una search condition se evalua a cierto, el comando SQLcorrespondiente se ejecuta. Si no coincide ninguna condicion de busqueda, el coman-do en la clausula ELSE se ejecuta.

Sentencia LOOP

15

[begin label:] LOOPstatement list

END LOOP [end label]

LOOP implementa un constructor de bucle simple que permite ejecucion repeti-da de comandos particulares. El comando dentro del bucle se repite hasta que acabael bucle, usualmente con un comando LEAVE.

Un comando LOOP puede etiquetarse. end label no puede darse hasta queeste presente begin label, y si ambos lo estan, deben ser el mismo.

Sentencia LEAVE

LEAVE label

Este comando se usa para abandonar cualquier control de flujo etiquetado. Pue-de usarse con BEGIN ... END o bucles.

Sentencia ITERATE

ITERATE label

ITERATE solo puede aparecer en comandos LOOP, REPEAT, y WHILE. ITE-RATE significa vuelve a hacer el bucle.

Por ejemplo:

CREATE PROCEDURE doiterate(p1 INT)BEGINlabel1: LOOPSET p1 = p1 + 1;IF p1 < 10 THEN ITERATE label1; END IF;LEAVE label1;

END LOOP label1;SET @x = p1;

END

Sentencia REPEAT

[begin label:] REPEAT

16

statement listUNTIL search conditionEND REPEAT [end label]

El comando/s dentro de un comando REPEAT se repite hasta que la condicionsearch condition es cierta.

Un comando REPEAT puede etiquetarse. end label no puede darse a no ser quebegin label este presente, y si lo estan, deben ser el mismo.

Por ejemplo:

mysql> delimiter //

mysql> CREATE PROCEDURE dorepeat(p1 INT)-> BEGIN-> SET @x = 0;-> REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;-> END-> //

Query OK, 0 rows affected (0.00 sec)

mysql> CALL dorepeat(1000)//Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x;++| @x |++| 1001 |++1 row in set (0.00 sec)

Sentencia WHILE

[begin label:] WHILE search condition DOstatement list

END WHILE [end label]

El comado/s dentro de un comando WHILE se repite mientras la condicion sear-ch condition es cierta.

17

Un comando WHILE puede etiquetarse. end label no puede darse a no ser quebegin label tambien este presente, y si lo estan, deben ser el mismo.

Por ejemplo:

CREATE PROCEDURE dowhile()BEGIN

DECLARE v1 INT DEFAULT 5;

WHILE v1 > 0 DO...SET v1 = v1 - 1;

END WHILE;END

A.2. Disparadores

A partir de MySQL 5.0.2 se incorporo el soporte basico para disparadores (trig-gers). Un disparador es un objeto con nombre dentro de una base de datos, el cual seasocia con una tabla y se activa cuando ocurre en esta un evento en particular. Porejemplo, las siguientes sentencias crean una tabla y un disparador para sentenciasINSERT dentro de la tabla. El disparador suma los valores insertados en una de lascolumnas de la tabla:

mysql> CREATE TABLE account (acct num INT, amount DECIMAL(10,2));mysql> CREATE TRIGGER ins sum BEFORE INSERT ON account

-> FOR EACH ROW SET @sum = @sum + NEW.amount;

A.2.1. Creacion de disparadores

CREATE TRIGGER nombre disp momento disp evento dispON nombre tabla FOR EACH ROW sentencia disp

Un disparador es un objeto con nombre en una base de datos que se asocia conuna tabla, y se activa cuando ocurre un evento en particular para esa tabla.

18

El disparador queda asociado a la tabla nombre tabla. Esta debe ser una tablapermanente, no puede ser una tabla TEMPORARY ni una vista.

momento disp es el momento en que el disparador entra en accion. Puede serBEFORE (antes) o AFTER (despues), para indicar que el disparador se ejecuteantes o despues que la sentencia que lo activa.

evento disp indica la clase de sentencia que activa al disparador. Puede ser IN-SERT, UPDATE, o DELETE. Por ejemplo, un disparador BEFORE para sentenciasINSERT podra utilizarse para validar los valores a insertar.

No puede haber dos disparadores en una misma tabla que correspondan al mismomomento y sentencia. Por ejemplo, no se pueden tener dos disparadores BEFOREUPDATE. Pero s es posible tener los disparadores BEFORE UPDATE y BEFOREINSERT o BEFORE UPDATE y AFTER UPDATE.

sentencia disp es la sentencia que se ejecuta cuando se activa el disparador. Sise desean ejecutar multiples sentencias, deben colocarse entre BEGIN ... END, elconstructor de sentencias compuestas. Esto ademas posibilita emplear las mismassentencias permitidas en rutinas almacenadas.

Note: Antes de MySQL 5.0.10, los disparadores no podan contener referenciasdirectas a tablas por su nombre. A partir de MySQL 5.0.10, se pueden escribir dis-paradores como el llamado testref, que se muestra en este ejemplo:

CREATE TABLE test1(a1 INT);CREATE TABLE test2(a2 INT);CREATE TABLE test3(a3 INT NOT NULL AUTO INCREMENT PRIMARYKEY);CREATE TABLE test4(

a4 INT NOT NULL AUTO INCREMENT PRIMARY KEY,b4 INT DEFAULT 0

);

DELIMITER |

CREATE TRIGGER testref BEFORE INSERT ON test1FOR EACH ROW BEGIN

INSERT INTO test2 SET a2 = NEW.a1;DELETE FROM test3 WHERE a3 = NEW.a1;UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;

END

19

|DELIMITER ;

INSERT INTO test3 (a3) VALUES(NULL), (NULL), (NULL), (NULL), (NULL),(NULL), (NULL), (NULL), (NULL), (NULL);

INSERT INTO test4 (a4) VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

Si en la tabla test1 se insertan los siguientes valores:

mysql> INSERT INTO test1 VALUES-> (1), (3), (1), (7), (1), (8), (4), (4);

Query OK, 8 rows affected (0.01 sec)Records: 8 Duplicates: 0 Warnings: 0

Entonces los datos en las 4 tablas quedaran as:

mysql> SELECT * FROM test1;++| a1 |++| 1 |++| 3 |++| 1 |++| 7 |++| 1 |++| 8 |++| 4 |++| 4 |++8 rows in set (0.00 sec)

20

mysql> SELECT * FROM test2;++| a2 |++| 1 |++| 3 |++| 1 |++| 7 |++| 1 |++| 8 |++| 4 |++| 4 |++8 rows in set (0.00 sec)

mysql> SELECT * FROM test3;++| a3 |++| 2 |++| 5 |++| 6 |++| 9 |++| 10 |++5 rows in set (0.00 sec)

mysql> SELECT * FROM test4;+++

21

| a4 | b4 |+++| 1 | 3 |+++| 2 | 0 |+++| 3 | 1 |+++| 4 | 2 |+++| 5 | 0 |+++| 6 | 0 |+++| 7 | 1 |+++| 8 | 1 |+++| 9 | 0 |+++| 10 | 0 |+++10 rows in set (0.00 sec)

Las columnas de la tabla asociada con el disparador pueden referenciarse em-pleando los alias OLD y NEW. OLD.nombre col hace referencia a una columna deuna fila existente, antes de ser actualizada o borrada. NEW.nombre col hace referen-cia a una columna en una nueva fila a punto de ser insertada, o en una fila existenteluego de que fue actualizada.

El uso de SET NEW.nombre col = valor necesita que se tenga el privilegio UP-DATE sobre la columna. El uso de SET nombre var = NEW.nombre col necesita elprivilegio SELECT sobre la columna.

Nota: Actualmente, los disparadores no son activados por acciones llevadas acabo en cascada por las restricciones de claves extranjeras. Esta limitacion se sub-sanara tan pronto como sea posible.

La sentencia CREATE TRIGGER necesita el privilegio SUPER. Esto se agrego enMySQL 5.0.2.

22

A.2.2. Borrado de disparadores

DROP TRIGGER [nombre esquema.]nombre disp

Elimina un disparador. El nombre de esquema es opcional. Si el esquema se omi-te, el disparador se elimina en el esquema actual.

Anteriormente a la version 5.0.10 de MySQL, se requera el nombre de tabla enlugar del nombre de esquema. (nom tabla.nom disp).

Nota: cuando se actualice desde una version anterior de MySQL 5 a MySQL5.0.10 o superior, se deben eliminar todos los disparadores antes de actualizar y vol-ver a crearlos despues, o DROP TRIGGER no funcionara luego de la actualizacion.

La sentencia DROP TRIGGER necesita que se posea el privilegio SUPER, quese introdujo en MySQL 5.0.2.

A.2.3. Uso de disparadores

El soporte para disparadores se incluyo a partir de MySQL 5.0.2. Actualmente,el soporte para disparadores es basico, por lo tanto hay ciertas limitaciones en loque puede hacerse con ellos. Esta seccion trata sobre el uso de los disparadores y laslimitaciones vigentes.

Un disparador es un objeto de base de datos con nombre que se asocia a unatabla, y se activa cuando ocurre un evento en particular para la tabla. Algunos usospara los disparadores es verificar valores a ser insertados o llevar a cabo calculossobre valores involucrados en una actualizacion.

Un disparador se asocia con una tabla y se define para que se active al ocurriruna sentencia INSERT, DELETE, o UPDATE sobre dicha tabla. Puede tambienestablecerse que se active antes o despues de la sentencia en cuestion. Por ejemplo,se puede tener un disparador que se active antes de que un registro sea borrado, odespues de que sea actualizado.

Este es un ejemplo sencillo que asocia un disparador con una tabla para cuandoreciba sentencias INSERT. Actua como un acumulador que suma los valores inser-tados en una de las columnas de la tabla.

La siguiente sentencia crea la tabla y un disparador asociado a ella:

23

mysql> CREATE TABLE account (acct num INT, amount DECIMAL(10,2));mysql> CREATE TRIGGER ins sum BEFORE INSERT ON account

-> FOR EACH ROW SET @sum = @sum + NEW.amount;

La sentencia CREATE TRIGGER crea un disparador llamado ins sum que seasocia con la tabla account. Tambien se incluyen clausulas que especifican el mo-mento de activacion, el evento activador, y que hacer luego de la activacion:

La palabra clave BEFORE indica el momento de accion del disparador. Eneste caso, el disparador debera activarse antes de que cada registro se inserteen la tabla. La otra palabra clave posible aqui es AFTER.

La plabra clave INSERT indica el evento que activara al disparador. En elejemplo, la sentencia INSERT causara la activacion. Tambien pueden crearsedisparadores para sentencias DELETE y UPDATE.

Las sentencia siguiente, FOR EACH ROW, define lo que se ejecutara cadavez que el disparador se active, lo cual ocurre una vez por cada fila afectadapor la sentencia activadora. En el ejemplo, la sentencia activada es un sencilloSET que acumula los valores insertados en la columna amount. La sentenciase refiere a la columna como NEW.amount, lo que significa el valor de lacolumna amount que sera insertado en el nuevo registro.

Para utilizar el disparador, se debe establecer el valor de la variable acumula-dor a cero, ejecutar una sentencia INSERT, y ver que valor presenta luego la variable.

mysql> SET @sum = 0;mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);mysql> SELECT @sum AS Total amount inserted;++| Total amount inserted |++| 1852.48 |++

En este caso, el valor de @sum luego de haber ejecutado la sentencia INSERT es14.98 + 1937.50 - 100, o 1852.48.

24

Para eliminar el disparador, se emplea una sentencia DROP TRIGGER. El nom-bre del disparador debe incluir el nombre de la tabla:

mysql> DROP TRIGGER account.ins sum;

Debido a que un disparador esta asociado con una tabla en particular, no sepueden tener multiples disparadores con el mismo nombre dentro de una tabla.Tambien se debera tener en cuenta que el espacio de nombres de los disparadorespuede cambiar en el futuro de un nivel de tabla a un nivel de base de datos, es decir,los nombres de disparadores ya no solo deberan ser unicos para cada tabla sinopara toda la base de datos. Para una mejor compatibilidad con desarrollos futuros,se debe intentar emplear nombres de disparadores que no se repitan dentro de labase de datos.

Adicionalmente al requisito de nombres unicos de disparador en cada tabla, hayotras limitaciones en los tipos de disparadores que pueden crearse. En particular,no se pueden tener dos disparadores para una misma tabla que sean activados enel mismo momento y por el mismo evento. Por ejemplo, no se pueden definir dosBEFORE INSERT o dos AFTER UPDATE en una misma tabla. Es improbableque esta sea una gran limitacion, porque es posible definir un disparador que ejecutemultiples sentencias empleando el constructor de sentencias compuestas BEGIN ...END luego de FOR EACH ROW. (Mas adelante en esta seccion puede verse unejemplo).

Tambien hay limitaciones sobre lo que puede aparecer dentro de la sentencia queel disparador ejecutara al activarse:

El disparador no puede referirse a tablas directamente por su nombre, inclu-yendo la misma tabla a la que esta asociado. Sin embargo, se pueden emplearlas palabras clave OLD y NEW. OLD se refiere a un registro existente que vaa borrarse o que va a actualizarse antes de que esto ocurra. NEW se refierea un registro nuevo que se insertara o a un registro modificado luego de queocurre la modificacion.

El disparador no puede invocar procedimientos almacenados utilizando la sen-tencia CALL. (Esto significa, por ejemplo, que no se puede utilizar un pro-cedimiento almacenado para eludir la prohibicion de referirse a tablas por sunombre).

El disparador no puede utilizar sentencias que inicien o finalicen una transac-cion, tal como START TRANSACTION, COMMIT, o ROLLBACK.

25

Las palabras clave OLD y NEW permiten acceder a columnas en los registrosafectados por un disparador. (OLD y NEW no son sensibles a mayusculas). Enun disparador para INSERT, solamente puede utilizarse NEW.nom col; ya que nohay una version anterior del registro. En un disparador para DELETE solo puedeemplearse OLD.nom col, porque no hay un nuevo registro. En un disparador paraUPDATE se puede emplear OLD.nom col para referirse a las columnas de un regis-tro antes de que sea actualizado, y NEW.nom col para referirse a las columnas delregistro luego de actualizarlo.

Una columna precedida por OLD es de solo lectura. Es posible hacer referenciaa ella pero no modificarla. Una columna precedida por NEW puede ser referenciadasi se tiene el privilegio SELECT sobre ella. En un disparador BEFORE, tambien esposible cambiar su valor con SET NEW.nombre col = valor si se tiene el privilegiode UPDATE sobre ella. Esto significa que un disparador puede usarse para modificarlos valores antes que se inserten en un nuevo registro o se empleen para actualizaruno existente.

En un disparador BEFORE, el valor de NEW para una columna AUTO INCREMENTes 0, no el numero secuencial que se generara en forma automatica cuando el registrosea realmente insertado.

OLD y NEW son extensiones de MySQL para los disparadores.

Empleando el constructor BEGIN ... END, se puede definir un disparador queejecute sentencias multiples. Dentro del bloque BEGIN, tambien pueden utilizarseotras sintaxis permitidas en rutinas almacenadas, tales como condicionales y bucles.Como sucede con las rutinas almacenadas, cuando se crea un disparador que ejecutasentencias multiples, se hace necesario redefinir el delimitador de sentencias si seingresara el disparador a traves del programa mysql, de forma que se pueda utilizarel caracter ; dentro de la definicion del disparador. El siguiente ejemplo ilustraestos aspectos. En el se crea un disparador para UPDATE, que verifica los valoresutilizados para actualizar cada columna, y modifica el valor para que se encuentreen un rango de 0 a 100. Esto debe hacerse en un disparador BEFORE porque losvalores deben verificarse antes de emplearse para actualizar el registro:

mysql> delimiter //mysql> CREATE TRIGGER upd check BEFORE UPDATE ON account

-> FOR EACH ROW-> BEGIN-> IF NEW.amount < 0 THEN-> SET NEW.amount = 0;

26

-> ELSEIF NEW.amount > 100 THEN-> SET NEW.amount = 100;-> END IF;-> END;//

mysql> delimiter ;

Podra parecer mas facil definir una rutina almacenada e invocarla desde el dis-parador utilizando una simple sentencia CALL. Esto sera ventajoso tambien si sedeseara invocar la misma rutina desde distintos disparadores. Sin embargo, una li-mitacion de los disparadores es que no pueden utilizar CALL. Se debe escribir lasentencia compuesta en cada CREATE TRIGGER donde se la desee emplear.

MySQL gestiona los errores ocurridos durante la ejecucion de disparadores deesta manera:

Si lo que falla es un disparador BEFORE, no se ejecuta la operacion en elcorrespondiente registro.

Un disparador AFTER se ejecuta solamente si el disparador BEFORE (deexistir) y la operacion se ejecutaron exitosamente.

Un error durante la ejecucion de un disparador BEFORE o AFTER deriva enla falla de toda la sentencia que provoco la invocacion del disparador.

En tablas transaccionales, la falla de un disparador (y por lo tanto de todala sentencia) debera causar la cancelacion (rollback) de todos los cambiosrealizados por esa sentencia. En tablas no transaccionales, cualquier cambiorealizado antes del error no se ve afectado.

27