Exercícios Trigger e Procedure

Prévia do material em texto

�PAGE �
�PAGE �1�
Banco de Dados II - George Hamilton
Regras de Negócio em Banco de Dados
	A possibilidade de definir restrições de integridade nos SGBDs ao invés de implementá-las nos programas de aplicação está baseada no conceito não só de compartilhamento de dados, mas também, de códigos de programas. 
	Restrições de integridade embutidas nos sistemas de informação formam um componente importante de programas de aplicação, que podem ser distribuídas por todo o sistema. Isto tem a possibilidade de gerar incompletude e redundância dessas restrições de integridade, que podem ser especificadas diferentemente ou até mesmo se são implementadas consistentemente, esta propriedade pode se perder pela falta de coordenação de manutenção do códigos. Desta forma, existem fortes argumentos para implementação centralizada das restrições de integridade relevantes do sistema de informação no banco de dados, como procedimentos armazenados ("stored procedures"), gatilhos("triggers") e funções definidas por usuários.	 
	
1- Procedimento Armazenado ("Stored Procedures")
	Procedimento armazenado é um módulo de lógica procedimental mantido, administrado e executado pelo SGBD. A razão primária para ser utilizado é mover as regras de negócio da aplicação para o banco de dados. Um procedimento armazenado não é fisicamente associado a outro objeto do banco de dados, como os gatilhos de banco de dados, podendo acessar e modificar uma ou mais tabelas. O exemplo abaixo garante a inserção da ID e do nome do cliente em uma tabela de histórico de clientes (HISTCLI), sempre que um cliente é excluído da tabela CLIENTE:
CREATE PROCEDURE p-excluicliente (idcli IN INTEGER) AS
ultimo CHAR(50);
primeiro CHAR(50);
BEGIN
	SELECT sobrenome,prenome INTO ultimo,primeiro
	FROM cliente WHERE id=idcli;
	INSERT INTO histcli VALUES(idcli,ultimo,primeiro);
	DELETE FROM cliente WHERE id=idcli;
 EXCEPTION
	WHEN NO_DATA_FOUND THEN
		raise_application_error(-20130,'ID de Cliente Inválida');
END p-excluicliente;
	
	Para que o mecanismo funcione adequadamente, o privilégio para o uso do comando DELETE na tabela CLIENTE deve ser revogado aos usuários e aplicações, de modo que a exclusão na tabela deva ser realizada somente pela chamada ao procedimento armazenado p-excluicliente, que deve ser o privilégio de execução concedido a todos os usuários. O procedimento “raise_aplication_error” é específico da linguagem PL/SQL do Oracle, para tratamento de exceções.
2- Gatilhos (“Triggers”)
	Gatilhos (“triggers”) são procedimentos especiais dirigidos por eventos, armazenados e executados em um SGBD, vinculados a uma tabela. Um gatilho não pode ser invocado diretamente pelo usuário, mas apenas disparado automaticamente pelo SGBD, devido à ocorrência de um evento resultante de uma ação, normalmente, de modificação da tabela associada ao gatilho. Atualmente, apenas os eventos de manipulação de banco de dados (inserção, exclusão e alteração) são considerados na especificação dos gatilhos. 
Duas granularidades de processamento são consideradas, ou seja, quantas vezes o corpo do trigger será executado quando ocorrer o evento: ao nível de tuplas (FOR EACH ROW) e ao nível de comandos (FOR EACH STATEMENT), sendo este último o modo “default”. Ao nível de comandos, o corpo do trigger será executado apenas uma vez quando ocorrer o evento. Ao nível de tuplas, o corpo do trigger será executado uma vez para cada registro afetado pelo evento disparado (para cada linha).
A seguinte sintaxe mostra o modo de criação de triggers ao nível de comandos:
CREATE [ OR REPLACE ] TRIGGER Nome do Trigger
Momento de Disparo Evento1 [ OR Evento2 OR Evento 3]
ON Tabela
Bloco PL/SQL : [DECLARE]
 	 BEGIN
 [EXCEPTION]
 		 END;
Nome do Trigger
 Nome do Gatilho
Momento de Disparo
Indica o momento que o trigger irá disparar em relação ao evento: BEFORE ou AFTER
Evento
Identifica a operação de manipulação da tabela que causa o disparo do trigger: INSERT ou DELETE ou UPDATE [ OF nome de coluna ]
Tabela
Indica a tabela associada ao trigger
Bloco PL/SQL
É o corpo do trigger que define a ação a ser realizada pelo trigger, começando com as palavras reservadas DECLARE ou BEGIN e terminando com END.
A seguinte sintaxe mostra o modo de criação de triggers ao nível de tuplas (ou de linha):
CREATE [ OR REPLACE ] TRIGGER Nome do Trigger
Momento de Disparo Evento1 [ OR Evento2 OR Evento 3]
 ON Tabela
[ REFERENCING OLD AS old | NEW AS new]
FOR EACH ROW
[WHEN ... condição]
Bloco PL/SQL : [DECLARE]
 	 BEGIN
 [EXCEPTION]
 		 END;
Nome do Trigger
 Nome do Gatilho
Momento de Disparo
Indica o momento que o trigger irá disparar em relação ao evento: BEFORE ou AFTER
Evento
Identifica a operação de manipulação da tabela que causa o disparo do trigger: INSERT ou DELETE ou UPDATE [ OF nome de coluna ]
Tabela
Indica a tabela associada ao trigger
REFERENCING
 Especifica nomes para especificar os valores OLD e NEW do registro corrente (o default é OLD e NEW)
WHEN
Especifica a restrição do trigger. Este predicado condicional é avaliado a cada registro para determinar se o corpo do trigger será executado.
Bloco PL/SQL
É o corpo do trigger que define a ação a ser realizada pelo trigger, começando com as palavras reservadas DECLARE ou BEGIN e terminando com END.
O uso de qualificadores new e old, dentro de um trigger de linha, referencia o valor de uma coluna antes e depois de ser modificada :
Operação de Manipulação (DML)
Valor OLD
Valor NEW
INSERT
null
Valor inserido
UPDATE
Valor antes de ser atualizado
Valor após a atualização
DELETE
Valor antes de ser excluído
null
O exemplo abaixo utiliza a mesma lógica do procedimento anterior para implementar um gatilho no Oracle:
	
CREATE TRIGGER t-excluicliente
BEFORE DELETE ON cliente
FOR EACH ROW
BEGIN
	INSERT INTO histcli VALUES (:old.id,:old.sobrenome,:old.prenome);
END;
 
	A diferença básica entre o gatilho t-excluicliente e o procedimento p-excluicliente está na forma de serem invocados, pois o procedimento deve ser chamado explicitamente pelo usuário ou aplicação, enquanto o gatilho é disparado automaticamente, cada vez que for executado o comando DELETE na tabela CLIENTE.
	O gatilho normalmente é usado para:
Executar uma ação desejada, prevista na linguagem SQL como ação de restrição de integridade declarativa, do tipo integridade referencial, quando o SGBD utilizado não os suporta. Por exemplo, o SGBD Oracle não permite ações de restrição de integridade declarativa para: update CASCADE, update e delete SET NULL, update e delete SET DEFAULT. Daí ser necessário implementá-las como gatilhos;
Cumprir integridade referencial quando tabelas filha e pai estão em diferentes nós de um banco de dados distribuído;
Prover auditoria sobre manipulações no banco de dados;
Gerar automaticamente valores de colunas derivadas, principalmente quando várias tabelas são utilizadas;
Manter o sincronismo de replicação de tabelas;
Implementar regras de negócio sob forma ativa.
3- Função
Uma função definida pelo usuário computa e retorna um resultado baseado sobre um conjunto de valores de entrada. Uma vez definida e criada dentro do SGBD, torna-se disponível para ser utilizada até mesmo em sentenças SQL. No exemplo abaixo, a função procuraid retorna o número da identidade (id) do cliente, quando chamada com os parâmetros de entrada de primeiro e último nomes do cliente. O comando SELECT a seguir executa uma chamada à função na cláusula WHERE.
CREATE FUNCTION procuraid(primeiro IN CHAR(50), ultimo IN CHAR(50))
RETURN INTEGER AS
idcli INTEGER;
BEGIN
	SELECT id INTO idcli FROM cliente
	WHERE sobrenome=ultimo AND prenome=primeiro;
	RETURN idcli;
 EXCEPTION
	WHEN NO_DATA_FOUND THEN
		raise_application_error(-20130,'ID de Cliente Inválida');
END procuraid;
SELECT *
FROM pedidos
WHERE id_cliente=procuraid('Renata','Terra');
4- Recomendações
	A utilização dessasfacilidades, para manutenção da integridade dos dados, deve ser bem planejada para não ocorrer inconvenientes, especialmente problemas de desempenho e inconsistência de regras. Algumas premissas e recomendações devem ser seguidas para um bom planejamento da manutenção de integridade:
Não utilizar gatilhos e procedimentos armazenados para duplicar a funcionalidade já construída no SGBD, como por exemplo, usar gatilhos para impor regras que podem ser facilmente cumpridas utilizando restrições de integridade declarativas;
Regra de negócio complexa não pode ser cumprida por restrição de integridade declarativa, sendo melhor definida por gatilho ou procedimento armazenado;
Utilizar gatilho para definir regra de negócio, quando todas as aplicações que acessam o banco de dados devam respeitar esta integridade, isto é, quando a regra é global. No entanto, havendo possibilidade de aplicar uma regra para apenas algumas aplicações, a utilização de procedimento armazenado é mais conveniente, pois as demais aplicações poderão acessar o banco de dados sem serem submetidas a esta regra de integridade.
5- Arquiteturas para o Compartilhamento de Regras
	Procedimentos armazenados, gatilhos, restrições declarativas e funções são definidos em um banco de dados, como os outros objetos, tais como tabelas, visões e índices, os quais são controlados pelo SGBD. Esses objetos são normalmente referenciados coletivamente como Objetos de Código do Servidor (SCOs-"Server Code Objects"), porque são realmente códigos de programas que estão armazenados e mantidos normalmente pelo servidor de banco de dados como objetos de banco de dados.
	Os SCOs possuem diversas vantagens em relação aos outros meios de manutenção de integridade dos dados:
Pelo fato dos SCOs residirem em um único lugar, ou seja no SGBD, fica mais fácil mantê-los, já que as modificações em seus códigos não acarretará à necessidade de se propagar para as diversas estações cliente. Deste modo, em um ambiente cliente/servidor, a estação cliente envolve-se primariamente com a interface do usuário e o computador servidor com o processamento dos dados;
Desempenho superior, devido à semântica da declaração da restrição de integridade ser claramente definida no SGBD, fazendo com que o otimizador de consultas do SGBD tenha um conhecimento pré-definido de como devem ser realizadas as restrições;
Flexibilidade para carga de dados, pois as restrições de integridade podem ser temporariamente desabilitadas para que grande quantidade de dados possa ser introduzida, sem sobrecarga de verificação das restrições. Após finalização desta entrada de dados, as restrições podem ser habilitadas. 
	As restrições declarativas, os procedimentos armazenados, os gatilhos e funções são mantidos no catálogo do SGBD. A extração dessas informações possibilita aos usuários entenderem melhor como os SCOs devem ser projetados.
	Em um ambiente cliente/servidor consegue-se distinguir melhor os SCOs pelos componentes que formam a arquitetura de uma aplicação, pois esta pode ser vista como um conjunto de funções que possibilita ao usuário o acesso aos dados, através de funções:
De lógica da interface do usuário, que são responsáveis pela apresentação e pelas atividades de entrada e saída de dados do usuário final.
De lógica do negócio, que são, em sua essência, a própria razão da existência da aplicação, através de implementações de regras de negócio e práticas administrativas de uma organização.
De gerência dos dados, que compreendem as funções de manipulação dos dados (responsáveis pelo armazenamento e recuperação dos dados) e as funções que realizam os acessos a dados (provêem o acesso físico aos dados).
	
A arquitetura cliente/servidor é definida pela distribuição desses componentes lógicos pelas diferentes plataformas, estabelecendo os tipos de arquitetura: de uma camada (centralizando todos os componentes) ou de múltiplas camadas (componentes lógicos distribuídos por duas ou mais plataformas).
	A arquitetura de duas camadas (“two tiers”) possui a gerência remota de dados , onde toda a gerência de dados está localizada no servidor e as funções relacionadas com a lógica da interface do usuário concentradas no cliente, podendo a lógica do negócio estar distribuída entre o cliente e o servidor, conforme apresentada na Figura 5.1.
FIGURA 5.1- Arquitetura cliente/servidor de duas camadas.
	O ambiente cliente/servidor move-se em direção à arquitetura de três camadas (“three tiers”), onde as funções de negócio compartilhadas residem em um servidor dedicado e as funções de gerência de dados continuam em um SGBD presente em outra plataforma. Observa-se uma camada intermediária, na Figura 5.2, que atua como fornecedora de serviços para os programas de aplicação localizados nas estações clientes e que, ao mesmo tempo, atua como cliente do servidor de banco de dados.
FIGURA 5.2- Arquitetura cliente/servidor de três camadas
6- Exercícios
A seguir, serão realizados exercícios de procedimentos armazenados e gatilhos em cima do esquema abaixo, já discutido nos exercícios de SQL.
Em cima da tabela de EMPREGADO_JN abaixo, faça um trigger de auditoria que armazene as informações históricas do empregado, bem como, o evento( delete ou update) que disparou este trigger, o usuário responsável pela alteração e a data do sistema.
CREATE TABLE EMPREGADO_JN (
 Operacao 		VARCHAR2(3) NOT NULL,
 Usuario 		VARCHAR2(30) NOT NULL,
 Data 	 	DATE NOT NULL,
 CPF 		CHAR(12) NOT NULL,
 Num_Departamento SMALLINT NOT NULL,
 Nome 	VARCHAR(30) NULL,
 Data_nascimento 	DATE NULL,
 Endereco 	VARCHAR(40) NULL,
 Sexo 	CHAR(1) NULL,
 Salario 	DECIMAL(10,2) NULL,
 Supervisor 	CHAR(12) NULL);
2) Faça um trigger para manter a faixa salarial em valores mínimo e máximo já estabelecidos anteriormente
3) Faça um trigger que não permita a diminuição salarial ou o aumento superior a 50%, emitindo uma mensagem de erro.
4) Faça um trigger para derivar automaticamente o valor do número de empregados em um determinado departamento.
5) Faça um trigger para manter o mínimo de 10 horas que um funcionário poderá trabalhar em um projeto.
6) Faça um trigger para exclusão em cascata do empregado, das suas alocações em trabalhos de projetos e de seus dependentes.
7) Faça um trigger de segurança, para restringir o horário de manipulação da tabela de empregados, entre às 08:00 e 18:00 horas dos dias úteis.
8) Faça uma procedure para atualizar o salário de um empregado.
9) Faça uma procedure para excluir registros da tabela empregados, informando a quantidade excluída.
Faça uma procedure para atualizar o nome do departamento