Banco de Dados - Aula 13 - 25 de Outubro

SISTEMAS DE INFORMAÇÃO
Sistemas de Bancos de Dados
2º Semestre – 2010
Pedro Antonio Galvão Junior
E-mail: [email protected] Fone: 9531 - 7555
[email protected]
Versão 1.10.10 – Out/2010.
Comandos Transact SQL (T-SQL)
Stored Procedure(Procedimentos Armazenados)
Stored Procedure
• Stored Procedure é um conjunto de comandos, ao qual é atribuído um nome. Este conjunto fica
armazenado no Banco de Dados e pode ser chamado a qualquer momento tanto pelo SGBD (sistema
Gerenciador de Banco de Dados) quanto por um sistema que faz interface com o mesmo.
• A utilização de Stored Procedures é uma técnica eficiente de executarmos operações reetitivas. Ao
invés de digitar os comandos cada vez que determinada operação necessite ser executada, criamos
um Stored Procedure e o chamamos. Em um Stored Procedure também podemos ter estruturas de
controle e decisão, típicas das linguagens de programação. Em termos de desenvolvimento de
aplicações, também temos vantagnes com a utilização de Stored Procedures.
• A diferença entre a Stored Procedure e a Function, esta relacionada a obrigatoriedade que a
function tem em retornar valores.
Conhecendo os Tipos de
Stored Procedure
1. System: Localizada no Banco Master, criada pelo próprio SQL Server;
2. Local: Criada pelo próprio usuário, localizada dentro do banco de dados definido pelo
usuário;
3. Temporária Local: Criada temporáriamente pelo usuário, seu tempo de vida e utilização
esta relacionado com a sessão que o usuário esta trabalhando;
4. Temporário Global: Criada temporáriamente pelo usuário, seu tempo de vida e
utilização esta relacionado com a conexão que o usuário esta trabalhando;
5. Remota: Executada em outra máquina;
6. Extendida: Localizada fora do banco de dados, possuim uma código fonte(linguagem),
capaz de executar comando para retornar informações de outras fontes de dados;
Stored Procedure
• Nomenclatura e definição:
Banco de Dados
Caracter Inicial
Tipo
Master
SP
Sistema
Definido pelo
Usuário
SP ou P
Local
Definido pelo
Usuário
#
Temporária Local
Definido pelo
Usuário
##
Temporário Global
Definido pelo
Usuário
SP ou P
Remota
Master
XP
Extendida(Extended)
Trabalhando com Stored Procedure
• CREATE PROCEDURE nome_do_stored_procedure
[
{@parametro tipo_de_dados_parametro}[=valor_default] [output]
]
[,...n]
AS
comando1,
comando2,
comando3,
...,
comando2
GO
Trabalhando com Stored Procedure
• ALTER PROCEDURE nome_do_stored_procedure
[
{@parametro tipo_de_dados_parametro}[=valor_default] [output]
]
[,...n]
AS
comando1,
comando2,
comando3,
...,
comando2
GO
Trabalhando com Stored Procedure
• Excluíndo a Stored Procedure:
– Drop Procedure NomedaSuaProcedure;
• Executando a Stored Procedure:
– Execute NomedaSuaProcedure;
– Exec NomedaSuaProcedure;
– SP_ExecuteSQL N’NomedaSuaProcedure.
• Recompilar as alterações na Stored Procedure:
– SP_RECOMPILE NomedaSuaProcedure;
PRÁTICA - I
Comandos Transact SQL (T-SQL)
Trigger(Gatilho)
Trigger
•
O comando CREATE TRIGGER cria um gatilho. O gatilho fica associado à tabela
especificada e executa a função especificada nome_da_função quando ocorrem
determinados eventos. O gatilho pode ser especificado para disparar antes de tentar
realizar a operação na linha (antes das restrições serem verificadas e o comando
INSERT, UPDATE ou DELETE ser tentado), ou após a operação estar completa (após as
restrições serem verificadas e o comando INSERT, UPDATE ou DELETE ter completado).
•
Se o gatilho for disparado antes do evento, o gatilho pode fazer com que a operação
não seja realizada para a linha corrente, ou pode modificar a linha sendo inserida (para
as operações de INSERT e UPDATE somente). Se o gatilho for disparado após o evento,
todas as mudanças, incluindo a última inserção, atualização ou exclusão, estarão
"visíveis" para o gatilho.
•
Um gatilho que está marcado FOR EACH ROW é chamado uma vez para cada linha que
a operação modifica. Por exemplo, um comando DELETE afetando 10 linhas faz com
que todos os gatilhos ON DELETE da relação de destino sejam chamados 10 vezes, uma
vez para cada linha excluída.
Trigger
•
Diferentemente, um gatilho que está marcado FOR EACH STATEMENT somente
executa uma vez para uma determinada operação, não importando quantas linhas
sejam modificadas; em particular, uma operação que não modifica nenhuma linha
ainda assim resulta na execução de todos os gatilhos FOR EACH STATEMENT aplicáveis.
•
Se existirem vários gatilhos do mesmo tipo definidos para o mesmo evento, estes
serão disparados na ordem alfabética de seus nomes.
•
Considerações Importantes:
–
–
–
–
–
O Trigger não pode ser chamado diretamente;
O Trigger faz parte de um bloco transacional;
Obrigatório associar um trigger a uma table;
Quando a tabela é excluída o trigger também é excluído;
O Trigger depende exclusivamente dele e da tabela que esta associado.
Triggers e suas informações
• Consultando a relação de triggers existentes em um banco de dados:
– Select * from Sys.Triggers
• Visualizando o código de um trigger existente:
– Use Estoques
Go;
Select Name, Definition
From Sys.SQL_Modules Inner Join Sys.Triggers
On Sys.SQL_Modules.object_id = sys.triggers.object_id
Trabalhando com Triggers
• Criando um novo trigger:
USE AdventureWorks
GO;
IF OBJECT_ID ('Sales.reminder1', 'TR') IS NOT NULL
DROP TRIGGER Sales.reminder1
GO;
CREATE TRIGGER reminder1
ON Sales.Customer
AFTER INSERT, UPDATE
AS RAISERROR ('Notify Customer Relations', 16, 10)
GO
Trabalhando com Triggers
• Alterando um trigger existente:
USE AdventureWorks
GO;
ALTER TRIGGER reminder1
ON Sales.Customer
AFTER INSERT, UPDATE
AS RAISERROR ('Notify Customer Relations!!!!', 16, 10)
GO
Trabalhando com Triggers
• Excluíndo a Table e removendo o trigger existente:
– Drop Table Sales.Customer;
• Excluíndo somente o trigger existente:
– Drop Trigger reminder1;
PRÁTICA - II
Revisão
 Stored Procedures.
 Triggers.