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!!
No hay comentarios.:
Publicar un comentario