4 Stored Procedures

Prévia do material em texto

1- STORED PROCEDURES
Conceito de stored procedures – Stored procedures são procedimentos (códigos em transact SQL) 
que o usuário pode armazenar num banco de dados e executá-los quando necessário, através de uma
chamada aos mesmos. Isso permite, no desenvolvimento de um sistema, que os códigos relativos às
operações no banco de dados fiquem armazenados no próprio servidor do banco de dados, o que
torna o código da aplicação menor e a adaptação do sistema à uma outra linguagem de programação
mais fácil (caso o código do sistema seja migrado para outra linguagem, os códigos relativos às
operações no banco de dados permanecem os mesmos). As stored procedures também promovem
uma menor troca de informação entre servidor de banco de dados e aplicação cliente. Entretanto, a
maior carga de trabalho é feita no lado do servidor.
Forma Geral: 
 
CREATE PROCEDURE <nome da stored procedure> (<lista de parâmetros>) 
<BLOCO DE COMANDOS TRANSACT-SQL>
onde:
Lista de parâmetros: especifica uma série de parâmetros e o tipo dos mesmos (por default os
parâmetros são IN, mas podem ser OUT ou INOUT). Para que um parâmetro seja OUT ou INOUT
deve-se explicitar o tipo do parâmetro como OUT ou INOUT na listagem de parâmetros da Stored
Procedure.
Vejamos algumas Stored Procedures propostas para a base de dados Banco. 
Stored procedure sem parâmetro:
 
No terminal do mysql :
mysql> delimiter /
mysql> create procedure sp_listaagencias()
-> begin
-> select * from agencia;
-> end;
-> /
Query OK, 0 rows affected (0.00 sec)
Utilizando a interface do mysql Workbench:
delimiter /
create procedure sp_listaagencias()
 begin
 select * from agencia;
 end
Executando a stored procedure:
call sp_listaagencias(); (** No caso do Workbench não há a necessidade do ponto e vírgula)
Store procedures com 1 parâmetro IN
delimiter /
create procedure sp_buscaagencia(codigo int)
begin
select * from agencia where cod_agencia=codigo;
end/
Executando a stored procedure:
call sp_buscaagencia(1);
Store procedure com parâmetros OUT e IN
delimiter /
create procedure sp_contasporagencia(out nro int, codag int)
begin
select count(numero_conta) into nro from conta where cod_agencia=codag;
end/
Executando a stored procedure:
call sp_contasporagencia(@numero, 1);
select @numero;
1.1 – Stored Procedures com desvio condicional (IF ou CASE) e declaração de variáveis
Podemos, como em linguagens de programação tradicionais, declarar variáveis no código SQL de
uma stored procedure e utilizá-las como em linguagens de programação tradicionais.
Comando IF: O comando IF implementa a estrutura condicional clássica conhecida na lógica de
programação. Caso a condição booleana seja verdadeira, então um conjunto de instruções SQL é
executado; caso seja falso as outras condições do IF são avaliadas. Se nenhuma condição é
verdadeira, então os comandos da cláusula ELSE são executados.
Sintaxe: 
IF <condição> THEN <comandos SQL>
 ELSEIF <condição> THEN <comandos SQL>
 ELSE <comandos SQL>
END IF
Vejamos o exemplo de uma stored procedure que armazena em um parâmetro de saída o valor 1 ou
0 (verdadeiro ou falso) caso encontre ou não a agência de código igual a um codigo dado como
entrada para a stored procedure.
delimiter /
create procedure sp_buscaagencia(codigo int, out busca int)
begin
 declare achou int;
 select count(*) into achou from agencia where cod_agencia=codigo;
 if achou=0 then set busca = 0;
 else set busca = 1;
 end if;
end/
Função IF: A função IF avalia uma condição e retorna um valor caso a avaliação da
condição seja verdadeira e outro valor caso contrário.
Sintaxe: 
IF(<CONDIÇÃO>, VALOR_CASO_TRUE, VALOR_CASO_FALSE)
Exemplo: stored procedure que associa um rótulo de classificação das contas de acordo com o valor
do saldo das mesmas. 
delimiter /
create procedure sp_classificacontas()
begin
 select numero_conta, cod_agencia, saldo, if(saldo<=850, "Classe Prata", "Classe Ouro") from
conta;
end/
Comando CASE: O comando CASE executa um bloco de comandos SQL de acordo com o valor
de uma condição de procura. A condição de procura pode ser uma expressão booleana simples ou
uma expressão booleana que envolva uma consulta SQL.
Sintaxe: 
CASE
WHEN <condição de procura> then <comandos SQL>
WHEN <condição de procura> then <comandos SQL>
.
.
.
ELSE <comandos SQL>
END CASE;
Exemplo: Vejamos uma stored procedure que realiza um aumento no total de fundos de
uma agência, de acordo com a seguinte regra: se o total de fundos for < 500000, então há um
reajuste de 0.10%, se total de fundos >= 500000 e menor que 1000000 então há um reajuste de
0.05%; caso total de fundos seja maior ou igual a 1000000 não há reajuste a ser feito. O código da
agência será o parâmetro de entrada da stored procedure.
delimiter /
create procedure sp_aumenta_total_fundos(codigo int)
begin
case
when (select fundos from agencia where cod_agencia=codigo)<500000 then
update agencia set fundos = fundos+(fundos*0.10) where cod_agencia=codigo;
when ((select fundos from agencia where cod_agencia=codigo)>=500000 and (select 
fundos from agencia where cod_agencia=codigo)<1000000) then update agencia set fundos 
= fundos+(fundos*0.05) where cod_agencia=codigo;
else select 'Não há atualizacao a fazer para o total de fundos da agencia';
end case;
end/
1.2 – Stored Procedures com laço de repetição (while)
Comando While – O laço de repetição do tipo WHILE executa um conjunto de instruções
SQL enquanto uma condição de parada não é atingida. A condição de parada deve retornar um valor
booleano e pode conter instruções SQL.
Sintaxe: 
WHILE <condição> DO <comandos SQL>; END WHILE; 
Exemplo: Vejamos uma stored procedure que aumenta o valor do total de fundos das agências
enquanto a média do total de fundos das mesmas for inferior a 10.000.000.
delimiter /
create procedure sp_atualiza_fundos_agencia()
begin
while (select avg(fundos) from agencia) < 10000000 do
update agencia set fundos = fundos + (fundos * 0.3);
end while;
end/
Exercícios
1 – Escreva stored procedures que executem as seguintes ações:
* Considerando a base de dados Banco:
a) Mostrar o nome da cidade e o número de clientes por cidade 
b) Mostrar o saldo das contas de um cliente. O código do cliente é um dado de entrada da sotred
procedure.
c) Mostrar o total de empréstimos de um determinado cliente. O código do cliente é um dado de
entrada da stored procedure.
d) Mostrar o total dos empréstimos por agência
e) Mostrar o saldo das contas por agência
* Considerando a base de dados Companhia:
a) Visualizar o número do projeto, o nome do projeto, o nome da localização e o nome do
departamento do mesmo.
b) Visualizar a matricula do empregado (ssn), o nome do empregado e os número de
dependentes por empregado.
c) Criar uma stored procedure que visualiza os mesmos dados acima, mas que agora recebe o
número da matricula do empregado como parâmetro de entrada.
d) Criar uma stored procedure que retorna o numero do projeto, o nome do projeto e o número de
horas gastas por projeto.
e) Criar uma stored procedure que retorne as informações sobre os empregados que tem salário
maior que um valor dado como parâmetro de entrada.
f) Criar uma stored procedure que apresente os gastos da empresa com salário, de acordo com o
sexo dos empregados.
g) Criar uma stored procedure que realiza um reajuste no salário dos empregados de acordo com a
seguinte regra: caso o empregado seja do departamento 1 e com salario maior que 3000, recebe 5%
de aumento; caso seja do departamento 2 recebe 4% de aumento; caso seja de outros departamentos
recebe 2% de aumento.
* Considerando a base de dados concurso:
a) Mostrar o total arrecadado com as inscrições do concurso
b) Mostrar o número decandidatos por município e por disciplina. Ordenar o resultado
primeiramente pelo nome do município e em seguida pelo nome da disciplina
c) Mostrar a classificação no concurso, para cada município, disciplina e tipo de inscrição. Os dados
a serem exibidos são os mostrados abaixo: