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
