miércoles, 24 de febrero de 2016

Funciones en SQL Parte II - Definidas por el usuario

Funciones definidas por el usuario

Buenos días queridos lectores, el día de hoy hablaremos de las funciones definidas por el usuario, es decir, las funciones que nosotros creamos, además de las que ya vimos que vienen incluidas en el motor de base de datos.

Las funciones definidas por el usuario no se pueden utilizar para realizar acciones que modifican el estado de la base de datos. Las funciones definidas por el usuario, como las funciones de sistema, se pueden llamar desde una consulta.
Como siempre queridos lectores es más fácil aprender haciéndolo que leyendo, así que veremos unos ejemplos y con ellos quedará comprendida la sintaxis ya verán.


Ejemplo 1:
Script que permita implementar una función que devuelva el promedio de dos números ingresados por el usuario

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

CREATE FUNCTION MIPROMEDIO(@VALOR1 INT, @VALOR2 INT)
RETURNS DECIMAL(10,2)
AS
BEGIN
       DECLARE @RESULTADO DECIMAL(10,2)
       SET @RESULTADO = (@VALOR1+@VALOR2)/2.0
       RETURN @RESULTADO
END
GO

El script lo primero que hace es evaluar si la función llamada "MIPROMEDIO" existe, si a función existe entonces la borra para después sobreescribirla, si no se realiza la validación entonces tendremos que borrar la función para volver a crearla. Esto lo hace mediante el bloque:

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

En el script se implementa la funcion MIPROMEDIO con dos parámetros de tipo entero, es decir, se deberán ingresar dos valores para calcular el promedio desde la función.

CREATE FUNCTION MIPROMEDIO(@VALOR1 INT, @VALOR2 INT)

Luego se define el tipo de salida de esta función, la salida es DECIMAL, ya que todo promedio siempre resultara una expresión decimal que podrá contener 10 dígitos a la izquierda y dos decimales

RETURNS DECIMAL(10,2)

Seguidamente se declara la variable @RESULTADO que tendrá el valor resultante de la expresión y se implementa la expresión del promedio, ya que tener en cuenta que se debe dividir entre 2.0 y no 2, ya que de eso dependerán los decimales.

DECLARE @RESULTADO DECIMAL(10,2)
SET @RESULTADO = (@VALOR1+@VALOR2)/2.0

Finalmente, se envía el resultado de la función por medio del operador RETURN. Así se obtendra el valor resultante de la función desde la invocación con Transact SQL o un lenguaje de programación.

RETURN @RESULTADO

Ahora tomaremos especial interés en el uso de la función de dos formas:

Primera forma: SELECT

SELECT DBO.MIPROMEDIO(12,13) AS [PROMEDIO]

En el script se usa la sentencia SELECT como medio de invocación a la función MIPROMEDIO, no olvide que para llamar a la función se debe especificar el nombre del propietario en este caso será DBO. Los parámetros 12 y 13 son los parametros requeridos en la función, la función solicita dos parámetros enteros, y 12 y 13 son los que le pasamos. La imagen siguiente muestra el resultado de la prueba con SELECT


Segunda forma: PRINT

DECLARE @N1 INT = 12, @N2 INT = 13
PRINT 'EL PROMEDIO DE: ' +CAST(@N1 AS CHAR(2)) + ' Y ' +CAST(@N2 AS CHAR(2))+ ' ES: '
+ CAST(DBO.MIPROMEDIO(@N1,@N2) AS CHAR(10))
GO

En el script se declaran dos variables de tipo INT que permitirán inicializar los valores que se enviarán a la función. No se olvide que la función PRINT solo podrá imprimir valores de tipo carácter; de otra forma emitirá error, por eso se aplica la función CAST a todo valor distinto a carácter. La siguiente imagen muestra el resultado de la prueba con PRINT.


Ejemplo 2:
Script que permita implementar una función que devuelva el nombre de un mes en letras y en inglés según una fecha ingresada por el usuario

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

CREATE FUNCTION MESLETRAS (@FECHA DATE)
RETURNS VARCHAR(20)
AS
BEGIN
       DECLARE @NOMBRE VARCHAR(20)
       SET @NOMBRE =
             CASE DATENAME(MONTH, @FECHA)
                    WHEN 'ENERO' THEN 'JANUARY'
                    WHEN 'FEBRERO' THEN 'FEBRUARY'
                    WHEN 'MARZO' THEN 'MARCH'
                    WHEN 'ABRIL' THEN 'APRIL'
                    WHEN 'MAYO' THEN 'MAY'
                    WHEN 'JUNIO' THEN 'JUNE'
                    WHEN 'JULIO' THEN 'JULY'
                    WHEN 'AGOSTO' THEN 'AUGUST'
                    WHEN 'SEPTIEMBRE' THEN 'SEPTEMBER'
                    WHEN 'OCTUBRE' THEN 'OCTOBER'
                    WHEN 'NOVIEMBRE' THEN 'NOVEMBER'
                    WHEN 'DICIEMBRE' THEN 'DECEMBER'
             END
       RETURN @NOMBRE
END
GO

En el script la función MESLETRAS tiene como parámetro una fecha que será enviada por el usuario y la función obtendrá el mes para remitirla con el nombre en inglés.

En esta función se hizo necesario el uso de la estructura múltiple CASE ya que son 12 comparaciones de un mismo valor que es el número de mes obtenido de la fecha ingresada como parámetro.

La función DATENAME permite devolver el nombre del mes desde una fecha predeterminada, pero lo devuelve en español y nosotros para el ejemplo lo necesitamos en inglés.

Mostraremos tres formas de ejecutar la función:

Primera forma: SELECT

SELECT DBO.MESLETRAS(GETDATE()) AS [MES ACTUAL]
GO



Segunda forma: PRINT

DECLARE @FEC DATE=GETDATE()
PRINT  'LA FECHA '+CAST(@FEC AS VARCHAR(15))+' TIENE COMO MES ' +DBO.MESLETRAS(GETDATE())
GO


Tercera forma: Usando la columna de una tabla

SELECT PAG.IDRESERVA AS [N° DE RESERVA], PAS.NOMBRES AS [PASAJERO],
             CAST(DAY(FECHA) AS CHAR(2)) + 'DE ' + DBO.MESLETRAS(FECHA)+' DEL '+
             CAST(YEAR(FECHA) AS CHAR(4)) AS [FECHA], PAG.MONTO AS [MONTO DE PAGO]
             FROM PAGO PAG
             INNER JOIN PASAJERO PAS ON PAG.IDPASAJERO=PAS.IDPASAJERO
GO

En el script se implemente la columna FECHA la cual se compone del día capturado con la función DAY y convertida en caracteres con CAST el mes en inglés en letras obtenido con la función MESLETRAS y el año obtenido desde la función YEAR.

En la imagen sigiente muestra que la columna fecha expres una fecha en formato de texto


Ejemplo 3:
Script que permita implementar una función que devuelva el promedio de costos de un determinado año ingresado por el usuario desde la tabla RESERVA


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

CREATE FUNCTION PROMEDIOCOSTO(@AÑO INT)
RETURNS DECIMAL(10,2)
BEGIN
       DECLARE @PROM DECIMAL (10,2)
       SELECT @PROM=AVG(COSTO)
                    FROM RESERVA
                    WHERE YEAR(FECHA)=@AÑO
RETURN @PROM
END
GO

En el script se implementa la función PROMEDIOCOSTO que tiene como parámetro un año que será parte de la condicional de la consulta. Como se define el tipo de salida DECIMAL(10,2), la variable @PROM tiene que ser definida de la misma capacidad, ya que al final, ese valor será el retornado por la función.

Vamos a llamar la función

Con Select: SELECT DBO.PROMEDIOCOSTO(2012) AS PROMEDIO



Con PRINT: 

DECLARE @PROMEDIO DECIMAL(10,2)
DECLARE @AÑO INT = 2012
SET @PROMEDIO = (DBO.PROMEDIOCOSTO(@AÑO))

PRINT 'EL PROMEDIO DE COSTOS DEL AÑO '+CAST(@AÑO AS CHAR(4)) + 'ES: ' + CAST(@PROMEDIO AS CHAR(15))



Ejemplo 4:

Script que permita implementar una función que devuelva el total de pasajeros de un determinado país ingresado por el usuario


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

CREATE FUNCTION PASAJEROSXPAIS(@PAIS VARCHAR(40))
RETURNS INT
BEGIN
       DECLARE @TOTAL INT
       SELECT @TOTAL=COUNT(*)
             FROM PASAJERO PAS
             INNER JOIN PAIS PAI ON PAS.IDPAIS=PAI.IDPAIS
             WHERE PAI.PAIS=@PAIS
RETURN @TOTAL
END
GO

En el script se implementa la función PASAJEROSXPAIS con el parámetro @PAIS que permite ingresar el nombre del país que será parte de la condicional en el conteo de pasajeros por país.

Se declara la variable @TOTAL que almacena el total devuelto por la función COUNT, siempre y cuando sea TRUE la condición WHERE PAI.PAIS=@PAIS, se tuvo que implementar un INNER JOIN a la consulta, ya que el nombre del país se encuentra en la tabla PAIS.

Para la ejecución de la función se puede ejecutar el script de las dos forms:

Con Select:

SELECT 'MEXICO' AS PAIS, DBO.PASAJEROSXPAIS('MEXICO') AS PASAJEROS



Con PRINT:

DECLARE @PAIS VARCHAR(40)='MEXICO'
PRINT 'EL TOTAL DE PASAJEROS DE ' + @PAIS + ' ES: ' + CAST(DBO.PASAJEROSXPAIS(@PAIS) AS CHAR(4))
GO



Ejemplo 5:
Script que permita implementar una función que devuelva el acumulado de los montos de un determinado mes y año ingresado por el usuario.

IF OBJECT_ID('ACUMULADOXMESYAÑO') IS NOT NULL
BEGIN
       DROP FUNCTION DBO.ACUMULADOXESYAÑO
END
GO

CREATE FUNCTION ACUMULADOXMESYAÑO(@MES INT, @AÑO INT)
RETURNS DECIMAL(10,2)
BEGIN
       DECLARE @TOTAL DECIMAL(10,2)
       SELECT @TOTAL=SUM(MONTO)
                    FROM PAGO
                    WHERE MONTH(FECHA)=@MES AND YEAR(FECHA)=@AÑO
RETURN @TOTAL
END
GO


En el script se implementa la función ACUMULADOXMESYAÑO en donde se ingresan como parámetros el mes y el año para obtener de ellos aquellos montos realizados en dicho mes y año.

Esta vez se implementa la función SUM que permitirá acumular todos los registros que cumplan con la condición de igual mes e igual año. Vamos a implementar la función únicamente con el SELECT de cualquier manera queridos lectores, ya deben poner implementar también el PRINT.

SELECT '1' AS MES, '2012' AS AÑO, DBO.ACUMULADOXMESYAÑO(1,2012) AS [PAGOS]

Como pueden observar, este SELECT no contiene un FROM, recuerden que la consulta se realiza dentro de la funcíon, así, que el llamar la función como columna de tabla es válido.

Ejemplo 6:
Script que permita implementar una función que devuelva el factorial de un número ingresado por el usuario. Deberá usar recursividad en la función.

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

CREATE FUNCTION DBO.FACTORIAL(@N INT)
RETURNS INT
AS
BEGIN
       DECLARE @FACT INT
       IF @N <=1
             SET @FACT=1
       ELSE
             SET @FACT =@N * DBO.FACTORIAL(@N-1)
RETURN @FACT
END

En el Script  se implementa la función FACTORIAL que tiene como parámetro @N de tipo entero. También se define como tipo de dato devuelto a INT ya que el valor factorial no es decimal en ninguna ocasión.

Dentro de la función se declara la variable @FACT que permitirá devolver el valor resultado, luego se condiciona al número ingresado; si es menor o igual a uno entonces @FACT=1, en caso contrario, la variable@FACT multiplicará la variable @N por la llamada a la misma función FACTORIAL (@N-1), para poder llamar al valor descendente  al valor ingresado, hasta llegar a uno donde se finalizarán las llamadas, a esta actividad se le llama recursividad.

Vamos a ejecutar el script nuevamente solo con la sentencia SELECT

DECLARE @N INT =4
SELECT @N AS [NUMERO], DBO.FACTORIAL(@N) AS [FACTORIAL]
GO


Hasta aquí la segunda parte de este blog de funciones creadas por el usuario, aunque falta una tercera parte, ya que este tema es bastante extenso y su uso es básico, espero les haya gustado queridos lectores, espero comenten, compartan o den clic a algún anuncio. Les dejo el link de la parte I para mayor comodidad y por si no lo han leído









Banderas de Visitantes

Flag Counter