PROGRAMAÇÃO EM BANCO DADOS Stored Procedure e Trigger

PROGRAMAÇÃO EM BANCO DADOS
Stored Procedure e Trigger
A tecnologia de banco de dados permite persistir dados de forma a compartilha-los com
varias aplicações.
Aplicação 1 aplicação 2 aplicação 3
SGDB
Banco de
Dados
Por meio dos SGBD - Sistema Gerenciador de Banco de Dados - é possível gerenciar o
acesso a dados em um BD.
A SQL - linguagem de consulta estruturada - , é uma linguagem padrão ANSI para
manipular dados por meio de um sistema gerenciador de banco de dados.
A SQL pode ser dividida em 3 sub-linguagens:
 DDL - linguagem de definição de dados
 DCL - linguagem de controle de dados
 DML - linguagem de manipulação de dados
Os SGDB ‘devem’ disponibilizar recursos para manipulação de dados por meio de PL
(linguagens procedimentais), já que a SQL é uma linguagem não procedimental.
Por exemplo:
Linguagem procedimental
linguagem não procedimenta
---------------------------------------------------------------------------------------Readln(y);
Y= x*x;
Println(x);
select x,y
f
rom raízes
where y = x*x
Println(y);
---------------------------------------------------------------------------------------Nota de Aula
Prof. Sidney Vieira
1
A programação em PL é feita por meio de function(função), Store Procedure
(procedimentos armazenada) e Trigger (gatilho). Cada SGDB tem sua maneira própria para
tratar esses elementos de programação.
As sintaxes apresentadas a seguir referem-se ao ambiente Postgresql.
Stored Procedure
O postgresql emprega o conceito de function para definir stored procedure
( procedimentos armazenados)
Sintaxe básica:
Definição da função:
-----------------------------------------------------------------------------------------------------------------Create function nome_da_função (tipo_dado_variavel1, tipo_dado_variavel2, …
tipo_dado_variavelN)
Return tipo AS
'<corpo da função>'
LANGUAGE `nome da linguagem usada no corpo`
--------------------------------------------------------------------------------------------------------------
Dentre as linguagens possíveis para o postgresql temos:
SQL
PLPGSQL
C
PERL
TEL
PYTHONU
uso da função:
--------------------------------------------------------------------------------------------------------------------------------nome_da_função(valor_para_variavel1,valor_para_variavel1, ..., valor_para_variavelN)
--------------------------------------------------------------------------------------------------------------------------------A aspa no nome da linguagem pode ser opcional, depende da versão do Postgresql (no
8.3 ela já é opcional)
Quando definimos que a linguagem de uma função e sql, LANGUAGE SQL, só podemso
empregar comandos da sql em seu corpo.
Nota de Aula
Prof. Sidney Vieira
2
A definição do corpo da função depende da linguagem para plpgsql, devendo ser
delimitada por aspas simples ' ou cifrão duplo $$. Obedece a seguinte sintaxe:
Uso de ' para delimitar o corpo da função
Uso de '$$ para delimitar o corpo da função
'declare
$$declare
variavel1 tipo_da_varialvel1,
variavel1 tipo_da_varialvel1,
variavel2 tipo_da_variavel2,
variavel2 tipo_da_variavel2,
...
...
BEGIN
BEGIN
INSTRUÇÃO 1
INSTRUÇÃO 1
INSTRUÇÃO 2
INSTRUÇÃO 2
RETURN VALOR RETORNO
RETURN VALOR RETORNO
END;'
END;$$
Obs:
1 – a declaração de variáveis, por meio da palavra reservada declare, é opcional
2 - Para que uma função retorne mais de um valor, mais de uma linha de uma tabela, o
tipo de dado no retorno return tipo deve ser SETOF nome_tabela
3 - As variáveis que correspondem aos n-valores passados como parâmetros para a
função devem ser referenciadas como $1, $2, $3, .. $n
4 – Quando o corpo da função for definido com o uso de aspas simples devemos
delimitar a função com $$ para que o compilador não interprete a aspa simples do corpo
da função como fim da função.
Exemplos:
1 - Função sem parâmetros de entrada
primeiro: definição da função
Create function dois()
returns integer
as `select 2;’
language sql;
segundo: uso da função
Select dois();
Nota de Aula
Prof. Sidney Vieira
3
2 - função com parâmetros de entrada
primeiro: criação da função
create funcition media(decimal,decimal)
returns decimal as
$$ select ($1+$2)/2; $$
language sql;
segundo: uso da função
caso1
select media(10,8);
caso2
select media(8,9);
3 - Função que totaliza os valores de itens de uma nota de venda da tabela item_nota
CREATE FUNCTION totalNota(INTEGER)
RETURNS INTEGER AS '
SELECT SUM(preco) FROM item_Nota WHERE numNota = $1;
'
LANGUAGE 'SQL';
4 – Função maior salário de tabela funcionário
Etapas:
4.1 – criar a tabela
create table func(cod integer, nome varchar(30), salário decimal(10,2), comissao
decimal(10,2));
4.2 inserir dados na tabela criada
insert into func values(1, 'ZE',1000,500);
insert into func values(2, 'IVO',1500,200);
insert into func values(3, 'EVA',1800,100);
4.3 crie uma função maior_salario, para exibir o maior salario
create function maior_salario() returns decimal
as 'select Max(salario) from func;' language sql;
Nota de Aula
Prof. Sidney Vieira
4
4.4 usar a função que retorna o maior salario
select maior_salario();
5 - Função que concatena textos
Create function soma(text, text) returns char as
$$
Declare
resultado text;
Begin
resultado := $1 || $2;
return resultado;
End;$$
language 'plpgsql';
usando a função: select soma('Sidney ','Silva');
6 – Função que retorna o nome dos clientes que fizeram cadastro hoje( data corrente)
CREATE FUNCTION cadastro_hoje()
RETURNS SETOF clientes AS
'
SELECT nome FROM clientes WHERE datacadastro = current_date;
'
LANGUAGE 'SQL';
7- Função que disponibilize o maior valor de venda.
create function maiorvenda() returns decimal
as 'select Max(valor) from venda;' language sql;
usando a função: select maiorvenda();
8- Função que disponibilize o valor total obtido com vendas
create function valortotal() returns decimal
as 'select sum(valor) from venda;'
language sql;
usando a função:
select valortotal();
9- Crie um “store procedure” que disponibilize data da ultima venda
Nota de Aula
Prof. Sidney Vieira
5
create function dataultimavenda() returns date
as 'select max(data) from venda;'
language sql;
usando a função:
select dataultimavenda();
TRIGGER
Uma função de gatilho, trigger, é uma ação que o SGBD deve desencadear antes ou
depois de um dos seguintes comandos: insert, update ou delete.
Sintaxe:
Create trigger nome_gatilho {before ou after} {evento[or evento]} on table for each {row
ou STATEMENT } execute procedure nome_função (v1,v2,...,vn)
Em gatilho onde tem-se FOR EACH ROW o procedimento em execute procedure é
chamado uma vez para cada linha que a operação modifica. Já se tivermos FOR EACH
STATEMENT ocorrerá somente uma chamada ao procedimento descrito em execute
procedure , não importando quantas linhas sejam modificadas .
Variáveis disponíveis no ambiente de programação - PL
NEW → Tipo de dado RECORD; variável contendo a nova linha do banco de dados, para as
operações de INSERT/UPDATE nos gatilhos no nível de linha.
OLD → Tipo de dado RECORD; variável contendo a antiga linha do banco de dados, para as
operações de UPDATE/DELETE nos gatilhos no nível de linha. .
TG_NAME → Tipo de dado name; variável contendo o nome do gatilho disparado.
TG_WHEN → Tipo de dado text; uma cadeia de caracteres contendo BEFORE ou AFTER,
dependendo da definição do gatilho.
TG_LEVEL → Tipo de dado text; uma cadeia de caracteres contendo ROW ou
STATEMENT, dependendo da definição do gatilho.
TG_OP → Tipo de dado text; uma cadeia de caracteres contendo INSERT, UPDATE, ou
DELETE, informando para qual operação o gatilho foi disparado.
TG_RELID → Tipo de dado oid; o ID de objeto da tabela que causou o disparo do gatilho.
Nota de Aula
Prof. Sidney Vieira
6
TG_RELNAME → Tipo de dado name; o nome da tabela que causou o disparo do gatilho.
TG_NARGS → Tipo de dado integer; o número de argumentos fornecidos ao procedimento de
gatilho na instrução CREATE TRIGGER.
TG_ARGV[] → Tipo de dado matriz de text; os argumentos da instrução CREATE TRIGGER. O
contador do índice começa por 0. Índices inválidos (menor que 0 ou maior ou igual a tg_nargs)
resultam em um valor nulo.
Exemplos:
1 – Gatilho que após efetuar uma inclusão, alteração ou exclusão na tabela func
armazema dados para auditoria na tabela func_auditoria
Primeiro – criar a tabela func_auditoria
--------------------------------------------------------------------------------------------------------------create table func_auditoria( operacao varchar(1), usuario varchar(30), data timestamp,
nome_func varchar(40), salario decimal(10,2));
------------------------------------------------------------------------------------------------------------Segundo – criar a função para o gatilho
--------------------------------------------------------------------------------------------------------------CREATE FUNCTION processa_audit_func() RETURNS TRIGGER AS $$
BEGIN
--- Cria uma linha na tabela func_auditoria para refletir a operação
-- realizada na tabela func. Utiliza a variável especial TG_OP
-- para descobrir a operação sendo realizada.
-IF (TG_OP = 'DELETE') THEN
INSERT INTO func_auditoria values('E', user, now(), OLD.nome, OLD.salario);
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO func_auditoria values('A', user, now(), OLD.nome, OLD.salario);
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO func_auditoria values ('I', user, now(), NEW.nome, NEW.salario);
RETURN NEW;
Nota de Aula
Prof. Sidney Vieira
7
END IF;
RETURN NULL; -- o resultado é ignorado uma vez que este é um gatilho AFTER
END;
$$ language plpgsql;
----------------------------------------------------------------------------------------------------------------Terceiro – criar o gatilho
--------------------------------------------------------------------------------------------------------------------CREATE TRIGGER funcionario_audit
AFTER INSERT OR UPDATE OR DELETE ON func
FOR EACH ROW EXECUTE PROCEDURE processa_audit_func();
---------------------------------------------------------------------------------------------------------------Quando algum usuario fizer um insert, update ou delete na tabela func o SGBD registrará os
dados na tabela func_auditoria
Exemplo: se fizermos uma conexão com o sgbd com o usuario postgres e atualizar a tabela func
com o comando::
update func set salario = 1200.00 where cod = 1;
podemos consulta a tabela func_auditoria:
SELECT * FROM func_auditoria;
Teremos como resultado:
----------------------------------------------------------------------"A";"postgres";"2009-10-09 17:09:43.258";"ZE";1000.00
------------------------------------------------------------------------
2 – Gatilho que antes de efetuar uma inserção na tabela func verifica se o nome do
funcionario é nulo e se o salario é nulo ou menor do que zero caso seja não faz a inclusão
e emite uma mensagem
Primeiro – definição da função de gatilho
-----------------------------------------------------------------------------------CREATE FUNCTION func_gatilho() RETURNS trigger AS $$
Nota de Aula
Prof. Sidney Vieira
8
BEGIN
-- Verificar se foi fornecido o nome do empregado
IF NEW.nome IS NULL THEN
RAISE EXCEPTION 'O nome do empregado não pode ser nulo';
END IF;
IF NEW.salario IS NULL THEN
RAISE EXCEPTION 'O não pode ter um salário nulo', NEW.nome_emp;
END IF;
-- Quem paga para trabalhar?
IF NEW.salario < 0 THEN
RAISE EXCEPTION 'O funcionario não pode ter um salário negativo',
NEW.nome_emp;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
---------------------------------------------------------------------------------------------------------------Segundo: criação do gatilho para a tabela func
----------------------------------------------------------------------------------------------------------------CREATE TRIGGER Novo_func BEFORE INSERT OR UPDATE ON func
FOR EACH ROW EXECUTE PROCEDURE func_gatilho();
---------------------------------------------------------------------------------------------------------------terceiro: o uso
Quando se tenta inserir:
insert into func(cod,salario) values(5,1800);
O SGBD emitirá a mensagem:
-------------------------------------------------------------------------------------ERROR: O nome do empregado não pode ser nulo.
------------------------------------------------------------------------------------
Nota de Aula
Prof. Sidney Vieira
9
3 – Gatilho que ao se efetuar uma inserção na tabela venda armazena na tabela data os
seguintes valores usuario que realizou o login, a palavra "venda" e a data com hora na
atual.
Create trigger auditoria after insert on venda for each Statement execute procedure
inseriu();
Create function inseriu() returns trigger as
$$
begin
insert into data values (user,'vendas',now());
return null;
end;
$$
language pspgsql;
Create table data(usuario varchar(50), tabela varchar(50), Datahora timestamp);
4 - Crie um gatilho que a cada mudança no preço de venda de um produto armazene os
dados na tabela histórico.
Create trigger atualiza_preco_venda after update on produto for each row execute
procedure incluir_historico();
Create function incluir_historico() returns trigger as
‘begin
if (old.valor_venda<>new.valor_venda)
then
insert into historico (data, user, valor_anterior, valor_atual)
values (now(),user,old.valor_venda, new.valor_venda);
return null;
end;'
language plpgsql;
Nota de Aula
Prof. Sidney Vieira
10