Triggers e Stored Procedure

(1)

(2)

Sumário

➢

Stored Procedures;

➢

Triggers;

(3)

Stored Procedures

➢ Coleção de comandos SQL

➢ Mecanismo semelhante ao RPC

➢ Código sob o controle do SGBD ➢ Passagem de parâmetros

➢ Desempenho melhor por estar

“mais próximo” dos dados

● Diminui tráfego de rede

(4)

Stored Procedures

➢

Funções

● Regras de negócio e integridade dos dados no SGBD ● Administração do sistema

● Lado “servidor” da lógica da aplicação

➢

Características

● Comandos SQL compartilhados por aplicações ● Melhor desempenho em ambientes C/S

(5)

Stored Procedures

➢

Menor flexibilidade

➢

Cada procedure é por default uma transação

separada

(6)

Triggers

insert update

delete

Evento s

Comando

SQL ProcedureStored

(7)

Triggers

Usar 650 parafusos

“estoque de parafusos: 850”

parafusos 1500 850

Se quantidade de parafusos em estoque é menor que 1000, então

= > comprar 1000

(8)

Postgres

➢ Diferente dos outros SGBDs:

● Store Procedures e triggers são tratados como funções. ● Presença de diversas linguagens procedurais para

programação de funções diretamente no banco de dados.

(9)

➢

Triggers (Gatilhos)

● Função trigger pode ser escrita em C ou em

qualquer outra linguagem procedural.

● Iremos utilizar o PL/pgSQL.

● Função trigger é uma função declarada sem

argumentos e retorna um tipo de dados trigger.

● Uma mesma função trigger pode ser utilizada

em vários triggers.

● Um trigger só pode ser definido depois que a

(10)

Postgres

➢

Triggers (Gatilhos)

● Ele é invocado (disparado) quando ocorre uma

tentativa de modificação nos dados da tabela (relação) à qual ele está associado (vinculado).

● Os triggers podem ser invocados por cada

linha afetada ou por cada comando INSERT, UPDATE ou DELETE.

● Podem ser definidos para executar antes ou

(11)

Postgres

➢

Triggers (Gatilhos)

● Os triggers são usados para garantir regras de

(12)

Sintaxe:

CREATE TRIGGER nome { BEFORE | AFTER } { evento [ OR ... ] } ON tabela [ FOR [ EACH ] { ROW | STATEMENT } ]

EXECUTE PROCEDURE nome_da_função ( argumentos )

Tigger Postgres

- A função de trigger deve ser declarada como uma função que não recebe argumentos e que retorna o tipo TRIGGER.

- Após criar a função de trigger, definimos o trigger pelo comando CREATE TRIGGER.

(13)

Sintaxe:

CREATE TRIGGER nome { BEFORE | AFTER } { evento [ OR ... ] } ON tabela [ FOR [ EACH ] { ROW | STATEMENT } ]

EXECUTE PROCEDURE nome_da_função ( argumentos )

Tigger Postgres

- A função de trigger deve ser declarada como uma função que não recebe argumentos e que retorna o tipo TRIGGER.

- Após criar a função de trigger, definimos o trigger pelo comando CREATE TRIGGER.

(14)

Entendendo a síntaxe...

● CREATE OR REPLACE: Sem este comando, ao

modificar a definição de uma função, você deve removê-la primeiro.

● AS ‘ ... ‘ : O corpo da função deve estar entre as aspas

simples.

● DECLARE estudante RECORD: a variável estudante

pode armazenar uma informação de uma linha.

● NEW.mat: Como o trigger é antes do INSERT, NEW

corresponde a nova linha a ser inserida.

● RAISE NOTICE: Usado para mostrar um erro. ● RETURN NULL: Skip a inserção da tupla.

(15)

Exemplo Triggers PL/pgSQL

● Suponha duas tabelas: Estudante e Exame. ● Estudantes fazem exames.

● Não utilizamos FOREIGN KEY na tabela

Exame.

● Escreva uma função trigger para forçar uma

restrição de integridade entre as tabelas Exame e Estudante.

(16)

Criação das tabelas

CREATE TABLE Estudante (mat INTEGER, name VARCHAR(20));

(17)

Criação do Trigger

CREATE TRIGGER rest_trigger

BEFORE INSERT ON Exame

FOR EACH ROW

(18)

Função trigger escrita em PL/pgSQL

CREATE OR REPLACE FUNCTION restr_integr() RETURNS TRIGGER AS $rest_trigger$

DECLARE estudantes RECORD; BEGIN

SELECT * INTO estudantes FROM Estudante WHERE NEW.mat = mat; IF NOT FOUND THEN

RAISE NOTICE \’Estudante inexistente! %\’, NEW.mat; RETURN NULL;

ELSE

RETURN NEW; END IF;

END;

(19)

Inserir tuplas…

➢

Inserir os seguintes estudantes:

(20)

➢ Ao tentar inserir na tabela de Exames, como o

estudante de mat=1 já existe, o trigger deverá permitir a inserção:

➢ Já a seguinte inserção, na tabela Exame não deve

ser permitida:

Agora, tente inserir em Exame...

Insert INTO Exame VALUES (1,’FBD I’);

(21)

(22)

➢ Qual a vantagem de utilizar PL/pgSQL?

● Praticidade da utilização de comandos SQLs integrados à

linguagem e na criação de cache dessas consultas.

➢ Oficialmente distribuidas com o servidor:

● PL/pgSQL, PL/TCL, PL/Python e PL/Perl.

➢ Podem ser instaladas:

● PL/Java, PL/Ruby, PL/R.

➢ Flexibilidade obtida separando o mecanismo do banco de

dados do executor das funções.

(23)

PL/pgSQL

➢Executa comandos SQL diretamente no corpo dos

procedimentos.

➢Segura, ambiente de execução é restrito ao SGBD.

DECLARE

... Declaração de Variáveis BEGIN

(24)

PL/pgSQL

IF condição 1 THEN

Seqüência de comandos 1 ELSIF condição 2 THEN

Seqüência de comandos 2 ELSE

Seqüência de comandos 3 END IF;

(25)

PL/pgSQL

LOOP

Seqüência de comandos END LOOP;

➢

Estruturas de Controle

LOOP

IF condição THEN

(26)

PL/pgSQL

WHILE condição LOOP

Seqüência de comandos END LOOP;

➢

Estruturas de Controle

FOR contador IN

[REVERSE] X..Y LOOP

(27)

PL/pgSQL

➢

Tratamento de Exceção:

RAISE NOTICE ‘%’, var ; (mensagem)

RAISE NOTICE ‘Texto qualquer’;

(28)

PL/pgSQL

➢

Custo é alto, usar somente se necessário

➢

Bloco de Exceção:

BEGIN comandos

EXCEPTION WHEN condição THEN

comandos de tratamento

WHEN condição THEN comandos de tratamento

(29)

PL/pgSQL

➢

Funções armazenadas (“stored procedures”)

(30)

PL/pgSQL

➢

Funções armazenadas:

CREATE FUNCTION atualizaValor ( valor varchar ) RETURNS boolean

CREATE [ OR REPLACE ] FUNCTION name ( [ [ argname ] argtype [, ...] ] )

RETURNS rettype

➢

Onde rettype pode ser :

1. Tipo primitivo

2. Estrutura composta

(31)

➢ Imprime a mensagem “Minha primeira rotina em

PL/pgSQL”:

Exemplo PL/pgSQL

CREATE OR REPLACE FUNCTION primeira_funcao() RETURNS VOID AS

$body$ BEGIN

RAISE NOTICE 'Minha primeira rotina em PL/pgSQL'; RETURN;

END; $body$

(32)

➢ Executando uma FUNCTION

Exemplo PL/pgSQL

(33)

PL/pgSQL

➢

Declarando variáveis:

Atribuindo valores: numero := 15;

Declarando variáveis: DECLARE

(34)

PL/pgSQL

CREATE OR REPLACE FUNCTION media_compras(pid_cliente Integer) RETURNS numeric AS

$body$ DECLARE linhaCliente Clientes%ROWTYPE; mediaCompras real; totalCompras real; periodo Integer; BEGIN

SELECT * INTO linhaCliente FROM clientes WHERE id_cliente = pid_cliente; periodo := age(current_date, linhaCliente.data_inclusao);

SELECT SUM(valor_total) INTO totalCompras FROM pedidos WHERE id_cliente = pid_cliente;

mediaCompras := totalCompras / periodo; RETURN mediaCompras;

END;

Variável armazena uma linha da tabela Clientes

(35)

Exemplo

➢ Na tabela Aluno

➢ Criar função armazenada ( “stored procedure”) para atualizar o valor currículo do aluno.

➢ Parâmetros

● valor novo para currículo ● Num_matricula

(36)

CREATE FUNCTION atualizaCurriculo(varchar valorNovo, varchar mat) RETURNS boolean AS

$$

BEGIN

UPDATE aluno SET curriculo = $1 WHERE num_matricula = $2;

RETURN FOUND; END;

$$

LANGUAGE 'plpgsql' ;

$1, $2 são parâmetros passados pela chamada da função

palavra reservada do sistema; booleano que retorna true sse houve alteração

(37)

PL/pgSQL

➢

Cursor:

➢ Áreas de memória utilizadas pelo PostgreSQL para armazenar registros enquanto eles são processados.

➢ Usados para percorrer as relacões e processar conjuntos de TUPLAS.

(38)

PL/pgSQL

cursor1 refcursor (30) ;

cursor2 CURSOR FOR

SELECT * FROM tab1 ;

cursor3 CURSOR (chave integer)

IS SELECT * FROM tab1

WHERE unique1 = chave; cursor1 refcursor;

(39)

Uso do cursor

1.

Declarar ( declare )

2.

Abrir ( open )

3.

Navegar/ Atribuir os valores ( fetch )

4.

Fechar ( close )

(40)

PL/pgSQL

➢ OPEN FOR SELECT

➢ OPEN FOR EXECUTE

➢

Abrir cursor:

OPEN cursor FOR SELECT query ;

(41)

Abrir cursor para SELECT

(42)

create function cursorLerAluno(varchar) returns text as $$

declare

c refcursor;

nm aluno.nome%type; cur aluno.curriculo%type; begin

OPEN c FOR SELECT nome, curriculo from aluno where num_matricula = $1;

fetch c into nm, cur; close c;

return nm || ','|| cur; end;

$$ language 'plpgsql';

(43)

PL/pgSQL

FETCH direction [ count ] { IN | FROM } cursor direction ::= { FORWARD | BACKWARD | RELATIVE }

count ::= { numrows | ALL | NEXT | PRIOR }

➢

FETCH Cursor:

(44)

CREATE OR REPLACE FUNCTION cursor_empregado() RETURNS void AS $BODY$

DECLARE

cursor1 CURSOR IS SELECT * FROM EMPREGADO; emp empregado%ROWTYPE;

BEGIN

open cursor1;

fetch cursor1 into emp; loop

exit when not found;

RAISE NOTICE ‘Empregado de id: %‘, emp.id; fetch cursor1 into emp;

end loop; close cursor1; END;

$BODY$

LANGUAGE 'plpgsql' VOLATILE ;

PL/pgSQL

Declara a váriável

cursor Abre o cursor

Coloca a próxima linha do cursor na váriável

(45)

Inserir tuplas…

➢

Inserir os seguintes estudantes:

(46)

➢ Ao tentar inserir na tabela de Exames, como o

estudante de mat=1 já existe, o trigger deverá permitir a inserção:

➢ Já a seguinte inserção, na tabela Exame não deve

ser permitida:

Agora, tente inserir em Exame...

Insert INTO Exame VALUES (1,’FBD I’);

(47)

declare

dataFrequencia char(20);

timestampFrequencia timestamp;

dataFrequencia = ano || '-' || mes || '-' || dia;

timestampFrequencia = CAST (dataFrequencia AS timestamp);

(48)

now();

date_part(year, freq_data);

CAST ($2 AS character);

timestampFrequencia = CAST ('2009-11-' || diaFrequencia AS timestamp);

CAST (date_part('month', freq_data) as integer);