I have a question when creating temporary tables in a procedure, when it ends, are those tables deleted? or they are loaded in cache.
I appreciate the help.
##################################################### Procedimiento
### limpiar tabla de porcentajes
delete from flor_nacional.porcentaje;
### sumar campos
create temporary table sumatoria as
select
ano,NSemana,TFlor,
sum(Acaros) as acaros,sum(Afidos) as afidos,sum(Babosa) as babosa,sum(Botrytis) as botrytis,
sum(Fusarium) as fusarium, sum(Hetero) as hetero,sum(Minador) as minador,sum(Trips) as trips,
sum(Trozador) as trozador,sum(Velloso) as velloso,sum(Abierto) as abierto,sum(DosPuntos) as dospuntos,
sum(Amarillamiento) as amarillamiento,sum(CabezaPeque) as cabezapeque,sum(Corto) as corto,
sum(Debil) as debil,sum(Decolorado) as decolorado,sum(Deformado) as deformado,sum(Delgados) as delgados,
sum(PetaloQuemado) as petaloquemado,sum(PuntasQuemado) as puntasquemado,sum(Rajado) as rajado,
sum(Torcido) as torcido,sum(Toxicidad) as toxicidad,sum(TInternacional) as tinternacional
from basecausales
group by ano,Nsemana,TFlor;
### suma total por semana
create temporary table TotalSemana as
select
ano,NSemana,TFlor,
acaros,afidos,babosa,botrytis,fusarium,hetero,minador,trips,trozador,velloso,
abierto,dospuntos,amarillamiento,cabezapeque,corto,debil,decolorado,
deformado,delgados,petaloquemado,puntasquemado,rajado,torcido,toxicidad,
tinternacional,
(
acaros+afidos+babosa+botrytis+fusarium+hetero+minador+trips+trozador+velloso+
abierto+dospuntos+amarillamiento+cabezapeque+corto+debil+decolorado+deformado+
delgados+petaloquemado+puntasquemado+rajado+torcido+toxicidad+tinternacional
) as TSemana
from sumatoria
group by ano,Nsemana,TFlor;
### calcular procentajes y guardarlos en tabla porcentaje
insert into flor_nacional.porcentaje
select
ano,NSemana,TFlor,Tsemana,
round((acaros/Tsemana)*100,1) as Acaros,
round((afidos/Tsemana)*100,1) as Afidos,
round((babosa/Tsemana)*100,1) as Babosa,
round((botrytis/Tsemana)*100,1) as Botrytis,
round((fusarium/Tsemana)*100,1) as Fusarium,
round((hetero/Tsemana)*100,1) as Hetero,
round((minador/Tsemana)*100,1) as Minador,
round((trips/Tsemana)*100,1) as Trips,
round((trozador/Tsemana)*100,1) as Trozador,
round((velloso/Tsemana)*100,1) as Velloso,
round((abierto/Tsemana)*100,1) as Abierto,
round((dospuntos/Tsemana)*100,1) as DosPuntos,
round((amarillamiento/Tsemana)*100,1) as Amarillamiento,
round((cabezapeque/Tsemana)*100,1) as CabezaPeque,
round((corto/Tsemana)*100,1) as Corto,
round((debil/Tsemana)*100,1) as Debil,
round((decolorado/Tsemana)*100,1) as Decolorado,
round((deformado/Tsemana)*100,1) as Deformado,
round((delgados/Tsemana)*100,1) as Delgados,
round((petaloquemado/Tsemana)*100,1) as PetaloQuemado,
round((puntasquemado/Tsemana)*100,1) as PuntasQuemado,
round((rajado/Tsemana)*100,1) as Rajado,
round((torcido/Tsemana)*100,1) as Torcido,
round((toxicidad/Tsemana)*100,1) as Toxicidad,
round((tinternacional/Tsemana)*100,1) as TInternacional
from TotalSemana
group by ano,Nsemana,TFlor;
MySQL temporary tables that need to save some temporary data is very useful. The temporary table is visible in the current connection, when the connection is closed, Mysql will automatically delete the table and free up all the space. enter link description here