Lección 17 - Funciones
![Nota Nota](./styles/prosilver/imageset/icon_post_target.gif)
Las funciones se pueden ver como cajas negras a las que les pasamos unos parámetros de entrada y tras procesarlos devuelven un único resultado o dato de salida.
Con anterioridad en este curso apareció la función CONCAT, que realiza la concatenación de dos o más cadenas de texto. Los parámetros de entrada en este caso son tantas cadenas como deseemos separadas por comas, y el resultado que devuelve es una única cadena con la concatenación de todas las cadenas de entrada.
En realidad existen infinidad de funciones con propósitos y utilidades múltiples. Las funciones están fuera del estándar SQL, cada SGBD tiene las suyas aunque existen funcionalidades presentes en todos ellos pudiendo tener diferente nombre. En esta lección veremos algunos ejemplos de funciones que por razones obvias solo podrán aplicarse al SGBD MySQL. Encontrará por Internet numerosas páginas donde documentarse sobre las funciones para este y otros SGBD.
En general una función recibe como parámetro valores, y en función de estos devuelven un resultado que es el que se considera al llamar a la función desde la cláusula SELECT de una consulta, o desde la cláusula WHERE, o desde cualquier lugar aplicable.
Alguna funciones no precisan parámetros, por ejemplo la función LOCALTIME y CURRENT_DATE. La primera devuelve la fecha y la hora del servidor de BD, la segunda solo la fecha, por tanto si usted quiere saber la fecha y la hora del servidor cuando le sirvió esta página, aquí tiene la respuesta:
Funciones para fechas
Las funciones más usadas son quizás las de tratamiento de fechas y cadenas alfanuméricas. Veamos un ejemplo de formateo de fecha: supongamos por ejemplo que: de la fecha y hora actual solo nos interesa mostrar el mes y el año, para ello se usa la función DATE_FORMAT. Esta función precisa dos parámetros, en primer lugar el dato de tipo fecha que se quiere formatear, y seguidamente la máscara que determina el formato. Para el mes y año una mascará posible es la siguiente: '%m-%Y', por tanto la llamada a la función DATE_FORMAT para formatear una fecha con mes y año se realiza del siguiente modo:
Y para que las fechas aparezcan en un formato más normal del que devuelve por defecto MySQL, al menos por lo que respecta a algunos países, la máscara es la siguiente: '%d-%m-%Y'. Así para mostrar por ejemplo los datos de la tabla EMPLEADOS con este formato para el campo F_NACIMIENTO podríamos construir la siguiente consulta:
Si se quieren usar barras en lugar de guiones para separar el día mes y año en una dato de tipo fecha, tan solo deberá indicarlo en la mascara como se muestra en el siguiente ejemplo:
Para conocer todas las posibilidades que ofrece MySQL o cualquier otro SGBD en lo que a máscaras de formato de fechas se refiere deberá consultar la documentación de cada sistema en particular. Encontrará numerosas páginas en Internet con información al respecto.
Veamos ahora una función que opera con fechas de modo que permite, por ejemplo, sumar días a una fecha obteniendo como resultado una nueva fecha. Para ello usamos la función de MySQL DATE_ADD.
El valor que devuelve la función DATE_ADD es un dato de tipo fecha, de modo que es posible usar la llamada a DATE_ADD como parámetro en la función DATE_FORMAT para darle formato al resultado que devuelve DATE_ADD:
Por último en lo que a funciones de fecha se refiere, aunque existen muchas más, veremos un ejemplo de la función DATEDIFF, que devuelve los días de diferencias entre dos fechas. Si usted recuerda la tabla VEHICULOS, donde se guardaba para cada unidad la fecha de la próxima revisión, se preguntará quizás como realizar una consulta que informe de los vehículos que deben pasar la revisión en los próximos 30 días, para ello supondremos que hoy es 15 de noviembre de 2009:
Funciones para cadenas
Otro tipo de funciones son las de tratamiento de cadenas. Ya se ha visto al principio de esta lección la funcion CONCAT, que permite concatenar cadenas. Con estas funciones podemos obtener subcadenas de una cadena dada, por ejemplo los cuatro primeros caracteres. Para ello usaremos SUBSTR abreviación de substring, es decir, subcadena. Como parámetros recibe el dato de tipo cadena a tratar en primer lugar, seguido de la posición dentro de la cadena donde se quiere obtener la subcadena, y por último la longitud o número de caracteres de esta. Ejemplos:
Con la función LENGTH se obtiene la longitud de una cadena:
La función REPLACE es de gran utilidad, remplaza en una cadena un texto por otro. Por ejemplo, imagine que usted vende manteles de varios colores y en función de un campo de tabla que vendrá de un filtro seleccionado por el usuario le viene el valor naranja, aunque el usuario podría haber seleccionado otros colores disponibles. Mediante la siguiente consulta podría establecer un texto fijo con una subcadena a remplazar por el color, que es variable y depende de lo que el usuario selecciona. La idea del siguiente ejemplo es que el literal 'naranja' seria en realidad un campo de tabla con el valor 'naranja'.
La función IF
Hablemos ahora de una función un poco particular pero de suma utilidad, la función IF de MySQL. En Oracle se llama DECODE y funciona de forma un poco distinta. Permite condicionar el valor que devuelve en función de si se cumple una condición que se establece. Si usted recuerda la tabla personas, donde se guardaba una 'S' para indicar Sí, y una 'N' para indicar No, con la función IF podemos dar una salida de resultados más humana decodificando esta codificación:
La función IF en este caso interroga si el campo RUBIA contiene un 'S', si es así devuelve 'Sí', en caso contrario devuelve 'No'
Funciones numéricas
Por último comentaremos algunas funciones para trabajar con números. Por ejemplo la función ROUND, que permite redondear un número a por ejemplo dos decimales, con lo que evitamos largas ristras de números en los resultados. O TRUNCATE que trunca un número por la parte decimal que se le indique, pudiendo así considerar únicamente la parte entera:
Consulte la documentación del SGBD que le ocupe para obtener más información al respecto.
Resumen
Las funciones esperan parámetros de un tipo de dato determinado y devuelven un valor de un tipo de dato determinado. El número de parámetros y el tipo de dato de cada parámetro depende de la especificación de cada función, al igual que el tipo de dato que devuelve cada una y de su funcionalidad.
Las funciones permiten obtener valores en función de los parámetros que se le pasa para mostrarlos u operar con ellos. Los parámetros pueden ser constantes, campos de tabla, o bien llamadas a una función. En este último caso el valor que devuelve la función ejerce de parámetro y por tanto será el dato que considerará la función que lo toma como parámetro.
Si la llamada se realiza en la cláusula SELECT el valor que devuelve la función se mostrará como un campo más de tabla, es apropiado entonces rebautizar la columna con un alias. Si se usa en la clausula WHERE el valor que devuelve formará parte de una condición que se evaluará como un campo más de tabla para mostrar o ignorar el registro. También es posible hacer la llamada a una función en la cláusula GROUP BY, si también se ha hecho en la cláusula SELECT y se pretende agrupar por esa columna.
No deben confundirse este tipo de funciones con las funciones de totalización (SUM, AVG, etc...) estas últimas operan con todos los registros seleccionados de una consulta, mientras que las primeras operan únicamente con valores de un solo registro, o si usted quiere, se llama a la función tantas veces como registros devuelve la consulta, y el resultado de la función forma parte de la fila resultante de cada registro.
Ejercicio 1
Realice una consulta que devuelva la media de salarios de la tabla EMPLEADOS agrupado por sexo. Redondee la media de salarios a un solo decimal y decodifique la columna sexo para que aparezca el literal HOMBRES y MUJERES en lugar de H y M. No olvide rebautizar las columnas con un alias apropiado.
Ejercicio 2
Realice una consulta sobre la tabla EMPLEADOS que devuelva el nombre, los apellidos, la fecha de nacimiento y la edad actual en años de cada empleado. Para aquellos empleados con 18 años o más.
Nota: la edad de un empleado en años es el número de días transcurridos desde el nacimiento dividido entre los 365 días que tiene un año.
Ejercicio 3
Realice una consulta sobre la tabla vehículos que devuelva el número de vehículos que deben pasar la revisión agrupado por el año en que deben pasarla.
![Mostrar soluciones](/ipropias/soluciones.gif)
Con anterioridad en este curso apareció la función CONCAT, que realiza la concatenación de dos o más cadenas de texto. Los parámetros de entrada en este caso son tantas cadenas como deseemos separadas por comas, y el resultado que devuelve es una única cadena con la concatenación de todas las cadenas de entrada.
- Código: Seleccionar todo
select concat('Esto ','es ','un ','ejemplo ',
'de ','concatenación ', 'de ', 'cadenas ',
'de ','texto.') as EJEMPLO_CONCAT
En realidad existen infinidad de funciones con propósitos y utilidades múltiples. Las funciones están fuera del estándar SQL, cada SGBD tiene las suyas aunque existen funcionalidades presentes en todos ellos pudiendo tener diferente nombre. En esta lección veremos algunos ejemplos de funciones que por razones obvias solo podrán aplicarse al SGBD MySQL. Encontrará por Internet numerosas páginas donde documentarse sobre las funciones para este y otros SGBD.
En general una función recibe como parámetro valores, y en función de estos devuelven un resultado que es el que se considera al llamar a la función desde la cláusula SELECT de una consulta, o desde la cláusula WHERE, o desde cualquier lugar aplicable.
Alguna funciones no precisan parámetros, por ejemplo la función LOCALTIME y CURRENT_DATE. La primera devuelve la fecha y la hora del servidor de BD, la segunda solo la fecha, por tanto si usted quiere saber la fecha y la hora del servidor cuando le sirvió esta página, aquí tiene la respuesta:
- Código: Seleccionar todo
select localtime , current_date
Funciones para fechas
Las funciones más usadas son quizás las de tratamiento de fechas y cadenas alfanuméricas. Veamos un ejemplo de formateo de fecha: supongamos por ejemplo que: de la fecha y hora actual solo nos interesa mostrar el mes y el año, para ello se usa la función DATE_FORMAT. Esta función precisa dos parámetros, en primer lugar el dato de tipo fecha que se quiere formatear, y seguidamente la máscara que determina el formato. Para el mes y año una mascará posible es la siguiente: '%m-%Y', por tanto la llamada a la función DATE_FORMAT para formatear una fecha con mes y año se realiza del siguiente modo:
- Código: Seleccionar todo
select date_format(localtime,'%m-%Y')
Y para que las fechas aparezcan en un formato más normal del que devuelve por defecto MySQL, al menos por lo que respecta a algunos países, la máscara es la siguiente: '%d-%m-%Y'. Así para mostrar por ejemplo los datos de la tabla EMPLEADOS con este formato para el campo F_NACIMIENTO podríamos construir la siguiente consulta:
- Código: Seleccionar todo
select ID_EMPLEADO, NOMBRE, APELLIDOS, date_format(F_NACIMIENTO,'%d-%m-%Y') F_NACIMIENTO
FROM EMPLEADOS
Si se quieren usar barras en lugar de guiones para separar el día mes y año en una dato de tipo fecha, tan solo deberá indicarlo en la mascara como se muestra en el siguiente ejemplo:
- Código: Seleccionar todo
select ID_EMPLEADO, NOMBRE, APELLIDOS, date_format(F_NACIMIENTO,'%d/%m/%Y') F_NACIMIENTO
FROM EMPLEADOS
Para conocer todas las posibilidades que ofrece MySQL o cualquier otro SGBD en lo que a máscaras de formato de fechas se refiere deberá consultar la documentación de cada sistema en particular. Encontrará numerosas páginas en Internet con información al respecto.
Veamos ahora una función que opera con fechas de modo que permite, por ejemplo, sumar días a una fecha obteniendo como resultado una nueva fecha. Para ello usamos la función de MySQL DATE_ADD.
- Código: Seleccionar todo
select date_add(current_date, INTERVAL 30 DAY) FECHA_ACTUAL_MAS_TREINTA_DIAS,
date_add(current_date, INTERVAL 6 MONTH) FECHA_ACTUAL_MAS_SEIS_MESES
El valor que devuelve la función DATE_ADD es un dato de tipo fecha, de modo que es posible usar la llamada a DATE_ADD como parámetro en la función DATE_FORMAT para darle formato al resultado que devuelve DATE_ADD:
- Código: Seleccionar todo
select date_format(date_add(current_date, INTERVAL 30 DAY) , '%d-%m-%Y') FECHA_ACTUAL_MAS_TREINTA_DIAS,
date_format(date_add(current_date, INTERVAL 6 MONTH) , '%d-%m-%Y') FECHA_ACTUAL_MAS_SEIS_MESES
Por último en lo que a funciones de fecha se refiere, aunque existen muchas más, veremos un ejemplo de la función DATEDIFF, que devuelve los días de diferencias entre dos fechas. Si usted recuerda la tabla VEHICULOS, donde se guardaba para cada unidad la fecha de la próxima revisión, se preguntará quizás como realizar una consulta que informe de los vehículos que deben pasar la revisión en los próximos 30 días, para ello supondremos que hoy es 15 de noviembre de 2009:
- Código: Seleccionar todo
select *
from vehiculos
where datediff(PROX_ITV,'2009-11-15') < 31
* * *
Funciones para cadenas
Otro tipo de funciones son las de tratamiento de cadenas. Ya se ha visto al principio de esta lección la funcion CONCAT, que permite concatenar cadenas. Con estas funciones podemos obtener subcadenas de una cadena dada, por ejemplo los cuatro primeros caracteres. Para ello usaremos SUBSTR abreviación de substring, es decir, subcadena. Como parámetros recibe el dato de tipo cadena a tratar en primer lugar, seguido de la posición dentro de la cadena donde se quiere obtener la subcadena, y por último la longitud o número de caracteres de esta. Ejemplos:
- Código: Seleccionar todo
select substr('ABCDEFGHIJ',1,4) LOS_CUATRO_PRIMEROS_CARACTERES
- Código: Seleccionar todo
select substr('ABCDEFGHIJ',4,3) LOS_TRES_CARACTERES_CENTRALES
- Código: Seleccionar todo
select substr('ABCDEFGHIJ',3) LA_CADENA_IGNORANDO_LOS_DOS_CARACTERES_INICALES
- Código: Seleccionar todo
select substr('ABCDEFGHIJ',-2) LOS_DOS_CARACTERES_FINALES
Con la función LENGTH se obtiene la longitud de una cadena:
- Código: Seleccionar todo
select length('ABCDEFGHIJ')
La función REPLACE es de gran utilidad, remplaza en una cadena un texto por otro. Por ejemplo, imagine que usted vende manteles de varios colores y en función de un campo de tabla que vendrá de un filtro seleccionado por el usuario le viene el valor naranja, aunque el usuario podría haber seleccionado otros colores disponibles. Mediante la siguiente consulta podría establecer un texto fijo con una subcadena a remplazar por el color, que es variable y depende de lo que el usuario selecciona. La idea del siguiente ejemplo es que el literal 'naranja' seria en realidad un campo de tabla con el valor 'naranja'.
- Código: Seleccionar todo
select REPLACE('Mantel de color &','&','naranja') PRODUCTO
* * *
La función IF
Hablemos ahora de una función un poco particular pero de suma utilidad, la función IF de MySQL. En Oracle se llama DECODE y funciona de forma un poco distinta. Permite condicionar el valor que devuelve en función de si se cumple una condición que se establece. Si usted recuerda la tabla personas, donde se guardaba una 'S' para indicar Sí, y una 'N' para indicar No, con la función IF podemos dar una salida de resultados más humana decodificando esta codificación:
- Código: Seleccionar todo
select NOMBRE , if(RUBIA='S','Sí','No') RUBIA
from PERSONAS
La función IF en este caso interroga si el campo RUBIA contiene un 'S', si es así devuelve 'Sí', en caso contrario devuelve 'No'
* * *
Funciones numéricas
Por último comentaremos algunas funciones para trabajar con números. Por ejemplo la función ROUND, que permite redondear un número a por ejemplo dos decimales, con lo que evitamos largas ristras de números en los resultados. O TRUNCATE que trunca un número por la parte decimal que se le indique, pudiendo así considerar únicamente la parte entera:
- Código: Seleccionar todo
select round(7.64739836953 , 2) , truncate(7.64739836953 , 0)
Consulte la documentación del SGBD que le ocupe para obtener más información al respecto.
* * *
Resumen
Las funciones esperan parámetros de un tipo de dato determinado y devuelven un valor de un tipo de dato determinado. El número de parámetros y el tipo de dato de cada parámetro depende de la especificación de cada función, al igual que el tipo de dato que devuelve cada una y de su funcionalidad.
Las funciones permiten obtener valores en función de los parámetros que se le pasa para mostrarlos u operar con ellos. Los parámetros pueden ser constantes, campos de tabla, o bien llamadas a una función. En este último caso el valor que devuelve la función ejerce de parámetro y por tanto será el dato que considerará la función que lo toma como parámetro.
Si la llamada se realiza en la cláusula SELECT el valor que devuelve la función se mostrará como un campo más de tabla, es apropiado entonces rebautizar la columna con un alias. Si se usa en la clausula WHERE el valor que devuelve formará parte de una condición que se evaluará como un campo más de tabla para mostrar o ignorar el registro. También es posible hacer la llamada a una función en la cláusula GROUP BY, si también se ha hecho en la cláusula SELECT y se pretende agrupar por esa columna.
No deben confundirse este tipo de funciones con las funciones de totalización (SUM, AVG, etc...) estas últimas operan con todos los registros seleccionados de una consulta, mientras que las primeras operan únicamente con valores de un solo registro, o si usted quiere, se llama a la función tantas veces como registros devuelve la consulta, y el resultado de la función forma parte de la fila resultante de cada registro.
* * *
Ejercicio 1
Realice una consulta que devuelva la media de salarios de la tabla EMPLEADOS agrupado por sexo. Redondee la media de salarios a un solo decimal y decodifique la columna sexo para que aparezca el literal HOMBRES y MUJERES en lugar de H y M. No olvide rebautizar las columnas con un alias apropiado.
Ejercicio 2
Realice una consulta sobre la tabla EMPLEADOS que devuelva el nombre, los apellidos, la fecha de nacimiento y la edad actual en años de cada empleado. Para aquellos empleados con 18 años o más.
Nota: la edad de un empleado en años es el número de días transcurridos desde el nacimiento dividido entre los 365 días que tiene un año.
Ejercicio 3
Realice una consulta sobre la tabla vehículos que devuelva el número de vehículos que deben pasar la revisión agrupado por el año en que deben pasarla.
![Banco de pruebas](/ipropias/database.gif)
![Mostrar soluciones](/ipropias/soluciones.gif)