trabalho linguagem proncendural para banco de Dados

Prévia do material em texto

Universidade Estácio de Sá 
 
 
 
 
Carla Dutra de Sá - 201902114485 
 
 
 
 
Tarefa Extra Av1 
 
 
 
 
 
 
 
 
 
24 de Maio de 2021 
Niterói 
 
 
 
 
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. 
mailto:edson.dionisio@gmail.com
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 umaexceçã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. 
 
	Trabalhando com estruturas condicionais
	Utilizando cursores