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

Acompanhe nesse artigo o comparativo no uso de Stored Procedures, Cursores e um pouco sobre as estruturas de repetição nos bancos de dados 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:

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:

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.

Artigos relacionados