Como trabalhar com Stored Procedures e Cursores no Oracle, SQL Server e Firebird e PostgreSQL

Em uma Stored Procedure temos que os dados são lidos, manipulados e atualizados de uma só vez. Em contrapartida, caso estivéssemos realizando a mesma codificação numa camada intermediária de nossa aplicação, teríamos que enviar esse conjunto de dados através da rede, manipular os dados e enviá-los de volta. Este tipo de processo deixa a tarefa mais lenta, além do que também dificulta o acesso a esses dados em alguma outra transação. Além disso, temos que o código armazenado pode servir para encapsularmos pedidos específicos que podem, de alguma forma, simplificar de maneira geral a nossa aplicação. Todos os bancos de dados abordados neste artigo, Oracle, SQL Server, Firebird e Postgres, suportam tanto Stored Procedures quanto Functions, com uma exceção referente ao Postgres, o qual não suporta Stored Procedures. Ao invés disso, o seu suporte é dado por Stored Functions, que têm a mesma intenção. Alguns dos fatores que diferem uma Stored Procedure de uma Stored Function são a incapacidade de retornar vários conjuntos de resultados e a falta de suporte a operações autônomas (como, por exemplo, o BEGIN e o COMMIT dentro de uma função).

Para entendermos melhor, vejamos as sintaxes básicas referentes a cada um dos bancos de dados e ver as possíveis diferenças referente a estruturação, como mostram os códigos das Listagens 1 a 4.

Listagem 1. Sintaxe básica de uma Stored Procedure no Oracle 12.1.

CREATE [OR REPLACE] PROCEDURE procedure_nome
      [ (parameter [,parameter]) ]
  IS
      [declaration_section]
  BEGIN
      executable_section
  [EXCEPTION
      exception_section]
  END [procedure_nome];

Listagem 2. Sintaxe básica da Stored Procedure no SQL Server 2014.

CREATE { PROCEDURE | PROC } [schema_nome.]procedure_nome
     [ @parameter [type_schema_nome.] datatype 
       [ VARYING ] [ = default ] [ OUT | OUTPUT | READONLY ]
     , @parameter [type_schema_nome.] datatype
       [ VARYING ] [ = default ] [ OUT | OUTPUT | READONLY ] ]
  [ WITH { ENCRYPTION | RECOMPILE | EXECUTE AS Clause } ]
  [ FOR REPLICATION ]
  AS
  BEGIN
     [declaration_section]
     executable_section
  END;

Listagem 3. Sintaxe básica de uma Stored Procedure no Firebird.

CREATE PROCEDURE <Procedure_Nome> 
   <Input_Parameter_List>
   RETURNS
   <Return_Parameter_List>
   AS
   <Local_Variable_Declarations>
   BEGIN
   <Procedure_Body>
   END

Agora que temos as sintaxes apresentadas referentes a Stored Procedures de cada banco de dados, veremos alguns exemplos simples de como podemos criar e utilizar estas Stored Procedures. No caso do banco de dados Postgres, para que possamos declarar uma Stored Function, utilizamos a palavra-chave Function, como é o caso do código da Listagem 4. Veja que inicialmente criaremos uma tabela contendo alguns campos e a nossa Function e, ao final, selecionamos os dados existentes na tabela.

Listagem 4. Criação de uma Stored Procedure no Postgres.

CREATE TABLE cliente
  (
    codigo integer NOT NULL,
    nome varchar(200),
    email varchar(200),
    dataAniversario date,
    ultimaDataCompra date,
    status boolean DEFAULT false,
    CONSTRAINT pk_codigo PRIMARY KEY (codigo)
  );
   
  CREATE FUNCTION InsereCliente(_codigo integer, _nome varchar(200), _email varchar(200), _dataNascimento date, _dataUltimaCompra date, _status boolean)
    RETURNS void AS
    $BODY$
        BEGIN
          INSERT INTO cliente(codigo, nome, email, dataNascimento, dataUltimaAtualizacao, status)
          VALUES((_codigo, _nome, _email, _dataNascimento, _dataUltimaCompra, _status);
        END;
    $BODY$
    LANGUAGE 'plpgsql'

Após a realização do insert das informações do cliente com a função InsereCliente, podemos verificar se as informações foram adicionadas corretamente a base de dados através das instruções a seguir:

select * from InsereCliente(1, 'Edson Dionisio', 'edson.dionisio@gmail.com', '1982-18-10', '2015-06-06', 't');
  select * from cliente;

Percebam que o primeiro select realiza a inserção de algumas informações para a base de dados através da nossa função criada. Já o segundo select realiza a operação normal, apenas de visualizar os dados inseridos na base de dados.

Veremos agora um exemplo referente a criação da Stored Procedure no Oracle, onde estamos interessados em fazer a atualização dos dados de um curso, como apresentado pela Listagem 5.

Listagem 5. Exemplo simples de criação de uma Stored Procedure no Oracle.

CREATE OR REPLACE Procedure AtualizaCurso
     ( nome_in IN varchar2 )   
  IS
     numero number;
     curso c1 is
     SELECT curso_numero
      FROM curso_tbl
      WHERE curso_nome = nome_in;
  BEGIN
     open c1;
     fetch c1 into numero;
     if c1%notfound then
        numero := 9999;
     end if;   
     INSERT INTO cursoAluno( curso_nome, curso_numero)
     VALUES ( nome_in, numero );
     commit;
     close c1;
  EXCEPTION
  WHEN OTHERS THEN
     raise_application_error(-20001, 'Ocorreu um erro na inserção do curso');
  END;

Como podemos ver, temos uma Stored Procedure chamada AtualizaCurso, que recebe um parâmetro chamado nome_in e, a partir disso, passa a procurar pelo número (código) com base no nome do curso especificado. Caso não tenhamos uma correspondência, atribuiremos o valor para o número como sendo 99999, por padrão. Por último, inserimos um novo registro na tabela CursoAluno se tudo ocorrer de forma correta.

Para darmos continuidade aos exemplos de procedures, veremos agora um exemplo utilizando o Firebird, como podemos ver na Listagem 6.

Listagem 6. Exemplo de Stored Procedure no Firebird.

CREATE OR ALTER PROCEDURE IAE_CLIENTES ( 
      tipo char(10), 
      par_codigo integer, 
      par_nome varchar(60), 
      par_cidade varchar(60)) 
  as 
  begin 
    if (Tipo = 'I') then 
        insert into cliente 
           (CODIGO, NOME, CIDADE) 
           values(:Par_Codigo, :Par_Nome, :Par_Cidade); 
    else 
    if (Tipo = 'A') then 
        update cliente 
           set NOME   = :Par_Nome, 
               CIDADE = :Par_Cidade 
        where (CODIGO = :Par_Codigo); 
    else 
    if (Tipo = 'E') then 
        delete from cliente 
        where (CODIGO = :Par_Codigo); 
    suspend; 
  end

Temos no código apresentado uma procedure criada para realizar uma atualização de salário do funcionário, onde além dessa atualização, também temos a porcentagem referente ao novo salário. Percebam também que antes de apresentarmos essa porcentagem verificamos se o novo salário não é igual ao antigo para que as operações necessárias possam ser realizadas. Por fim, veremos um exemplo de criação de Stored Procedure com o SQL Server, onde nosso objetivo é que, quando um registro é inserido na tabela de funcionários, também realizemos a inserção dos dados na tabela de backup, onde manteremos uma cópia de algumas das informações do funcionário, chamada de funcionarioBackup.

Dito isso, criaremos nossas tabelas de acordo com o código da Listagem 7 e, em seguida, iremos preenchê-las com alguns dados.

Listagem 7. Exemplo de Stored Procedure no SQL Server 2014.

create table dbo.OrdemServico
  (Codigo integer not null primary key nonclustered, 
  DataCompra datetime not null, 
   ValorCompra nvarchar(5) not null) 
   with (memory_optimized = on)
  go
   
  create procedure dbo.InsereOrdemServico(@Codigo integer, @ValorCompra nvarchar(5))
  with native_compilation, schemabinding, execute as owner
  as 
  begin atomic with
  (transaction isolation level = snapshot,
  language = N'English')
  declare @DataCompra datetime = getdate();
    insert into dbo.OrdemServico (Codigo, ValorCompra, DataCompra) values (@Codigo, @ValorCompra, @DataCompra);
  end
  go

No código temos a instrução NATIVE_COMPILATION, que indica que este Stored Procedure Transact-SQL é compilado nativamente, onde precisamos ter declaradas as opções Schemabinding, Execute As e Begin Atomic. Isso porque a opção Schemabinding deve ser vinculada com o esquema dos objetos que faz referência. No que diz respeito ao Execute As, temos que ele especifica o contexto de execução. Já o Begin Atomicgarante a execução atômica da Stored Procedure.

Trabalhando com estruturas condicionais

Com relação as estruturas de repetição, não há muita diferença entre as bases de dados apresentadas, pois todas agem com o mesmo propósito, mudando apenas a forma como elas são declaradas. Para que não tenhamos problemas quanto a isso, vejamos as sintaxes básicas referentes ao Loop While, começando pelo Firebird, como podemos ver no código da Listagem 8.

Listagem 8. Sintaxe básica do loop while no Firebird.

WHILE 
   <conditional_test> 
   DO
   <statements>;

A estrutura apresentada é bastante simples, onde se a condição for TRUE, as declarações seguintes ao WHILE serão executadas. Caso contrário, elas serão falsas e saíram do loop. Vejamos então a estrutura básica do Loop WHILE para a base de dados ORACLE, SQL SERVER e PostGres, respectivamente de acordo com as Listagens 9, 10 e 11.

Listagem 9. Sintaxe básica do WHILE para Oracle.

[ label ] WHILE condition LOOP
    statements
  END LOOP [ label ];

Listagem 10. Sintaxe básica do WHILE para SQL SERVER.

WHILE Boolean_expression 
  { 
  sql_statement | statement_block | BREAK | CONTINUE 
  }

Listagem 11. Sintaxe básica do WHILE para PostGres.

[ <<label>> ]
  WHILE boolean-expression LOOP
      statements
  END LOOP [ label ];

Dentre as quatro sintaxes apresentadas, tanto a da Oracle quanto a do Postgres são bastante parecidas, onde a diferença básica entre elas é a utilização das tags (<<>>) para o label utilizado. Já no que diz respeito a sintaxe do SQL Server podemos definir blocos contendo as declarações e as palavras-chaves BREAK e CONTINUE. Onde quaisquer declarações que apareçam após a palavra-chave END, marcando o fim do ciclo, serão executadas. Já com relação a palavra-chave CONTINUE, esta reinicia o loop, desconsiderando qualquer declaração que venha após a palavra-chave CONTINUE.

Como feito anteriormente, antes de entrarmos para o próximo tópico, vejamos exemplos referentes a utilização de cada uma das estruturas para que possamos entender melhor as diferenças existentes entre elas. Para isso, vejamos os códigos presentes nas Listagens 12 a 15.

Listagem 12. Utilizando o Loop While no Oracle.

DECLARE
    i  BOOLEAN := FALSE;
  BEGIN
    WHILE concluido LOOP
      DBMS_OUTPUT.PUT_LINE ('não aparece nada aqui.');
      concluído := TRUE;
    END LOOP;
    WHILE NOT concluído LOOP
      DBMS_OUTPUT.PUT_LINE ('Agora temos algo a apresentar! =) ');
      concluído := TRUE;
    END LOOP;
  END;
  /

Listagem 13. Utilizando o Loop While no SQL SERVER.

USE AdventureWorks2012;
  GO
  WHILE (SELECT AVG(ListPrice) FROM Production.Product) <= $450
  BEGIN
     UPDATE Production.Product
        SET ListPrice = ListPrice * 2
     SELECT MAX(ListPrice) FROM Production.Product
     IF (SELECT MAX(ListPrice) FROM Production.Product) > $500
        BREAK
     ELSE
        CONTINUE
  END

Neste exemplo apresentado na Listagem 13 utilizamos uma base de dados criada pela Microsoft que é o AdventureWorks2012. Esta é uma base de exemplos com bastante informações que podemos utilizar em diversos casos de testes.

Listagem 14. Utilizando o Loop While no Firebird.

while (a < 20) do
  begin
    insert into Numbers(A) values (:a);
    a = a + 1;
    when any do
    begin
      execute procedure log_error (current_timestamp, 'Erro no loop');
      leave;
    end
  end
  b = 0;

Listagem 15. Utilizando o Loop While no Postgres.

CREATE FUNCTION AdicionaInteiros (integer, integer) RETURNS integer AS '
    DECLARE
      numero1 ALIAS FOR $1;
      numero2 ALIAS FOR $2;
       resultado INTEGER = 0;
      BEGIN
      WHILE resultado != numero2 LOOP
        resultado := resultado + 1;
      END LOOP;    
      RETURN resultado;
    END;
  ' LANGUAGE 'plpgsql';

Utilizando cursores

Um cursor é uma área de trabalho temporária criada na memória do sistema quando uma instrução SQL é executada. Um cursor contém informações sobre uma instrução select e as linhas de dados acessadas por ela. Esta área de trabalho temporária é usada para armazenar os dados recuperados a partir do banco de dados, e em seguida, manipulá-los. Um cursor pode conter mais de uma linha, mas com relação ao processamento, este pode ser apenas por linha. Dito isso, veremos como são apresentadas as sintaxes para a criação e utilização dos cursores referentes a cada uma das bases de dados estudadas neste artigo. As sintaxes estão apresentadas de acordo com as Listagens 16 a 19.

Listagem 16. Sintaxe básica de um cursor no SQL SERVER.

ISO Syntax
  DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR 
       FOR select_statement 
       [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
  [;]
  Transact-SQL Extended Syntax
  DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] 
       [ FORWARD_ONLY | SCROLL ] 
       [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] 
       [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
       [ TYPE_WARNING ] 
       FOR select_statement 
       [ FOR UPDATE [ OF column_name [ ,...n ] ] ]
  [;]

Com relação a sintaxe básica do SQL SERVER podemos ver que ele possui algumas diferenças com relação as outras bases de dados, como é o caso das palavras-chaves INSENSITIVE, SCROLL, READ ONLY, UPDATE [OF columns[,...,n]], LOCAL, GLOBAL, dentre outros termos apresentados:

• O termo INSENSITIVE define uma cópia temporária dos dados a serem utilizados, de forma a que todas as respostas são passadas pelo tempdb;
• O termo SCROLL especifica as opções de busca;
• O termo RED ONLY previne que o cursor realize atualizações, deixando ele apenas para leitura;
• O termo LOCAL especifica que o escopo que está em uso é o local para os Stored Procedures, triggers ou Batches.

Listagem 17. Sintaxe básica de um cursor no Oracle.

CURSOR cursor_name
  IS
    SELECT_statement;

No que diz respeito aos cursores para o banco de dados Oracle, estes podem ser de dois tipos que são os cursores implícitos e os explícitos:

• Os cursores implícitos são criados, por padrão, quando trabalhamos com a execução das DML’s (INSERT, UPDATE e DELETE). Eles também são criados quando uma instrução SELECT retorna apenas uma linha a ser executada;
• Os cursores explícitos são criados quando executamos uma instrução SELECT que retorna mais de uma linha.

Tanto o cursor implícito como o explícito possuem a mesma funcionalidade, o que difere deles é a forma como eles são acessados.

Listagem 18. Sintaxe básica de um cursor no Postgres.

nome [ [ NO ] SCROLL ]
   CURSOR [ ( arguments ) ] 
  FOR query;

Como podemos observar na Listagem 18, temos a palavra-chave FOR que pode ser substituída por um IS para que possa ser compatível com o Oracle. Os argumentos (arguments) são uma lista de tipos de dados que definem os nomes que serão substituídos por valores de parâmetros na consulta especificada, estando estes separados por vírgulas. Os valores reais serão especificados no momento em que o cursor for aberto.

Listagem 19. Sintaxe básica de um cursor no Firebird.

<open_stmt> ::=
      OPEN <cursor_name>;
   <cursor_name> ::=   <identifier>

No caso do cursor para o Firebird temos a instrução OPEN, que é responsável por abrir um cursor local, ou seja, esta ação significa que a consulta associada será executada e que o conjunto de resultados será mantido disponível para processamentos posteriores com a instrução FETCH. Para isso, o cursor deverá ter sido declarado na seção de declarações do programa PSQL. Caso o cursor já esteja aberto e ocorra uma tentativa de reabri-lo, esta ação acarretará numa falha que irá gerar uma exceção em tempo de execução.

Para que possamos entender melhor com relação aos cursores, vejamos agora alguns exemplos de sua utilização com as bases de dados em questão, de acordo com as Listagens 20 a 23.

Listagem 20. Exemplo de cursor no Oracle.

DECLARE linhas number(5);
  BEGIN
    UPDATE Funcionarios 
    SET salario = salario + 550;
    IF SQL%NOTFOUND THEN
      dbms_output.put_line('Nenhum salário foi atualizado..');
    ELSIF SQL%FOUND THEN
      linhas := SQL%ROWCOUNT;
      dbms_output.put_line('O salário de um total de ' || linhas || ' funcionários foi atualizado');
    END IF; 
  END;

Neste exemplo realizado com a base de dados Oracle temos que os salários de todos os funcionários presentes na tabela 'funcionários' são atualizados. Em caso de não haver atualização do salário desses funcionários, uma mensagem será apresentada. Além disso, caso todos os funcionários tenham sido atualizados, uma outra mensagem será apresentada informando o sucesso da operação.

Listagem 21. Exemplo de cursor no Postgres.

DECLARE
                CURSOR funcionariosCursor IS 
                SELECT codigo, nomeFunc FROM funcionario
                WHERE departamento = 25;
                codigo funcionario.codigo%TYPE;
                nomeFunc funcionario.nomeFunc%TYPE;
         BEGIN
                OPEN funcionariosCursor;
                FETCH funcionariosCursor INTO codigo, nomeFunc;
                EXIT WHEN NOT FOUND;
                END LOOP; 
         END;
  CLOSE funcionariosCursor;
  END;

Neste exemplo de cursor estamos buscando recuperar todos os funcionários que pertençam ao departamento de código 25 e, para isso, utilizamos um loop para realizar esta busca.

Listagem 22. Exemplo de cursor no SQL SERVER 2014.

SET NOCOUNT ON
  DECLARE @Id int
  DECLARE @nome varchar(50)
  DECLARE @salario int
   DECLARE funcionarioCursor CURSOR
  STATIC FOR 
  SELECT FuncCodigo, FuncNome, FuncSalario from Funcionarios
  OPEN funcionarioCursor
  IF @@CURSOR_ROWS > 0
   BEGIN
   FETCH NEXT FROM funcionarioCursor INTO @Id, @nome, @salario 
   WHILE @@Fetch_status = 0
   BEGIN
   PRINT 'Código: '+ convert(varchar(20), @Id) + ', Nome do funcionário : '+@nome+ ', Salário : '+convert(varchar(20), @salario)
   FETCH NEXT FROM funcionarioCursor INTO @Id, @nome, @salario
   END
  END
  CLOSE funcionarioCursor
  DEALLOCATE funcionarioCursor
  SET NOCOUNT OFF

Neste caso estamos utilizando o cursor para mostrarmos uma mensagem contendo todos os dados cadastrados do funcionário na base de dados. Para que este cursor funcione é necessário criar antes uma tabela contendo alguns registros para que, a partir daí, possamos recuperar os valores.

Listagem 23. Exemplo de cursor no Firebird.

CREATE PROCEDURE USANDO_CURSOR
     RETURNS(
        codigo BIGINT,
        NomeCli VARCHAR(40))
  AS
     DECLARE VARIABLE ExisteCliente CHAR(1);
     DECLARE VARIABLE CursorTeste CURSOR FOR (
        SELECT CODIGO, NOME FROM VENDAS);
  BEGIN
      OPEN CursorTeste;
      ExisteCliente = 'S';
      WHILE (ExisteCliente = 'S') DO BEGIN
        FETCH CursorTeste INTO :codigo, :NomeCli;
        IF (ROW_COUNT = 1) THEN BEGIN
           NomeCli = NomeCli || ' cliente existe na base..';
           SUSPEND;
        END ELSE
           ExisteCliente = 'N';
     END
      CLOSE CursorTeste;
  END;

Com isso encerramos o nosso artigo, onde apresentamos alguns conceitos e exemplos simples das diferenças existentes em algumas das bases de dados existentes no momento.