TYPE CURSOR - Retornar um Resultset numa Procedure - Oracle

Retornar um resultset como parâmetro de saída numa procedure em PL/SQL não é algo tão intuitivo como no Interbase ou no MS SQL Server. Portanto, vamos desenvolver um exemplo onde construiremos um procedure em PL/SQL para retornar um resultset. Em seguida vamos implementar um programa em Delphi que vai executar esse procedimento e recuperaremos na interface o resultado do Select.

Para criar a pakcage:
No PL/SQL Developer, no Object Browser, ou somente Browser, selecione a pasta (folder) “Packages” com o botão direito do mouse click em new. Conforme ilustrado abaixo:



Defina o Nome da package:




Note que será criado uma área de declaração como interface da package e outra área para o “body” da package.



Eu vou definir um tipo cursor o qual será usado para um parâmetro de saída da procedure “CalulaMedia”. Nesse parâmetro retornarei o resultset que desejo exibir na aplicação.

Interface da package:


Body da package:



Construindo a interface em Delphi:

Ok, recapitulando: Vamos construir um aplicativo o qual acessará o banco de dados Oracle onde criamos a package “My_Pkg”. A middleware de acesso a dados será OLE_DB, cujos objetos de acesso a dados estão disponibilizados no Delphi na palheta ADO (Activex Data Object). Estou documentando esse exemplo porque não foi nada fácil, nem intuitivo, recuperar um parâmetro do tipo cursor no Delphi. Na nossa primeira tentativa nos deparamos com muitos problemas o maior deles foi justamente com o parâmetro de retorno do tipo cursor no componente “TADOStoredProc”. Constatamos que nesta tecnologia o tipo “ftCursor” quando atribuído a propriedade “DataType” do parâmetro disparava uma exceção informado que os argumentos estavam incorretos.



Constatamos que para fazer funcionar tínhamos que deletar esse parâmetro da lista de parâmetros, deixando somente o parâmetro de input. Ainda referente ao cenário da primeira tentativa, somente obtivemos sucesso com “TADOStoredProc” enquanto a procedure no banco possuía dois parâmetros, um deles era o tipo cursor, parâmetro de saída, o outro era um parâmetro para filtro na cláusula “where” do comando SQL. Vejamos um exemplo:
Duas condições devem ser atendidas – O tipo cursor deve ser o primeiro parâmetro pois o último, de entrada, deve obrigatoriamente ser definido como “default”. Criei para este exemplo uma procedure que efetua um cálculo simples:

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

  vResultado := 2 * (Raio * PI);

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

A constante “PI” declarei na área de declaração de constantes da package “MY_PKG”. Veja o Código da package agora:

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(
    ZnCursor OUT ZNCursorType,
    Raio IN NUMBER DEFAULT 0) IS
 vResultado NUMBER;
BEGIN  
/* Calcula a retificação da circunferência */ 

 vResultado := 2 * (Raio * PI);

OPEN ZnCursor FOR
 SELECT  'Aluno Estação ZN: ' AS Nome,
          ResultadoAprovacao AS ResultadoZN,
          Media AS ValorMedia       
 FROM dual;
  
END RetCircunferencia;

end MY_Pkg;

Inicie uma nova aplicação, no form1 adicione um “ADOConnection”, um TADOStoredProc, um TDataSource, um TDBGRid, um TEdit, um TLabel. Conecte o ADOConnection com o banco aonde vc criou a procedure. No ADOConnection, o menu popup, com o botão direito do mouse selecione build connection. Veja a ilustrção abaixo:



Não esqueça de alterar a propriedade “LoginPrompt” do ADOConnetion para “False”.

Em seguida conecte a ADOStoredProc no ADOConnection (pela propriedade “Connection” do ADOStoredProc). Conecte o DataSource no ADOStoredProc (propriedade DataSet do DataSource), conecte o DBGrid no DataSource (propriedade “DataSource” do DBGrid). Na propriedade “ProcedureName” do ADOStoredProc digite “[eschema].MY_PKG.RetCircunferencia”. Veja na propriedade parameters do ADOStoredProc a lista de parâmetros recuperados da procedure.



Alterei a propriedade Name do Tedit para “EdtRaio”. Adicione um TBitBtn, nomeie de BtnExcRaio,No Evento OnClick digite conforme exemplificado abaixo:

procedure TForm1.BtnExcRaioClick(Sender: TObject);
begin
  with ADOStoredProc1 do
  begin
    Parameters[0].Value := StrtoFloat(EdtRaio.Text);
    Open;
  end;
end;

Ok, tudo parece estar certo, tudo pronto para testarmos, correto? Posso adiantar que se você executar agora vai levar uma exceção bacana na lata.



Pra funcionar ainda temos que, sem razão aparente, deletar o parâmetro “ZNCURSOR” da lista de parâmetros da propriedade “Parameters” do ADOStoredProc.



Para demonstrar o quanto é complicado alcançar o objetivo proposto no início do nosso exmplo (executar um procedure no Oracle e recuperar o valor de um parâmetro tipo cursor), veja que ainda faltam algumas configurações a fazer no ADOStoredProc. Antes garanta que o ADOConnection esteja desconectado (propriedade “Connected = False”):
1° - Altere a propriedade “EnableBCD” do ADOStoredProc para “False”.
2° - Altere a propriedade “DataType” do parâmtro “RAIO” de “ftBCD” para “ftFloat”. Você pode fazer isso na propriedade “Parameters” do ADOStoredProc.

Agora adicione os campos persitentes, no fields Editor do ADOStoredProc. Em seguida execute o programa e teste:





Com um pouco de persistência obtivemos sucesso! Contudo, os problemas não param por aqui. Por exemplo, se você tiver mais de um parâmetro de entra o bicho vai pegar e o bagulho não vai funfar. Justamente, esse é o caso da procedure “CalculaMedia”, de jeito nenhum conseguimos fazer funcionar com o ADOStoredProc. Tentando de várias outras formas conseguimos sucesso trocando o dataset para TADODataSet, só assim funcionou e mesmo assim tivemos que fazer as mesmas alterações quanto ao tipo do parâmetro e deleção o tipo cursor. A única exceção foi a não obrigatoriedade do parâmetro de saída, tipo cursor, na procedure “CalculaMedia” ser o primeiro na declaração.

No próximo artigo daremos continuidade ....

segue o código da package:

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 out NUMBER,
  Nota2 in NUMBER,
  Nota3 in NUMBER,
  Nota4 in NUMBER,
  ValorAprovacao IN NUMBER,
  Media out NUMBER,
  ZnCursor IN OUT ZNCursorType);

end MY_Pkg;
/
create or replace package body MY_Pkg IS

-- Public constant declarations
  ValorAprovacao constant NUMBER  := 6;

-- Private variable declarations
  ResultadoAprovacao VARCHAR2(20);

-- Function and procedure implementations
procedure CalculaMedia(
  Nota1 in out NUMBER,
  Nota2 in NUMBER,
  Nota3 in NUMBER,
  Nota4 in NUMBER,
  ValorAprovacao IN NUMBER,
  Media out NUMBER,
  ZnCursor IN OUT ZNCursorType) is
begin
  Media := (Nota1 + Nota2 + Nota3 + Nota4)/ 4;
  IF (Media >=  ValorAprovacao) THEN
    ResultadoAprovacao := 'Aprovado';
  ELSE
    ResultadoAprovacao := 'Reprovado';
  END IF;
  open ZnCursor FOR
  SELECT  'Aluno Estação ZN: ' AS Nome,
          ResultadoAprovacao AS ResultadoZN
  FROM dual;

end CalculaMedia;


end MY_Pkg;
/