jueves, 25 de febrero de 2016

Funciones en SQL SERVER Parte III - Definida por el usuario (Tabla en línea)

Buenas tardes queridos lectores, vamos a continuar con los post de Transact SQL SERVER, viendo el tema de las funciones, en la primera parte, vimos las funciones agregadas al sistema, es decir las que trae el propio SQL SERVER precargadas, en la parte dos, vimos como crear funciones definidas por el usuario, es decir, creadas por nosotros mismos, en ésta tercera parte seguiremos viendo como crear funciones definidas por nosotros mismos, pero que nos retornen una tabla, como se habrán fijado las funciones que creamos en el post pasado solo tenían un valor de retorno, en está ocasión el retorno será una tabla completa. Pero comencemos con los ejemplos que así es como se aprende.

Recuerden que me estoy pasando en el libro que muestro en la imagen del Lic Manuel A. Torres Remon, desde Perú.





Ejemplo 1:
Script que implemente una función de tabla en línea que muestre los registros de la tabla PASAJERO.



IF OBJECT_ID('MISPASAJEROS') IS NOT NULL
BEGIN
       DROP FUNCTION MISPASAJEROS
END
GO

CREATE FUNCTION MISPASAJEROS()
RETURNS TABLE
AS
       RETURN (SELECT * FROM PASAJERO)

GO

En el Script se implementa la función MISPASAJEROS que tiene por misión devolver los registros de la tabla PASAJERO. La diferencia con la funció escalar es que cuando se retorna una tabla con la clausula RETURNS TABLE; devuelve más de un valor, eso quiere decir que la forma de ejecución es distinta a la función escalar, vista en el post anterior. Veamos la ejecución de la función:

SELECT * FROM DBO.MISPASAJEROS()
GO

La imagen siguiente muestra el resultado de la ejecución de la función tipo tabla


Como pueden ver queridos lectores la ejecución de la función es similar a ejecutar una simple con la sentencia SELECT.

Ejemplo 2:
Script que implemente una función de tabla en línea que muestre los registros de la tabla PASAJERO dependiendo del país de procedencia



IF OBJECT_ID('PASAJEROSXPAIS') IS NOT NULL
BEGIN
       DROP FUNCTION DBO.PASAJEROSXPAIS
END
GO

CREATE FUNCTION PASAJEROSXPAIS(@PAIS VARCHAR(30))
RETURNS TABLE
AS
RETURN (SELECT PAS.IDPASAJERO, PAS.NOMBRES, PAI.PAIS, PAS.TELEFONO, PAS.EMAIL
                           FROM PASAJERO PAS
                           INNER JOIN PAIS PAI ON PAS.IDPAIS=PAI.IDPAIS
                           WHERE PAI.IDPAIS=@PAIS)
GO

En el Script se implementa la función PASAJEROSXPAIS que tiene por misión devolver los registros de los pasajeros que tienen como criterio el nombre del país.

En este caso se ha incorporado un parámetro a la función y este valor será el que condicione los registros de la tabla PASAJERO unido a la tabla PAIS.

El script siguiente muestra la forma de ejecutar una función de tabla en línea con parámetro:

SELECT * FROM DBO.PASAJEROSXPAIS('MEXICO')

El resultado de la ejecución de la función es el siguiente:


Si cambiamos el parámetro y buscamos los pasajeros de Perú, sería como sigue:

SELECT * FROM DBO.PASAJEROSXPAIS('PERU')

El resultado de la consulta sería como sigue:


Ejemplo 3:
Script que implemente una función de tabla en línea que muestre los montos acumulados de los pagos realizados por un determinado pasajero. Para esto implemente la función que permita mostrar el país, otra función que permita acumular los pagos y la función tabla en línea que permita mostrar los pasajeros y su monto acumulado.

--1
IF OBJECT_ID('MUESTRAPAIS') IS NOT NULL
BEGIN
       DROP FUNCTION DBO.MUESTRAPAIS
END
GO

IF OBJECT_ID('ACUMULADOPAGO') IS NOT NULL
BEGIN
       DROP FUNCTION DBO.ACUMULADOPAGO
END
GO

IF OBJECT_ID('PAGOXPASAJEROS') IS NOT NULL
BEGIN
       DROP FUNCTION DBO.PAGOXPASAJEROS
END
GO

--2
CREATE FUNCTION MUESTRAPAIS(@ID CHAR(4))
RETURNS VARCHAR(30)
AS
BEGIN
       RETURN (SELECT PA.PAIS
             FROM PAIS PA
             WHERE PA.IDPAIS=@ID)
END
GO

--2.
CREATE FUNCTION ACUMULADOPAGO(@ID CHAR(5))
RETURNS DECIMAL(10,2)
AS
BEGIN
       RETURN (SELECT SUM(MONTO)
             FROM PAGO PA
             WHERE PA.IDPASAJERO=@ID)
END
GO

--3.
CREATE FUNCTION PAGOXPASAJEROS()
RETURNS TABLE
AS
       RETURN(
             SELECT PAS.IDPASAJERO,PAS.NOMBRES,
                           DBO.MUESTRAPAIS(PAS.IDPAIS) AS [PAIS],
                           DBO.ACUMULADOPAGO(PAS.IDPASAJERO) AS [ACUMULADO PAGO]
                    FROM PASAJERO PAS                
       )
      

En el punto uno se implementan tres funciones que permitirán verificar que las funciones a ejecutar no se encuentren en la base de datos, en caso contrario serán eliminadas de la base.

En el punto dos se implementa la función escalar MUESTRAPAIS que tiene por misión devolver el nombre del país según su código obtenido desde la tabla PASAJERO

CREATE FUNCTION MUESTRAPAIS(@ID CHAR(4))
RETURNS VARCHAR(30)
AS
BEGIN
       RETURN (SELECT PA.PAIS FROM PAIS PA
                                               WHERE PA.IDPAIS=@ID)
END
GO

Como notará en el script no se usó la variable local para guardar el nombre del país, sino que el valor resultante de la función se está enviando directamente por la clausula RETURN.

En el punto tres se implementa la función escalar ACUMULADOPAGO que tiene como parámetro el @ID del pasajero que permitirá condicionar la suma de los montos de acuerdo a dicho valor

CREATE FUNCTION ACUMULADOPAGO(@ID CHAR(5))
RETURNS DECIMAL(10,2)
AS
BEGIN
       RETURN (SELECT SUM(MONTO)
                    FROM PAGO PA
                    WHERE PA.IDPASAJERO=@ID)
END
GO

En el punto cuatro es se implementa la tabla en línea PAGOXPASAJEROS sin parámetros, cuya misión es devolver los registros desde la tabla pasajero mostrando el nombre del país obtenido desde la función MUESTRAPAIS y el acuulado de pago realizados desde la función ACUMULADOPAGO

CREATE FUNCTION PAGOXPASAJEROS()
RETURNS TABLE
AS
       RETURN(
                    SELECT PAS.IDPASAJERO, PAS.NOMBRES, DBO.MUESTRAPAIS(PAS.IDPAIS) AS [PAIS],
                                  DBO.ACUMULADOPAGO(PAS.IDPASAJERO) AS [ACUMULADOPAGO]
                    FROM PASAJERO PAS
                    INNER JOIN PAIS PAI ON PAS.IDPAIS=PAI.PAIS
                    )
GO

La idea principal de scipt es no usar JOIN para la obtención de valores que se encuentran fuera de alcance de la tabla PASAJERO

Para ejecutar la función PAGOXPASAJEROS deberá ejecutar el script:

--Ejecutar
SELECT * FROM DBO.PAGOXPASAJEROS();

El resultado de la ejecución se muestra a continuación:



Ejemplo 4:
Script que implemente una función tabla multisentencia que muestre los registros de la tabla AEROPUERTO adicionando el nombre del país.


--1
IF OBJECT_ID('MISAEROPUERTOS') IS NOT NULL
BEGIN
       DROP FUNCTION DBO.MISAEROPUERTOS
END
GO
--2
CREATE FUNCTION MISAEROPUERTOS()
RETURNS @TABLA TABLE(
       IDA CHAR(4),
       NOM VARCHAR(40),
       PAI VARCHAR(40)
)
AS
BEGIN
--3
       INSERT INTO @TABLA
             SELECT AER.IDAERO,AER.NOMBRE,PAI.PAIS
                    FROM AEROPUERTO AER
                    JOIN PAIS PAI ON AER.IDPAIS=PAI.IDPAIS
       RETURN
END
GO



En el punto uno se implementa el script que permite verificar si la función MISAEROPUERTOS ya existía en la base de datos en caso sea así eliminarlo.

En el punto dos se implementa el script de la tabla multisentencia en la cual se tiene que indicar que le retorno es de tipo tabla RETURNS @TABLA TABLE y definir los campos de dicha tabla, éstos deben ser proporcional a los campos involucrados en la consulta de inserción.

En el punto tres se realiza la inserción de registros hacia la variable de tipo table @TABLA proveniente desde la tabla AEROPUERTO unida con PAIS. No olvide que por ser una función de tabla multisentencia no tiene valor de salida entonces el operador RETURN se imprime solo.

Para la ejecución de la función se debe colocar el siguiente script:

--PRUEBA
SELECT *
       FROM MISAEROPUERTOS()
GO

La imagen siguiente muestra el resultado de la ejecución de la consulta que involucra a la función MISAEROPUERTOS,


Como notará en la imagen del encabezado de la consulta resultante no es lo esperado, esto se debe a que las variables declaradas como columnas de la variable de tipo table, así lo definió; entonces podemos mejorar el aspecto si colocamos el siguiente script:

SELECT IDA AS CODIGO, NOM AS AEROPUERTO, PAI AS PAIS
       FROM MISAEROPUERTOS()
GO

La imagen siguiente muestra el resultado de la ejecución:


Ejemplo 5:
Script que implemente una función de Tabla multisentencia que muestre los registros de tres distintas tablas en una misma función, para esto se deberá tomar dos columnas de cada tabla y asignarlos a una variable de tipo table para poder mostrarlo.


--1
IF OBJECT_ID('MISAEROPUERTOS') IS NOT NULL
BEGIN
       DROP FUNCTION DBO.MISAEROPUERTOS
END
GO
--2
IF OBJECT_ID('FN_VISTATABLA') IS NOT NULL
BEGIN
       DROP FUNCTION DBO.FN_VISTATABLA
END
GO

CREATE FUNCTION FN_VISTATABLA(@TABLA VARCHAR(20))
RETURNS @MATRIZ TABLE (CODIGO CHAR(10),DESCRIPCION VARCHAR(50))
AS
BEGIN
       IF @TABLA='PAIS'
             INSERT INTO @MATRIZ
                    SELECT IDPAIS,PAIS
                           FROM PAIS
             ELSE IF @TABLA='PASAJERO'
                    INSERT INTO @MATRIZ
                    SELECT IDPASAJERO,NOMBRES
                           FROM PASAJERO
              ELSE IF @TABLA='AEROPUERTO'
                    INSERT INTO @MATRIZ
                    SELECT IDAERO,NOMBRE
                           FROM AEROPUERTO
       RETURN
END 
GO

En el script se implementa la función FN_VISTATABLA con respecto al nombre de la función no es necesario especificarlo con la palabra FN por delante. Se le adiciona un parámetro a la función de tabla multisentencia para que identifique a que tabla necesita mostrar.

Se declara la variable de retorno tipo table llamado @MATRIZ, este tiene por misión dos columnas de cualquiera de las tablas involucradas en la consulta. Por este motivo, también es que se declara la columna CODIGO y DESCIPCION como dos campos genéricos a todas las tablas.

Luego se condiciona el valor de la variable @TABLA, buscando así el identificador de la tabla involucrada cuando la condición devuelve TRUE, entonces se insertara dentro de la variable de tipo table @MATRIZ dos columnas mediante la sentencia SELECT. En este caso al encontrar que la variable es PAIS entonces insertará los valores de la columna PAIS en dicha variable y así sucederá con las demás condiciones, hay que tener en cuenta que para este caso las columnas son del mismo tipo, en caso que no sea así, use CAST o CONVERT para sincronizar las columnas y no tener errores de tipo desde el motor de base de datos.

El siguiente script permite ejecutar la consulta según el país ingresado:

SELECT * FROM FN_VISTATABLA('PAIS')
SELECT * FROM FN_VISTATABLA('PASAJERO')
SELECT * FROM FN_VISTATABLA('AEROPUERTO')
GO

En cualquiera de los casos el resultado sería como lo muestra la siguiente imagen:


Hasta aquí el post queridos lectores, espero que haya sido de utilidad y agrado, espero compartan, comenten, den +1 clic en algún anuncio que les interese. En el siguiente post veremos otro tema básico y por demás interesante, que son los procedimientos almacenados, tanto de sistema como de usuario. 

Un abrazo!!



Banderas de Visitantes

Flag Counter