Banco de Dados II – Aula 13

PERMISSÃO PARA MODO DEBUG

ScreenHunter_114 May. 06 23.47

EXECUÇÃO DA PROCEDURE

ScreenHunter_115 May. 06 23.48

PASSOS PARA FAZER O DEBUG

ScreenHunter_116 May. 07 00.23

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

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

ScreenHunter_117 May. 07 01.00

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:

Untitled2

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:

Untitled3

EXEMPLO DA TRIGGER SECURE_EMP

Untitled4

TESTANDO A TRIGGER SECURE_EMP

Untitled5

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

Untitled6

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

Untitled

VISIBILIDADE INTERNA E EXTERNA DOS COMPONENTES DA PACKAGE

Untitled2

CRIANDO A ESPECIFICAÇÃO DA PACKAGE

Untitled3

  • 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

Untitled4

CRIANDO O CORPO DA PACKAGE NO SQL DEVELOPER

Untitled5

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

Untitled6

  • 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

  1. 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;
  1. 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

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

learningdatabase.com.br

Tecnologias em Banco de Dados Relacionais, Modelagem de dados dimencionais, tecnologias SQL Servere e Oracle

Aprendendo Programação

Algorítmos, C, C++,Pascal, Python, R

WikiDBA

by Virendra Yaduvanshi - Microsoft SQL Server Database Architect | Consultant | Blogger | Specialist | DBA | Speaker

Blog - Fabiano Neves Amorim

SELECT * FROM [Coisas Da Minha Cabeça] WHERE dbo.fn_TempoParaPost() < dbo.fn_TempoLivre()

Tércio Costa, Oracle Developer, OCE SQL, ACE Associate

Guia de estudos para certificação ORACLE SQL(1Z0-047, 1Z0-051, 1Z0-061 e 1Z0-071) e PL/SQL(1Z0-144, 1Z0-146 e 1Z0-148)

Strate SQL

Data Adventures with an Architect

%d blogueiros gostam disto: