Re: execute block like Firebird does

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