Las funciones en SQL Server son los objetos de la base de datos que contienen un conjunto de sentencias SQL para realizar una tarea específica . Una función acepta parámetros de entrada, realiza acciones y luego devuelve el resultado. Debemos tener en cuenta que las funciones siempre devuelven un valor único o una tabla. El objetivo principal de las funciones es replicar fácilmente la tarea común. Podemos crear funciones una vez y usarlas en múltiples ubicaciones según nuestras necesidades. SQL Server no permite el uso de funciones para insertar, eliminar o actualizar registros en las tablas de la base de datos.
Las siguientes son las reglas para crear funciones de SQL Server:
- Una función debe tener un nombre y el nombre no puede comenzar con un carácter especial como @, $, # u otros caracteres similares.
- Las declaraciones SELECT son las únicas que operan con funciones.
- Podemos usar una función en cualquier lugar como AVG, COUNT, SUM, MIN, DATE y otras funciones con la consulta SELECT en SQL.
- Cada vez que se llama a una función, se compila.
- Las funciones deben devolver un valor o resultado.
- Las funciones utilizan sólo parámetros de entrada.
- No podemos usar declaraciones TRY y CATCH en funciones.
Tipos de funciones
SQL Server clasifica las funciones en dos tipos:
- Funciones del sistema
- Funciones definidas por el usuario
Describamos ambos tipos en detalle.
Funciones del sistema
Las funciones definidas por el sistema se conocen como funciones del sistema. En otras palabras, todos los funciones integradas Las funciones admitidas por el servidor se denominan funciones del sistema. Las funciones integradas nos ahorran tiempo mientras realizamos la tarea específica. Este tipo de funciones suelen funcionar con la instrucción SQL SELECT para calcular valores y manipular datos.
Aquí está la lista de algunas funciones del sistema utilizadas en SQL Server:
- Funciones de cadena (LEN, SUBSTRING, REPLACE, CONCAT, TRIM)
- Funciones de fecha y hora (datetime, datetime2, smalldatetime)
- Funciones agregadas (COUNT, MAX, MIN, SUM, AVG)
- Funciones Matemáticas (ABS, POTENCIA, PI, EXP, LOG)
- Funciones de clasificación (RANK, DENSE_RANK, ROW_NUMBER, NTILE)
La siguiente imagen muestra todas las funciones de base de datos integradas utilizadas en SQL Server:
Funciones definidas por el usuario
Funciones que son creado por el usuario en la base de datos del sistema o en una base de datos definida por el usuario se conocen como funciones definidas por el usuario. Las funciones UDF aceptan parámetros, realizan acciones y devuelven el resultado. Estas funciones nos ayudan a simplificar nuestro desarrollo al encapsular una lógica empresarial compleja y hacerla disponible para su reutilización en cualquier lugar según las necesidades. Las funciones definidas por el usuario hacen que el código necesario para consultar datos sea mucho más fácil de escribir. También mejoran la legibilidad y la funcionalidad de las consultas, además de permitir que otros usuarios repliquen los mismos procedimientos.
SQL Server clasifica las funciones definidas por el usuario principalmente en dos tipos:
- Funciones escalares
- Funciones con valores de tabla
Aquí están las descripciones de estas funciones UDF.
Funciones escalares
Función escalar en SQL Server siempre acepta parámetros, ya sean únicos o múltiples y devuelve un valor único . Las funciones escalares son útiles en la simplificación de nuestro código. Supongamos que tenemos un cálculo complejo que aparece en varias consultas. En tal caso, podemos crear una función escalar que encapsule la fórmula y la use en cada consulta en lugar de en cada consulta.
La siguiente es la sintaxis que ilustra la creación de una función escalar en SQL Server:
CREATE FUNCTION schema_name.function_name (parameter_list) RETURNS data_type AS BEGIN statements RETURN value END
Los parámetros de sintaxis anteriores se describen a continuación:
Primero definiremos el nombre de la función después del CREAR FUNCIÓN palabras clave. El nombre del esquema es opcional. Si no definimos el nombre del esquema, SQL Server usa el esquema predeterminado dbo . A continuación definiremos la lista de parámetros encerrado entre paréntesis. En tercer lugar, escribiremos las declaraciones para la función y luego, en el DEVOLUCIONES declaración, defina el tipo de datos del valor de retorno. Finalmente, agregamos la declaración RETURN para devolver un valor dentro del cuerpo de la función.
Ejemplo
Este ejemplo creará una función para calcular las ventas netas según la cantidad, el precio y el valor del descuento:
CREATE FUNCTION udfNet_Sales( @quantity INT, @price DEC(10,2), @discount DEC(3,2) ) RETURNS DEC(10,2) AS BEGIN RETURN @quantity * @price * (1 - @discount); END;
Ahora podemos usar esta función para calcular las ventas netas de cualquier pedido de venta en la tabla definida.
La siguiente imagen muestra donde podemos encontrar las funciones escalares:
Podemos llamar a las funciones escalares de la misma manera que la función integrada en SQL Server. Por ejemplo, podemos llamar a la función udfNet_Sales anterior como se muestra a continuación:
SELECT dbo.udfNet_Sales(25, 500, 0.2) AS net_sales;
Al ejecutar esta función se devolverán las ventas netas:
SQL Server también nos permite para modificar la función escalar usando ALTER palabra clave. Aquí está la sintaxis para hacer esto:
ALTER FUNCTION schema_name.function_name (parameter_list) RETURNS data_type AS BEGIN statements RETURN value END
Podemos usar la siguiente declaración para eliminar la función escalar de la base de datos de SQL Server:
DROP FUNCTION [schema_name.]function_name;
Funciones con valores de tabla
Las funciones con valores de tabla en SQL Server son las función definida por el usuario que devuelve datos de tipo tabla. Dado que esta función el tipo de retorno es una tabla , podemos usarlo de la misma manera que usamos una tabla.
Podemos clasificar la función con valores de tabla en dos tipos:
1. Funciones de valores de tabla en línea
Esta función UDF devuelve una variable de tabla basada en la acción realizada por la función. Se debe utilizar una única instrucción SELECT para determinar el valor de la variable de la tabla.
Ejemplo
El siguiente ejemplo creará una función de valores de tabla y recuperará los datos de la tabla de empleados:
--It creates a table-valued function to get employees CREATE FUNCTION fudf_GetEmployee() RETURNS TABLE AS RETURN (SELECT * FROM Employee)
En esta sintaxis, el TABLA DE DEVOLUCIONES especifica que la función devolverá una tabla. ya que no hay COMIENZO...FIN declaración, simplemente consulta datos de la tabla de empleados. Además, si no tiene ningún parámetro, se llamará directamente.
responder en java
Podemos llamar a las funciones con valores de tabla usando el DE cláusula de la SELECCIONAR consulta. Por ejemplo , podemos llamar a lo anterior udf_GetEmployee funcionar de la siguiente manera:
SELECT * FROM udf_GetEmployee();
La ejecución de esta función devolverá el siguiente resultado:
SQL Server también nos permite modificar las funciones con valores de tabla usando la palabra clave ALTER en lugar de la palabra clave CREATE. El resto del guión es el mismo.
Funciones con valores de tabla de declaraciones múltiples (MSTVF)
Esta función UDF devuelve una variable de tabla en función de la acción realizada por la función. Puede contener declaraciones únicas o múltiples para producir el resultado, y también es una función que devuelve el resultado de múltiples declaraciones en forma tabular. Es útil porque podemos ejecutar varias declaraciones en esta función y obtener resultados agregados en la tabla devuelta. Podemos definir esta función usando una variable de tabla como valor de retorno. Dentro de la función, ejecutamos múltiples consultas e insertamos datos en esta variable de tabla.
El siguiente ejemplo crea un nombre de función. 'MULTIVALORADO' que devuelve el ' @Empleado' mesa. Contiene tres campos llamados id, emp_name y salario del 'Empleado' tabla usando la instrucción INSERT y luego usa la instrucción UPDATE para actualizar el nombre del empleado.
CREATE FUNCTION MULTIVALUED() RETURNS @Employee TABLE (id INT, emp_name VARCHAR(50), salary INT) AS BEGIN INSERT INTO @Employee SELECT E.id, E.emp_name, E.salary FROM Employee E; UPDATE @Employee SET emp_name = 'Graeme Smith' WHERE id = 3; RETURN END
Podemos llamar a las funciones con valores de tabla de declaraciones múltiples utilizando la cláusula FROM de la consulta SELECT. Por ejemplo , podemos llamar a la función anterior de la siguiente manera:
SELECT * FROM MULTIVALUED();
La ejecución de esta función devolverá el siguiente resultado:
¿Cuándo se deben utilizar funciones con valores de tabla?
Las funciones con valores de tabla se utilizan comúnmente como vistas parametrizadas. Las funciones con valores de tabla son más flexibles que los procedimientos almacenados porque se pueden utilizar en cualquier lugar donde se utilicen tablas.
Conclusión
Este artículo explicará una descripción completa de las funciones utilizadas en SQL Server. Aquí hemos aprendido principalmente dos tipos de funciones utilizadas en SQL Server: funciones de sistema y funciones definidas por el usuario.