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