创建方案对象并加载数据

本部分说明如何为应用程序创建表、版本视图、触发器和序列，如何将数据加载到表中，以及如何向需要这些对象的用户授予对这些方案对象的权限。

创建方案对象并加载数据的步骤：

以 app_data 用户身份连接到 Oracle Database。

有关说明，请参阅“从 SQL*Plus 连接到 Oracle Database ”或“从 SQL Developer 连接到 Oracle Database ”。
创建表，并使用加载数据后必须添加的外键约束条件以外的所有必需约束条件。
创建版本视图。
创建触发器。
创建序列。
将数据加载到表中。
添加外键约束条件。

创建表

本节说明如何为应用程序创建表，除了必须添加的约束条件（加载数据后必须添加的约束条件除外）。

注：您必须以 app_data 用户身份连接到 Oracle Database。

在以下过程中，可以在 SQL*Plus 或 SQL Developer 工作表中输入语句。或者，可以使用 SQL Developer 工具“Create Table（创建表）”创建表。

创建表的步骤：

创建职务编号，用于存储有关公司中职务的信息（每项职务对应一行）：

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

创建部门编号，用于存储公司中有关部门的信息（每个部门对应一行）：

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

创建 employees#，用于存储有关公司中员工的信息（每位员工对应一行）：

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

REF 约束的原因如下：

员工必须具有现有职务。也就是说，列 employees#.job_id 中的值也必须是 job#.job_id 列中的值。
一个雇员必须有一位同时也是雇员的经理。也就是说，列 employees#.manager_id 中的值也必须是 employees#.employee_id 列中的值。
员工必须在现有部门工作。也就是说，列 employees#.department_id 中的值也必须是 departments#.department_id 列中的值。

此外，雇员的经理必须是该雇员工作的部门的经理。也就是说，列 employees#.manager_id 中的值也必须是 departments#.manager_id 中的值。但是，在创建 departments# 时无法指定必需的约束条件，因为 employees# 尚不存在。因此，您必须稍后向部门编号添加外键约束条件（请参阅添加外键约束条件）。

创建 job_history#，用于存储公司中每个员工的职务历史记录（员工担任的每项职务对应一行）：

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

REF 约束条件的原因是雇员、职务和部门必须存在：

列 job_history#.employee_id 中的值也必须是 employees#.employee_id 列中的值。
job_history#.job_id 列中的值也必须是 jobs#.job_id 列中的值。
列 job_history#.department_id 中的值也必须是 departments#.department_id 列中的值。

另请参阅：创建表

创建版本化视图

注：您必须以 app_data 用户身份连接到 Oracle Database。

要创建版本视图，请使用以下语句（按任意顺序）。可以在 SQL*Plus 或 SQL Developer 工作表中输入语句。或者，可以使用 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#
/

注：应用程序必须始终通过版本视图引用基表。否则，版本视图不会包含这些表，并且您不能使用 EBR 在使用完成的应用程序时对其进行升级。

另请参见：

创建视图
Oracle Database Development Guide，了解有关版本视图的一般信息
Oracle Database Development Guide，了解有关准备应用程序以使用版本视图的信息

创建触发器

注：您必须以 app_data 用户身份连接到 Oracle Database。

应用程序中的触发器将强制实施以下业务规则：

职务为 j 的员工的薪金必须介于职务 j 的最低和最高薪金之间。
如果具有作业 j 的员工具有薪金 s ，则无法将 j 的最低薪金更改为大于 s 的值或将 j 的最高薪金更改为小于 s 的值。（这样做会使现有数据无效。）

另请参阅：使用触发器，了解有关触发器的信息

创建触发器以强制执行第一个业务规则

第一个业务规则是：具有职务 j 的员工必须具有职务 j 的最低和最高薪金之间的薪金。

如果在“雇员”表中插入新行，或者更新“雇员”表的“薪金”列或“职务标识”列，则会违反此规则。

要强制执行该规则，请在版本视图员工上创建以下触发器。可以在 SQL*Plus 或 SQL Developer 工作表中输入 CREATE TRIGGER 语句。或者，可以使用 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 可防止其他用户在触发器查询表作业时更改表作业。在查询期间防止对职务进行更改是必要的，因为非阻塞读取可防止触发器“看到”其他用户在触发器更改员工时对职务所做的更改（并防止这些用户“看到”触发器对员工所做的更改）。

防止在查询期间更改职务的另一种方法是将 FOR UPDATE 子句包含在 SELECT 语句中。但是，SELECT FOR UPDATE 限制的并发性高于“共享模式”中的“锁定表”作业。

LOCK TABLE jobs IN SHARE MODE 阻止其他用户更改作业，但不会锁定共享模式中的作业。对工作的改变可能比对员工的改变要少得多。因此，在共享模式下锁定作业比在独占模式下锁定单行作业提供更多的并发性。

另请参见：

Oracle Database Development Guide，了解有关锁定表的信息（共享模式）
有关 SELECT FOR UPDATE 的信息，请参见Oracle Database PL/SQL Language Reference
“创建触发器”
“教程：显示 employee_pkg 子程序的工作方式”以查看 employees_aiufer 触发器的工作方式

创建触发器以强制执行第二个业务规则

第二个业务规则是：如果作业 j 的员工具有薪金 s ，则无法将 j 的最低薪金更改为大于 s 的值或将 j 的最高薪金更改为小于 s 的值。（这样做会使现有数据无效。）

更新“职务”表的“最低薪金”列或“最高薪金”列时，可能会违反此规则。

要强制执行该规则，请在版本查看作业上创建以下触发器。可以在 SQL*Plus 或 SQL Developer 工作表中输入 CREATE TRIGGER 语句。或者，可以使用 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 可防止其他用户在触发器查询表雇员时更改表雇员。防止在查询期间对员工进行更改是必要的，因为非阻塞读取可防止触发器“看到”其他用户在触发器更改职务时对员工所做的更改（并防止这些用户“看到”触发器对职务所做的更改）。

对于此触发器，SELECT FOR UPDATE 不是 LOCK TABLE IN SHARE MODE 的替代项。在尝试更改此职务的薪资范围时，此触发器必须防止其他用户将薪资更改为超出新范围。因此，触发器必须锁定 employees 表中具有此 job_id 的所有行，锁定可能更新为具有此 job_id 的所有行。

LOCK TABLE employees IN SHARE MODE 的一个替代方法是使用 DBMS_LOCK 程序包创建名为 job_id 的命名锁，然后在员工表和职务表上使用触发器使用此命名锁来防止并发更新。但是，使用 DBMS_LOCK 和多个触发器会对运行时性能产生负面影响。

LOCK TABLE employees IN SHARE MODE 的另一种替代方法是在“雇员”表上创建一个触发器，该触发器针对每个已更改的雇员行锁定职务中的相应职务行。但是，这种方法会导致对“雇员”表的更新执行过多的工作，而这些更新经常发生。

LOCK TABLE employees IN SHARE MODE 比前面的替代方案更简单，对作业表所做的更改很少见，并且很可能在应用程序维护时发生，锁定表不会给用户带来不便。

另请参见：

Oracle Database Development Guide，了解有关使用 SHARE MODE 锁定表的信息
Oracle Database PL/SQL Packages and Types Reference（了解有关 DBMS_LOCK 程序包的信息）
“创建触发器”
“教程：显示 admin_pkg 子程序的工作方式”

创建序列

注：您必须以 app_data 用户身份连接到 Oracle Database。

要为新部门和新雇员创建生成唯一主键的序列，请使用以下语句（按任一顺序）。可以在 SQL*Plus 或 SQL Developer 工作表中输入语句。或者，可以使用 SQL Developer 工具“Create Sequence（创建序列）”创建序列。

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

为了避免与要从示例方案 HR 中的表加载的数据冲突，employee_sequence 和 departments_sequence 的起始编号必须分别超过 employees.employee_id 和 departments.department_id 的最大值。在“加载数据”之后，此查询将显示以下最大值：

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

结果：

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

另请参阅：“创建和管理序列”

正在加载数据

注：您必须以 app_data 用户身份连接到 Oracle Database。

使用示例方案 HR 中的表中的数据加载应用程序的表。

注：以下过程通过应用程序的版本视图引用应用程序的表。

在以下过程中，可以在 SQL*Plus 或 SQL Developer 工作表中输入语句。

要将数据加载到表中，请执行以下操作：

使用表 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
 /

结果：

 19 rows created.

使用 HR.DEPARTMENTS 表中的数据加载部门：

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

结果：

 27 rows created.

使用搜索的 CASE 表达式和 SQL 函数从 HR.EMPLOYEES 和 HR.JOB_HISTORY 表加载数据，以获取 employees.country_code 和 employees.phone_number 来自 HR.phone_number 和 SQL 函数，并使用标量子查询从 HR.JOB_HISTORY 中获取 employees.job_start_date：

 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
 /

结果：

 107 rows created.

注：前面的 INSERT 语句会触发在“创建触发器以强制执行第一个业务规则”中创建的触发器。

使用表 HR.JOB_HISTORY 中的数据加载 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
 /

结果：

 10 rows created.

提交更改：
```
 COMMIT;
```

另请参见：

“关于 INSERT 语句”
“关于示例方案 HR ”
“在查询中使用 CASE 表达式”
“在查询中使用与 NULL 相关的函数”了解有关 NVL 函数的信息
Oracle Database SQL Language Reference（了解有关 SQL 函数的信息）

添加外键约束条件

注：您必须以 app_data 用户身份连接到 Oracle Database。

现在，表部门和雇员都包含数据，请使用以下 ALTER TABLE 语句添加外键约束条件。可以在 SQL*Plus 或 SQL Developer 工作表中输入语句。或者，可以使用 SQL Developer 工具“Add Foreign Key”添加约束条件。

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

如果在部门编号和雇员编号包含数据之前添加此外键约束条件，则在尝试使用数据加载其中任一项时会出现以下错误：

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

另请参阅：“教程：将约束条件添加到现有表”

向用户授予对方案对象的权限

注：您必须以 app_data 用户身份连接到 Oracle Database。

要向用户授予权限，请使用 SQL 语句 GRANT。可以在 SQL*Plus 或 SQL Developer 工作表中输入 GRANT 语句。

仅向 app_code 授予创建 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;

仅向 app_admin 授予创建 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;

另请参阅： Oracle Database SQL Language Reference 以了解有关 GRANT 语句的信息