Funciones en SQL Server
En el artículo anterior hablamos sobres los Procedimientos Almacenados importantísimos al momento de trabajar de lleno con base de datos y comunicarlas con una aplicación. Pero no solo los procedimientos almacenados toman un rol importante, también existen las Funciones. En este artículo hablaremos que son las funciones, tipos de funciones que podemos crear y para que las podemos usar. tratare de ser los más exacto posible explicando este tema ya que al igual que los procedimientos almacenados son temas sumamente amplios dada su utilidad.
Defincion de una funcion y sintaxis
Una función en SQL son un conjunto de
sentencias las cuales operan como una sola unidad lógica. En la estructura de
una función se define un nombre y su vez
nos va a retornar parámetros de salida y en dependencia de la operación podemos
definir parámetros de entrada. SQL Server Por Default nos muestra Funciones
propias del sistema las cuales no pueden ser modificadas, nosotros vamos a
estudiar las funciones definidas por el usuario, las cuales podremos crear, modificar
y eliminar dada las circunstancias requeridas.
Crear un funcion:
CREATE FUNCTION NOMBREFUNCION ([PARAMETRO] [TIPODEDATO]) RETURN TIPODATO AS BEGIN (INSTRUCCION,OPERACIONES, ETC..) RETURN(ACA SE RETORNA EL VALOR DEL CAMPO CON EL MISMO VALOR DE LA FUNCION) END
Modificar una función:
ALTER FUNCTION NOMBREFUNCION ([PARAMETRO] [TIPODEDATO]) RETURN TIPODATO AS BEGIN (INSTRUCCION,OPERACIONES, ETC..) RETURN(ACA SE RETORNA EL VALOR DEL CAMPO CON EL MISMO VALOR DE LA FUNCION) END
Eliminar una función:
DROP FUNCTION NOMBREFUNCION
Explicamos la sintaxis de una función
CREATE FUNCTION: Con dicha instrucción estamos indicando que es una función la que crearemos.
NOMBREFUNCION: Especificamos el nombre que tendrá la función.
([PARÁMETRO] [TIPODATO]): definimos el nombre del parámetro y el tipo de datos a usar el cual puede ser, cadena, entero, de tipo fecha etc..
RETURN TIPODATO: Acá se declara el tipo de dato de la función.
AS: Indicamos el inicio de una instrucción.
BEGIN: Indicamos que vamos a iniciar un bloque de sentencias.
(INSTRUCCIÓN,OPERACIONES, ETC..): Indicamos la sentencias y operaciones a realizar en la función.
RETURN(): acá indicamos el retorno del valor de la variable de la función.
END: Indicamos el final del bloque de instrucciones dentro de la función.
Como nota importante debemos mencionar las siguientes de que no podemos hacer con una función:
- En una función no podemos ejecutar procedimientos almacenados.
- No podemos hacer unos de la sentencia INTO para insertar registros en otra tabla.
- No se pueden anidar más de 30 funciones al realizar una ejecución.
- No podemos formatear un resultado arrojado en formato XML.
- En las funciones solo están permitidas las tablas temporales con variables.
Funciones Escalares
Una funciona escalar devuelve un único
valor. En el proceso de creación de una función escalar debemos indicar el
valor que nos va retornar la función.
La sintaxis de un función escalar es la siguiente:
CREATE FUNCTION NOMBREFUNCION ([PARAMETRO] [TIPODEDATO]) RETURN TIPODATO AS BEGIN (INSTRUCCION,OPERACION) RETURN VALOR END
A continuación vamos a crear un ejemplo de función donde obtengamos el promedio de notas de un alumno en base a 5 clases vistas.
CREATE FUNCTION ObtenerPromedio
(@Nota1 decimal(4,2),
@Nota2 decimal(4,2),
@Nota3 decimal(4,2),
@Nota4 decimal(4,2),
@Nota5 decimal(4,2)
)
RETURNS decimal(6,2)
AS
BEGIN
DECLARE @Resultado decimal(6,2)
SET @Resultado =(@Nota1+@Nota2+@Nota3+@Nota4+@Nota5)/5
RETURN @Resultado
END Ahora esta función vamos a crearla en SQL Management Studio :
--Usando PRINT PRINT dbo.NOMBREFUNCION(PARAMETROS) --Usando SELECT SELECT dbo.NOMBREFUNCION(PARAMETROS)
Ahora ejecutemos la función utilizando primeramente PRINT para ver el resultado:
Si utilizamos PRINT podemos observar que el mensaje obtenido lo va mostrar a manera de un mensaje impreso en pantalla y el resultado del promedio es 79. Ahora obtengamos el resultado de la misma función utilizando SELECT.
Con SELECT Podemos observar que el resultado es mostrado a manera de una columna y podemos personalizarlo agregándole un nombre a dicha columna.
Ahora si queremos modificar la función ya existente para eso debemos hacer uso de la Instrucción ALTER la cual nos va permitir poder modificar la función ya creada. Hagamos el ejemplo en base a la función "ObtenerPromedio" agreguemos una nota más a la función y obtengamos el nuevo promedio de notas del alumno.
ALTER FUNCTION ObtenerPromedio
(@Nota1 decimal(4,2),
@Nota2 decimal(4,2),
@Nota3 decimal(4,2),
@Nota4 decimal(4,2),
@Nota5 decimal(4,2),
@Nota6 decimal(4,2)
)
RETURNS decimal(6,2)
AS
BEGIN
DECLARE @Resultado decimal(6,2)
SET @Resultado =(@Nota1+@Nota2+@Nota3+@Nota4+@Nota5+@Nota6)/6
RETURN @Resultado
ENDLo que hemos hecho es agregar una nota del alumno más como parámetro y así obtener el nuevo promedio ya que al sumar una nueva nota el promedio obtenido ya no será 79 . ahora ejecutemos la función en SQL Server Management Studio.
Hemos modificado la función agregando una nota más eso significa que ahora nos va a mostrar un nuevo promedio. Ejecutemos la función mediante un SELECT para ver el nuevo resultado.
El resultado obtenido ahora es 76 esto debido a que la función la hemos modificado al agregar una nota más por ende el promedio calculado será otro.
Para eliminar la función "ObtenerPromedio" debemos hacer unos de la instrucción DROP FUNCTION.
DROP FUNCTION ObtenerPromedio
Funciones con valor de Tabla
CREATE FUNCTION NOMBREFUNCION
(@PARAMETRO TIPODATO)
returns @NOMBRETABLARETORNO TABLE-- nombre de la tabla
--formato de la tabla
(CAMPO1 TIPODATO,
CAMPO2 TIPODATO,
CAMPO3 TIPODATO
)
AS
BEGIN
INSERT @NOMBRETABLARETORNO
SELECT CAMPOS
FROM TABLA
WHERE CAMPO OPERADOR @PARAMETRO
RETURN
END A continuación vamos a realizar un ejemplo crearemos una función para mostrar los productos cuyo precio es más bajo.
CREATE FUNCTION FProductoBajoPrecio
(@Precio decimal(6,2))
returns @precios table
(ProductoID nvarchar(50),
NombreProducto varchar(200),
Presentacion varchar(100),
PrecioVenta decimal(18,2)
)
as
begin
insert @precios
select ProductoID,NombreProducto,Presentacion,PrecioVenta
from SivProductos
where PrecioVenta < @Precio
return
end A continuación vamos a crear la función en SQL Server Management Studio:
Lo que hacemos con esta función es pasarle un parámetro en base a un rango de precio en el ejemplo hemos puesto 10 lo que significa que la función nos va a mostrar los registros de productos cuyo precio de venta sea menor al del rango establecido.
También a como mencionáramos al inicio del artículo también podemos anidar funciones pero estas no pueden exceder en más de 30 funciones en ejecución. veamos un ejemplo
Para el siguiente ejemplo vamos a crear una función que nos retorne el nombre de una persona en mayúscula.
CREATE FUNCTION NOMBREMAYUSCULA ( @NOMBRE VARCHAR(50), @APELLIDO VARCHAR(50) ) RETURNS VARCHAR(100) AS BEGIN RETURN (UPPER(@APELLIDO) + ', ' + UPPER(@NOMBRE)) END GO
Ahora vamos a crear una tabla llamada Persona posterior a esto vamos a insertar algunos registros
CREATE TABLE DBPersonas(
Codigo int PRIMARY KEY IDENTITY(1,1) NOT NULL,
Nombre varchar (100) NOT NULL,
direccion varchar(100) NULL,
Edad int NULL
)
INSERT INTO DBPersonas VALUES ('Mario','calle la calzada',25)
INSERT INTO DBPersonas VALUES ('Lucia','km 25 carretera sur',18)
INSERT INTO DBPersonas VALUES ('Kevin','Ciudad Doral',20) Ahora vamos a crear una función que nos muestre los registros de personas pero que su nombre y apellido sea mostrado en mayúscula veamos la función.
CREATE FUNCTION MOSTRARPERSONAS
(@CODIGO INT)
RETURNS TABLE
AS
RETURN (SELECT dbo.NOMBREMAYUSCULA(Apellido,Nombre) Nombres,Direccion,Edad
FROM DBPersonas WHERE Codigo = @CODIGO)
GO Ahora el siguiente código lo pegaremos en SQL Server Management Studio para crear la función:
Funciones Integradas
- SUM(): Nos permite Obtener la suma de una columna.
- AVG(): Obtenemos el promedio de una columna.
- COUNT(): Realiza el conteo de registros de una columna.
- MAX(): Obtiene el registros máximo de una columna.
- MIN():Obtenemos el registro mínimo de una columna.
- DATETIME(): Mostramos la fecha y hora actual en sistema SQL Server.
- CONCAT(): Realizamos la concatenación de cadena de caracteres.
- LOWER(): Mostramos el texto de los registros en Minúscula.
- UPPER(): Mostramos el texto de registros en Mayúscula.
0 Comentarios