Triggers-procedures

Fundamentos de Bancos de

Dados

José Maria Monteiro

Departamento de Computação

Universidade Federal do Ceará

Sumário



_{Stored Procedures;}

_Triggers;

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}

Aplicação Stored Procedures exec procedure retorna resultado

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}

Stored Procedures



_{Menor flexibilidade}



_{Pode piorar desempenho se o plano de execução}

não for periodicamente refeito



_{Cada procedure é por default uma transação}

separada



_{Falta de padronização/portabilidade!!!}

Triggers

Triggers

= > comprar 1000

Trigger - Exemplo

DEFINE TRIGGER Departamentos D, Empregados E,

Empregados G:

WHERE E.Salario > G.Salario AND E.CDep = D.Codigo AND

D.Gerente = G.CPF

ACTION_PROCEDURE(NOTIF_GERENTE(D.Gerente); Notificar o gerente se qualquer empregado do seu

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.

 _{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.

PL/pgSQL

_{Executa comandos SQL diretamente no corpo dos}

procedimentos.

_{Segura, ambiente de execução é restrito ao SGBD.} _{Uso de cache otimizado.}

_{A programação é feita em blocos:}

DECLARE

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

... Comando PL/pgSQL END

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;

PL/pgSQL

_{Estruturas de Controle}

PL/pgSQL

WHILE condição LOOP

Seqüência de comandos END LOOP;



_{Estruturas de Controle}

FOR contador IN

[REVERSE] X..Y LOOP

Seqüência de comandos END LOOP;

PL/pgSQL



_{Tratamento de Exceção:}

RAISE NOTICE ‘%’, var ; (mensagem)

RAISE NOTICE ‘Texto qualquer’;

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 END;

PL/pgSQL



_{Funções armazenadas (“stored procedures”)}

_Triggers

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 3. Tipo do domínio



_{Funções armazenadas:}

PL/pgSQL

CREATE FUNCTION primeira_funcao() RETURNS VOID AS $body$ BEGIN RETURN; END; $body$ LANGUAGE 'plpgsql';

 _{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$

LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION primeira_funcao() RETURNS VOID AS

$body$ BEGIN

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

END; $body$

 _{Executando uma FUNCTION}

Exemplo PL/pgSQL

select primeira_funcao()

PL/pgSQL

_{Declarando variáveis:}

PL/pgSQL



_{Executando SQL:}

CREATE OR REPLACE FUNCTION exclui_cliente(pid_cliente Integer) RETURNS Integer

AS $body$ DECLARE

vLinhas Integer DEFAULT 0; BEGIN

DELETE FROM clientes WHERE id_cliente = pid_cliente; GET DIAGNOSTICS vLinhas = ROW_COUNT;

RETURN vLinhas; END;

$body$

 _{Executando uma FUNCTION}

select exclui_cliente(3)

select exclui_cliente(3)

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;

$body$ LANGUAGE 'plpgsql';

Variável armazena uma linha da tabela Clientes

Variável armazena resultado da consulta Atribuição

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}

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' ;

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

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 dados.

 _{Criada por resultado de COMMIT, ROLLBACK,}

INSERT, SELECT , UPDATE, DELETE

PL/pgSQL

cursor1 refcursor

(30) ;

cursor2 CURSOR FOR

SELECT * FROM tab1 ;

cursor3 CURSOR (chave integer) IS SELECT *

FROM tab1

WHERE unique1 = chave;

cursor1

refcursor;

Uso do cursor

1.

Declarar ( declare )

Abrir ( open )

3.

Navegar/ Atribuir os valores ( fetch )

Fechar ( close )

* Postgres: invocação de função com cursor

deve ocorrer dentro de transação

PL/pgSQL

 _{OPEN FOR SELECT}

 _{OPEN FOR EXECUTE}



_{Abrir cursor:}

OPEN cursor FOR SELECT query ;

OPEN cursor FOR EXECUTE

Abrir cursor para SELECT



_{Cursor sem limite ( cursor refcursor )}

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';

Abrir cursor para EXECUTE

 _{Cursor sem limite}

 _{Palavra reservada EXECUTE}

 _{EXECUTE pede string ( usar apóstrofo )}  _{Situações de SQL dinâmico ( passagem dos}

parâmetros como tabela , comando )

 _{Pode-se usar uma variável que armazena o}

create or replace function cursorLerAluno(varchar) returns text as $$ declare c refcursor; nm aluno.nome%type; cur aluno.curriculo%type; begin

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

fetch c into nm, cur; close c;

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

$$

language 'plpgsql';

PL/pgSQL

FETCH direction [ count ] { IN | FROM } cursor

direction ::= { FORWARD | BACKWARD |

RELATIVE }

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



_{FETCH Cursor:}

create or replace function teste() returns boolean as $body$ declare c refcursor; reg aluno%rowtype; i integer; tot integer; begin

OPEN c FOR SELECT * from aluno ; SELECT count(*) INTO tot from aluno; i:=1;

FOR i IN 1..tot LOOP FETCH c INTO reg;

raise notice '%', reg.nome ;

raise notice '%', reg.num_matricula ; END LOOP; close c; RETURN FOUND; end; $body$ language 'plpgsql‘;

FETCH Cursor

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

cursor Abre o cursor

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



_{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}

sua função trigger já estiver criada.

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}

depois de qualquer modificação no banco de dados.

Postgres



_{Triggers (Gatilhos)}

 _{Os triggers são usados para garantir regras de}

negócio e integridade dos dados, ou para ações complexas, tais como automaticamente atualizar um resumo dos dados.

 _{Caso a execução de um trigger possa violar}

alguma restrição, então ele não será executado.

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.

 _{Estudante(mat integer, nome varchar(20))}  _{Exame(eid integer, enome varchar(15))}

Criação das tabelas

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

CREATE TABLE Exame (eid INTEGER, enome VARCHAR(20));

Função trigger escrita em PL/pgSQL

RETURNS trigger AS ‘

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; ‘ LANGUAGE ‘PLpgSQL’;

Criação do Trigger

CREATE TRIGGER rest_trigger

BEFORE INSERT ON Exame

FOR EACH ROW

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.}

Inserir tuplas…



_{Inserir os seguintes estudantes:}

Insert INTO Estudante VALUES (1,’José’); Insert INTO Estudante VALUES (2,’João’);

 _{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’);

declare

dataFrequencia char(20);

timestampFrequencia timestamp;

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

timestampFrequencia = CAST (dataFrequencia AS timestamp);

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);