Créer des objets de schéma et charger des données

Cette section explique comment créer les tables, éditer des vues, des déclencheurs et des séquences pour l'application, comment charger des données dans les tables et comment accorder des privilèges sur ces objets de schéma aux utilisateurs qui en ont besoin.

Etapes de création des objets de schéma et de chargement des données :

  1. Connectez-vous à Oracle Database en tant qu'utilisateur app_data.

    Pour obtenir des instructions, voir "Connexion à Oracle Database à partir de SQL*Plus" ou "Connexion à Oracle Database à partir de SQL Developer".

  2. Créez les tables avec toutes les contraintes nécessaires, à l'exception de la contrainte de clé étrangère que vous devez ajouter après le chargement des données.

  3. Créez les vues d'édition.

  4. Créez les déclencheurs.

  5. Créez les séquences.

  6. Chargez les données dans les tables.

  7. Ajoutez la contrainte de clé étrangère.

Créer les tables

Cette section explique comment créer les tables pour l'application, avec toutes les contraintes nécessaires, sauf une, que vous devez ajouter après avoir chargé les données.

Note : Vous devez être connecté à Oracle Database en tant qu'utilisateur app_data.

Dans la procédure suivante, vous pouvez entrer les instructions dans SQL*Plus ou dans la feuille de calcul de SQL Developer. Vous pouvez également créer les tables à l'aide de l'outil Create Table de SQL Developer.

Etapes de création des tables :

  1. Create jobs#, qui stocke des informations sur les emplois dans la société (une ligne pour chaque emploi) :

     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. Créer un numéro de service, qui stocke des informations sur les services de la société (un enregistrement pour chaque service) :

     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. Créer un numéro d'employé, qui stocke des informations sur les employés de la société (un enregistrement pour chaque employé) :

     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#
 )
 /

    Les raisons des contraintes REF sont les suivantes :

    • Un employé doit avoir un emploi existant. Autrement dit, les valeurs de la colonne employees#.job_id doivent également être des valeurs de la colonne jobs#.job_id.

    • Un employé doit avoir un gestionnaire qui est également un employé. Autrement dit, les valeurs de la colonne employees#.manager_id doivent également être des valeurs de la colonne employees#.employee_id.

    • Un employé doit travailler dans un service existant. Autrement dit, les valeurs de la colonne employees#.department_id doivent également être des valeurs de la colonne departments#.department_id.

    De plus, le gestionnaire d'un employé doit être le gestionnaire du service dans lequel il travaille. Autrement dit, les valeurs de la colonne employees#.manager_id doivent également être des valeurs dans la colonne departments#.manager_id. Toutefois, vous n'avez pas pu spécifier la contrainte nécessaire lors de la création du numéro de service, car le numéro d'employé n'existait pas encore. Par conséquent, vous devez ajouter une contrainte de clé étrangère au numéro de service plus tard (voir "Ajout de la contrainte de clé étrangère").

  4. Créez job_history#, qui stocke l'historique d'emploi de chaque employé de la société (un enregistrement pour chaque emploi occupé par l'employé) :

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

    Les motifs des contraintes REF sont que l'employé, l'emploi et le service doivent exister :

    • Les valeurs de la colonne job_history#.employee_id doivent également être des valeurs de la colonne employees#.employee_id.

    • Les valeurs de la colonne job_history#.job_id doivent également être des valeurs dans la colonne jobs#.job_id.

    • Les valeurs de la colonne job_history#.department_id doivent également être des valeurs dans la colonne department#.department_id.

Voir aussi : "Création de tables"

Créer des vues d'édition

Note : Vous devez être connecté à Oracle Database en tant qu'utilisateur app_data.

Pour créer les vues d'édition, utilisez les instructions suivantes (dans n'importe quel ordre). Vous pouvez entrer les instructions dans SQL*Plus ou dans la feuille de calcul de SQL Developer. Vous pouvez également créer les vues d'édition à l'aide de l'outil Create View de 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#
/

Note : L'application doit toujours référencer les tables de base au moyen des vues d'édition. Sinon, les vues d'édition ne couvrent pas les tables et vous ne pouvez pas utiliser EBR pour mettre à niveau l'application terminée lorsqu'elle est utilisée.

Voir aussi :

Créer des déclencheurs

Note : Vous devez être connecté à Oracle Database en tant qu'utilisateur app_data.

Les déclencheurs de l'application appliquent les règles suivantes :

Voir aussi : Utilisation de déclencheurs, pour plus d'informations sur les déclencheurs

Créer le déclencheur pour appliquer la première règle métier

La première règle d'affaires est la suivante : Un employé dont l'emploi est j doit avoir un salaire compris entre le salaire minimum et le salaire maximum pour l'emploi j.

Cette règle peut être enfreinte soit lorsqu'une nouvelle rangée est insérée dans la table des employés, soit lorsque la colonne salary or job_id de la table des employés est mise à jour.

Pour appliquer la règle, créez le déclencheur suivant sur les employés de la vue d'édition. Vous pouvez entrer l'instruction CREATE TRIGGER dans SQL*Plus ou dans la feuille de calcul de SQL Developer. Vous pouvez également créer le déclencheur à l'aide de l'outil Create Trigger de 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 empêche les autres utilisateurs de modifier les tâches de table pendant que le déclencheur les interroge. Il est nécessaire d'empêcher les modifications apportées aux emplois au cours de l'interrogation, car les lectures sans blocage empêchent le déclencheur de "voir" les modifications apportées aux emplois par d'autres utilisateurs alors que le déclencheur modifie les employés (et empêchent ces utilisateurs de "voir" les modifications apportées par le déclencheur aux employés).

Une autre façon d'empêcher les modifications apportées aux travaux au cours de l'interrogation consiste à inclure la clause FOR UPDATE dans l'instruction SELECT. Toutefois, SELECT FOR UPDATE limite les accès simultanés à plus de tâches LOCK TABLE IN SHARE MODE.

LOCK TABLE jobs IN SHARE MODE empêche les autres utilisateurs de modifier des tâches, mais pas de verrouiller elles-mêmes les tâches en mode de partage. Les changements apportés aux emplois seront probablement beaucoup plus rares que les changements apportés aux employés. Par conséquent, le verrouillage des travaux en mode de partage offre plus de concurrence que le verrouillage d'une seule rangée de travaux en mode exclusif.

Voir aussi :

Créer le déclencheur pour appliquer la deuxième règle métier

La deuxième règle d'affaires est la suivante : Si un employé dont l'emploi est j a des s de salaire, vous ne pouvez pas remplacer le salaire minimum de j par une valeur supérieure à s ou le salaire maximum de j par une valeur inférieure à s. (Cela rendrait les données existantes non valides.)

Cette règle peut être violée lorsque la colonne min_salary ou max_salary de la table des travaux est mise à jour.

Pour appliquer la règle, créez le déclencheur suivant sur les travaux de vue d'édition. Vous pouvez entrer l'instruction CREATE TRIGGER dans SQL*Plus ou dans la feuille de calcul de SQL Developer. Vous pouvez également créer le déclencheur à l'aide de l'outil Create Trigger de 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 empêche les autres utilisateurs de modifier les employés de table pendant que le déclencheur l'interroge. Il est nécessaire d'empêcher les modifications apportées aux employés au cours de l'interrogation, car les lectures sans blocage empêchent le déclencheur de "voir" les modifications apportées aux employés par d'autres utilisateurs pendant que le déclencheur change d'emploi (et empêchent ces utilisateurs de "voir" les modifications apportées aux emplois par le déclencheur).

Pour ce déclencheur, SELECT FOR UPDATE n'est pas une alternative à LOCK TABLE IN SHARE MODE. Pendant que vous tentez de modifier la fourchette de salaires pour cet emploi, ce déclencheur doit empêcher d'autres utilisateurs de modifier un salaire pour qu'il soit en dehors de la nouvelle fourchette. Par conséquent, le déclencheur doit verrouiller toutes les rangées de la table des employés qui ont ce job_id et verrouiller toutes les rangées que quelqu'un pourrait mettre à jour pour avoir ce job_id.

Une alternative à LOCK TABLE employees IN SHARE MODE consiste à utiliser l'ensemble DBMS_LOCK pour créer un verrou nommé avec le nom de job_id, puis à utiliser des déclencheurs sur les tables d'employés et d'emplois pour utiliser ce verrou nommé afin d'empêcher les mises à jour concurrentes. Toutefois, l'utilisation de DBMS_LOCK et de plusieurs déclencheurs a une incidence négative sur les performances d'exécution.

Une autre alternative à LOCK TABLE employees IN SHARE MODE consiste à créer un déclencheur sur la table des employés qui, pour chaque rangée d'employés modifiée, verrouille la rangée d'emploi correspondante dans les emplois. Cependant, cette approche entraîne un travail excessif sur les mises à jour de la table des employés, qui sont fréquentes.

LOCK TABLE employees IN SHARE MODE est plus simple que les alternatives précédentes, et les modifications apportées à la table des tâches sont rares et risquent de se produire lors de la maintenance de l'application, lorsque le verrouillage de la table ne gêne pas les utilisateurs.

Voir aussi :

Création des séquences

Note : Vous devez être connecté à Oracle Database en tant qu'utilisateur app_data.

Pour créer les séquences qui génèrent des clés primaires uniques pour les nouveaux services et les nouveaux employés, utilisez les instructions suivantes (dans l'un ou l'autre ordre). Vous pouvez entrer les instructions dans SQL*Plus ou dans la feuille de calcul de SQL Developer. Vous pouvez également créer des séquences à l'aide de l'outil Create Sequence de SQL Developer.

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

Pour éviter tout conflit avec les données que vous chargerez à partir des tables de l'exemple de schéma HR, les numéros de début pour employee_sequence et department_sequence doivent dépasser les valeurs maximales de employees.employee_id et department.department_id, respectivement. Après "Chargement des données", cette interrogation affiche les valeurs maximales suivantes :

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

Résultat :

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

Voir aussi : "Création et gestion des séquences"

Chargement des données

Note : Vous devez être connecté à Oracle Database en tant qu'utilisateur app_data.

Chargez les tables de l'application avec les données des tables du schéma HR.

Note : La procédure suivante référence les tables de l'application au moyen de leurs vues d'édition.

Dans la procédure suivante, vous pouvez entrer les instructions dans SQL*Plus ou dans la feuille de calcul de SQL Developer.

Pour charger des données dans les tables :

  1. Charger les emplois avec les données de la table 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
 /

    Résultat :

     19 rows created.

  2. Charger les services avec les données de la table HR.DEPARTMENTS :

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

    Résultat :

     27 rows created.

  3. Charger les employés avec les données des tables HR.EMPLOYEES et HR.JOB_HISTORY, à l'aide d'expressions CASE recherchées et de fonctions SQL pour obtenir employees.country_code et employees.phone_number des fonctions HR.phone_number et SQL et d'une sous-interrogation scalaire pour obtenir employee.job_start_date de 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
 /

    Résultat :

     107 rows created.

    Note : L'énoncé INSERT précédent déclenche le déclencheur créé dans "Création du déclencheur pour appliquer la première règle d'affaires".

  4. Chargez job_history avec les données de la table 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
 /

    Résultat :

     10 rows created.

  5. Valider les modifications :

     COMMIT;

Voir aussi :

Ajout d'une contrainte de clé étrangère

Note : Vous devez être connecté à Oracle Database en tant qu'utilisateur app_data.

Maintenant que les services et les employés des tables contiennent des données, ajoutez une contrainte de clé étrangère avec l'instruction ALTER TABLE suivante. Vous pouvez entrer l'instruction dans SQL*Plus ou dans la feuille de calcul de SQL Developer. Vous pouvez également ajouter la contrainte à l'aide de l'outil SQL Developer Add Foreign Key.

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

Si vous ajoutez cette contrainte de clé étrangère avant que les services# et les employés# contiennent des données, vous obtenez cette erreur lorsque vous tentez de charger l'une d'entre elles avec des données :

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

Voir aussi : "Tutoriel : Ajout de contraintes aux tables existantes"

Accorder des privilèges aux utilisateurs sur les objets de schéma

Note : Vous devez être connecté à Oracle Database en tant qu'utilisateur app_data.

Pour accorder des privilèges aux utilisateurs, utilisez l'instruction SQL GRANT. Vous pouvez entrer les instructions GRANT dans SQL*Plus ou dans la feuille de calcul de SQL Developer.

Accordez à app_code uniquement les privilèges dont il a besoin pour créer 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;

Accordez à app_admin uniquement les privilèges dont il a besoin pour créer 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;

Voir aussi : Informations de référence sur le langage SQL pour Oracle Database pour plus d'informations sur l'énoncé GRANT