TYPE CURSOR - Retornar um Resultset numa Procedure - Oracle & DBExpress

Nos artigos anteriores vimos como retornar um resultset num parâmetro de saída de uma procedure no Oracle (ADOStoredProc, ADODataset). Seguiremos dando continuidade ao assunto abordando o problema trocando a tecnologia de acesso a dados. Usaremos DBExpress para isso e documentaremos as adversidades que encontradas.

Construindo o Exemplo com DBExpress para testar a procedure com mais de um parâmetro de entrada:

Primeiro vamos criar uma outra procedure na package “MY_Pkg”:

procedure CalculaMediaStr(
Nota1 in VARCHAR2,
Nota2 in VARCHAR2,
Nota3 in VARCHAR2,
MediavalAprovacao in VARCHAR2,
ZnCursor OUT ZNCursorType) IS
BEGIN
/* Calcula a média de um aluno */

Media := (Nota1 + Nota2 + Nota3)/ 3;
IF (Media >=  TO_NUMBER(MediavalAprovacao)) THEN
  ResultadoAprovacao := 'Aprovado';
ELSE
  ResultadoAprovacao := 'Reprovado';
END IF;

/* retornando no CURSOR o resultado do aluno */
OPEN ZnCursor FOR
  SELECT  'Aluno Estação ZN: ' AS Nome,
    ResultadoAprovacao AS ResultadoZN,
    TO_Char(Media) AS ValorMedia
FROM dual;

end CalculaMediaStr;

Adicione mais uma página no PagControl1. Além disso adicione um SQLConnection, um SQLStoredProc, um DataSetProvider, um ClientDataSet, um DataSource. Na nova TabSheet, adicione um Panel, quatro Edits, um BitBtn (BtnMediaStr), um DBGrid. Conecte todos os componentes dataware, configure o SQLStoredProc conforme ilustração abaixo:

Veja abaixo a listagem de parâmetros do SQLStoredProc:

Uma vantagem da DBExpress é que você não precisa deletar o parâmetro cursor. Conect os componentes da DBExpress (SQLConnection e SQLStoredProc), com os componentes Midas(DataSetProvider e ClientDataset):

Componentes Dataware - * indica a propriedade para associação entres os componentes

No ClientDataSet, com o botão direito do mouse selecione “Fetch Params”, em seguida adicione os campos persistentes no “Fields Editor”.

Componentes MIDAS:

O Componente SQLStoredProc perde os parâmetros quando efetua uma execução da procedure “CALCULAMEDIASTR”. Por isso somos obrigados a montar toda lista de parâmetros manualmente. Antes de codificar o evento OnClick do “BtnMediaStr”, onde faremos de fato a chamada a execução da “proc” vamos implementar um método que montará a lista de parâmetros do “SQLStoredProc”. Já que criamos uma proceedure nova para este exemplo, visto que teríamos que trabalhar somente com tipo não numéricos retornados ou enviados ao banco de dados, aproveitei e mudei um pouco a lógica da procedure “CALCULAMEDIASTR” em relação a calcula média anterior. Neste nova versão, o valor referência para aprovação é um parâmetro de entrada: “MediavalAprovacao”.

procedure TForm1.BuildParams;
var
 i: Integer;

 procedure AddParameter(Dataset: TClientDataSet; DataType: TFieldType;
   Name: String; ParamType: TParamType);
 var
   MyParameter: TParam;
 begin
  {Somos obrigados a criar dinamicamente os parâmetros}
   MyParameter := TParam.Create(DataSet.Params);
   MyParameter.DataType := DataType;
   MyParameter.Name := Name;
   MyParameter.ParamType := ParamType;
   DataSet.Params.AddParam(MyParameter);
 end;

begin

 with ClientDataSet do
 begin
   Close;

   Params.Clear;
   AddParameter(ClientDataSet, ftInteger, 'MEDIAVALAPROVACAO', ptInput);
   AddParameter(ClientDataSet, ftInteger, 'NOTA3', ptInput);
   AddParameter(ClientDataSet, ftInteger, 'NOTA2', ptInput);
   AddParameter(ClientDataSet, ftInteger, 'NOTA1', ptInput);
   AddParameter(ClientDataSet, ftCursor, 'ZnCursor', ptOutput);
 end;
end;

Codificando o evento OnClick do “BtnMediaStr”

 BuildParams;
 with ClientDataSet do
 begin
   Close;
   Params[0].AsString :=  EdtMediaAprovacao.Text;
   Params[1].AsString :=  EdtNota1.Text;
   Params[2].AsString :=  EdtNota2.Text;
   Params[3].AsString :=  EdtNota3.Text;
 Open;
end;

Testando a plicação:



Tivemos que converter, no comando SQL a variável “Media”, para tipo caractere. Segue código da package atual:

create or replace package MY_Pkg is

-- Author  : GMottazn
-- Created : 12/03/2008 09:58:08
-- Purpose :

-- Public type declarations
 TYPE ZNCursorType IS REF CURSOR;

-- Public variable declarations
-- ;

-- Public function and procedure declarations
procedure CalculaMedia(
 Nota1 in INTEGER,
 Nota2 in INTEGER,
 Nota3 in INTEGER,
 Nota4 in INTEGER,
 ZnCursor OUT ZNCursorType);

PROCEDURE RetCircunferencia(
  ZnCursor1 OUT ZNCursorType,
  Raio IN NUMBER DEFAULT 0);


procedure CalculaMediaStr(
 Nota1 in VARCHAR2,
 Nota2 in VARCHAR2,
 Nota3 in VARCHAR2,
 MediavalAprovacao in VARCHAR2,
 ZnCursor OUT ZNCursorType);

end MY_Pkg;
/
create or replace package body MY_Pkg is

-- Public constant declarations
 ValorAprovacao constant NUMBER  := 6;
 PI CONSTANT NUMBER := 3.1416; -- Constante usada na procedure "RetCricunferencia"

-- Private variable declarations
 ResultadoAprovacao VARCHAR2(20);
 Media NUMBER;
-- Function and procedure implementations
procedure CalculaMedia(
 Nota1 in INTEGER,
 Nota2 in INTEGER,
 Nota3 in INTEGER,
 Nota4 in INTEGER,
 ZnCursor OUT ZNCursorType) IS
BEGIN
 /* Calcula a média de um aluno */

 Media := (Nota1 + Nota2 + Nota3 + Nota4)/ 4;
 IF (Media >=  ValorAprovacao) THEN
   ResultadoAprovacao := 'Aprovado';
 ELSE
   ResultadoAprovacao := 'Reprovado';
 END IF;

 /* retornando no CURSOR o resultado do aluno */
 OPEN ZnCursor FOR
   SELECT  'Aluno Estação ZN: ' AS Nome,
     ResultadoAprovacao AS ResultadoZN,
     Media AS ValorMedia
 FROM dual;

end CalculaMedia;


PROCEDURE RetCircunferencia(
 ZnCursor1 OUT ZNCursorType,
 Raio IN NUMBER DEFAULT 0) IS
 vResultado NUMBER;
BEGIN
 /* Calcula a retificação da circunferência */

 vResultado := 2 * (Raio * PI);

 OPEN ZNCursor1 FOR
   SELECT
     'Retificação da Circuferência' AS Descricao,
     VResultado AS Resultado
   FROM
     Dual;
END RetCircunferencia;


procedure CalculaMediaStr(
 Nota1 in VARCHAR2,
 Nota2 in VARCHAR2,
 Nota3 in VARCHAR2,
 MediavalAprovacao in VARCHAR2,
 ZnCursor OUT ZNCursorType) IS
BEGIN
 /* Calcula a média de um aluno */

 Media := (Nota1 + Nota2 + Nota3)/ 3;
 IF (Media >=  TO_NUMBER(MediavalAprovacao)) THEN
   ResultadoAprovacao := 'Aprovado';
 ELSE
   ResultadoAprovacao := 'Reprovado';
 END IF;

/* retornando no CURSOR o resultado do aluno */
 OPEN ZnCursor FOR
   SELECT  'Aluno Estação ZN: ' AS Nome,
     ResultadoAprovacao AS ResultadoZN,
     TO_Char(Media) AS ValorMedia
   FROM dual;

end CalculaMediaStr;

end MY_Pkg;
/