109 - funciones almacenadas

Ver video

Una función es un conjunto de sentencias de forma similar a un procedimiento almacenado pero como diferencia solo debe retornar un valor simple (int, varchar, float etc.)

Una función tiene un nombre, acepta parámetros solo de entrada (no hay que anteceder la palabra clave in) y retorna un valor obligatoriamente.

Luego a una función a diferencia de un procedimiento almacenado se lo puede llamar desde una sentencia select.

drop function if exists f_mayor;

delimiter //
create function f_mayor(
  valor1 int,
  valor2 int) 
  returns int
  deterministic
begin 
  if valor1>valor2 then
    return valor1;
  else
    return valor2;
  end if;
end //
delimiter ;

select f_mayor(50, 120);

Hemos creado una función que recibe dos parámetros de tipo entero y mediante la palabra clave 'returns' indicamos el tipo de dato que retornará dicha función:

create function f_mayor(
  valor1 int,
  valor2 int) 
  returns int
  deterministic

Otra palabra clave que debemos especificar en la declaración de la función es si la misma es determinística (es determinística si retornan el mismo resultado si se las invoca enviando el mismo valor de entrada)

Luego definimos el algoritmo de la función:

begin 
  if valor1>valor2 then
    return valor1;
  else
    return valor2;
  end if;
end //

Cuando se encuentra un return dentro del algoritmo, la función finaliza inmediatamente retornando el valor indicado.

Podemos llamar a la función empleando la cláusula select:

select f_mayor(50, 120);

También podemos almacenar el valor devuelto en una variable para ser utilizada en forma posterior:

set @resultado=f_mayor(20, 12);
select @resultado;

Problema

Tenemos una tabla llamada 'sitios' donde almacenamos las url de distintos sitios web, la cantidad de páginas que se visualizan por mes y la cantidad de estrellas asignadas (un valor entre 1 y 5)

Borrar la tabla si existe y proceder a crearla:

drop table if exists sitios;

create table sitios (
    url varchar(100),
    cantpaginas int,
    estrellas tinyint,
    primary key(url)
);

Insertar algunos registros de prueba:

insert into sitios(url,cantpaginas,estrellas) values ('lanacion.com.ar',17000000,3);
insert into sitios(url,cantpaginas,estrellas) values ('clarin.com',42000000,3);
insert into sitios(url,cantpaginas,estrellas) values ('infobae.com',33000000,5);
insert into sitios(url,cantpaginas,estrellas) values ('lavoz.com.ar',25000000,2);

Implementar una función que le enviemos la cantidad de estrellas que tiene un sitio y nos devuelva un varchar con tantos '*' como indica el parámetro:

drop function if exists f_estrellas;

delimiter //
create function f_estrellas(
  cant tinyint)
  returns varchar(15)
  deterministic
 begin
   declare estrellas varchar(15) default '';
   declare x int default 0;
   while x<cant do
     set estrellas=concat(estrellas,'*');
     set x=x+1;
   end while;
   return estrellas;
 end //
 delimiter ; 
 
 select url,f_estrellas(estrellas) from sitios;

Tenemos un resultado similar a esto cuando llamamos a la función 'f_estrellas':

Confeccionar una segunda función que le enviemos la cantidad de páginas que se visualizan por mes y nos retorne un varchar indicando si el sitio tiene 'tráfico bajo', 'tráfico medio' o 'alto tráfico'.

Tener en cuenta:
Es de 'tráfico bajo' si entrega menos de 20000000 de páginas.
Es de 'tráfico medio' si entrega entre 20000000 y 40000000 de páginas.
Es de 'tráfico alto' si entrega má 40000000 de páginas.

Primero borramos la función almacenada si existe y procedemos a crearla:

 drop function if exists f_tipositio;
 
 delimiter //
 create function f_tipositio(
   cantidad int)
   returns varchar(20)
   deterministic
 begin
case 
    when cantidad<20000000 then
      return 'tráfico bajo';
    when cantidad>=20000000 and cantidad<40000000 then
      return 'tráfico medio';
    when cantidad>=40000000 then
      return 'tráfico alto';
  end case; 
 end //
 delimiter ;
 
 select url,f_estrellas(estrellas), cantpaginas, f_tipositio(cantpaginas) from sitios; 

Tenemos el siguiente resultado cuando llamamos a la función 'f_tipositio':

Confeccionar una tercer función que nos retorne la url del sitio que tiene mayor tráfico:

Ahora confeccionar una función que retorne la 'url' del sitio que tiene mayor tráfico:

drop function if exists f_mayor_trafico;
 
 delimiter //
 create function f_mayor_trafico()
   returns varchar(100)
   deterministic
 begin
   declare vurl varchar(100);
   select url into vurl from sitios order by cantpaginas desc limit 1;
   return vurl;
 end //
 delimiter ;
 
 select f_mayor_trafico();

Como podemos ver en una función podemos perfectamente ejecutar comandos SQL: