PERMISSÃO PARA MODO DEBUG
EXECUÇÃO DA PROCEDURE
PASSOS PARA FAZER O DEBUG
EXEMPLO DE CÓDIGO
CREATE OR REPLACE PROCEDURE SP_TESTE_DEBUG
(p_employee_id_inicial IN EMPLOYEES.EMPLOYEE_ID%TYPE,
p_employee_id_final IN EMPLOYEES.EMPLOYEE_ID%TYPE)
IS
v_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE;
v_last_name EMPLOYEES.LAST_NAME%TYPE;
v_hire_date EMPLOYEES.HIRE_DATE%TYPE;
v_contador NUMBER;
CURSOR cur_ret_employees is
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE EMPLOYEE_ID BETWEEN p_employee_id_inicial AND p_employee_id_final;
BEGIN
v_contador := 0;
FOR r_employees IN cur_ret_employees LOOP
FETCH cur_ret_employees
INTO v_employee_id, v_last_name, v_hire_date;
EXIT WHEN cur_ret_employees%NOTFOUND;
v_contador := v_contador +1;
DBMS_OUTPUT.PUT_LINE('Record : '|| v_contador);
DBMS_OUTPUT.PUT_LINE('EmployeeId: '|| v_employee_id);
DBMS_OUTPUT.PUT_LINE('Last Name : ' || v_last_name);
DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_hire_date);
DBMS_OUTPUT.PUT_LINE('');
END LOOP;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error was detected');
END;
/
TESTE DE DEBUG
- Não crie bloco anônimo para fazer o teste do debug no código;
- Nos parâmetros, coloque intervalos pequenos somente para ver o resultado;
TRIGGERS (GATILHOS)
- Trigger é um bloco PL/SQL que é armazenado no banco de dados e executado em resposta de um específico evento;
- O banco de dados Oracle executa um trigger automaticamente quando é especificada uma ocorrência de uma condição;
- Uma trigger pode ser definida em uma tabela, view, schema (dono do schema), ou banco de dados (para todos os usuários)
TIPOS DE EVENTOS DAS TRIGGERS
- Podemos escrever triggers que disparam em uma das operações que ocorre no banco de dados:
- DML (insert, update, delete)
- DDL (create, alter, drop)
- Operações no banco como SERVERERROR, LOGON, LOGOFF, STARTUP ou SHUTDOWN;
CENÁRIOS DE APLICAÇÕES PARA IMPLEMENTAÇÃO
- Podemos utilizar triggers em:
- Segurança
- Auditoria
- Integridade de Dados
- Integridade Referencial
- Replicação de dados
- Dados derivados de cálculos
- Eventos de Logging
DISPONIBILIDADE DOS TIPOS DE TRIGGERS
- Em DML simples
- BEFORE
- AFTER
- INSTEAD OF
TIPOS DE EVENTOS DA TRIGGER E CORPO
- Um tipo de evento da trigger determina como as cláusulas da declaração DML irá executar a trigger. As possibilidade são:
- INSERT
- UPDATE [OF COLUMN]
- DELETE
- O corpo determina a ação é chamada e é um bloco PL/SQL ou a chamada de uma procedure;
CRIANDO TRIGGER DML
ESPECIFICANDO O TEMPO DE EXECUÇÃO
- Voce pode especificar o tempo que a trigger irá ser executada em uma das declarações:
- BEFORE: Executa o corpo da trigger antes do acionamento do evento DML na tabela;
- AFTER: Executa o corpo da trigger depois do acionamento do evento DML na tabela;
- INSTEAD OF: Executa o corpo da trigger ao invés do evento disparado (geralmente um comando DML);
SEQUENCIA DE DISPARO DAS TRIGGERS: MANIPULAÇÃO SIMPLES DE LINHA
- A sequencia de disparo de uma trigger em uma tabela quando um linha é manipulada:
SEQUENCIA DE DISPARO DAS TRIGGERS: MANIPULAÇÃO DE VÁRIAS LINHAS
- A sequencia de disparo de uma trigger em uma tabela quando são manipuladas várias linhas:
EXEMPLO DA TRIGGER SECURE_EMP
TESTANDO A TRIGGER SECURE_EMP
USANDO PREDICADOS CONDICIONAIS
CREATE OR REPLACE TRIGGER secure_emp BEFORE
INSERT OR UPDATE OR DELETE ON employees
BEGIN
IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR
(TO_CHAR(SYSDATE,'HH24') NOT BETWEEN '08' AND '18') THEN
IF DELETING THEN RAISE_APPLICATION_ERROR(
-20502,'Voce pode deletar empregados durante o horario comercial.');
ELSIF INSERTING THEN RAISE_APPLICATION_ERROR (
-20500,'Voce pode inserir empregados durante o horario comercial.');
ELSIF UPDATING ('SALARY') THEN RAISE_APPLICATION_ERROR (
-20503,'Voce pode atualizar o salary em horario comercial');
ELSE RAISE_APPLICATION_ERROR(
-20504,'Voce pode atualizar a tabela empregados durante o horario comercial');
END IF;
END IF;
END;
desc employees
insert into employees (EMPLOYEE_ID, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID)
VALUES (207,'TESTE','TESTE',TO_DATE('05/05/2017','MM/DD/YYYY'),'HR_REP')
CRIANDO TRIGGER DE DML DE LINHA
–EXEMPLO 2
CREATE OR REPLACE TRIGGER SALARIO_RESTRITO
BEFORE INSERT OR UPDATE OF SALARY ON EMPLOYEES
FOR EACH ROW
BEGIN
IF NOT (:NEW.job_id IN ('AD_PRES','AD_VP'))
AND :NEW.salary > 15000 THEN
RAISE_APPLICATION_ERROR (-20202,
'Empregado não pode ganhar mais que $15000.');
END IF;
END;
--teste da chamada
UPDATE employees
SET salary = 15500
WHERE last_name = 'Russell';
USANDO QUALIFICADORES OLD E NEW
- Quando uma linha é atingida, o motor do run-time PL/SQL cria e popula duas estruturas de dados:
- OLD: armazena os valores originais do registro processado pela trigger;
- NEW: contém os novos valores;
- NEW e OLD tem a mesma estrutura do registro declarado usando %ROWTYPE na tabela a qual a trigger é atachada (criada);
CREATE TABLE AUDIT_EMP (
USER_NAME VARCHAR2(30),
TIME_STAMP DATE,
ID NUMBER(6),
OLD_LAST_NAME VARCHAR2(25),
NEW_LAST_NAME VARCHAR2(25),
OLD_TITLE VARCHAR2(10),
NEW_TITLE VARCHAR2(10),
OLD_SALARY NUMBER(8,2),
NEW_SALARY NUMBER(8,2)
)
/
CREATE OR REPLACE TRIGGER AUDIT_EMP_VALUES
AFTER DELETE OR INSERT OR UPDATE ON EMPLOYEES
FOR EACH ROW
BEGIN
INSERT INTO AUDIT_EMP(USER_NAME, TIME_STAMP, ID,
OLD_LAST_NAME, NEW_LAST_NAME,
OLD_TITLE, NEW_TITLE,
OLD_SALARY, NEW_SALARY)
VALUES (USER, SYSDATE, :OLD.EMPLOYEE_ID,
:OLD.LAST_NAME, :NEW.LAST_NAME,
:OLD.JOB_ID, :NEW.JOB_ID,
:OLD.SALARY, :NEW.SALARY);
END;
--EXMEPLO DE CHAMADA DA FUNÇÃO
INSERT INTO EMPLOYEES (EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY,
EMAIL, HIRE_DATE)
VALUES (999,'TEMP EMP','SA_REP',6000, 'TEMP EMP', TRUNC(SYSDATE));
/
UPDATE EMPLOYEES
SET SALARY = 7000,
LAST_NAME = 'Smith'
WHERE EMPLOYEE_ID = 999;
SELECT *
FROM AUDIT_EMP;
Exercícios
1. Crie uma tabela chamada empregados2 com a mesma estrutura da tabela employees. Faça uma trigger que a medida que a tabela employees for sido preenchida, essa tabela empregados2 deve ter os mesmos dados;
2. Faça uma trigger que verifique se os dados da tabela empregados2 está sendo alterada. Caso o salário esteja sendo alterado, a trigger não deve permitir gravar o registro;
3. Crie uma tabela chamada empregados3. Copie todos os dados que tenham o job_id = ‘IT_PROG’ da tabela employees para essa tabela. Crie uma outra tabela chamada log_empregados3, contendo as estrutura da tabela empregados3, com dados antes e depois de alterações. Faça uma trigger na tabela empregados3 para fazer essa auditoria;
PACKAGES (Pacotes)
- Package é um objeto de um schema que agrupa logicamente tipos PL/SQL, variáveis e subprogramas;
- Packages geralmente são divididos em 2 partes:
- A especificação (spec)
- O corpo
- A especificação é uma interface para a Package. Ela declara os tipos, variáveis, constantes, exceções, cursores, e subprogramas que pode ser referenciados do lado de fora da Package;
- O corpo define as queries de cursores e o código para os subprogramas;
- Habilita o servidor Oracle a ler múltiplos objetos na memória de uma vez;
VANTAGENS DO USO DAS PACKAGES
- Modularidade: Encapsulando construtores relacionados;
- Fácil manutenção: mantendo logicamente funcionalidades relacionadas juntas;
- Fácil projeto de aplicação: Codificando e compilando a especificação e o corpo separadamente;
- Escondendo informação:
- Somente as declarações na Especificação da Package são visíveis e acessíveis para aplicações;
- Construtores privados no corpo da Package são escondidos e inacessíveis;
- Toda codificação é escondida no corpo da Package;
- Adição de funcionalidades: Persistência de variáveis públicas e cursores;
- Melhor desempenho:
- Toda a Package é carregada para a memória quando uma Package é referenciada na primeira vez;
- Há uma única cópia na memória para todos os usuários;
- A hierárquia de dependência é simplificada;
- Overloading: Múltiplos subprogramas com o mesmo nome;
COMPONENTES DE UMA PACKAGE PL/SQL
VISIBILIDADE INTERNA E EXTERNA DOS COMPONENTES DA PACKAGE
CRIANDO A ESPECIFICAÇÃO DA PACKAGE
- Variáveis declaradas na especificação da Package são inicializados NULL por padrão;
- Todos os construtores declarados na especificação da Package são visíveis a usuários que tem privilégios na Package;
CRIANDO A ESPECIFICAÇÃO DA PACKAGE NO SQL DEVELOPER
CRIANDO O CORPO DA PACKAGE NO SQL DEVELOPER
EXEMPLO DE ESPECIFICAÇÃO DA PACKAGE
CREATE OR REPLACE PACKAGE COMM_PKG IS V_STD_COMM NUMBER := 0.10; --INICIALIZADO COM 0.10 PROCEDURE RESET_COMM(P_NEW_COMM NUMBER); END COMM_PKG; /
- V_STD_COMM é variável pública global inicializada com 0.10;
- RESET_COMM é procedure publica usada para resetar a comissão padrão baseada em algumas regras de negócio; Será implementadado no corpo da Package;
CRIANDO O CORPO DA PACKAGE
- Identificadores definidos como privado o corpo da Package e não visível do lado de fora do corpo da package;
- Todos construtores privados precisam ser declarados antes de eles serem referenciados;
- Construtores públicos são visíveis para todo o corpo da Package;
EXEMPLO DE CORPO DA PACKAGE
CREATE OR REPLACE PACKAGE BODY COMM_PKG IS
FUNCTION VALIDATE(P_COMM NUMBER) RETURN BOOLEAN IS
V_MAX_COMM EMPLOYEES.COMMISSION_PCT%TYPE;
BEGIN
SELECT MAX(COMMISSION_PCT)
INTO V_MAX_COMM
FROM EMPLOYEES;
RETURN (P_COMM BETWEEN 0.0 AND V_MAX_COMM);
END VALIDATE;
PROCEDURE RESET_COMM(P_NEW_COMM NUMBER) IS
BEGIN
IF VALIDATE(P_NEW_COMM) THEN
V_STD_COMM := P_NEW_COMM; -- RESETA A VARIAVEL PUBLICA
ELSE
RAISE_APPLICATION_ERROR (-20210,'COMISSAO RUIM');
END IF;
END RESET_COMM;
END COMM_PKG;
/
CHAMANDO SUBPROGRAMAS DA PACKAGE
EXECUTE COMM_PKG.RESET_COMM(0.15);
Exercícios
- Faça uma Package que faça a Insert de funcionários (employees) e Select por ID. Deve conter também uma função que retorne o nome completo do funcionário, passando por parâmetro o ID. Faça os devidos testes de chamadas;
- Crie uma Package que faça o Insert, Update, Delete, e Select por ID do Departamento e outra Select pelo Nome do Departamento. Para fazer Inserção e atualização, é necessário verificar se o nome do departamento não exista na tabela. Em caso de um departamento já existente, exiba uma mensagem para ser exibido pelo ambiente chamador. Faça os devidos testes de chamadas;
Video 01
