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.

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

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

Funciones en SQL SERVER Parte I