Funciones personalizadas
Otra de las nuevas ventajas de MySQL 5 es que se pueden definir funciones personalizadas, para incluirse en consultas, vistas y store procedures.
El lenguaje a utilizar es SQL y su sintaxis es muy similar a la de una función en un lenguaje de programación, se pasan parámetros y la función regresa un resultado dependiendo del proceso que se realice , veamos un ejemplo
DELIMITER //
CREATE FUNCTION `func_fecha_db`(fech DATETIME) RETURNS char(14)
BEGIN
RETURN date_format(fech,’%d/%m/%Y’);
END
//
DELIMITER ;
Antes que nada hay que cambiar el delimitador de ejecución en la línea de comandos este comúnmente es el símbolo de ; esto con el fin de poder definir el cuerpo de la función después se regresa a su estado original
En la función anterior se define el nombre para la misma como fun_fecha_db, recibe un parámetro de tipo DATETIME para procesar ese valor y regresa un resultado de tipo CHAR(14), Iniciamos el proceso de la función la cual regresa la fecha formateada en dd/mm/aaaa
Ejecutemos los siguientes comandos:
mysql>
mysql> DELIMITER //
mysql> CREATE FUNCTION `func_fecha_db`(fech DATETIME) RETURNS char(14)
-> BEGIN
-> RETURN date_format(fech,’%d/%m/%Y’);
-> END
-> //
Query OK, 0 rows affected (0.05 sec)
mysql> DELIMITER ;
Aquí ya creamos la función y la almacenamos en la base de datos, ahora veamos como nos procesa las fechas:
mysql> SELECT func_fecha_db (‘2005-12-25’);
+——————————+
func_fecha_db (‘2005-12-25’)
+——————————+
25/12/2005
+——————————+
1 row in set (0.34 sec)
Como podemos ver le pasamos una fecha en formato de MySQL nativo la cual es aaaa-mm-dd y nos regresa formateada la fecha como dd/mm/aaaa
Ahora realicemos una prueba usando la función interna de MySQL que nos regresa la fecha y hora actual esta es NOW()
mysql> SELECT func_fecha_db (NOW());
+———————–+
func_fecha_db (NOW())
+———————–+
25/12/2005
+———————–+
1 row in set (0.00 sec)
Obtenemos el mismo resultado, veamos que aplicación podrá tener en nuestra base de datos de ejemplo, primero agreguemos un campo de fecha a la tabla contacto y después actualicemos las fechas de esos campos
MySQL> ALTER TABLE `pruebas`.`contacto` ADD COLUMN `fecha` DATE NOT NULL DEFAULT 0 AFTER `id_depto`;
mysql> SELECT * FROM contacto;
+—-+——————+——————–+———-+————+
id nombre correo id_depto fecha
+—-+——————+——————–+———-+————+
1 Juan Perez Comun jperez@ejemplo.com 1 0000-00-00
2 Pito Paez ppaez@ejemplo.com 5 0000-00-00
3 Pedro Mtz pmtz@ejemplo.com 2 0000-00-00
+—-+——————+——————–+———-+————+
3 rows in set (0.00 sec)
mysql> update contacto SET fecha=’2005-10-24′ WHERE id=1;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update contacto SET fecha=’2005-11-24′ WHERE id=2;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update contacto SET fecha=’2005-12-24′ WHERE id=3;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM contacto;
+—-+——————+——————–+———-+————+
id nombre correo id_depto fecha
+—-+——————+——————–+———-+————+
1 Juan Perez Comun jperez@ejemplo.com 1 2005-10-24
2 Pito Paez ppaez@ejemplo.com 5 2005-11-24
3 Pedro Mtz pmtz@ejemplo.com 2 2005-12-24
+—-+——————+——————–+———-+————+
3 rows in set (0.00 sec)
Como podemos ver ya tenemos el campo fecha agregado en la tabla de contacto y las fechas actualizadas en la misma tabla ahora hagamos una consulta que use nuestra función
mysql> SELECT nombre,correo,func_fecha_db(fecha) AS fechaok FROM contacto;
+——————+——————–+————+
nombre correo fechaok
+——————+——————–+————+
Juan Perez Comun jperez@ejemplo.com 24/10/2005
Pito Paez ppaez@ejemplo.com 24/11/2005
Pedro Mtz pmtz@ejemplo.com 24/12/2005
+——————+——————–+————+
3 rows in set (0.00 sec)
El resultado es que nuestra consulta devuelve los datos con las fechas ya formateadas, vayamos un poco mas adelante y ahora creemos una vista que haga uso de esta misma función y una las dos tablas en una sola vista.
mysql> CREATE VIEW view_func AS SELECT nombre,correo,func_fecha_db(fecha) AS fechaok,depto FROM contacto,depto WHERE contacto.id_depto=depto.id_depto;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM view_func;
+——————+——————–+————+—————+
nombre correo fechaok depto
+——————+——————–+————+—————+
Juan Perez Comun jperez@ejemplo.com 24/10/2005 Conta
Pito Paez ppaez@ejemplo.com 24/11/2005 Mercadotecnia
Pedro Mtz pmtz@ejemplo.com 24/12/2005 Finanzas
+——————+——————–+————+—————+
3 rows in set (0.01 sec)
Como podemos ver la vista fue creada con una consulta muy similar a la de la vista anterior solo que agregamos el campo de fecha y lo formateamos con la función que creamos, y el resultado una vista que regresa los datos ya formateados y listos para desplegar.
El uso de funciones personalizadas puede optimizar mucho el trabajo de las consultas complejas y en conjunto con las vistas son una potente herramienta para el desarrollador y el administrador de la base de datos.
Se deja un ejemplo de una función un poco mas compleja la cual sirve para regresar la fecha pero en un formato castellano, esto sirve para evitar que el lenguaje de programación tenga que realizar estas conversiones y la base de datos minimice el trabajo de la aplicación.
DELIMITER //
CREATE FUNCTION `detalle`(fecha DATETIME) RETURNS varchar(150)
BEGIN
DECLARE dia INT;
DECLARE mes INT;
DECLARE dia_s VARCHAR(20);
DECLARE mes_s VARCHAR(20);
DECLARE fecha_f VARCHAR(150);
SET dia = DAYOFWEEK(fecha);
SET mes = MONTH(fecha);
CASE dia
WHEN 1 THEN SET dia_s = ‘Domingo’;
WHEN 2 THEN SET dia_s = ‘Lunes’;
WHEN 3 THEN SET dia_s = ‘Martes’;
WHEN 4 THEN SET dia_s = ‘Miercoles’;
WHEN 5 THEN SET dia_s = ‘Jueves’;
WHEN 6 THEN SET dia_s = ‘Viernes’;
WHEN 7 THEN SET dia_s = ‘Sabado’;
END CASE;
CASE mes
WHEN 1 THEN SET mes_s = ‘Enero’;
WHEN 2 THEN SET mes_s = ‘Febrero’;
WHEN 3 THEN SET mes_s = ‘Marzo’;
WHEN 4 THEN SET mes_s = ‘Abril’;
WHEN 5 THEN SET mes_s = ‘Mayo’;
WHEN 6 THEN SET mes_s = ‘Junio’;
WHEN 7 THEN SET mes_s = ‘Julio’;
WHEN 8 THEN SET mes_s = ‘Agosto’;
WHEN 9 THEN SET mes_s = ‘Septiembre’;
WHEN 10 THEN SET mes_s = ‘Octubre’;
WHEN 11 THEN SET mes_s = ‘Noviembre’;
WHEN 12 THEN SET mes_s = ‘Diciembre’;
END CASE;
SET fecha_f = CONCAT(dia_s,’ ‘,DAY(fecha),’ ‘,mes_s,’ ‘,YEAR(fecha));
RETURN fecha_f;
END
//
DELIMITER ;
Se sugiere analizar la función y realizar pruebas con este ejemplo
Bueno pues eso es todo amigos nos vemos en el tercer y ultimo capitulo de esta serie de MySQL5
Saludos desde un byte, un campo, un registro una tabla y una base de datos
Dante Robles