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

Bien nuevamente aqui estoy enviando un correo a toda la gente que como acostumbro decir me es importante, son mis amigos, mis compañeros y mis mejores jueces, como ya es costumbre cada dia 31 de diciembre de cada año envio este correo con mis reflexiones y pensamientos.

Sin decir mas ahi empezamos

Bien haciendo en breve repaso este año 2005 fue un año con muchas emociones, sentimientos y podria casi decir que extraño, porque paso de todo, como escribi hace un año, este año no hice realmente propositos, no desee nada al contrario me propuse dejar que las cosas sucedieran de una u otra forma, empeze a dejar de desear para empezar a querer, empeze a confiar en la capacidad y a dejar de creer que las cosas se arreglan solas, en resumidas cuentas empezamos a hacer que las cosas sucedieran no dejamos nada a la suerte.

Este año mi papa se adelanto a mejor vida, su tiempo aqui con nosotros se termino, fue algo que realmente me afecto, tuve muchas palabras de aliento en aquel momento, tuve mucha gente cerca tanto fisica como moralmente, pero hubo unas palabras de unos buenos amigos recientes que fueron las que me dieron un poco de paz en la que me explicaron que en la cultura japonesa la forma en que se toma la muerte es distinta, la persona que falta se une con nuestros antepasados para que desde ahi se encargue de ayudarnos y ver por nosotros, caray me senti tan tranquilo me agrado pensar que desde ese punto de vista ya el estaba mejor, como lo decia en alguna ocasion definitivamente el desde ahi se encargo de ayudarme y lo sigue haciendo.

Pero que nos ha dejado el 2005, realmente nos ha dejado las bases para muchas cosas que en el futuro se veran, nos ha dejado Fe, confianza, Desicion, y sobre todo amigos, un año de cambios y cambios buenos, en lo que como siempre lo he dicho mis amigos estuvieron conmigo y yo con ellos.

Este año 2006 no creo tampoco hacer propositos, porque la verdad ya se lo que quiero hacer y sobre todo tengo la certeza de que contando con gente como ustedes no hay porque dudar, como dice un buen amigo mio tambien hay dos caminos que tomar el facil y el dificil, el facil es no querer hacer
nada y por consecuencia estancarse, el dificil lleva un esfuerzo muchas veces grande pero con unos frutos enormes , este 2006 tenemos que tomar el camino dificil para hacer muchas cosas pero los frutos y los resultados seran enormes, menos mal que vamos juntos y nos apoyamos.

Como siempre gracias a todos y cada uno de ustedes, por no dejarme flaquear y permitirme seguir adelante, por confiar en mis ideas y sueños, por haber logrado materializar tantos de ellos, y seguir haciendo de este mundo algo mas mejor.

Ahora si este es mi ultimo teclazo de este año 2005, no espero escribir nada ya….. solo decirles pasen bien estas fiestas, diviertanse , relajense, no se estresen , y el 2006 ya estaremos todos juntos para hacerlo mejor.

Feliz año 2006

Sofia en italiano se dice Sophen que asi mismo significa Sabiduria….

Saludos su amigo,Cuate,Compadre,Men y como quieran llamarme

Dante