Schemaobjekte erstellen und Daten laden

In diesem Abschnitt wird gezeigt, wie Sie die Tabellen, Editionsansichten, Trigger und Sequences für die Anwendung erstellen, Daten in die Tabellen laden und den Benutzern, die sie benötigen, Berechtigungen für diese Schemaobjekte erteilen.

Schritte zum Erstellen der Schemaobjekte und Laden der Daten:

  1. Verbindung zu Oracle Database als Benutzer app_data herstellen.

    Eine Anleitung finden Sie unter "Verbindung mit Oracle Database aus SQL*Plus herstellen" oder "Verbindung mit Oracle Database aus SQL Developer herstellen".

  2. Erstellen Sie die Tabellen mit allen erforderlichen Constraints, mit Ausnahme des Fremdschlüssel-Constraints, das Sie nach dem Laden der Daten hinzufügen müssen.

  3. Erstellen Sie die Editionsansichten.

  4. Erstellen Sie die Trigger.

  5. Erstellen Sie die Sequenzen.

  6. Die Daten in die Tabellen laden

  7. Fügen Sie das Fremdschlüssel-Constraint hinzu.

Tabellen erstellen

In diesem Abschnitt wird gezeigt, wie Sie die Tabellen für die Anwendung mit allen erforderlichen Constraints erstellen, mit Ausnahme einer, die Sie nach dem Laden der Daten hinzufügen müssen.

Hinweis: Sie müssen als Benutzer app_data mit Oracle Database verbunden sein.

In der folgenden Prozedur können Sie die Anweisungen entweder in SQL*Plus oder im Arbeitsblatt von SQL Developer eingeben. Alternativ können Sie die Tabellen mit dem SQL Developer-Tool "Tabelle erstellen" erstellen.

Schritte zum Erstellen der Tabellen:

  1. Erstellen Sie die Jobnummer, in der Informationen zu den Jobs im Unternehmen gespeichert werden (ein Datensatz für jeden Job):

     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. Erstellen Sie die Abteilungsnummer, in der Informationen zu den Abteilungen im Unternehmen gespeichert werden (ein Datensatz für jede Abteilung):

     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. Erstellen Sie eine Personalnummer, in der Informationen über die Mitarbeiter im Unternehmen gespeichert werden (ein Datensatz für jeden Mitarbeiter):

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

    Die Gründe für die REF-Constraints sind:

    • Ein Mitarbeiter muss eine vorhandene Tätigkeit haben. Das heißt, Werte in der Spalte employees#.job_id müssen auch Werte in der Spalte jobs#.job_id sein.

    • Ein Mitarbeiter muss einen Manager haben, der auch ein Mitarbeiter ist. Das heißt, Werte in der Spalte employees#.manager_id müssen auch Werte in der Spalte employees#.employee_id sein.

    • Ein Mitarbeiter muss in einer vorhandenen Abteilung arbeiten. Das heißt, Werte in der Spalte employees#.department_id müssen auch Werte in der Spalte departments#.department_id sein.

    Außerdem muss der Manager eines Mitarbeiters der Manager der Abteilung sein, in der der Mitarbeiter tätig ist. Das heißt, Werte in der Spalte employees#.manager_id müssen auch Werte in der Spalte departments#.manager_id sein. Sie konnten den erforderlichen Constraint jedoch nicht angeben, wenn Sie die Abteilungsnummer erstellt haben, da die Personalnummer noch nicht vorhanden war. Daher müssen Sie später einen Fremdschlüssel-Constraint zu departments# hinzufügen (siehe "Adding the Foreign Key Constraint").

  4. job_history# erstellen, in dem die Tätigkeitshistorie jedes Mitarbeiters im Unternehmen gespeichert wird (ein Datensatz für jede Tätigkeit des Mitarbeiters):

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

    Die Gründe für die REF-Constraints sind, dass Mitarbeiter, Tätigkeit und Abteilung vorhanden sein müssen:

    • Die Werte in der Spalte job_history#.employee_id müssen auch Werte in der Spalte employees#.employee_id sein.

    • Werte in der Spalte job_history#.job_id müssen auch Werte in der Spalte jobs#.job_id sein.

    • Werte in der Spalte job_history#.department_id müssen auch Werte in der Spalte departments#.department_id sein.

Siehe auch: "Tabellen erstellen"

Editionsansichten erstellen

Hinweis: Sie müssen als Benutzer app_data mit Oracle Database verbunden sein.

Um die Editionsansichten zu erstellen, verwenden Sie die folgenden Anweisungen (in beliebiger Reihenfolge). Sie können die Anweisungen entweder in SQL*Plus oder im Arbeitsblatt von SQL Developer eingeben. Alternativ können Sie die Editionsansichten mit dem SQL Developer-Tool "Ansicht erstellen" erstellen.

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

Hinweis: Die Anwendung muss die Basistabellen immer über die Editionsansichten referenzieren. Andernfalls decken die Editionsansichten die Tabellen nicht ab, und Sie können EBR nicht verwenden, um die fertige Anwendung zu aktualisieren, wenn sie verwendet wird.

Siehe:

Trigger erstellen

Hinweis: Sie müssen als Benutzer app_data mit Oracle Database verbunden sein.

Die Trigger in der Anwendung setzen die folgenden Geschäftsregeln durch:

Siehe auch: Trigger verwenden, für Informationen zu Triggern

Trigger zum Erzwingen der ersten Geschäftsregel erstellen

Die erste Geschäftsregel lautet: Ein Mitarbeiter mit der Tätigkeit j muss ein Gehalt zwischen dem Mindest- und dem Höchstgehalt für die Tätigkeit j aufweisen.

Diese Regel kann verletzt werden, wenn entweder eine neue Zeile in die Mitarbeitertabelle eingefügt wird oder wenn die Spalte Gehalt oder job_id der Mitarbeitertabelle aktualisiert wird.

Um die Regel durchzusetzen, erstellen Sie den folgenden Trigger für die Editions-View-Mitarbeiter. Sie können die Anweisung CREATE TRIGGER entweder in SQL*Plus oder im Arbeitsblatt von SQL Developer eingeben. Alternativ können Sie den Trigger mit dem SQL Developer-Tool Create Trigger erstellen.

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 verhindert, dass andere Benutzer die Tabellenjobs ändern, während der Trigger sie abfragt. Die Verhinderung von Job-Änderungen während der Abfrage ist erforderlich, da nicht blockierende Lesevorgänge verhindern, dass der Trigger Änderungen sieht, die andere Benutzer an Jobs vornehmen, während der Trigger Mitarbeiter ändert (und verhindern, dass diese Benutzer die Änderungen sehen, die der Trigger an Mitarbeitern vornimmt).

Eine weitere Möglichkeit, Änderungen an Jobs während der Abfrage zu verhindern, besteht darin, die FOR UPDATE-Klausel in die SELECT-Anweisung aufzunehmen. SELECT FOR UPDATE beschränkt jedoch den gleichzeitigen Zugriff mehr als LOCK TABLE-Jobs im SHARE MODE.

LOCK TABLE jobs IN SHARE MODE verhindert, dass andere Benutzer Jobs ändern, Jobs jedoch nicht selbst im Freigabemodus sperren. Änderungen an den Arbeitsplätzen werden wahrscheinlich viel seltener sein als Änderungen an den Mitarbeitern. Daher bietet das Sperren von Jobs im Share-Modus mehr gleichzeitigen Zugriff als das Sperren einer einzelnen Zeile von Jobs im Exclusive-Modus.

Siehe:

Trigger zur Durchsetzung der zweiten Geschäftsregel erstellen

Die zweite Geschäftsregel lautet: Wenn ein Mitarbeiter mit der Tätigkeit j ein Gehalt s hat, können Sie das Mindestgehalt für j nicht in einen Wert größer als s oder das Höchstgehalt für j in einen Wert kleiner als s ändern. (Dadurch werden vorhandene Daten ungültig.)

Diese Regel kann verletzt werden, wenn die Spalte min_salary oder max_salary der Tätigkeitstabelle aktualisiert wird.

Um die Regel durchzusetzen, erstellen Sie den folgenden Trigger für die Editionen-View-Jobs. Sie können die Anweisung CREATE TRIGGER entweder in SQL*Plus oder im Arbeitsblatt von SQL Developer eingeben. Alternativ können Sie den Trigger mit dem SQL Developer-Tool Create Trigger erstellen.

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 verhindert, dass andere Benutzer die Tabellenmitarbeiter ändern, während der Trigger sie abfragt. Die Verhinderung von Änderungen an Mitarbeitern während der Abfrage ist erforderlich, da nicht blockierende Lesevorgänge verhindern, dass der Trigger Änderungen sieht, die andere Benutzer an Mitarbeitern vornehmen, während der Trigger die Tätigkeiten ändert (und diese Benutzer daran hindern, die Änderungen zu sehen, die der Trigger an Tätigkeiten vornimmt).

Für diesen Trigger ist SELECT FOR UPDATE keine Alternative zu LOCK TABLE IN SHARE MODE. Während Sie versuchen, den Gehaltsbereich für diese Tätigkeit zu ändern, muss dieser Trigger andere Benutzer daran hindern, ein Gehalt außerhalb des neuen Bereichs zu ändern. Daher muss der Trigger alle Zeilen in der Mitarbeitertabelle sperren, die diese job_id und alle Zeilen sperren, die jemand aktualisieren könnte, um diese job_id zu erhalten.

Eine Alternative zu LOCK TABLE employees IN SHARE MODE besteht darin, mit dem Package DBMS_LOCK eine benannte Sperre mit dem Namen job_id zu erstellen und dann Trigger für die Angestellten- und Tätigkeitstabellen zu verwenden, um diese benannte Sperre zu verwenden und gleichzeitige Aktualisierungen zu verhindern. Die Verwendung von DBMS_LOCK und mehreren Triggern wirkt sich jedoch negativ auf die Laufzeitperformance aus.

Eine weitere Alternative zu LOCK TABLE employees IN SHARE MODE besteht darin, einen Trigger für die Mitarbeitertabelle zu erstellen, der für jede geänderte Mitarbeiterzeile die entsprechende Tätigkeitszeile in Tätigkeiten sperrt. Dieser Ansatz verursacht jedoch übermäßige Arbeit an Aktualisierungen der Tabelle der Mitarbeiter, die häufig vorkommen.

LOCK TABLE employees IN SHARE MODE ist einfacher als die vorherigen Alternativen. Änderungen an der Jobtabelle sind selten und werden wahrscheinlich bei der Anwendungswartung auftreten, wenn das Sperren der Tabelle Benutzer nicht belastet.

Siehe:

Sequences erstellen

Hinweis: Sie müssen als Benutzer app_data mit Oracle Database verbunden sein.

Um die Belegnummern zu erstellen, die eindeutige Primärschlüssel für neue Abteilungen und neue Mitarbeiter generieren, verwenden Sie (in jeder Reihenfolge) die folgenden Vergütungsausweise. Sie können die Anweisungen entweder in SQL*Plus oder im Arbeitsblatt von SQL Developer eingeben. Alternativ können Sie die Sequences mit dem SQL Developer-Tool Create Sequence erstellen.

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

Um Konflikte mit den Daten zu vermeiden, die Sie aus Tabellen im Beispielschema HR laden, müssen die Startnummern für employees_sequence und departments_sequence die Höchstwerte von employees.employee_id bzw. departments.department_id überschreiten. Nach "Daten laden" zeigt diese Abfrage die folgenden Höchstwerte an:

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

Ergebnis:

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

Siehe auch: "Sequenzen erstellen und verwalten"

Daten laden

Hinweis: Sie müssen als Benutzer app_data mit Oracle Database verbunden sein.

Laden Sie die Tabellen der Anwendung mit Daten aus Tabellen im Beispielschema HR.

Hinweis: Das folgende Verfahren referenziert die Tabellen der Anwendung über ihre Editionsansichten.

In der folgenden Prozedur können Sie die Anweisungen entweder in SQL*Plus oder im Arbeitsblatt von SQL Developer eingeben.

So laden Sie Daten in die Tabellen:

  1. Tätigkeiten mit Daten aus der Tabelle HR.JOBS laden:

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

    Ergebnis:

     19 rows created.

  2. Abteilungen mit Daten aus der Tabelle HR.DEPARTMENTS laden:

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

    Ergebnis:

     27 rows created.

  3. Laden Sie Mitarbeiter mit Daten aus den Tabellen HR.EMPLOYEES und HR.JOB_HISTORY. Verwenden Sie gesuchte CASE-Ausdrücke und SQL-Funktionen, um employees.country_code und employees.phone_number aus den Funktionen HR.phone_number und SQL und einer skalaren Unterabfrage abzurufen, um employees.job_start_date aus HR.JOB_HISTORY abzurufen:

     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
 /

    Ergebnis:

     107 rows created.

    Hinweis: Die vorherige INSERT-Anweisung löst den Trigger aus, der unter Trigger zum Erzwingen der ersten Geschäftsregel erstellen erstellt wurde.

  4. Laden Sie job_history mit Daten aus der Tabelle 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
 /

    Ergebnis:

     10 rows created.

  5. Schreiben Sie folgende Änderungen fest:

     COMMIT;

Siehe:

Fremdschlüssel-Constraint hinzufügen

Hinweis: Sie müssen als Benutzer app_data mit Oracle Database verbunden sein.

Da nun die Tabellenabteilungen und -mitarbeiter Daten enthalten, fügen Sie mit der folgenden ALTER TABLE-Anweisung ein Fremdschlüssel-Constraint hinzu. Sie können die Anweisung entweder in SQL*Plus oder im Arbeitsblatt von SQL Developer eingeben. Alternativ können Sie das Constraint mit dem SQL Developer-Tool "Fremdschlüssel hinzufügen" hinzufügen.

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

Wenn Sie dieses Fremdschlüssel-Constraint hinzufügen, bevor die Abteilungen# und Mitarbeiter# Daten enthalten, wird dieser Fehler angezeigt, wenn Sie versuchen, einen dieser Constraints mit Daten zu laden:

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

Tutorial: "Tutorial: Vorhandenen Tabellen Constraints hinzufügen"

Benutzern Berechtigungen für die Schemaobjekte erteilen

Hinweis: Sie müssen als Benutzer app_data mit Oracle Database verbunden sein.

Um Benutzern Berechtigungen zu erteilen, verwenden Sie die SQL-Anweisung GRANT. Sie können die GRANT-Anweisungen entweder in SQL*Plus oder im Arbeitsblatt von SQL Developer eingeben.

Erteilen Sie app_code nur die Berechtigungen, die zum Erstellen von employees_pkg erforderlich sind:

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;

Erteilen Sie app_admin nur die Berechtigungen, die er zum Erstellen von admin_pkg benötigt:

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;

Siehe auch: Oracle Database SQL Language Reference für Informationen zur GRANT-Anweisung