Buscar este blog

martes, 10 de diciembre de 2013

FUNCIONES SQL SERVER - Utilidades y Diferencias con Stores Procedures y Vistas --

Amigos,

    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.

  
   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"