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

Cette section explique comment créer des tables, des vues d'édition, 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 :

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

Pour obtenir des instructions, reportez-vous à Connexion à Oracle Database à partir de SQL*Plus ou à Connexion à Oracle Database à partir de SQL Developer.
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 avoir chargé les données.
Créez les vues d'édition.
Créez les déclencheurs.
Créez les séquences.
Chargez les données dans les tables.
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.

Remarque : 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 SQL Developer Create Table.

Etapes de création des tables :

Créer emplois#, qui stocke des informations sur les emplois de 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
 )
 /

Créez un numéro de service, qui stocke les informations sur les services de la société (une ligne par 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)
 )
 /

Créez employees#, qui stocke les informations sur les employés de la société (une ligne par 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 responsable 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.

En outre, le responsable d'un salarié doit être le responsable du service dans lequel il travaille. Autrement dit, les valeurs de la colonne employees#.manager_id doivent également être des valeurs de la colonne departments#.manager_id. Toutefois, vous n'avez pas pu spécifier la contrainte nécessaire lors de la création de departments#, car employees# n'existait pas encore. Par conséquent, vous devez ajouter une contrainte de clé étrangère au numéro de service ultérieurement (reportez-vous à la section "Ajout de la contrainte de clé étrangère").

Créez job_history#, qui stocke l'historique de chaque employé de la société (une ligne 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 raisons 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 de la colonne jobs#.job_id.
Les valeurs de la colonne job_history#.department_id doivent également être des valeurs de la colonne departments#.department_id.

Voir aussi : "Créer des tables"

Création des vues Editioning

Remarque : 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 SQL Developer Create View.

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

Remarque : L'application doit toujours référencer les tables de base via les vues de modification. 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éation de vues"
Guide de développement Oracle Database, pour des informations générales sur l'édition de vues
Guide de développement Oracle Database, pour plus d'informations sur la préparation d'une application à l'utilisation de vues d'édition

Création des déclencheurs

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

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

Un employé ayant le poste j doit avoir un salaire compris entre le salaire minimum et le salaire maximum pour le poste j.
Si un employé occupant le poste j a un salaire s, 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. (Cette opération rendrait les données existantes non valides.)

Voir aussi : Utiliser des déclencheurs, pour plus d'informations sur les déclencheurs.

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

La première règle métier est la suivante : un employé occupant le poste j doit avoir un salaire compris entre le salaire minimum et le salaire maximum pour le poste j.

Cette règle peut être enfreinte lorsqu'une nouvelle ligne est insérée dans la table employees ou lorsque la colonne salary ou job_id de la table employees est mise à jour.

Pour appliquer la règle, créez le déclencheur suivant sur les employés de la vue de modification. 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 SQL Developer Create Trigger.

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 travaux de table pendant que le déclencheur l'interroge. Il est nécessaire d'empêcher les modifications apportées aux emplois au cours de la requête, car les lectures non bloquantes empêchent le déclencheur de "voir" les modifications apportées aux emplois par d'autres utilisateurs alors que le déclencheur change d'employés (et empêchent ces utilisateurs de "voir" les modifications apportées aux employés par le déclencheur).

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. Cependant, SELECT FOR UPDATE limite la simultanéité d'accès aux données plus que les travaux LOCK TABLE du MODE SHARE.

LOCK TABLE jobs IN SHARE MODE empêche les autres utilisateurs de modifier les travaux, mais pas de verrouiller eux-mêmes les travaux en mode partage. Les changements d'emploi seront probablement beaucoup plus rares que les changements d'employés. Par conséquent, le verrouillage des travaux en mode partage offre plus de simultanéité que le verrouillage d'une seule ligne de travaux en mode exclusif.

Voir aussi :

Guide de développement Oracle Database, pour plus d'informations sur le verrouillage des tables dans le MODE Partage
Référence du langage PL/SQL d'Oracle Database, pour plus d'informations sur SELECT FOR UPDATE
"Création de déclencheurs"
"Tutoriel : Comment fonctionnent les sous-programmes employees_pkg" pour voir comment fonctionne le déclencheur employees_aiufer

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

La deuxième règle métier est la suivante : si un employé occupant le poste j a un salaire s, 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. (Cette opération rendrait les données existantes non valides.)

Cette règle peut être enfreinte lorsque la colonne min_salary ou max_salary de la table jobs 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 SQL Developer Create Trigger.

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 la 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 la requête, car les lectures non bloquantes empêchent le déclencheur de "voir" les modifications apportées aux employés par d'autres utilisateurs pendant que le déclencheur change de travail (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 au paramètre LOCK TABLE IN SHARE MODE. Pendant que vous essayez de modifier la fourchette de salaires pour cet emploi, ce déclencheur doit empêcher les autres utilisateurs de modifier un salaire pour qu'il se situe en dehors de la nouvelle fourchette. Par conséquent, le déclencheur doit verrouiller toutes les lignes de la table employees dont l'ID travail est et verrouiller toutes les lignes que quelqu'un peut mettre à jour pour avoir cet ID travail.

Une alternative à LOCK TABLE employees IN SHARE MODE consiste à utiliser le package DBMS_LOCK pour créer un verrou nommé portant le nom de job_id, puis à utiliser des déclencheurs sur les tables employees et jobs afin d'utiliser ce verrou nommé pour empêcher les mises à jour simultanées. Toutefois, l'utilisation de DBMS_LOCK et de plusieurs déclencheurs a un impact négatif sur les performances d'exécution.

Une autre alternative à LOCK TABLE employees IN SHARE MODE consiste à créer un déclencheur sur la table employees qui, pour chaque ligne d'employés modifiée, verrouille la ligne 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 de travaux sont rares et susceptibles de se produire lors de la maintenance de l'application, lorsque le verrouillage de la table ne gêne pas les utilisateurs.

Voir aussi :

Guide de développement Oracle Database, pour plus d'informations sur le verrouillage de tables avec SHARE MODE
Manuel Oracle Database PL/SQL Packages and Types Reference, pour plus d'information sur le package DBMS_LOCK
"Création de déclencheurs"
Tutoriel : Affichage du fonctionnement des sous-programmes admin_pkg

Créer les séquences

Remarque : 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 les séquences à l'aide de l'outil SQL Developer Create Sequence.

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

Pour éviter tout conflit avec les données que vous allez charger à partir des tables de l'exemple de schéma HR, les numéros de départ de employees_sequence et departments_sequence doivent dépasser les valeurs maximales de employees.employee_id et departments.department_id, respectivement. Après "Charger les données", cette requête affiche les valeurs maximales suivantes :

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

Résultats :

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

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

Charger les données

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

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

Remarque : La procédure suivante référence les tables de l'application via leurs vues de modification.

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 :

Charger les travaux 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ésultats :

 19 rows created.

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ésultats :

 27 rows created.

Charger les employés dont les données proviennent des tables HR.EMPLOYEES et HR.JOB_HISTORY, à l'aide d'expressions CASE et de fonctions SQL recherchées, afin d'obtenir employees.country_code et employees.phone_number à partir des fonctions HR.phone_number et SQL, ainsi qu'une sous-interrogation scalaire pour obtenir employees.job_start_date à partir 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ésultats :

 107 rows created.

Remarque : l'instruction INSERT précédente déclenche le déclencheur créé dans la section "Création du déclencheur pour appliquer la première règle métier".

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ésultats :

 10 rows created.

Validez les modifications :
```
 COMMIT;
```

Voir aussi :

"A propos de l'instruction INSERT"
"A propos du schéma échantillon HR"
Utilisation d'expressions CASE dans les requêtes
"Utilisation de fonctions liées à NULL dans les requêtes" pour plus d'informations sur la fonction NVL
Manuel Oracle Database SQL Language Reference, pour plus d'informations sur l'instruction SQL

Ajout de la contrainte de clé étrangère

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

Maintenant que les tables que les services et les employés 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 départements# et employees# ne contiennent des données, vous obtenez cette erreur lorsque vous essayez de charger l'un d'entre eux 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 sur les objets de schéma aux utilisateurs

Remarque : 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 : Référence de langage SQL Oracle Database pour plus d'informations sur l'instruction GRANT.