Creazione degli oggetti dello schema e caricamento dei dati

Questa sezione mostra come creare le tabelle, le viste di edizione, i trigger e le sequenze per l'applicazione, come caricare i dati nelle tabelle e come concedere i privilegi su questi oggetti dello schema agli utenti che ne hanno bisogno.

Passi per creare gli oggetti dello schema e caricare i dati:

  1. Eseguire la connessione a Oracle Database come utente app_data.

    Per le istruzioni, vedere "Connessione a Oracle Database da SQL*Plus" o "Connessione a Oracle Database da SQL Developer".

  2. Creare le tabelle, con tutti i vincoli necessari ad eccezione del vincolo di chiave esterna da aggiungere dopo il caricamento dei dati.

  3. Creare le viste di edizione.

  4. Creare i trigger.

  5. Creare le sequenze.

  6. Caricare i dati nelle tabelle.

  7. Aggiungere il vincolo FOREIGN KEY.

Creazione delle Tabelle

Questa sezione mostra come creare le tabelle per l'applicazione, con tutti i vincoli necessari tranne uno, che è necessario aggiungere dopo aver caricato i dati.

Nota: è necessario essere connessi a Oracle Database come utente app_data.

Nella procedura riportata di seguito è possibile immettere le istruzioni in SQL*Plus o nel foglio di lavoro di SQL Developer. In alternativa, è possibile creare le tabelle con lo strumento SQL Developer Crea tabella.

I passi per creare le tabelle:

  1. Crea numero di mansioni, che memorizza le informazioni sulle mansioni nell'azienda (una riga per ogni mansione):

     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. Creare il numero di reparti, che memorizza le informazioni sui reparti dell'azienda (una riga per ogni reparto):

     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. Creare il numero di dipendenti, che memorizza le informazioni sui dipendenti nell'azienda (una riga per ogni dipendente):

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

    I motivi dei vincoli REF sono i seguenti:

    • Un dipendente deve avere una mansione esistente. Cioè, i valori nella colonna employee#.job_id devono essere valori anche nella colonna jobs#.job_id.

    • Un dipendente deve avere un manager che sia anche un dipendente. Cioè, i valori nella colonna employee#.manager_id devono essere anche valori nella colonna employee#.employee_id.

    • Un dipendente deve lavorare in un reparto esistente. Cioè, i valori nella colonna employee#.department_id devono essere valori anche nella colonna department#.department_id.

    Inoltre, il manager di un dipendente deve essere il manager del reparto in cui lavora il dipendente. Cioè, i valori nella colonna dipendenti#.manager_id devono essere anche valori nella colonna reparti#.manager_id. Tuttavia, non è stato possibile specificare il vincolo necessario quando è stato creato il numero di reparti perché il numero di dipendenti non esisteva ancora. Pertanto, è necessario aggiungere un vincolo di chiave esterna ai reparti n. più tardi (vedere "Aggiunta del vincolo di chiave esterna").

  4. Crea job_history#, che memorizza la cronologia delle mansioni di ogni dipendente dell'azienda (una riga per ogni mansione posseduta dal dipendente):

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

    I motivi dei vincoli REF sono che il dipendente, la mansione e il reparto devono esistere:

    • I valori nella colonna job_history#.employee_id devono essere anche valori nella colonna employee#.employee_id.

    • I valori nella colonna job_history#.job_id devono essere anche valori nella colonna jobs#.job_id.

    • I valori nella colonna job_history#.department_id devono essere valori anche nella colonna reparti#.department_id.

Vedere anche: "Creazione di tabelle"

Creazione delle viste di edizione

Nota: è necessario essere connessi a Oracle Database come utente app_data.

Per creare le viste di edizione, utilizzare le istruzioni riportate di seguito (in qualsiasi ordine). È possibile immettere le istruzioni in SQL*Plus o nel foglio di lavoro di SQL Developer. In alternativa, è possibile creare le viste di edizione con lo strumento Crea vista di 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#
/

Nota: l'applicazione deve sempre fare riferimento alle tabelle di base tramite le viste di edizione. In caso contrario, le viste di edizione non coprono le tabelle e non è possibile utilizzare EBR per aggiornare l'applicazione finita quando è in uso.

Vedere anche:

Creazione dei trigger

Nota: è necessario essere connessi a Oracle Database come utente app_data.

I trigger nell'applicazione applicano le seguenti regole business:

Vedere anche: Utilizzo dei trigger per informazioni sui trigger

Creazione del trigger per applicare la prima regola business

La prima regola aziendale è: un dipendente con la mansione j deve avere uno stipendio compreso tra lo stipendio minimo e quello massimo per la mansione j.

Questa regola può essere violata quando viene inserita una nuova riga nella tabella dei dipendenti o quando viene aggiornata la colonna stipendio o job_id della tabella dei dipendenti.

Per applicare la regola, creare il trigger seguente sui dipendenti della vista di edizione. È possibile immettere l'istruzione CREATE TRIGGER in SQL*Plus o nel foglio di lavoro di SQL Developer. In alternativa, è possibile creare il trigger con lo strumento SQL Developer Crea 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 impedisce ad altri utenti di modificare i job della tabella durante la query del trigger. La prevenzione delle modifiche ai job durante la query è necessaria perché le letture non bloccanti impediscono al trigger di "vedere" le modifiche apportate dagli altri utenti ai job mentre il trigger sta modificando i dipendenti e impediscono a tali utenti di "vedere" le modifiche apportate dal trigger ai dipendenti.

Un altro modo per impedire modifiche ai job durante la query è includere la clausola FOR UPDATE nell'istruzione SELECT. Tuttavia, SELECT FOR UPDATE limita la concorrenza più dei job LOCK TABLE IN SHARE MODE.

LOCK TABLE jobs IN SHARE MODE impedisce ad altri utenti di modificare i job, ma non di bloccare i job in modalità condivisione. I cambiamenti ai posti di lavoro saranno probabilmente molto più rari dei cambiamenti ai dipendenti. Pertanto, il blocco dei job in modalità condivisione fornisce una maggiore concorrenza rispetto al blocco di una singola riga di job in modalità esclusiva.

Vedere anche:

Creazione del trigger per applicare la seconda regola business

La seconda regola aziendale è: se un dipendente con la mansione j ha uno stipendio s, non è possibile modificare lo stipendio minimo per j in un valore maggiore di s o lo stipendio massimo per j in un valore minore di s. (A tal fine renderebbero non validi i dati esistenti.)

Questa regola può essere violata quando viene aggiornata la colonna min_salary o max_salary della tabella dei job.

Per applicare la regola, creare il trigger seguente nei job di visualizzazione dell'edizione. È possibile immettere l'istruzione CREATE TRIGGER in SQL*Plus o nel foglio di lavoro di SQL Developer. In alternativa, è possibile creare il trigger con lo strumento SQL Developer Crea 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 impedisce ad altri utenti di modificare i dipendenti della tabella durante la query del trigger. Impedire le modifiche ai dipendenti durante la query è necessario perché le letture non bloccanti impediscono al trigger di "vedere" le modifiche apportate da altri utenti ai dipendenti mentre il trigger sta modificando le mansioni (e impediscono a tali utenti di "vedere" le modifiche apportate dal trigger alle mansioni).

Per questo trigger, SELECT FOR UPDATE non è un'alternativa a LOCK TABLE IN SHARE MODE. Mentre si sta tentando di modificare l'intervallo di stipendio per questa mansione, questo trigger deve impedire ad altri utenti di modificare uno stipendio in modo che non sia compreso nel nuovo intervallo. Pertanto, il trigger deve bloccare tutte le righe nella tabella dei dipendenti con questo job_id e bloccare tutte le righe che qualcuno potrebbe aggiornare per avere questo job_id.

Un'alternativa a LOCK TABLE employees IN SHARE MODE consiste nell'utilizzare il package DBMS_LOCK per creare un lock denominato con il nome del job_id e quindi utilizzare i trigger nelle tabelle dipendenti e job per utilizzare questo lock denominato per impedire gli aggiornamenti concorrenti. Tuttavia, l'utilizzo di DBMS_LOCK e di più trigger influisce negativamente sulle prestazioni del runtime.

Un'altra alternativa a LOCK TABLE employees IN SHARE MODE consiste nel creare un trigger nella tabella dei dipendenti che, per ogni riga modificata di dipendenti, blocca la riga mansione corrispondente nelle mansioni. Tuttavia, questo approccio causa un lavoro eccessivo sugli aggiornamenti della tabella dei dipendenti, che sono frequenti.

LOCK TABLE employees IN SHARE MODE è più semplice delle alternative precedenti e le modifiche alla tabella dei job sono rare e potrebbero verificarsi al momento della manutenzione dell'applicazione, quando il blocco della tabella non disturba gli utenti.

Vedere anche:

Creazione delle sequenze

Nota: è necessario essere connessi a Oracle Database come utente app_data.

Per creare le sequenze che generano chiavi primarie univoche per nuovi reparti e nuovi dipendenti, utilizzare i seguenti rendiconti (in entrambi gli ordini). È possibile immettere le istruzioni in SQL*Plus o nel foglio di lavoro di SQL Developer. In alternativa, è possibile creare le sequenze con lo strumento SQL Developer Crea sequenza.

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

Per evitare conflitti con i dati che verranno caricati dalle tabelle nello schema HR di esempio, i numeri iniziali per employee_sequence e department_sequence devono superare i valori massimi rispettivamente di employee.employee_id e department.department_id. Dopo "Caricamento dei dati", questa query visualizza i valori massimi riportati di seguito.

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

Risultato:

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

Vedere anche: "Creazione e gestione delle sequenze"

Caricamento dei dati

Nota: è necessario essere connessi a Oracle Database come utente app_data.

Caricare le tabelle dell'applicazione con i dati delle tabelle nello schema HR di esempio.

Nota: la procedura riportata di seguito fa riferimento alle tabelle dell'applicazione mediante le relative viste di edizione.

Nella procedura riportata di seguito è possibile immettere le istruzioni in SQL*Plus o nel foglio di lavoro di SQL Developer.

Per caricare i dati nelle tabelle:

  1. Carica OdL con dati dalla tabella 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
 /

    Risultato:

     19 rows created.

  2. Caricare i reparti con i dati della tabella HR.DEPARTMENTS:

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

    Risultato:

     27 rows created.

  3. Caricare i dipendenti con i dati delle tabelle HR.EMPLOYEES e HR.JOB_HISTORY, utilizzando le espressioni CASE e le funzioni SQL cercate per ottenere i dipendenti.country_code e employee.phone_number dalle funzioni HR.phone_number e SQL e una subquery scalare per ottenere i dipendenti.job_start_date da 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
 /

    Risultato:

     107 rows created.

    Nota: l'istruzione INSERT precedente attiva il trigger creato in "Creazione del trigger per applicare la prima regola business".

  4. Carica job_history con i dati della tabella 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
 /

    Risultato:

     10 rows created.

  5. Eseguire il commit delle modifiche:

     COMMIT;

Vedere anche:

Aggiunta del vincolo FOREIGN KEY

Nota: è necessario essere connessi a Oracle Database come utente app_data.

Ora che le tabelle reparti e dipendenti contengono dati, aggiungere un vincolo di chiave esterna con la seguente istruzione ALTER TABLE. È possibile immettere l'istruzione in SQL*Plus o nel foglio di lavoro di SQL Developer. In alternativa, è possibile aggiungere il vincolo con lo strumento SQL Developer Aggiungi chiave esterna.

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

Se si aggiunge questo vincolo di chiave esterna prima che il numero di reparti e il numero di dipendenti contengano dati, si ottiene questo errore quando si tenta di caricare uno dei due con i dati:

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

Esercitazione: "Esercitazione: Aggiunta di vincoli alle tabelle esistenti"

Concessione dei privilegi sugli oggetti dello schema agli utenti

Nota: è necessario essere connessi a Oracle Database come utente app_data.

Per concedere privilegi agli utenti, utilizzare l'istruzione SQL GRANT. È possibile immettere le istruzioni GRANT in SQL*Plus o nel foglio di lavoro di SQL Developer.

Concedere all'app_code solo i privilegi necessari per creare employee_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;

Concedere a app_admin solo i privilegi necessari per creare 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;

Vedere anche: Oracle Database SQL Language Reference per informazioni sull'istruzione GRANT