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ú.

• Funciones del Sistema (Parte I) 
• Funciones definidas por el usuario (Parte II) 
• Funciones de tabla en línea (Parte III)

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!!

Funciones Parte II