Explaining better my problem. All reports our customer use are customizable.
So, when a customer runs a report it just runs all SQLs that are inside that
report, being SQL or Execute Blocks. But because they are completelly
customizable, one customer has 80 reports with 300 Execute Blocks on them
and other one has just 10 reports with 100 execute blocks and they can be
used to different purposes. If, instead of rewriting them, just create a
function on each one, imagine that Customer A will have 300 hundred
functions and Customer B will have only 100. And worse, some of those
functions have same header but different body. Can you imagine a mess like
that ?
95% of those execute blocks are simple and can be replaced by a well done
SQL or a CTE. But we have hundreds of these blocks and we need to not just
recompile them but rewrite them using a different approach.
Show you some examples of our execute blocks.
This one is easy to understand and can be easily replaced.
execute block returns(CargaTotal Moeda) as
declare variable Aluno_ID I32;
declare variable Turma_ID I32;
declare variable MateriasIn t10000;
declare variable Presente I16;
declare variable JustificativaHistorico_ID I32;
declare variable qtdeAulas i32;
declare variable qtdePresencas i32;
begin
select Pessoa_Id From mov_Contrato Where Contrato_Id = %d Into :Aluno_Id;
Turma_Id = %d;
qtdeAulas = 0;
qtdePresencas = 0;
for select Presente, JustificativaHistorico_ID from col_Aula A inner join
col_Frequencia F on F.Aula_ID = A.Aula_ID where
a.Materia_Id in (select distinct a.Materia_Id from col_aula a where
a.Turma_Id = :Turma_Id) and f.Aluno_ID = :Aluno_Id
into :Presente, :JustificativaHistorico_ID do begin
qtdeAulas = :qtdeAulas + 1;
if ((:Presente=1) or (:JustificativaHistorico_ID is not Null)) then
qtdePresencas = :qtdePresencas + 1;
end
if (:qtdeAulas > 0) then
CargaTotal = (Cast(:qtdePresencas as Moeda) / Cast(:qtdeAulas as Moeda)
* 100.00);
else
CargaTotal = 0;
Suspend;
end
But other ones needs to be carefully read to be translated.
execute block returns (
Curso_ID type of column col_Curso.Curso_ID,
Turma_ID type of column col_Turma.Turma_ID,
Curso type of column col_Curso.Descricao,
Turma type of column col_Turma.Nome,
Iniciando Logico,
PeriodoSequencia I32,
Periodo T50,
Ordem I32,
DescricaoSemana varchar(15),
SemanaInicio type of column col_Aula.Data,
SemanaFim type of column col_Aula.Data,
AulaData Data,
Contrato_ID type of column mov_Contrato.Contrato_ID,
Contrato type of column mov_Contrato.NumeroContrato,
Aluno_ID type of column rel_AlunoTurma.Aluno_ID,
AlunoDaAula type of column rel_AlunoTurma.lkAluno,
StatusAtual type of column mov_Contrato.lkStatus,
StatusNoPeriodo type of column mov_Contrato.lkStatus,
Presente type of column col_Frequencia.Presente
) as
declare variable Semanas I32 = %0:d;
declare variable I I32;
declare variable tmpData Data;
declare variable PrevIni Data = '%1:s'; --Execute block doesn´t
have IN Param, so we change this variable using Format();
declare variable PrevFim Data = '%2:s'; --This one too.
declare variable HoraInicio VarChar(6) = ' 00:00';
declare variable HoraFinal VarChar(6) = ' 23:59';
declare variable PeriodoManha type of column sys_LookUp.Descricao =
'Matutino';
declare variable PeriodoTarde type of column sys_LookUp.Descricao =
'Vespertino';
declare variable PeriodoNoite type of column sys_LookUp.Descricao =
'Noturno';
declare variable StatusPauta Memo;
declare variable StatusDesistente I32;
declare variable sqlTemp Memo;
declare variable Turmas Memo = ':ListaTurma';
declare variable sqlPeriodo Memo = 'select :Numero, '':Descricao'',
'':DataIni'', '':DataFim'' from rdb$database:where';
declare variable sqlAulas Memo;
declare variable sqlLista Memo = 'select distinct
col_Curso.Curso_ID,
col_Curso.Descricao,
col_Turma.Turma_ID,
col_Turma.Nome,
case when extract(hour from col_Aula.Data) < 12 then 1 when extract(hour
from col_Aula.Data) between 12 and 18 then 2 when extract(hour from
col_Aula.Data) > 18 then 3 end,
case when extract(hour from col_Aula.Data) < 12 then '':PeriodoManha''
when extract(hour from col_Aula.Data) between 12 and 18 then
'':PeriodoTarde'' when extract(hour from col_Aula.Data) > 18 then
'':PeriodoNoite'' end
from
col_Turma inner join col_Curso using(Curso_ID) inner join col_Aula
using(Turma_ID)
where 1=1
and col_Turma.Turma_ID in (:Turmas)
and col_Aula.Data between '':PrevIni'' and '':PrevFim''
order by
col_Curso.Descricao,/*Iniciando */ 5, /* PeriodoSequencia */6,
col_Turma.Nome';
begin
I= 2;
tmpData = dateADD(-1 Day to :prevIni);
sqlAulas = '';
while (:I < :Semanas + 2) do begin
I = :I + 1;
sqlAulas = sqlAulas || replace(:sqlPeriodo, ':Numero', :I);
sqlAulas = replace(:sqlAulas, ':Descricao', 'Semana ' || lpad(:I - 2, 2,
0) );
sqlAulas = replace(:sqlAulas, ':DataIni', :tmpData || :HoraInicio);
tmpData = dateadd(1 week to :tmpData);
sqlAulas = replace(:sqlAulas, ':DataFim', :tmpData || :HoraFinal);
sqlAulas = replace(:sqlAulas, ':where', ' union' || ascii_char(13));
end
sqlLista = replace(:sqlLista, ':PeriodoManha', :PeriodoManha);
sqlLista = replace(:sqlLista, ':PeriodoTarde', :PeriodoTarde);
sqlLista = replace(:sqlLista, ':PeriodoNoite', :PeriodoNoite);
sqlLista = replace(:sqlLista, ':Turmas', :Turmas);
sqlLista = replace(:sqlLista, ':PrevIni', :PrevIni || :HoraInicio);
sqlLista = replace(:sqlLista, ':PrevFim', :PrevFim || :HoraFinal);
for execute statement :sqlLista into :Curso_ID, :Curso, :Turma_ID, :Turma,
:PeriodoSequencia, :Periodo
do begin
select min(col_Aula.data) from col_aula where col_Aula.Turma_ID =
:Turma_ID into :tmpData;
if (:tmpData is not null) then begin
sqlTemp = :sqlAulas || replace(:sqlPeriodo, ':Numero', 0);
sqlTemp = replace(:sqlTemp, ':Descricao', 'Primeira Aula');
sqlTemp = replace(:sqlTemp, ':DataIni', :tmpData || :HoraInicio);
sqlTemp = replace(:sqlTemp, ':DataFim', :tmpData || :HoraFinal);
sqlTemp = replace(:sqlTemp, ':where', ' union' || ascii_char(13));
end
select max(col_Aula.data) from col_aula where col_Aula.Turma_ID =
:Turma_ID and col_Aula.data > :prevIni into :tmpData;
if (:tmpData is not null) then begin
sqlTemp = :sqlTemp || replace(:sqlPeriodo, ':Numero', 1);
sqlTemp = replace(:sqlTemp, ':Descricao', 'Ultima Aula');
sqlTemp = replace(:sqlTemp, ':DataIni', :tmpData || :HoraInicio);
sqlTemp = replace(:sqlTemp, ':DataFim', :tmpData || :HoraFinal);
sqlTemp = replace(:sqlTemp, ':where', ' union');
end
sqlTemp = substring(trim(:sqlTemp) from 1 for
(OCTET_LENGTH(trim(:sqlTemp)) - OCTET_LENGTH(' union'))) || ' where 1=1
order by 1';
for execute statement :sqlTemp into :ordem, DescricaoSemana,
:SemanaInicio, :SemanaFim do begin
for select
alunoaula.Data,
alunoaula.contrato_id,
alunoaula.numerocontrato,
alunoaula.aluno_id,
alunoaula.lkaluno,
alunoaula.statusperiodo,
alunoaula.statusatual,
alunoaula.presente
from
alunoaula(null, :SemanaInicio, :SemanaFim, :Turma_ID)
into
:AulaData, :Contrato_ID, :Contrato, :Aluno_ID, :AlunoDaAula,
:StatusNoPeriodo, :statusAtual, :Presente
do
suspend;
end
end
end
As you can see, they are like functions, have for, while, if, etc.
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
