Por definición es importante indicar que una función es un conjunto de instrucciones SQL que realizan una tarea específica de manera automática. Las funciones fomentan la reutilización del código. Por ejemplo, si tiene que escribir repetidamente grandes scripts SQL para realizar la misma tarea, usted puede crear una función que realice esa tarea. Por ello, la próxima vez, en lugar de reescribir el SQL, de manera repetida lo que usted puede hacer es simplemente llamar a esa función. Una función acepta entradas en forma de parámetros y devuelve un valor. SQL Server viene con un conjunto de funciones integradas que realizan una variedad de tareas.
Por supuesto, es importante mencionar que se puede crear un procedimiento almacenado para agrupar un conjunto de instrucciones SQL para ser posteriormente ejecutadas, sin embargo, es importante recordar que no se puede llamar a los procedimientos almacenados dentro de las instrucciones SQL. Hay que considerar que las funciones, por otro lado, pueden ser invocadas. Además, otro problema con las funciones es que deben llamarse para cada fila. Por lo tanto, si está utilizando funciones con grandes conjuntos de datos, los mismos pueden tener problemas de rendimiento.
Trabajemos con un ejemplo simple.
Preparando los datos
Primero, creemos algunos datos ficticios. Para tal fin debemos utilizar estos datos para crear funciones definidas por el usuario.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
CREATE DATABASE schooldb CREATE TABLE student ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, gender VARCHAR(50) NOT NULL, DOB datetime NOT NULL, total_score INT NOT NULL, ) INSERT INTO student VALUES (1, 'Jolly', 'Female', '12-JUN-1989', 500), (2, 'Jon', 'Male', '02-FEB-1974', 545), (3, 'Sara', 'Female', '07-MAR-1988', 600), (4, 'Laura', 'Female', '22-DEC-1981', 400), (5, 'Alan', 'Male', '29-JUL-1993', 500), (6, 'Kate', 'Female', '03-JAN-1985', 500), (7, 'Joseph', 'Male', '09-APR-1982', 643), (8, 'Mice', 'Male', '16-AUG-1974', 543), (9, 'Wise', 'Male', '11-NOV-1987', 499), (10, 'Elis', 'Female', '28-OCT-1990', 400); |
De esta manera Este script creará la base de datos “schooldb” en su servidor. Consecuentemente, la base de datos tendrá una tabla con cinco columnas, las cuales son: id, nombre, género, fecha de nacimiento y “puntaje total”. También podemos indicar que la tabla también contendrá 10 registros de estudiantes ficticios.
Funciones integradas
Como se discutió anteriormente, el servidor SQL agrega algunas funciones integradas a cada base de datos. Si se desea ver la lista de funciones integradas para su base de datos “schooldb”, vaya al Explorador de objetos -> Bases de datos -> schooldb -> Programabilidad -> Funciones -> Funciones del sistema. Esto le proporciona la lista de todas las funciones integradas, como se muestra a continuación.
Dentro del archivo de las funciones del sistema, las funciones integradas se agrupan en diferentes grupos según su funcionalidad. Por ejemplo, si abre la carpeta “Funciones de fecha y hora”, como se muestra arriba, verá todas las funciones relacionadas con la fecha y la hora. Recuerde que al expandir cualquier función usted verá el tipo de parámetro y el valor devuelto por la función.
Si expande la función “Nombre de fecha”, verá que esta función acepta dos parámetros. El primer parámetro es la “Parte de fecha” que es de tipo varchar y el segundo parámetro es “Expresión” y en este caso se puede observar que ese es un parámetro de tipo de fecha y hora. La función devuelve un valor de tipo varchar.
Para ver la función Datename en acción; nosotros deberemos crear una consulta que seleccione los nombres y el año de nacimiento de los estudiantes. De tal forma que Cuando insertamos los registros ficticios, y especificamos la fecha de nacimiento completa de cada estudiante, que incluía el año, mes y año. Sin embargo, usando la función de nombre de fecha, recuperaremos solo el año de nacimiento de un estudiante. Por ello debemos echar un vistazo a la siguiente consulta.
|
1 2 3 4 5 6 |
USE schooldb SELECT name, DATENAME(YEAR, DOB) AS BIRTH_YEAR FROM student |
Observe que en la consulta anterior hemos utilizado la función de nombre de fecha. Recuerde que incluimos “AÑO” como parte de la fecha y la columna DOB como la expresión de fecha y hora a la función. Por ello se observa que la función anterior devolverá lo siguiente.
| Name | BIRTH_YEAR |
| Jolly | 1989 |
| Jon | 1974 |
| Sara | 1988 |
| Laura | 1981 |
| Alan | 1993 |
| Kate | 1985 |
| Joseph | 1982 |
| Mice | 1974 |
| Wise | 1987 |
| Elis | 1990 |
Funciones definidas por el usuario
Por lo general Las funciones integradas existentes no siempre ofrecen la funcionalidad deseada. Por ejemplo, tome la función “Nombre de fecha” que vimos en acción en la sección anterior. Recuerde que, aunque recupera la fecha en múltiples formatos, ¿qué puede suceder si desea recuperar la fecha en un formato diferente? uno que no sea compatible con la función “datename”. Por ejemplo, ¿qué sucede si queremos recuperar la fecha de nacimiento de los estudiantes (DOB) utilizando la manera indicada en el formato “viernes 29 de julio de 2009”? por cuanto es diferente del formato utilizado anteriormente. Ninguna función integrada recupera la fecha de nacimiento en este formato. Para hacer esto, tendríamos que llamar a la función “Nombre de fecha” varias veces y confiar que sea efectiva en la concatenación de cadenas para recuperar la fecha en nuestro formato deseado. Eche un vistazo al siguiente script que recupera la fecha en el formato que acabamos de comentar.
|
1 2 3 4 5 6 7 8 9 10 11 |
USE schooldb SELECT name, DATENAME(DW, DOB)+ ', '+ DATENAME(DAY, DOB)+ ' '+ DATENAME(MONTH, DOB) +', '+ DATENAME(YEAR, DOB) AS DOB FROM student |
En la consulta anterior, habíamos llamado a la función datename cuatro veces. Por ello, cada vez que fue registrado y verificado se ubicaba en, una parte de fecha diferente. En la primera llamada, pasamos DW como el parámetro que devuelve el día de la semana, luego concatenamos el resultado con otra llamada a la función datename que devolvió el día del mes. Del mismo modo, recuperaremos el nombre del mes y el nombre del año de la columna DOB de la tabla del alumno. Por ello La consulta anterior recupera los siguientes resultados.
| Name | DOB |
| Jolly | Monday, 12 June, 1989 |
| Jon | Saturday, 2 February, 1974 |
| Sara | Monday, 7 March, 1988 |
| Laura | Tuesday, 22 December, 1981 |
| Alan | Thursday, 29 July, 1993 |
| Kate | Thursday, 3 January, 1985 |
| Joseph | Friday, 9 April, 1982 |
| Mice | Friday, 16 August, 1974 |
| Wise | Wednesday, 11 November, 1987 |
| Elis | Sunday, 28 October, 1990 |
Idealmente, debería haber tenido una función que tome la expresión datetime como parámetro y pueda devolver la fecha en el formato deseado. En cambio, tuvimos que llamar a la función “datename” cuatro veces.
Aquí es que se puede ver que se tiene que donde las funciones definidas por el usuario son útiles. SQL Server permite a los usuarios crear funciones personalizadas de acuerdo con sus requisitos exactos.
Hay tres tipos de funciones definidas por el usuario en SQL Server:
- Funciones escalares (devuelve un valor único)
- Funciones de valor de tabla en línea (contiene una sola instrucción TSQL y devuelve un conjunto de tabla)
- Funciones de valor de tabla de múltiples declaraciones (contiene varias declaraciones TSQL y devuelve el conjunto de tablas)
Por ello es importante indicar que en este artículo veremos solo las funciones escalares definidas por el usuario.
Crear funciones definidas por el usuario
Vayamos a crear una función llamada “getFormattedDate”. Veremos que en esta función se aceptará un valor de tipo datetime y se devolverá un valor varchar que en realidad es nuestra fecha formateada.
Es importante que luego abra una nueva ventana de consulta y ejecute el siguiente script.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
USE schooldb GO CREATE FUNCTION getFormattedDate ( @DateValue AS DATETIME ) RETURNS VARCHAR(MAX) AS BEGIN RETURN DATENAME(DW, @DateValue)+ ', '+ DATENAME(DAY, @DateValue)+ ' '+ DATENAME(MONTH, @DateValue) +', '+ DATENAME(YEAR, @DateValue) END |
Aquí en este procedimiento el script comienza con el comando “USE schooldb” porque queremos crear esta función dentro de la base de datos “schooldb”. Seguidamente nosotros, escribimos una declaración GO para crear una nueva tanda de declaración por lotes. Esta es La declaración de función en el servidor SQL que siempre comienza con CREATE FUNCTION. Los parámetros son incluidos a la función y se especifican dentro del paréntesis de apertura y cierre que sigue al nombre de la función.
En el script anterior, creamos una función “getFormattedDate” el mismo que acepta un parámetro @DateValue de tipo DATETIME. Posteriormente se tiene que después de eso, se especifica el tipo función de retorno que es VARCHAR (MAX) en nuestro caso. Finalmente, el cuerpo de la función se define dentro de las instrucciones BEGIN y END. Aquí, en el cuerpo de la función “getFormattedDate”, nosotros estaremos creando la fecha con formato llamando a la función “Datename” repetidamente en varias ocasiones.
Finalmente, para que se considere que ahora si esta función realmente se ha creado, vaya al Explorador de objetos -> Bases de datos -> schooldb -> Programabilidad -> Funciones -> Funciones escaladas. Es en esta instancia debería ver su función recién creada. Si expande la función, verá el parámetro que toma la función. Por ello deberá echar un vistazo a la siguiente captura de pantalla como referencia.
Probando una función definida por el usuario
Es importante verificar que la función funciona correctamente. Para tal fin, abra una nueva ventana de consulta y ejecute el siguiente script.
|
1 2 3 4 5 6 7 8 |
USE schooldb SELECT name, [dbo].[getFormattedDate](DOB) FROM student |
En esta ocasión estamos usando una instrucción SELECT con el objetivo de recuperar los valores para el nombre y las columnas DOB de la tabla del alumno. Con objeto de formatear los valores de fecha y hora en la columna DOB, estamos utilizando la función “getFormattedDate” que acabamos de crear. Es importante mencionar que debe agregar el esquema de la base de datos antes de la función definida por el usuario. Si observa el script, hemos especificado el esquema en la siguiente línea:
|
1 2 3 |
[dbo].[getFormattedDate](DOB) |
DOB se utiliza como parámetro de la función “getFormatted” que retorna la fecha formateada. La consulta anterior recuperará los siguientes resultados:
| Name | (No column name) |
| Jolly | Monday, 12 June, 1989 |
| Jon | Saturday, 2 February, 1974 |
| Sara | Monday, 7 March, 1988 |
| Laura | Tuesday, 22 December, 1981 |
| Alan | Thursday, 29 July, 1993 |
| Kate | Thursday, 3 January, 1985 |
| Joseph | Friday, 9 April, 1982 |
| Mice | Friday, 16 August, 1974 |
| Wise | Wednesday, 11 November, 1987 |
| Elis | Sunday, 28 October, 1990 |
Ahí lo tenemos; Una simple función definida por el usuario.
Referencias:
- Comparación de procedimientos almacenados y funciones
- Comprender cuándo usar funciones definidas por el usuario en SQL Server
- Una lista completa de funciones incorporadas en SQL
Otros grandes artículos de Ben
| Comprender la caché del plan de consultas de SQL Server |
| Cómo SQL Server selecciona una víctima de punto muerto |
| Cómo usar las funciones integradas de SQL Server y crear funciones escalares definidas por el usuario |
Ver todas las entradas de Ben Richardson
- Cómo usar las funciones de Windows en SQL Server - December 16, 2019
- Como comprender el tipo de datos GUID en SQL Server - September 30, 2019
- Cómo utilizar las funciones integradas de SQL Server y crear funciones escalares definidas por el usuario - September 30, 2019