2008.2
Stored Procedures
Renata Viegas
2008.2
Variáveis
• Conceito
– Objetos que armazenam valores de acordo com o
tipo especificado
• Declaração
DECLARE @<nome_var> <tipo>
– quando uma variável é criada, o seu valor inicial
é NULL
– Recebem valores com SET ou SELECT
2008.2
Variáveis
• Exemplo
DECLARE @quant
varchar(40)
int,
@estado
char(2),
SET @quant = 100
SELECT @estado = ‘PB’
SELECT @nome = (SELECT f.nome FROM
funcionario f WHERE f.cod = 1500)
SELECT @quant, @estado, @nome
GO
@nome
2008.2
Estrutura de Decisão
• Definição
– Estrutura condicional que impõe condições para a execução de alguns
comandos SQL especificados.
• Sintaxe
IF <condicao>
comandos sql
[ELSE
comandos sql ]
Para mais de um comando
no IF/ELSE, abra um bloco
com BEGIN e END
– A condição deve ser expressa por uma expressão lógica que retorna
Verdadeiro ou Falso e pode conter um SELECT, que deve estar entre
parênteses.
2008.2
Estrutura de Decisão
• Exemplo: Criar uma procedure para eliminar
um usuário cujo período de trabalho é igual a
4 horas. O código do usuário é passado por
parâmetro.
2008.2
Estrutura de Decisão - Exemplo
CREATE PROCEDURE spEliminaUser
@codUsuario varchar(8)
AS
IF EXISTS (SELECT codigo FROM usuario WHERE codigo= @codUsuario)
IF (SELECT periodo FROM usuario WHERE cod = @codUsuario) = 4
DELETE FROM usuario
WHERE codigo = @codUsuario
ELSE
PRINT ‘Usuario nao pode ser eliminado’
ELSE
PRINT (‘Usuário não existente’)
EXEC spEliminaUser ‘Jose’
2008.2
Estrutura de Decisão
• Exemplo: Criar uma procedure para eliminar
um usuário cujo código é passado por
parâmetro, desde que ele não tenha recebido
qualquer mensagem
2008.2
Estrutura de Decisão - Exemplo
CREATE PROCEDURE spEliminaUser2
@ cod_usuario char(8) AS
IF (SELECT COUNT(*) FROM recebedor
WHERE destinatario = @ cod_usuario) = 0
BEGIN
DELETE FROM usuario
WHERE cod = @ cod_usuario
PRINT ‘Usuario removido’
END
ELSE
PRINT ‘Usuario nao pode ser removido’
2008.2
Estrutura de Decisão
• Exemplo: Criar uma stored procedure que
informe se o preço de venda de um
determinado produto é maior, menor ou igual
a média do preço de venda de todos os
produtos da empresa.
2008.2
Estrutura de Decisão - Exemplo
CREATE PROCEDURE spPreco
@codProd int AS
DECLARE @media decimal(7,2)
SELECT @media = AVG(venda) FROM produto
IF (SELECT venda FROM produto WHERE codigo = @codProd) < @media
PRINT ‘Preço menor que a média’
ELSE
IF (SELECT venda FROM produto WHERE codigo = @codProd ) > @media
PRINT ‘Preco maior que a media’
ELSE
PRINT ‘Preco igual a media’
2008.2
Estrutura de Decisão
• Observações
– O SELECT correspondente à condição pode conter vários JOIN’s
– O comando PRINT pode exibir o conteúdo de variáveis + texto
DECLARE @codigo int, @nome varchar(40)
SELECT @codigo = 14
SET @nome = ‘Folha de Pagamento’
PRINT(‘Programa : ‘ + @nome)
PRINT(‘Codigo: ‘ + CONVERT(char, @codigo) )
2008.2
Exercícios
• Crie uma SP que, passando o código do
cliente como parâmetro, mostre a quantidade
de pedidos que o mesmo fez, apenas se o
cliente for de Recife
2008.2
Exercícios
create procedure QuantPedido
@cliente int
as
if exists (select codigo from cliente where codigo = @cliente)
if (select ci.nome from cidade ci, cliente c where ci.codigo = c.cidade and
c.codigo=@cliente)='Recife'
begin
select count(*) as quantidade
from cliente c, pedido pe
where c.codigo = pe.cliente and c.codigo = @cliente
end
else print ('Cliente não é de Recife')
else print ('Cliente não Cadastrado')
2008.2
Exercícios
• Crie uma SP que, passando como parâmetro
o código do funcionário, exiba uma
mensagem se o funcionário mora na mesma
cidade em que ele nasceu. Caso contrário,
faça com que a Procedure exiba outra
mensagem.
2008.2
Exercícios
create procedure CidadeNaturalidade
@funcionario int
As
if exists (select codigo from funcionario where codigo = @funcionario)
if ((select naturalidade from funcionario where codigo = @funcionario )=
(select cidade from funcionario where codigo = @funcionario))
print (‘Funcionário nasceu e mora na mesma cidade')
else print (‘Funcionário nasceu em uma cidade e mora em outra')
else print ('Funcionário não Cadastrado')
2008.2
Estrutura de Repetição: WHILE
• Definição
– Estrutura de repetição que executa um bloco de comandos
enquanto uma determinada condição for avaliada como
verdadeira.
• Sintaxe:
WHILE <condicao>
comandos sql
BREAK: força a saída do laço
[BREAK]
CONTINUE: reinicia o loop.
[CONTINUE]
– A condição deve ser expressa por uma expressão lógica que
retorna Verdadeiro ou Falso e pode conter um SELECT, que deve
estar entre parênteses
2008.2
Exemplo
declare @qtd int, @qtdIt int
set @qtd = (select count(*) from pedido)
set @qtdIt = (select count(*) from itens)
select @qtd, @qtdIt
While (@qtd >= @qtdIt)
Begin
Print ('Quantidade de Pedidos é maior')
set @qtdIt = @qtdIt + 1
Print ('Valor de @qtd é ' + convert (varchar(5), @qtd))
Print ('Valor de @qtdIt é ' + convert (varchar(5), @qtdIt))
If (@qtd < @qtdIt)
Begin
Print ('Vou parar')
Break
End
Else
Begin
Print ('Vou continuar')
Continue
End
End
Go
2008.2
Estrutura de Repetição: WHILE
• Exemplo: Criar uma stored procedure que reajuste
o preço dos produtos de acordo com as seguintes
regras:
– O percentual de aumento dos produtos será fornecido
como parâmetro
– Os produtos só sofrerão reajuste desde que a média de
preço seja inferior ao valor de uma média passado como
parâmetro
– Os produtos sofrerão reajustes repetidas vezes até que o
maior preço não ultrapasse o limite estabelecido por
parâmetro
2008.2
Estrutura de Repetição: WHILE
CREATE PROCEDURE spReajusta
@percentual numeric(5,2), @media money, @maior money
AS
WHILE (SELECT AVG(venda) FROM produto) < @media
BEGIN
UPDATE produto
SET venda = venda*(1+@percentual/100)
Print ('Atualiza o preço')
IF (SELECT MAX(venda) FROM produto) >= @maior
BREAK
ELSE
Print ('continua')
END