Criando os Objetos de Esquema e Carregando os Dados

Esta seção mostra como criar tabelas, editar views, acionadores e sequências para o aplicativo, como carregar dados nas tabelas e como conceder privilégios sobre esses objetos de esquema aos usuários que precisam deles.

Etapas para criar os objetos de esquema e carregar os dados:

  1. Conecte-se ao Oracle Database como usuário app_data.

    Para obter instruções, consulte "Connecting to Oracle Database from SQL*Plus" ou "Connecting to Oracle Database from SQL Developer".

  2. Crie as tabelas, com todas as restrições necessárias, exceto a constraint de chave estrangeira que você deve adicionar após carregar os dados.

  3. Crie as exibições de edição.

  4. Crie os acionadores.

  5. Crie as sequências.

  6. Carregue os dados nas tabelas.

  7. Adicione a restrição de chave estrangeira.

Criando as Tabelas

Esta seção mostra como criar as tabelas para o aplicativo, com todas as restrições necessárias, exceto uma, que você deve adicionar após carregar os dados.

Observação: Você deve estar conectado ao Oracle Database como usuário app_data.

No procedimento a seguir, você pode informar as instruções no SQL*Plus ou na Planilha do SQL Developer. Como alternativa, você pode criar as tabelas com a ferramenta SQL Developer Create Table.

Etapas para criar as tabelas:

  1. Criar nº de cargos, que armazena informações sobre os cargos na empresa (uma linha para cada cargo):

     CREATE TABLE jobs#
 ( job_id      VARCHAR2(10)
               CONSTRAINT jobs_pk PRIMARY KEY,
   job_title   VARCHAR2(35)
               CONSTRAINT jobs_job_title_not_null NOT NULL,
   min_salary  NUMBER(6)
               CONSTRAINT jobs_min_salary_not_null NOT NULL,
   max_salary  NUMBER(6)
               CONSTRAINT jobs_max_salary_not_null NOT NULL
 )
 /

  2. Crie o nº de departamentos, que armazena informações sobre os departamentos da empresa (uma linha para cada departamento):

     CREATE TABLE departments#
 ( department_id    NUMBER(4)
                   CONSTRAINT departments_pk PRIMARY KEY,
   department_name  VARCHAR2(30)
                   CONSTRAINT department_name_not_null NOT NULL
                   CONSTRAINT department_name_unique UNIQUE,
   manager_id       NUMBER(6)
 )
 /

  3. Crie o nº de funcionários, que armazena informações sobre os funcionários na empresa (uma linha para cada funcionário):

     CREATE TABLE employees#
 ( employee_id     NUMBER(6)
                   CONSTRAINT employees_pk PRIMARY KEY,
   first_name      VARCHAR2(20)
                   CONSTRAINT emp_first_name_not_null NOT NULL,
   last_name       VARCHAR2(25)
                   CONSTRAINT emp_last_name_not_null NOT NULL,
   email_addr      VARCHAR2(25)
                   CONSTRAINT emp_email_addr_not_null NOT NULL,
   hire_date       DATE
                   DEFAULT TRUNC(SYSDATE)
                   CONSTRAINT emp_hire_date_not_null NOT NULL
                   CONSTRAINT emp_hire_date_check
                     CHECK(TRUNC(hire_date) = hire_date),
   country_code    VARCHAR2(5)
                   CONSTRAINT emp_country_code_not_null NOT NULL,
   phone_number    VARCHAR2(20)
                   CONSTRAINT emp_phone_number_not_null NOT NULL,

   job_id          CONSTRAINT emp_job_id_not_null NOT NULL
                   CONSTRAINT emp_jobs_fk REFERENCES jobs#,
   job_start_date  DATE
                   CONSTRAINT emp_job_start_date_not_null NOT NULL,
                   CONSTRAINT emp_job_start_date_check
                     CHECK(TRUNC(JOB_START_DATE) = job_start_date),
   salary          NUMBER(6)
                   CONSTRAINT emp_salary_not_null NOT NULL,

   manager_id      CONSTRAINT emp_mgr_to_empno_fk REFERENCES employees#,

   department_id   CONSTRAINT emp_to_dept_fk REFERENCES departments#
 )
 /

    Os motivos das constraints REF são os seguintes:

    • Um funcionário deve ter um cargo existente. Ou seja, os valores na coluna employees#.job_id também devem ser valores na coluna jobs#.job_id.

    • Um funcionário deve ter um gerente que também seja um funcionário. Ou seja, os valores na coluna employees#.manager_id também devem ser valores na coluna employees#.employee_id.

    • Um funcionário deve trabalhar em um departamento existente. Ou seja, os valores na coluna employees#.department_id também devem ser valores na coluna departments#.department_id.

    Além disso, o gerente de um funcionário deve ser o gerente do departamento no qual o funcionário trabalha. Ou seja, os valores na coluna employees#.manager_id também devem ser valores na coluna departments#.manager_id. No entanto, você não pôde especificar a restrição necessária quando criou departments#, porque employees# ainda não existia. Portanto, você deverá adicionar mais tarde uma constraint de chave estrangeira a departments# (consulte "Adding the Foreign Key Constraint").

  4. Crie o job_history#, que armazena o histórico de cargos de cada funcionário na empresa (uma linha para cada cargo mantido pelo funcionário):

     CREATE TABLE job_history#
 ( employee_id  CONSTRAINT job_hist_to_employees_fk REFERENCES employees#,
   job_id       CONSTRAINT job_hist_to_jobs_fk REFERENCES jobs#,
   start_date   DATE
               CONSTRAINT job_hist_start_date_not_null NOT NULL,
   end_date     DATE
               CONSTRAINT job_hist_end_date_not_null NOT NULL,
   department_id
             CONSTRAINT job_hist_to_departments_fk REFERENCES departments#
             CONSTRAINT job_hist_dept_id_not_null NOT NULL,
             CONSTRAINT job_history_pk PRIMARY KEY(employee_id,start_date),
             CONSTRAINT job_history_date_check CHECK( start_date < end_date )
 )
 /

    Os motivos das restrições REF são que o funcionário, o cargo e o departamento devem existir:

    • Os valores na coluna job_history#.employee_id também devem ser valores na coluna employees#.employee_id.

    • Os valores na coluna job_history#.job_id também devem ser valores na coluna jobs#.job_id.

    • Os valores na coluna job_history#.department_id também devem ser valores na coluna departments#.department_id.

Consulte Também: "Criando Tabelas"

Criando Views de Edição

Observação: Você deve estar conectado ao Oracle Database como app_data do usuário.

Para criar as exibições de edição, use as instruções a seguir (em qualquer ordem). Você pode informar as instruções no SQL*Plus ou na Planilha do SQL Developer. Como alternativa, você pode criar as views de edição com a ferramenta Criar View do SQL Developer.

CREATE OR REPLACE EDITIONING VIEW jobs AS SELECT * FROM jobs#
/
CREATE OR REPLACE EDITIONING VIEW departments AS SELECT * FROM departments#
/
CREATE OR REPLACE EDITIONING VIEW employees AS SELECT * FROM employees#
/
CREATE OR REPLACE EDITIONING VIEW job_history AS SELECT * FROM job_history#
/

Observação: o aplicativo deve sempre fazer referência às tabelas base por meio das exibições de edição. Caso contrário, as visualizações de edição não abrangem as tabelas e você não pode usar o EBR para atualizar o aplicativo finalizado quando ele estiver em uso.

Consulte também:

Criando Triggers

Observação: Você deve estar conectado ao Oracle Database como app_data do usuário.

Os acionadores no aplicativo impõem estas regras de negócios:

Consulte Também: Usando Triggers, para obter informações sobre triggers

Criando o Trigger para Aplicar a Primeira Regra de Negócios

A primeira regra de negócios é: um funcionário com o cargo j deve ter um salário entre os salários mínimo e máximo do cargo j.

Essa regra pode ser violada quando uma nova linha é inserida na tabela de funcionários ou quando a coluna salary ou job_id da tabela de funcionários é atualizada.

Para impor a regra, crie o acionador a seguir na exibição de edição de funcionários. Você pode informar a instrução CREATE TRIGGER no SQL*Plus ou na Planilha do SQL Developer. Como alternativa, você pode criar o trigger com a ferramenta Create Trigger do SQL Developer.

CREATE OR REPLACE TRIGGER employees_aiufer
AFTER INSERT OR UPDATE OF salary, job_id ON employees FOR EACH ROW
DECLARE
  l_cnt NUMBER;
BEGIN
  LOCK TABLE jobs IN SHARE MODE;  -- Ensure that jobs does not change
                                  -- during the following query.
  SELECT COUNT(*) INTO l_cnt
  FROM jobs
  WHERE job_id = :NEW.job_id
  AND :NEW.salary BETWEEN min_salary AND max_salary;

  IF (l_cnt<>1) THEN
    RAISE_APPLICATION_ERROR( -20002,
      CASE
        WHEN :new.job_id = :old.job_id
        THEN 'Salary modification invalid'
        ELSE 'Job reassignment puts salary out of range'
      END );
  END IF;
END;
/

LOCK TABLE jobs IN SHARE MODE impede que outros usuários alterem os jobs da tabela enquanto o trigger o está consultando. É necessário impedir alterações em jobs durante a consulta porque as leituras sem bloqueio impedem que o trigger "veja" as alterações que outros usuários fazem em jobs enquanto o trigger está alterando os funcionários (e impedem que esses usuários "vejam" as alterações que o trigger faz aos funcionários).

Outra maneira de evitar alterações em cargos durante a consulta é incluir a cláusula FOR UPDATE na instrução SELECT. No entanto, SELECT FOR UPDATE restringe a simultaneidade mais do que as tarefas LOCK TABLE no SHARE MODE.

LOCK TABLE jobs IN SHARE MODE impede que outros usuários alterem jobs, mas não bloqueiem os jobs no próprio modo de compartilhamento. Mudanças nos empregos provavelmente serão muito mais raras do que mudanças nos funcionários. Portanto, bloquear jobs no modo de compartilhamento fornece mais simultaneidade do que bloquear uma única linha de jobs no modo exclusivo.

Consulte também:

Criando o Trigger para Aplicar a Segunda Regra de Negócios

A segunda regra de negócios é: se um funcionário com o cargo j tiver o salário s, não será possível alterar o salário mínimo de j para um valor maior que s ou o salário máximo de j para um valor menor que s. (Para isso, os dados existentes serão inválidos.)

Essa regra pode ser violada quando a coluna min_salary ou max_salary da tabela de cargos é atualizada.

Para impor a regra, crie o trigger a seguir nos jobs de exibição de edição. Você pode informar a instrução CREATE TRIGGER no SQL*Plus ou na Planilha do SQL Developer. Como alternativa, você pode criar o trigger com a ferramenta Create Trigger do SQL Developer.

CREATE OR REPLACE TRIGGER jobs_aufer
AFTER UPDATE OF min_salary, max_salary ON jobs FOR EACH ROW
WHEN (NEW.min_salary > OLD.min_salary OR NEW.max_salary < OLD.max_salary)
DECLARE
  l_cnt NUMBER;
BEGIN
  LOCK TABLE employees IN SHARE MODE;

  SELECT COUNT(*) INTO l_cnt
  FROM employees
  WHERE job_id = :NEW.job_id
  AND salary NOT BETWEEN :NEW.min_salary and :NEW.max_salary;

  IF (l_cnt>0) THEN
    RAISE_APPLICATION_ERROR( -20001,
      'Salary update would violate ' || l_cnt || ' existing employee records' );
  END IF;
END;
/

LOCK TABLE employees IN SHARE MODE impede que outros usuários alterem os funcionários da tabela enquanto o trigger o está consultando. É necessário impedir alterações nos funcionários durante a consulta porque as leituras sem bloqueio impedem que o trigger "veja" as alterações que outros usuários fazem aos funcionários enquanto o trigger está alterando jobs (e impedem que esses usuários "veja" as alterações que o trigger faz aos jobs).

Para este trigger, SELECT FOR UPDATE não é uma alternativa ao LOCK TABLE IN SHARE MODE. Enquanto você está tentando alterar a faixa salarial deste cargo, esse acionador deve impedir que outros usuários alterem um salário para que fique fora da nova faixa. Dessa forma, o acionador deve bloquear todas as linhas na tabela de funcionários que tenham esse job_id e bloquear todas as linhas que alguém possa atualizar para que tenham esse job_id.

Uma alternativa a LOCK TABLE employees IN SHARE MODE é usar o pacote DBMS_LOCK para criar um bloqueio nomeado com o nome do job_id e, em seguida, usar triggers nas tabelas de funcionários e jobs para usar esse bloqueio nomeado para evitar atualizações simultâneas. No entanto, o uso de DBMS_LOCK e vários acionadores afeta negativamente o desempenho do tempo de execução.

Outra alternativa ao LOCK TABLE employees IN SHARE MODE é criar um acionador na tabela de funcionários que, para cada linha de funcionários alterada, bloqueia a linha de cargo correspondente nos cargos. No entanto, essa abordagem causa trabalho excessivo em atualizações na tabela de funcionários, que são frequentes.

O LOCK TABLE employees IN SHARE MODE é mais simples do que as alternativas anteriores, e as alterações na tabela de jobs são raras e provavelmente ocorrerão no momento da manutenção do aplicativo, quando o bloqueio da tabela não incomoda os usuários.

Consulte também:

Criando as Sequências

Observação: Você deve estar conectado ao Oracle Database como app_data do usuário.

Para criar as sequências que geram chaves primárias exclusivas para novos departamentos e novos funcionários, use as seguintes instruções (em qualquer uma das ordens). Você pode informar as instruções no SQL*Plus ou na Planilha do SQL Developer. Como alternativa, você pode criar as sequências com a ferramenta SQL Developer Create Sequence.

CREATE SEQUENCE employees_sequence START WITH 210;
CREATE SEQUENCE departments_sequence START WITH 275;

Para evitar conflitos com os dados que serão carregados das tabelas no esquema de amostra HR, os números iniciais para employees_sequence e departments_sequence devem exceder os valores máximos de employees.employee_id e departments.department_id, respectivamente. Após "Carregando os Dados", esta consulta exibe estes valores máximos:

SELECT MAX(e.employee_id), MAX(d.department_id)
FROM employees e, departments d;

Resultado:

MAX(E.EMPLOYEE_ID) MAX(D.DEPARTMENT_ID)
------------------ --------------------
               206                  270

Consulte Também: "Criando e Gerenciando Sequências"

Carregando os dados

Observação: Você deve estar conectado ao Oracle Database como app_data do usuário.

Carregue as tabelas do aplicativo com dados das tabelas no esquema de amostra HR.

Observação: O procedimento a seguir faz referência às tabelas do aplicativo por meio de suas exibições de edição.

No procedimento a seguir, você pode informar as instruções no SQL*Plus ou na Planilha do SQL Developer.

Para carregar dados nas tabelas:

  1. Carregue cargos com dados da tabela HR.JOBS:

     INSERT INTO jobs (job_id, job_title, min_salary, max_salary)
 SELECT job_id, job_title, min_salary, max_salary
   FROM HR.JOBS
 /

    Resultado:

     19 rows created.

  2. Carregue departamentos com dados da tabela HR.DEPARTMENTS:

     INSERT INTO departments (department_id, department_name, manager_id)
 SELECT department_id, department_name, manager_id
   FROM HR.DEPARTMENTS
 /

    Resultado:

     27 rows created.

  3. Carregue funcionários com dados das tabelas HR.EMPLOYEES e HR.JOB_HISTORY, usando expressões CASE e funções SQL pesquisadas para obter employees.country_code e employees.phone_number das funções HR.phone_number e SQL e uma subconsulta escalar para obter employees.job_start_date do HR.JOB_HISTORY:

     INSERT INTO employees (employee_id, first_name, last_name, email_addr,
   hire_date, country_code, phone_number, job_id, job_start_date, salary,
   manager_id, department_id)
 SELECT employee_id, first_name, last_name, email, hire_date,
   CASE WHEN phone_number LIKE '011.%'
     THEN '+' || SUBSTR( phone_number, INSTR( phone_number, '.' )+1,
       INSTR( phone_number, '.', 1, 2 ) -  INSTR( phone_number, '.' ) - 1 )
     ELSE '+1'
   END country_code,
   CASE WHEN phone_number LIKE '011.%'
     THEN SUBSTR( phone_number, INSTR(phone_number, '.', 1, 2 )+1 )
     ELSE phone_number
   END phone_number,
   job_id,
   NVL( (SELECT MAX(end_date+1)
         FROM HR.JOB_HISTORY jh
         WHERE jh.employee_id = employees.employee_id), hire_date),
   salary, manager_id, department_id
   FROM HR.EMPLOYEES
 /

    Resultado:

     107 rows created.

    Observação: A instrução INSERT anterior aciona o trigger criado em "Criando o Trigger para Impor a Primeira Regra de Negócios".

  4. Carregue o histórico_cargos com dados da tabela HR.JOB_HISTORY:

     INSERT INTO job_history (employee_id, job_id, start_date, end_date,
   department_id)
 SELECT employee_id, job_id, start_date, end_date, department_id
   FROM HR.JOB_HISTORY
 /

    Resultado:

     10 rows created.

  5. Fazer commit das alterações:

     COMMIT;

Consulte também:

Adicionando a Restrição de Chave Estrangeira

Observação: Você deve estar conectado ao Oracle Database como app_data do usuário.

Agora que as tabelas departamentos e funcionários contêm dados, adicione uma constraint de chave estrangeira com esta instrução ALTER TABLE. Você pode informar a instrução no SQL*Plus ou na Planilha do SQL Developer. Como alternativa, você pode adicionar a restrição com a ferramenta Add Foreign Key do SQL Developer.

ALTER TABLE departments#
ADD CONSTRAINT dept_to_emp_fk
FOREIGN KEY(manager_id) REFERENCES employees#;

Se você adicionar essa constraint de chave estrangeira antes que os números dos departamentos e dos funcionários contenham dados, receberá esse erro ao tentar carregar qualquer um deles com dados:

ORA-02291: integrity constraint (APP_DATA.JOB_HIST_TO_DEPT_FK) violated - parent key not found

Consulte Também: "Tutorial: Adicionando Restrições a Tabelas Existentes"

Concedendo Privilégios nos Objetos de Esquema aos Usuários

Observação: Você deve estar conectado ao Oracle Database como app_data do usuário.

Para conceder privilégios aos usuários, use a instrução SQL GRANT. Você pode informar as instruções GRANT no SQL*Plus ou na Planilha do SQL Developer.

Conceda ao app_code somente os privilégios necessários para criar employees_pkg:

GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO app_code;
GRANT SELECT ON departments TO app_code;
GRANT SELECT ON jobs TO app_code;
GRANT SELECT, INSERT on job_history TO app_code;
GRANT SELECT ON employees_sequence TO app_code;

Conceda a app_admin somente os privilégios necessários para criar admin_pkg:

GRANT SELECT, INSERT, UPDATE, DELETE ON jobs TO app_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON departments TO app_admin;
GRANT SELECT ON employees_sequence TO app_admin;
GRANT SELECT ON departments_sequence TO app_admin;

Consulte Também: Referência de Linguagem SQL do Oracle Database para obter informações sobre a instrução GRANT