Hoy les traigo un tema que puede parecer triyado, pero sobre el que no está de más hacer un repaso, estoy hablando de las Funciones.
Definición:
Ante todo las funciones son un conjunto de sentencias que funcionan como una unidad lógica.
Pero si las funciones son un conjunto de sentencias que devuelve un conjunto de resultados, qué diferencia tienen con los Store Procedures y las Vistas?
Hablemos de algunas diferencias fundamentales que pueden justificar su uso:
- A diferencia de un procedimiento almacenado, las funciones pueden ser referenciadas en el From/Where/Having y Select de una consulta y utlizadas en el join de un consulta
- Deben retornar un valor mientras que los SP pueden ejecutar instrucciones sin devolver un parámetro de salida.
- Pueden tener sólo parámetros de entrada mientras los store procedures de entrada y salida
- Las Funciones pueden ser "llamadas" desde un Sp, mientras que los procedimientos no pueden ser llamados desde una Función.
- Las Funciones sólo permiten instrucciones de tipo select mientras que los SP permiten también instrucciones DML (Insert - Update - Delete)
- A diferencia de las vistas, pueden incluir todo tipo de sentencias lógicas como IF, mientras que las vistas solo pueden devolver el resultado de un select.
- En el caso de las funciones escalares pueden emplearse como campo en cualquier consulta
- Las Funciones no retienen su plan de ejecución cacheado, por lo tanto no es reutilizable, deben ser compiladas cada vez que son llamadas, por el contrario los SP son precompilados.
- Las Funciones no permiten la utilización de Try Catch por bloques mientras que los Stores Procedures si.
- Las Funciones no son trackeables a través del transaction manager mientras los sp si.
- Las Funciones no retienen su plan de ejecución cacheado, por lo tanto no es reutilizable, deben ser compiladas cada vez que son llamadas, por el contrario los SP son precompilados.
- Las Funciones no permiten la utilización de Try Catch por bloques mientras que los Stores Procedures si.
- Las Funciones no son trackeables a través del transaction manager mientras los sp si.
Tipos de Funciones
Yo les propongo pues que entendamos la utilidad de las funciones mediante ejemplos prácticos para cada tipo de función. Empecemos...
1) Funciones Escalares (Scalar-valued Functions)
1.1) Retornan un único valor
1.2) Pueden emplearse en cualquier consulta como un campo más de la misma
1.3) Admite parámetro de entrada y devuelve un único valor como salida.
1.4) Al ser "llamadas" se las debe llamar con su nombre completo dbo.NombreFunción
La siguiente función llamada "F_Vacaciones" calcula y devuelve las vacaciones que corresponden a cada empleado a partir de su fecha de ingreso a la empresa (recibida como parámetro) .
--
Creamos una tabla llamada “empleados” con el nombre y su fecha de ingreso --
CREATE TABLE [dbo].[empleados](
[nombre] [varchar](30) NOT NULL,
[fecha_ingreso] [datetime] NULL)
ON [PRIMARY]
-- Insertamos un empleado junto con su fecha
de ingreso -
INSERT INTO empleados
(nombre, fecha_ingreso)
values
('pablo', '2015-02-03 08:59:59')
--Creamos una función escalar
"F_Vacaciones" donde se calcula y devuelve
las vacaciones que corresponden a cada
empleado a partir de su fecha de ingreso
a la empresa(recibida como parámetro) .
Create Function F_Vacaciones
-- Parametros de
entrada con valor por default --
(@fechaingreso
datetime='2010-01-01 00:00:00')
-- tipo de datos
retornado --
Returns varchar(10)
as
-- cuerpo de la
funcion --
Begin
-- declaro
variable que va a devolver --
declare
@vacaciones varchar(10)
-- declaro
variables generales --
declare
@antiguedad int
-- calculo las
antiguedad de los empledos en base a la fecha de ingreso --
select @antiguedad =
DATEDIFF(mm, @fechaingreso, getdate())
-- Determino las vacaciones que corresponden en base a la antiguedad --
select @vacaciones =
case when @antiguedad
<= 12 then '7 dias'
when @antiguedad >
12 then '2 semanas'
when @antiguedad >
60 then '3 semanas'
when @antiguedad >
120 then
'4 semanas'
end;
-- retorno en @vacaciones el valor que corresponde al empleado --
return
@vacaciones
end;
-- Listamos la
tabla empledo utilizando como campo la función creada --
select
nombre,
fecha_ingreso,
dbo.F_Vacaciones(fecha_ingreso) as 'vacaciones'
from empleados;
2) Funciones de Tabla (Table-valued Functions)
1.1) Retornan una tabla
1.2) Pueden emplearse en lugar del from de una consulta
1.3) Pueden "joinearse" en cualquier consulta.
1.4) No necesitan ser llamadas por su nombre completo.
La siguiente función llamada F_Baratas devuelve una tabla con todas aquellas novelas cuyo precio sea menor al pasado como parámetro
.
-- Create table Libros --
create table libros
(id integer,
novela varchar (50),
autor varchar (35),
editorial varchar (40),
novela_tipo int,
precio decimal (9,3))
-- Insertamos dos registros --
insert into libros
(id,novela,autor,editorial, novela_tipo, precio)
values
(1,'Cuentos
de la Selva', 'Horacio
Quiroga', 'ATENEO',4, 9.43)
insert into libros
(id,novela,autor,editorial, novela_tipo, precio)
values
(2,'Relatos de un Náufrago', 'García Marquez', 'ATENEO', 1, 25.43)
Create Function f_baratas
-- Parámetro/s
de Entrada --
(@Valor decimal (6,2))
-- Nombre de la
Tabla de Retorno --
returns
@Libros table
-- Formato de la
Tabla de Retorno
(Id Integer,
Novela varchar(40),
Autor varchar(40)
Precio decimal (6,2))
as
-- Cuerpo de la
funcion --
Begin
-- Inserta Datos
en la Tabla --
insert
@Libros
select
id, novela,
autor, precio from
libros
where precio
< @Valor
-- retorna la
tabla --
return
end;
-- Llamado de la
Función --
select * from f_baratas (10);
-- Podemos hacer un
Join --
Select *, l.novela_tipo from
f_baratas(10) as b
left join libros as l
on b.id = l.id
-- O Podemos usarlo como vista --
Select id, autor
from f_baratas(10)
3) Funciones de Agregado (Aggregate Functions)
3.1) Son las típicas funciones que combinan varios valores y retornan un único valor
3.2) Ejemplos Típicos "count" "sum" "min" "avg" etc.
4) Funciones del Sistema (System Functions)
4.1) Son las distintas funciones preestablecidas por el sql server.
4.2) Hay de distintos tipos...4.2.1) De configuración, Select @@version;
4.2.2) De fecha y hora ... Datead ()
4.2.3) De metadatos (devuelven info sobre las bd y objetos)
4.2.4)etc. (recomiendo abrir el front end del sql server y ver cada una de Ellas)
Amigos, esto fue todo, espero haber sido claro y ante consultas saben que cuenta conmigo
Saludos desde Argentina,
Gustavo Herrera para "SQL Server para Todos"
