Para crear una función o un procedimiento se deben usar las instrucciones CREATE FUNCTION o CREATE PROCEDURE.
Ejemplo:
delimiter $
CREATE FUNCTION age (date1 DATE, date2 DATE)
RETURNS INT
BEGIN
DECLARE age INT;
SET age = (YEAR(date2) - YEAR(date1)) - IF(RIGHT(date2,5) < RIGHT(date1,5),1,0);
RETURN age;
END$
delimiter ;
SELECT age('1985-12-25, CURDATE()) as Edad;
Un procedimiento es similar a una función, con la diferencia de que no puede regresar ningún valor, así que no incluye ninguna instrucción RETURN. Ejemplo:
DROP PROCEDURE IF EXISTS nacio_en_anio;
CREATE PROCEDURE nacio_en_anio (anio_nacimiento INT)
SELECT nombre, ap_paterno, nacimiento, muerte FROM presidentes WHERE YEAR(nacimiento) = anio_nacimiento;
El resultado de el procedimiento anterior no es regresado como valor, sino como resultset al cliente que lo manda llamar. Para invocar un procedimiento se debe utilizar la instrucción CALL. Ejemplo:
CALL nacio_en_anio(1908);
El ejemplo anterior ilustra una cosa que los procedimientos pueden hacer y que las funciones no: Los procedimientos pueden acceder a tablas. A su vez, se puede definir un procedimiento que realiza alguna operación determinada en una tabla y que incluya en su definición un parámetro como IN o INOUT para regresar el valor del procedimiento cuando éste regrese. Esta técnica también es utilizada si se necesita regresar más de un valor, ya que una función no puede regresar más de un valor.
De manera predeterminada, un parámetro de un procedimiento es de tipo IN; un parámetro definido de esta manera se recibe en el procedimiento pero cualquier modificación realizada en él no se conservará una vez que el procedimiento termine. Un parámetro OUT es lo contrario: el procedimiento asignará algún valor al parámetro, el cual podrá ser accedido una vez que el procedimiento haya regresado. Un parámetro INOUT permite mandar un valor al procedimiento y obtenerlo de vuelta.
El siguiente ejemplo ilustra lo anterior:
delimiter $
CREATE PROCEDURE cuenta_nacimientos
(anio_nacimiento INT, OUT cuantos INT)
BEGIN
DECLARE c CURSOR FOR
SELECT COUNT(*) FROM presidentes WHERE YEAR(nacimiento) = anio_nacimiento;
OPEN c;
FETCH c into cuantos;
CLOSE c;
END$
delimiter ;
El procedimiento anterior no solamente invoca la instrucción SELECT y asigna el valor de COUNT(*) a una variable. Si hiciera eso, el resultado del query hubiera sido desplegado en la pantalla del cliente. Para evitar ese despliegue, el procedimiento inicializa un cursor y lo usa para ejecutar la instrucción SELECT. De esta manera, el resultado del SELECT se queda dentro del procedimiento de tal manera que pueda procesar el resultado directamente.
Para utilizar un cursor, se debe declarar asociándolo al query que será ejecutado. Después se deberá abrir el cursor, obtener los renglones resultantes y por último se deberá cerrar el cursor.
El soporte para TRIGGERS en MySQL se realizó a partir de la versión 5.0.2. Un trigger puede ser definido para activarse en un INSERT, DELETE o UPDATE en una tabla y puede configurarse para activarse ya sea antes o después de que se haya procesado cada renglón por el query.
Los TRIGGERS en MySQL tienen la misma limitante que las funciones. No pueden referirse a una tabla en general. Pueden solamente referirse a un valor del renglón que está siendo modificado por el query que se está ejecutando.
Las características más importantes de los TRIGGERS son:
BEFORE TRIGGER, puede cambiar el nuevo valor antes de que sea almacenado en la base de datos,
lo que permite realizar un filtrado de la información.
El siguiente ejemplo muestra un BEFORE TRIGGER para un
SELECT de una tabla:
CREATE TABLE t(i INT, dt DATETIME);
delimiter $
CREATE TRIGGER t_ins BEFORE INSERT ON t
FOR EACH ROW BEGIN
SET NEW.dt = CURRENT_TIMESTAMP;
IF NEW.i < 0 THEN
SET NEW.i = 0;
END IF;
END$
delimiter ;
Las estructuras de control permiten, como su nombre lo indica, controlar el flujo de las instrucciones dentro de un procedimiento o una función. En la siguiente explicación, cada ocurrencia de instrucción(es), indica una lista de una o más instrucciones, cada una de las cuales debe terminar con ";".
Algunas de las estructuras pueden llevar una etiqueta (BEGIN, LOOP,
REPEAT y WHILE).
Las etiquetas no son sensibles a las mayúsculas o minúsculas pero deben seguir las siguientes reglas:
BEGIN [instrucción(es)] END
etiqueta: BEGIN [instrucción(es)] END [etiqueta]
La estructura BEGIN Ö END se utiliza para agrupar un conjunto de instrucciones. Si un procedimiento o una función necesita contener más de una intrucción, éstas deberán aparecer dentro de un BEGIN Ö END. De la misma manera, si el procedimiento o función contienen una rutina DECLARE, ésta deberá aparecer al principio del bloque BEGIN Ö END.
CASE [expresión]
WHEN expresión1 THEN instruccion(es)
[WHEN expresión2 THEN instruccion(es)]
...
[ELSE instruccion(es)]
END CASE;
IF expr1 THEN instruccion(es)
[ELSEIF expr2 THEN instruccion(es)] ...
[ELSE instruccion(es)]
END IF
ITERATE etiqueta
ITERATE solamente puede aparecer dentro de un LOOP,
REPEAT y WHILE
. Lo que realmente significa es: "Haz el ciclo de Nuevo". Por ejemplo:
delimiter $
CREATE PROCEDURE doiterate(p1 INT)
BEGIN
label1: LOOP
SET p1 = p1 + 1;
IF p1 < 10 THEN
ITERATE label1;
END IF;
LEAVE label1;
END LOOP label1;
SET @x = p1;
END$
delimiter ;
LEAVE etiqueta
Esta instrucción es utilizada para salir de alguna estructura de control. Puede ser usada dentro de un BEGIN Ö END o dentro de algún ciclo.
[etiqueta_inicio:] LOOP
instruccion(es)
END LOOP [etiqueta_fin]
LOOP implementa un ciclo simple, permitiendo que una instrucción o conjunto de instrucciones se repitan. Las instrucciones dentro de este ciclo se repetirán hasta que se ocasione alguna salida, lo cual se hace generalmente con una instrucción LEAVE.
Un ciclo LOOP puede ser etiquetado. etiqueta_fin no puede estar presente a menos que etiqueta_inicio también lo está y, si ambos están presentes, deberán ser iguales.
[etiqueta_inicio:] REPEAT
instruccion(es)
UNTIL condicion
END REPEAT [etiqueta_fin];
La instrucción o instrucciones dentro de un ciclo REPEAT se repetirán hasta que la condicion sea verdadadera.
Un ciclo REPEAT puede ser etiquetado. etiqueta_fin no puede estar presente a menos que etiqueta_inicio también lo está y, si ambos están presentes, deberán ser iguales.
RETURN expresión;
La instrucción RETURN se utiliza solamente dentro de una función. Al ejecutarse, terminará por completo la función dentro de la que se encuentra.
[etiqueta_inicio:] WHILE condición DO
instruccion(es)
END WHILE [etiqueta_fin]
La instrucción o instrucciones dentro de un WHILE serán repetidas mientras la condición sea verdadera.
Un ciclo WHILE puede ser etiquetado. etiqueta_fin no puede estar presente a menos que etiqueta_inicio también lo está y, si ambos están presentes, deberán ser iguales.
En una declaración se pueden crear:
DECLARE nombre_de_variable [, nombre_de_variableÖ] TIPO [valor predeterminado ];
DECLARE nombre_de_condición CONDITION FOR condición
condicion: {SQLSTATE [VALOR] sqlstate_value | mysql_errno}
DECLARE nombre_del_cursor CURSOR FOR instrucción_select
DECLARE handler_type
HANDLER FOR handler_condition [, handler_condition] Ö
instruccion
handler_type: {CONTINUE | EXIT}
handler_condition:
{
SQLSTATE[VALUE] sqlstate_value
| mysql_errno
| condition_name
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION
}
La declaración de una variable local, una condición, un cursor o un manejador solamente puede aparecer al principio de un bloque
BEGIN Ö END. Si se necesitan hacer diferentes declaraciones, éstas deben hacerse en el siguiente orden:
Las variables locales se pueden declarar dentro de alguna rutina en la misma línea (siempre y cuando sean del mismo tipo), separando cada una por una coma. Para darle un valor a éstas o para inicializarlas, se utilzará la instrucción SET.
La instrucción DECLARE Ö CONDITION crea el nombre para una condición. Dicho nombre puede referirse a una instrucción DECLARE Ö HANDLER. nombre_de_condición puede ser ya sea un valor SQLSTATE representado por cinco caracteres o un valor numérico específico de MySQL.
La instrucción DECLARE Ö CURSOR declara un cursor para ser asociado a algún SELECT , el cual no deberá contener la instrucción INTO. El cursor puede abrirse con la cláusula OPEN. Se deberá utilizar la instrucción FETCH para obtener los renglones resultantes del SELECT y se deberá cerrar con la instrucción CLOSE.
La instrucción DECLARE Ö HANDLER asocia una o más condiciones con una instrucción a ser ejecutada cuando alguna de las condiciones ocurre. El valor del handler_type indica qué ocurre cuando la condición se ejecuta. Con la instrucción CONTINUE , la ejecución de la instrucción continúa, con la instrucción EXIT el bloque BEGIN actual terminará.
handler_condition puede ser alguno de los siguientes valores:
DECLARE Ö CONDITION.
Ejemplo:
delimiter $
CREATE PROCEDURE ejemplo ()
BEGIN
DECLARE 'Constraint Violation'
CONDITION FOR SQLSTATE '23000';
DECLARE EXIT HANDLER FOR
'Constraint Violation' ROLLBACK;
START TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (1);
COMMIT;
END;
delimiter ;
CLOSE nombre_de_cursor
Cierra el cursor, el cual deberá estar abierto. Un cursor abierto es cerrado automáticamente cuando el bloque BEGIN dentro del cual está termina.
FETCH [[NEXT] FROM] nombre_de_cursor INTO variable [, variable2,Ö]
Obtiene el siguiente renglón para el cursor actual y almacena cada una de sus columnas en las variables mencionadas. El cursor debe estar abierto. Si no está disponible ningún renglón, un error con un valor de SQLSTATE igual a 02000 ocurre.
OPEN nombre_de_cursor
Abre el cursor para que pueda ser utilizado con la instrucción FETCH.
En las rutinas de MySQL, los comentarios pueden hacerse de tres maneras:
#":
# Este es un comentario
/* Este es un comentario de una línea */
/* Este es un comentario
que abarca
varias líneas */
--
-- Este es un comentario
--