02 Function, Trigger, Stored Procedure - Oracle Database

Prévia do material em texto

Instituto Federal do 
Sudeste de Minas 
Gerais
Tecnologia em Sistemas para Internet
Functions, Triggers e 
Stored Procedures
Oracle Database
Alunos
ʂ Armando Assunção
ʂ Richardson William
ʂ Samuel Gonçalves
ʂ Wagner Almeida
“
PL/SQL
ʂ PL/SQL é uma linguagem procedural projetada 
especificamente para encapsular instruções SQL;
ʂ Programas PL/SQL são compilados e armazenados no 
banco de dados Oracle;
ʂ Em tempo de execução, tanto PL/SQL e SQL executam 
dentro do mesmo processo do servidor, trazendo a 
eficiência ideal.
O que é PL/SQL?
Por quê usar PL/SQL?
ʂ Uma aplicação que usa o banco de dados Oracle (e 
outros) não é de grande valia se não houver garantia de 
que apenas os dados corretos e completos sejam 
persistidos;
ʂ A maneira correta de obter essa garantia é evitar a 
exposição dos detalhes de implementação.
Como esta garantia é alcançada?
ʂ Subprogramas PL/SQL entregam dados encapsulando a 
lógica de negócios envolvida.
ʂ Os dados podem ser alterados e visualizados somente 
através de uma interface PL/SQL.
PL/SQL
O PL/SQL pode ser usado em algumas linguagens de 
programação:
ʂ PHP
ʂ Java
ʂ Python
ʂ .NET
ʂ Node.js
Introdução
ʂ A unidade básica de um programa é o bloco, que agrupa 
variáveis e instruções.
ʂ Um bloco é definido pelas palavras reservadas DECLARE, 
BEGIN, EXCEPTION e END.
Exemplo 1 - Hello World!
ʂ Exemplo básico. Apenas imprime uma mensagem na tela.
BEGIN
 dbms_output.put_line('Hello world!');
END;
PL/SQL
ʂ Devido a cada bloco possuir sua própria seção de 
declaração e exceção, desenvolvedores podem usar 
blocos anônimos para prover escopo para identificadores 
e tratamentos de exceção em um programa maior.
Exemplo 2 - Bloco completo
DECLARE
 today date := sysdate;
BEGIN
 dbms_output.put_line('Today is ' || to_char(today,'Day'));
EXCEPTION WHEN OTHERS THEN
 dbms_output.put_line(sqlerrm);
END;
Exemplo 3 - Adicionando lógica
DECLARE
 today date := sysdate;
BEGIN
 IF to_char(today,'D') < 4 THEN
 dbms_output.put_line('Have a wonderful week');
 ELSE
 dbms_output.put_line('Enjoy the rest of the week');
 END IF;
 dbms_output.put_line('Today is ' || to_char(today,'Day'));
END;
“
Stored Procedures
Stored Procedures
ʂ Procedures são apenas blocos de código PL/SQL com um 
nomes definidos;
ʂ Conhecidos como subprogramas autônomos;
ʂ Quando compilados, são armazenados no banco de 
dados e se tornam stored procedures.
ʂ A sua função principal é executar comandos.
Stored Procedures
ʂ Procedures podem aceitar argumentos;
ʂ Normalmente não retorna valores;
ʂ Mesmo não retornando valores, tem efeitos fora dele;
ʂ São executados por programas, sessões SQL ou Triggers.
ʂ São muito utilizadas na lógica de negócios.
Vantagens de Stored Procedures
ʂ Fornece reutilização de código;
ʂ Portabilidade;
ʂ Produtividade;
ʂ Aumento de performance;
ʂ Segurança.
Sintaxe de stored procedures
CREATE [OR REPLACE] PROCEDURE procedure_name
[ ( p_parameter1 [MODE1] datatipe1
 ,p_parameter2 [MODE2] datatipe2
… ) ]
{IS | AS}
bloco PL/SQL
Parametros
REPLACE - indica que caso a procedure exista ela será 
eliminada e substituída pela nova versão criada pelo 
comando.
PROCEDURE_NAME – indica o nome da procedure.
PARÂMETRO – indica o nome da variável PL/SQL que é 
passada na chamada da procedure.
Parametros
MODE - Indica qual o tipo do parâmetro, se é IN(leitura), OUT
(escrita) ou IN OUT(leitura e escrita). O valor padrão é IN.
IS | AS – São sinônimos quando usados na criação de 
procedures. Por convenção usamos IS na criação de 
procedures e AS na criação de pacotes.
BLOCO PL/SQL – indica as ações que serão executadas por 
aquela procedure.
“
Exemplos Procedures
Exemplo 01 - Descrição
ʂ Cria uma procedure que deleta um funcionário com 
determinado id.
ʂ A procedure recebe apenas o id do funcionário.
Exemplo 01 - Declaração
CREATE OR REPLACE PROCEDURE proc_deleta_func(
p_id_func IN FUNCIONARIOS.ID_FUNC%TYPE
)
IS
BEGIN
DELETE FROM Funcionarios WHERE id_func = 
p_id_func;
END proc_deleta_func;
Exemplo 01 - Utilização
EXECUTE proc_deleta_func(1) ;
ou
BEGIN
proc_deleta_func(1);
END;
Exemplo 02 - Descrição
ʂ Cria uma procedure que aumenta o salário dos 
funcionários de um determinado departamento .
ʂ A procedure recebe o id do departamento e o valor da 
porcentagem.
Exemplo 02 - Declaração
CREATE OR REPLACE PROCEDURE proc_aumenta_salario_depto(
p_id_depto IN Funcionarios.id_dpto%TYPE,
porcentagem IN NUMBER)
IS
BEGIN
UPDATE
Funcionarios
SET salario = salario * (1 + porcentagem / 100)
WHERE id_dpto = p_id_depto;
END proc_aumenta_salario_depto;
Exemplo 02 - Utilização
EXECUTE proc_aumenta_salario_depto(4, 50) ;
ou
BEGIN
proc_aumenta_salario_depto(4, 50);
END;
Exemplo 03 - Descrição
ʂ Para apagar uma procedure é necessário apenas do 
comando:
DROP PROCEDURE procedure_name;
Exemplo 03 - Utilização
ʂ Apagando a procedure proc_deleta_func.
DROP PROCEDURE proc_deleta_func;
“
Functions
ʂ Blocos PL/SQL nomeados;
ʂ Semelhantes às Stored Procedures;
ʂ Obrigatoriamente devem retornar um valor.
Functions
Vantagens de se utilizar Functions
ʂ Promovem reusabilidade;
ʂ Facilidade de manutenção;
ʂ Facilidade de utilização: Podem ser chamadas a partir de 
blocos PL/SQL ou instruções SQL.
Algumas Utilizações
ʂ Conversões de dados;
ʂ Formatar datas;
ʂ Contar o total de linhas;
ʂ Obter dados com base em critérios estabelecidos;
ʂ Alterar dados.
Sintaxe Básica Criação e Substituição
CREATE [OR REPLACE] FUNCTION nome_da_função
[( parameter1 [ mode1 ] datatype1,
parameter2 [ mode2 ] datatype2,
. . .)]
RETURN datetype_return
IS | AS
Bloco PL/SQL;
Parâmetros
CREATE [OR REPLACE] - Utilizado para criar uma função. O 
parâmetro OR REPLACE é opcional e indica que caso a 
function exista, será eliminada e substituída pela nova versão 
criada. O parâmetro OR REPLACE também é muito utilizado 
para alterar uma função;
NOME_DA_FUNÇÃO - indica o nome da função;
Parâmetros
PARAMETER - Indica o nome da variável PL/SQL que é 
passada na chamada da function.
DATETYPE - Indica o tipo de dado do parâmetro. Pode ser 
qualquer tipo de dado do SQL ou do PL/SQL. Pode usar 
referências como %TYPE, %ROWTYPE ou qualquer tipo de dado 
escalar ou composto. 
Parâmetros
RETURN - Indica o retorno da function, ou seja, o que a 
function deverá retornar ao local de chamada da mesma;
datetype_return - Indica o tipo de dado que será retornado 
pela function. Podem ser tipos SQL ou PL/SQL;
Parâmetros
IS|AS - A sintaxe do comando aceita tanto IS como AS. Por 
convenção usamos IS na criação de function/procedures e AS 
quando estivermos criando packages;
Bloco PL/SQL - Indica as ações que serão executadas pela 
function.
“
Exemplos Functions
Exemplo 1 - Descrição
ʂ Cria uma função que retorna o novo salário de um 
funcionário;
ʂ A função receberá o valor atual e a projeção de aumento 
em valor decimal.
Exemplo 1 - Declaração 
CREATE OR REPLACE FUNCTION 
projecao_novo_salario(valor IN NUMBER, 
 porcentagem IN NUMBER) 
 RETURN NUMBER IS
BEGIN
 RETURN (valor * (1 + porcentagem));
END projecao_novo_salario;
Exemplo 1 - Utilização
SELECT nome_func, id_dpto, salario, 
 projecao_novo_salario ( salario, 0.6 )
 FROM funcionarios
 WHERE nome_func = ‘Richardson William’;
Exemplo 2 - Descrição
ʂ Altera a função inicial para que verifique se a 
porcentagem recebida por parâmetro alterará o salario 
corrente (valor diferente de 0);
ʂ Caso seja um valor válido, retorna o novo salário;
ʂ Caso contrário, exibe o salário atual.Exemplo 2 - Declaração
CREATE OR REPLACE FUNCTION projecao_novo_salario(valor IN 
NUMBER, porcentagem IN NUMBER) 
 RETURN NUMBER IS
BEGIN
 IF porcentagem != 0 THEN
 RETURN (valor * (1 + porcentagem));
 ELSE
 RETURN valor;
 END IF;
END projecao_novo_salario;
Exemplo 2 - Utilização
SELECT nome_func, 
 id_dpto, salario, 
 projecao_novo_salario ( salario, 0 )
 FROM funcionarios
 WHERE nome_func = 'Wagner Almeida';
Retornando mais de um valor na Function
ʂ Para retornar mais de um valor existem duas formas:
ʂ Retornando as linhas utilizando RECORD e %
ROWTYPE;
ʂ Definir um tipo de dado (objeto) que terá os atributos 
(valores) que desejamos retornar.
Obs.: Será exemplificada apenas a segunda forma, pois a 
primeira já foi vista utilizando PostgreSQL e a forma de 
utilização é análoga no Oracle Database.
Exemplo 3 - Criando tipo da dados 
ʂ Para retornar mais de um valor utilizando a segunda 
forma, são necessários 3 passos. Primeiro é preciso criar 
um tipo objeto com os atributos referentes aos dados 
que se deseja armazenar.
Exemplo 3 - Definição de um objeto
CREATE OR REPLACE TYPE dados_funcionario 
AS OBJECT ( codigo NUMBER,
 nome VARCHAR2(80),
 salario VARCHAR2(80) );
Exemplo 3 - Criação do tipo Table
ʂ Também é necessário criar um tipo table do objeto criado 
anteriormente, para que se possa retornar um grupo 
destes objetos.
Exemplo 3 - Definição do tipo Table
CREATE OR REPLACE TYPE dados_funcionario_table 
AS TABLE OF dados_funcionario;
Exemplo 3 - Testando o objeto e o tipo criados
CREATE OR REPLACE FUNCTION teste_f 
RETURN dados_funcionario_table PIPELINED IS
BEGIN
 PIPE ROW (dados_funcionario( 1, 'Fernanda', 
 1300));
END;
SELECT * FROM TABLE(teste_f());
Exemplo 3 - Parâmetros adicionais utilizados
ʂ PIPELINED - Keyword utilizada para que uma function 
possa trabalhar como uma tabela;
ʂ PIPE ROW - Retorna uma linha da tabela. Pode ser usado 
apenas em functions criadas como PIPELINED.
Exemplo 4 - Descrição 
ʂ Cria uma função que retorna todos os funcionários que 
recebem um valor igual ou maior ao salário recebido por 
parâmetro.
Exemplo 4 - Declaração 
CREATE OR REPLACE FUNCTION funcionarios_salario_base(valor 
IN NUMBER) RETURN dados_funcionario_table PIPELINED IS
BEGIN
FOR linha IN (SELECT id_func, nome_func, salario FROM 
funcionarios WHERE salario >= valor)
 LOOP
 PIPE ROW (dados_funcionario(linha.id_func, 
linha.nome_func, linha.salario));
 END LOOP;
END;
Exemplo 4 - Utilização
ʂ Lista todos os funcionários que tem o salário maior ou 
igual a R$ 7000:
SELECT * FROM TABLE(funcionarios_salario_base(7000));
Para saber mais - PIPELINED
ʂ Toda vez que se utiliza uma function como PIPELINED os 
resultados são retornados em subsets;
ʂ Não é necessário aguardar todo o processamento pois os 
resultados são retornados em tempo de execução;
ʂ Isso faz com que a obtenção dos dados seja mais rápida 
do que functions convencionais.
Alterar Functions
ʂ Da mesma forma que ocorre com views, alterar functions 
no Oracle Database não é tão trivial;
ʂ Para facilitar este processo, a forma mais usual é 
sobrescrever à function que se deseja alterar (utilizando o 
parâmetro OR REPLACE na criação da function).
Apagar Function
ʂ Para apagar uma function basta apenas utilizar o 
comando:
DROP FUNCTION nome_da_função;
“
Triggers
Triggers
ʂ É um gatilho de tipo especial de Procedimento 
Armazenado (Stored Procedure);
ʂ Contém um bloco PL/SQL que é associado a uma 
operação DML;
ʂ Executado antes ou depois de ocorre uma Operação DML.
 
Porque usar Triggers?
ʂ Geração automática de valores de colunas derivados;
ʂ Prevenção de transações inválidas;
ʂ Reforçar regras de negócio complexas;
ʂ Prover auditoria;
ʂ Gerar estatísticas sobre acesso às tabelas;
ʂ Prover log de transações.
Tipos de Triggers
ʂ Trigger Row Level
ʂ Trigger Statement 
ʂ Column Trigger
ʂ Table Trigger
Trigger Row Level
ʂ A trigger é disparada sempre que ocorrer uma transação 
sobre uma tabela.
ʂ São Utilizadas em Operações como:
◇ Gravação de LOGS de auditoria;
◇ Verificação de dados (Consistência);
◇ Implementação de integridade referencial.
Trigger Statement 
ʂ Está trigger trata execuções de ações que ocorre na 
tabela independentemente de quantas linhas foram 
afetadas;
ʂ Recomenda para a execução de comandos INSERT, 
UPDATE e DELETE;
Column Trigger
ʂ Trigger é associada a uma coluna da tabela;
ʂ Trigger é disparada quando a coluna que ela está 
associada sofre uma operação DML;
ʂ Caso for outra coluna sofre a operação a trigger não e 
disparada.
Table Trigger
ʂ Trigger associada a uma tabela;
ʂ A trigger e dispara sempre que alguma coluna da tabela 
associada sofrer operação DML;
Sintaxe - Trigger
CREATE [OR REPLACE] TRIGGER [schema.] NOME_TRIGGER
 [BEFORE|AFTER|INSTEAD OF]
 [DELETE|OR INSERT|OR UPDATE[OF coluna]]
 ON [schema.] nome_tabela_ou_view
 [REFERENCING [OLD [AS] OLD] [NEW [AS] NEW]
 [FOR EACH ROW]
 [WHEN [condição]] BLOCO PL/SQL
Sintaxe - Trigger
ʂ OR REPLACE - Sobrescreve a trigger caso ela já exista;
ʂ NOME_TRIGGER - Indica nome da trigger; 
ʂ schema. - Especifique o esquema para conter o gatilho. 
Se você omitir o esquema , então Oracle Database cria o 
gatilho no seu próprio esquema
Sintaxe - Trigger
ʂ BEFORE - Indica que a trigger será disparada antes da 
ação ocorrer;
ʂ AFTER - Indica que a trigger será disparada após a 
ocorrência da ação;
ʂ INSTEAD OF - indica que a trigger irá ser executada no 
lugar da instrução que disparou a trigger.
Sintaxe - Trigger
ʂ INSERT - Define que a trigger e diparada sempre que 
ocorre uma ação de INSERT;
ʂ DELETE - Define que a trigger e diparada sempre que 
ocorre uma ação de DELETE;
ʂ UPDATE -Define que a trigger e diparada sempre que 
ocorre uma ação de UPDATE;
Sintaxe - Trigger
ʂ nome_tabela_ou_view: Indica o nome da tabela ou da 
view que a trigger está associada;
ʂ REFERENCING: Irá indicar ser utilizara OLD ou NEW;
ʂ OLD e NEW: São referenciadores de valores sendo OLD 
valores antigos e NEW valores novos estando presentes 
apenas nas Trigger Row Level;
Sintaxe - Trigger
ʂ FOR EACH ROW: Quantas vezes o corpo da trigger será 
executado;
ʂ WHEN: Nas Trigger Row Level, podemos restringir a ação 
da trigger segundo uma condição, onde a mesma será 
disparada apenas para as linhas que satisfaçam a 
condição prevista;
Sintaxe - Trigger
ʂ BLOCO PL/SQL: Quais ações serão executadas na trigger, 
sendo o corpo da trigger.
“
Exemplos de Triggers
Criando uma Trigger
create or replace TRIGGER log_func_dml AFTER INSERT OR 
DELETE OR UPDATE ON funcionarios
REFERENCING OLD as velho NEW as novo FOR EACH ROW
--Bloco SQL--
DECLARE
 Operacao CHAR(1);
 Instancia varchar2 (60);
 Cliente varchar2 (60);
 Usuario varchar2 (60);
BEGIN
Criando uma Trigger
SELECT SYS_CONTEXT('USERENV', 'INSTANCE_NAME') "Instância",
 SYS_CONTEXT('USERENV', 'HOST')"CLIENTE",
 SYS_CONTEXT('USERENV', 'SESSION_USER')"Usuário BD" into instancia, 
cliente, usuario FROM DUAL;
 IF INSERTING THEN
 Operacao := 'I';
 ELSIF UPDATING THEN
 Operacao := 'U';
 ELSE /* Há Deleção */
 Operacao := 'D';
 END IF;
Criando uma Trigger
INSERT INTO log_funcionarios (ID_LOG,COD_FUNC,NOME_FUNC_OLD,
NOME_FUNC_NEW,
SALARIO_OLD,SALARIO_NEW,EMAIL_OLD,EMAIL_NEW,
ID_DPTO_OLD,ID_DPTO_NEW,DATA_ALTERACAO,OPERACAO,
INSTANCIA,CLIENTE,USUARIO) 
VALUES (log_funcionarios_seq.NEXTVAL,:velho.id_func,
:velho.nome_func,:novo.nome_func,:velho.salario,:novo.salario,
:velho.email,:novo.email,:velho.id_dpto, :novo.id_dpto,SYSDATE, Operacao,Instancia,Cliente,Usuario); 
END;
Criando uma Trigger
SELECT * FROM log_funcionarios;
INSERT INTO FUNCIONARIOS VALUES(30,'Pedro 
Henrique',5000.00,'pedroh@gmail.com',4);
SELECT * FROM log_funcionarios;
Ativando e Desativando Trigger
Ao criar uma trigger ela e ativada 
automaticamente, para poder desativar ela só 
utilizar o seguinte comando:
Desativar:
ALTER TRIGGER nome_trigger DISABLE;
Ativar:
ALTER TRIGGER nome_trigger ENABLE;
Alterando a Trigger
ALTER TRIGGER log_func_dml DISABLE;
INSERT INTO FUNCIONARIOS VALUES(30,'Ilma 
Barbosa',3200.00,'ilma.ilma@ilma.com',2);
SELECT * FROM log_funcionarios;
Alterando a Trigger
ALTER TRIGGER log_func_dml ENABLE;
UPDATE FUNCIONARIOS SET salario= 8000.00, 
email = 'ilma_ilma@ilma.com', id_dpto = 4 
WHERE id_func = 30;
SELECT * FROM log_funcionarios;
Apagando uma Trigger
O comando para apagar a Trigger é:
DROP TRIGGER [schema.] nome_trigger;
Restrições do uso de Triggers
ʂ Não podemos realizar os comandos COMMIT, ROLLBACK e 
SAVEPOINT em um Trigger, mesmo que seja uma 
procedure executada em um Trigger.
ʂ Não podemos fazer select na mesma tabela que sofre a 
ação de um Trigger , pois isso pode provocar um erro 
chamado MUTANT TABLE.
Referências
ʂ oracle-base.com
ʂ docs.oracle.com
ʂ www.devmedia.com.br
ʂ aprendaplsql.com