Exemplo - Emerson Barros

Stored Procedure,
Function and Trigger
Slide 1
Objetivos
1.
2.
3.
4.
Programando no banco de dados
Stored Procedure
Function
Trigger
Slide 2
1. Programando no banco de dados
1.1 Variáveis
1.2 Controle de fluxo
Slide 3
1.1 Variáveis
 Declaração de variáveis:
DECLARE @limit int
DECLARE @min int, @max int
 Atribuindo valor à variável:
SET @min = 0, @max = 100
SET @limit = 10
 Atribuindo valor à no bloco SQL:
SELECT @price = price FROM titles
WHERE title_id = 'PC2091'
Slide 4
1.2 Controle de fluxo
1.2.1
1.2.2
1.2.3
1.2.4
1.2.5
1.2.6
Slide 5
BEGIN…END
IF…ELSE
CASE … WHEN
RETURN [n]
WHILE
PRINT
1.2.1 BEGIN…END
 Define os blocos de código (início/fim)
 Outras linguagens de programação:
 C#, Java, C: { … }
 Pascal, Delphi: BEGIN … END
Slide 6
1.2.2 IF…ELSE
 Condicional
IF Condição
Código T-SQL | bloco de código
[ELSE
Código T-SQL | bloco de código ]
Exemplo:
IF (SELECT vendasAno FROM livros WHERE livro_id = 'PC1035') > 5000
PRINT ‘Vendas no ano são que R$5000,00 para PC1035.‘
Slide 7
1.2.3 CASE … WHEN
CASE input_expression
WHEN when_expression THEN result_expression
[WHEN when_expression THEN result_expression…n]
[ELSE else_result_expression ]
END
Exemplo:
SELECT CASE regra
WHEN '30' THEN ‘Pagar 30 dias após a nota final'
WHEN '60' THEN ' Pagar 60 dias após a nota final '
WHEN ‘No ato' THEN ‘Pagar quando receber a nota fiscal'
ELSE ‘Nenhum'
END as RegrasPagamentos FROM vendas
Outras linguagens de programação
C#, Java: Switch … Case ; VB: Select … Case
Slide 8
1.2.4 RETURN [n]
 Pode ser usado para Trigger, Procedure ou Function e
returna um valor.
1.2.5 WHILE
 Repete o bloco de código enquanto a condição for
verdadeira
WHILE Condição
Código SQL | bloco de códico
[BREAK] Código SQL | bloco de código
[CONTINUE]
Exemplo:
WHILE (SELECT AVG(comissao) FROM vendas) < 25
BEGIN
UPDATE vendas SET comissao = comissao * 1.05
IF (SELECT MAX(comissao)FROM vendas) > 27 BREAK
ELSE CONTINUE
END
SELECT MAX(comissao) AS “Comissão Máxima"
FROM vendas
Slide 10
1.2.6 PRINT
 Mostra mensagem no SQL Query Analyze (Console)
PRINT string
 Outras linguagens de programação:
 Java: System.out.print
 C#, VB.NET: Console.WriteLine
Slide 11
2. Stored Procedure
2.1 O que é uma Stored Procedure?
2.2 Stored Procedure vs. comando SQL
2.3 Criar, alterar e apagar uma procedure
Slide 12
2.1 O que é uma Store Procedure?
 Uma stored procedure é uma coleção de comandos T-SQL
que SQL Server compila em um plano de execução.
 A Procedure é armazenada em um area de cache da memoria
queando é executada pela primeira vez. Isso é feito para que
o SQL Server não precise recompilar ela cada vez que for ser
executada.
 Ela aceita parametros de entrada, parâmetros de saída e
pode retornar mensagens de sucesso ou falha.
Slide 13
2.2 Stored Procedure vs. Comando SQL
SQL Statement
Stored Procedure
Criação
- Verifica sintaxe
- Compila
Primeira execução
- Verifica sintaxe
- Compila
- Executa
- Returna dados
Primeira execução
- Execute
- Returna dados
Segunda execução
- Verifica sintaxe
- Compila
- Executa
- Returna dados
Segunda execução
- Executa
- Returna dados
Slide 14
2.3 Criar, alterar e apagar uma procedure
2.3.1 Criar uma Procedure
2.3.2 Alterar uma Procedure
2.3.3 Apagar uma Procedure
Slide 15
2.3.1 Create a Procedure
2.3.1.1
2.3.1.2
2.3.1.3
2.3.1.4
Slide 16
Sintaxe
Exemplo 1 (sem parametros)
Exemplo 2 (com parametros)
Exemplo 3 (usando RETURN)
2.3.1.1 Sintaxe
CREATE PROC[EDURE] nome_procedure
[ @nome_parametro tipo_parametro] [= default]
OUTPUT][,...,n]
AS
CódigoT-SQL (s)
Slide 17
2.3.1.2 Exemplo 1 (sem parametros)
CREATE PROC Membros_Departamento
AS
SELECT Dep_Nome, COUNT(Emp_ID) NumeroMembros
FROM Departamentos D, Empregados E
WHERE D.Dep_ID = E.Dep_ID
GROUP BY Dep_Nome
Run Procedure
Execute Membros_Departamento
Slide 18
2.3.1.3 Exemplo 2 (com parametros)
CREATE PROC Membros_Departamento @DeptNome varchar(50)
AS
SELECT Dep_Nome, COUNT(Emp_ID) NumeroEmpregados
FROM Departamentos D, Empregados E
WHERE D.Dep_ID = E.Dep_ID and Dep_Nome = @DeptNome
GROUP BY Dep_Nome
Run Procedure
Execute Membros_Departamento ‘Contabilidade’
Slide 19
2.3.1.4 Exemplo 3 (Using RETURN )
CREATE PROC GROUPLEADER_MEMBERS
@Cod_Emp varchar(10) = null
AS
IF @ Cod_Emp is null
BEGIN
PRINT ‘Por favor entre com o código do empregado!'
RETURN
END
SELECT * FROM Empregados
WHERE Cod_Emp = @ Cod_Emp
ORDER BY Nome
Slide 20
2.3.2 Update a Procedure
ALTER PROC[EDURE] procedure_name
[ @nome_paramtro tipo_parametro]
[= default] [OUTPUT]
[,...,n]
AS
código t-sql
Slide 21
2.3.3 Delete a Procedure
 DROP PROCEDURE nome_procedure
Slide 22
3. Function
3.1
3.2
3.3
3.4
O que é uma Function?
Scalar functions - Exemplo
Inline Table-valued Functions - Exemplo
Multi-statement Table-Valued Functions - Exemplo
Slide 23
3.1 O que é uma Function?
 SQL Server suporta três tipos de definição de funções:
 Scalar functions
 Inline table-valued functions
 Multi-statement table-valued functions
Slide 24
3.2 Scalar functions - Exemplo
CREATE FUNCTION ReceitaDiaria(@data datetime) Returns money
AS
BEGIN
DECLARE @total money
SELECT @total = sum(Quantidade * Preco)
FROM Pedidos_Venda s, Pedidos_Venda_Items si
WHERE s.Numero = si.Numero and year(Data) = year(@data)
and month(Data) = month(@data) and day(Data)= day(@Date)
RETURN @total
END
Use:
select ReceitaDiaria(GETDATE())
Slide 25
3.3 Inline Table-valued Functions - Exemplo
CREATE FUNCTION MediaPrecoPorItem (@ preco money = 0.0)
RETURNS table
AS
RETURN ( SELECT Descricao_Item, Preco_Item
FROM Itens
WHERE Preco_Item > @preco)
Use:
select * from MediaPrecoPorItem (15.00)
Slide 26
3.4 Multi-statement Table-Valued Functions - Exemplo
CREATE FUNCTION MediaPrecoPorItem (@ preco money = 0.0)
RETURNS @table table (Descricao varchar(50) null, Preco money null)
AS
begin
insert @table SELECT Descricao_Item, Preco_Item
FROM Itens
WHERE Preco_Item > @preco
return
end
Use:
select * from MediaPrecoPorItem(15.00)
Slide 27
4. Trigger
4.1
4.2
4.3
4.4
4.5
4.6
O que é uma Trigger?
Sintaxe de criação
Habilitar/Desabilitar
Tabelas de valores inseridos e excluídos
Exemplo
Outras funções
Slide 28
4.1 O que é uma Trigger?
 Trigger é um procedimento que é executado
automaticamente como parte de uma modificação de dados.
 A trigger é criada em uma tabela e associada com uma ou
mais ações ligadas com uma modificação de dados
(INSERT, UPDATE, or DELETE).
 Quando uma das ações para que a trigger está definida
ocorre, a trigger é acionada automaticamente
 Alguns exemplos de uso de triggers:





Slide 29
Manutenção de dados duplicados
Complexas restrições de coluna
Integridade referencial em cascata
Padrões complexos
Inter-banco de dados de integridade referencial
4.2 Sintaxe de criação
CREATE TRIGGER nome_trigger
ON <nome_tabela>
<{FOR | AFTER}>
{[DELETE] [,] [INSERT] [,] [UPDATE]}
AS
Código SQL [...n]
Slide 30
4.3 Habilitar/Desabilitar
 Sitaxe para desabilitar
Disable trigger <trigger_name> on <table_name>
 Sitaxe para habilitar
Enable trigger <trigger_name> on <table_name>
Slide 31
4.4 Deleted and Inserted tables
 Quando você cria uma trigger, você tem acesso a duas tabelas
temporárias (a tabela de valores apagados e inseridos). Eles são
representados como tabelas, mas diferentemente das tabelas de
banco de dados. Eles são armazenados na memória não no disco.
 Quando um insert, update ou delete é executed. Todos os dados
serão copiados para essa tabela com a mesma estrutura.
Insert
Update
novo
Inserted Table
novo
Delete
antigo
antigo
Deleted Table
 Os valores inseridas e excluídas são acessíveis apenas dentro da
trigger. Uma vez que a trigger é concluída, essas tabelas não
estão mais disponíveis.
Slide 32
4.5 Exemplo
CREATE TRIGGER Print_Update
ON Invetario_Bicicleta
FOR UPDATE
AS
PRINT “A tabela invertário de bicicletas foi
atualizada"
Slide 33
4.6 Outras Funções
 Ver conteúdo da trigger
sp_helptext <trigger name>
 Ver número de triggers na a table
sp_helptrigger <table name>
Slide 34
Slide 35