Aula 10 - Proc + trig + Fun + view + trans

Programação em Banco

de Dados

2

Views

Views

 É uma “tabela virtual” criada a partir de uma consulta, para

representar uma determinada visão dos dados.

 Uma view pode ser consultada da mesma forma que uma tabela,

mas sua atualização está sujeita a certas regras.

 Embora consigamos visualizar os registros através da view, os

4

Views

 Para que utilizar views:

 Definir a percepção que um usuário ou aplicação possui dos

dados

 Simplificar a construção de consultas, definindo consultas

frequentes ou complexas

 Possibilitar a visualização de maneira diferenciada dos mesmos

dados

 Restringir o acesso aos dados: um usuário que não possua

Views

 Criando views – exemplo:

CREATE VIEW vw_empregado AS

SELECT

p.PrimeiroNome, d.IDEquipe,

d.DataContratacao FROM

Professores P

JOIN Disciplina D ON D.IDProf = P.IDProf;

6

Views

 Alterando views – exemplo:

ALTER VIEW vw_empregado AS SELECT p.PrimeiroNome, p.Sobrenome, d.IDEquipe, d.DataContratacao FROM Professores P

JOIN Disciplina D ON D.IDProf = P.IDProf;

 _{Excluindo views – exemplo:}

Stored Procedure

STORED PROCEDURES /

PROCEDIMENTOS

8

Stored Procedure

 Stored Procedures nada mais são do que um conjunto de

instruções SQL que são executadas dentro do banco de dados. É como escrever um programa dentro do próprio banco de dados para executar tudo lá dentro.

 Podemos conceituar Procedures ou Stored Procedures

(procedimentos armazenados) como blocos de comandos SQL agrupados como uma unidade que executam um conjunto de operações e tarefas relacionadas.

 _{Stored Procedures são procedimentos executados no servidor, e}

Stored Procedure

 São códigos armazenados no servidor que podem ser executados a

qualquer momento

 Uma stored procedure pode receber e enviar parâmetros de/para

uma aplicação

 São armazenadas pré-compiladas, o que melhora sua performance

10

Stored Procedure

 Sintaxe:

CREATE PROCEDURE <nome_procedure>

(parametro<tipo>[parametro <tipo>] //parâmetros AS

BEGIN

Stored Procedure

 Exemplo:

CREATE PROC Departments_Members()

SELECT Dep_Name, COUNT(Emp_ID) NumberOfMember

FROM Departments D, Employees E WHERE D.Dep_ID = E.Dep_ID

GROUP BY Dep_Name

_Executando:

Execute Departments_Members

12

Stored Procedure

 Exemplo (recebendo parâmetro):

CREATE PROCEDURE sp_ObtemInventario (local varchar(10))

SELECT produto, quantidade FROM inventario

WHERE deposito = local;



EXECUTE nome_procedure

call nome_procedure (mysql)



EXECUTE sp_ObtemInventario ‘Belo Horizonte’

Stored Procedure

 _{Comandos de decisão:}

IF <teste> BEGIN

Comandos END

ELSE

BEGIN

Comandos END

_{Declarar variáveis:}

14

Stored Procedure

 _{Comando de repetição}

WHILE <teste> BEGIN

Stored Procedure



_{Um exemplo mais completo:}

CREATE PROCEDURE sp_retorna_quantidade

@estado char(2)

AS

BEGIN

DECLARE @quantidade INT

SELECT

@quantidade = count(*) FROM

lojas WHERE

16

Triggers

Triggers

 Trigger é utilizada para poder colocar uma determinada regra de

negócio da aplicação.

 Os Triggers padrão são códigos que são disparadas

automaticamente quando uma operação como um: - INSERT

- UPDATE - DELETE

 Quando ocorre o disparo:

- BEFORE: antes da execução do comando; - AFTER: depois da execução do comando;

 Aplicação:

18

Triggers

 Sintaxe:

CREATE TRIGGER <nome_trigger> { BEFORE | AFTER }

{ event [ OR event ... ] } ON <nome_tabela>

{FOR EACH ROW<condição>} BEGIN

<rotina> END;

 Buscando o valor das colunas:

Triggers

 Exemplo:

CREATE TRIGGER del_dependente BEFORE

DELETE

ON FUNCIONARIO FOR EACH ROW

DELETE FROM dependente WHERE matfunc = :old.matfunc;

 Excluindo uma trigger:

Sintaxe: DROP TRIGGER <nome_trigger>;

20

Funções

Funções

 É um rotina armazenada que retorna um valor

 Não podem ser utilizadas para alterar dados em tabelas

 Podem ser chamadas por uma query, por exemplo

Semelhantes a funções internas

 _{Conteúdo semelhante a um procedimento armazenado}

22

Funções



_Sintaxe:

CREATE FUNCTION nome

[ (@param 1 tipo1 [=valor_padrao1 RETURNS tipo_retorno

BEGIN

Funções



_Exemplo:

CREATE FUNCTION fn_CustomerNamesInRegion ( @RegionParameter INT )

RETURNS table AS

RETURN (

SELECT *

FROM Sales.Customer

WHERE TerritoryID = @RegionParameter )

24

Funções



Alterando funções:

ALTER FUNCTION fn_NewRegion <Novo conteúdo de função>



Descartando funções

Transações

26

Transações

 Para evitar que ocorram conflitos entre operações concorrentes,

bem como seja possível manter a integridade do banco de dados, é suportado o conceito de transação de banco de dados

 Um transação é um conjunto coeso de operações de banco de

dados.

 “Transação é um programa em execução que forma uma unidade

Transações

 Transações devem obedecer aos requisitos ACID:

 Atomicidade: os comandos de uma transação devem ser

executados como um todo. Caso algum evento impeça esta

execução, as atualizações de todos os comandos (mesmo os já executados) não devem ser efetivadas. Deve garantir que toda transação iniciada, ou termina com sucesso ou é desfeita.

 Consistência: nenhuma transação pode ter suas atualizações

28

Transações

 Transações devem obedecer aos requisitos ACID (cont.):

 Independência: a execução de uma transação

concorrentemente a outra não deve causar interferências, ou seja, a execução de uma transação não pode interferir em outras transações executadas concorrentemente.

 Durabilidade: após atingir o final com sucesso, as atualizações

de uma transação devem ser garantidas indefinidamente no

Transações

A execução de uma transação envolve os seguintes comandos:

– BEGIN TRANSACTION

– COMMIT

30

Transações

Exemplo: O cliente 123, quer retirar 10.000 de sua conta para fazer um investimento.

BEGIN TRANSACTION UPDATE conta

SET saldo_conta = saldo_conta 10000‐ WHERE cod_cliente = 123

UPDATE investimento

SET saldo_investimento = saldo_investimento + 10000 WHERE cod_cliente = 123

Transações

 Caso a transação viole alguma restrição de integridade definida ou outra falha qualquer aconteça, suas atualizações serão desfeitas.

Resumindo o que vimos até aqui:

 BEGIN TRANSACTION Inicia uma transação;

 COMMIT

Fecha a transação em curso e torna definitivas as alterações efetuadas sobre o banco de dados durante essa transação; libera todos os locks

 _ROLLBACK

32

Transações

 Locks:

 Os mecanismos de locking e de gestão de transações estão

ligados entre si num SGBD relacional

 O sistema de locking é utilizado para sincronizar o acesso

concorrente de vários usuários aos mesmos dados

 Gerenciam a concorrência, ou seja, operações alterando os

Transações

 Deadlocks:

 Erro que pode acontecer em qualquer sistema de

multiprocessamento ou multi-threading, sendo banco de dados ou não

 Um deadlock ocorre quando duas ou mais operações

34

Transações

 Deadlocks (cont.):

 Exemplo:

• A transação X adquire um lock de leitura do registro 1.

• A transação Y adquire um lock de leitura do registro 2.

• A transação X solicita um lock de escrita sobre o registo 2 e fica bloqueada até que a transação Y termine e liberte o lock que possui.

Transações

 Liberando deadlocks:

• O SGBD implementa um mecanismo – Deadlock Monitor – que periodicamente analisa as transações ativas para

detectar travas.

• Quando uma condição de dealock é encontrada, o monitor termina automaticamente com erro em uma das transações de forma que a outra transação possa terminar

normalmente.

Dúvidas?