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