建立綱要物件和載入資料

本節顯示如何為應用程式建立表格、編輯視觀表、觸發程式和序列、如何將資料載入表格，以及如何將這些綱要物件的權限授與需要的使用者。

建立綱要物件並載入資料的步驟：

以使用者 app_data 的身分連線至 Oracle Database。

如需相關指示，請參閱從 SQL*Plus 連線至 Oracle Database 或從 SQL Developer 連線至 Oracle Database 。
建立包含所有必要限制條件的表格，但載入資料後必須新增的外來索引鍵限制條件除外。
建立版本檢視。
建立觸發器。
建立序號。
將資料載入表格。
新增外來索引鍵限制條件。

建立表格

本節顯示如何為應用程式建立表格，以及所有必要的限制 (除了其中一個以外)，您必須在載入資料後新增這些限制。

注意：您必須以 app_data 使用者身分連線到 Oracle Database。

在下列程序中，您可以在 SQL*Plus 或 SQL Developer 的「工作表」中輸入敘述句。或者，您可以使用 SQL Developer 工具「建立表格」來建立表格。

建立表格的步驟：

建立職務 #，儲存公司內職務的相關資訊 (每一職務一列)：

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

建立員工編號，儲存公司中員工的相關資訊 (每位員工一列)：

 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 中的值。不過，您無法在建立部門 # 時指定必要的限制，因為員工 # 尚未存在。因此，您必須稍後再將外來索引鍵限制條件新增至部門 # (請參閱新增外來索引鍵限制條件 )。

建立 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 資料欄中的值也必須是 employee#.employee_id 資料欄中的值。
job_history#.job_id 欄中的值也必須是 job#.job_id 欄中的值。
資料欄 job_history#.department_id 中的值也必須是資料欄 departments#.department_id 中的值。

另請參閱：「建立表格」

建立版本檢視

注意：您必須以使用者 app_data 的身分連線到 Oracle Database。

若要建立版本檢視，請使用下列陳述式 (依任何順序)。您可以在 SQL*Plus 或 SQL Developer 的「工作表」中輸入敘述句。或者，您也可以使用 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#
/

注意：應用程式必須一律透過版本檢視參照基礎表格。否則，版本檢視不會涵蓋表格，而無法使用 EBR 來升級使用中的已完成應用程式。

另請參閱：

「建立檢視表」
Oracle Database Development Guide，瞭解版本檢視的一般資訊
Oracle Database Development Guide，瞭解有關準備應用程式使用版本檢視的資訊

建立觸發器

注意：您必須以使用者 app_data 的身分連線到 Oracle Database。

應用程式中的觸發器會強制執行下列業務規則：

工作為 j 的員工在工作 j 的薪資下限與上限之間必須有薪資。
如果工作 j 的員工有薪資 s ，則您無法將 j 的薪資下限變更為大於 s 的值，或將 j 的薪資上限變更為小於 s 的值。(若要這麼做，將現有資料變成無效。)

另請參閱：使用觸發程式，瞭解觸發程式的相關資訊

建立觸發程式以強制執行第一個商業規則

第一個商業規則是：工作 j 的員工在工作 j 的薪資下限與上限之間必須有薪資。

插入新資料列至員工表格，或更新員工表格的 salary 或 job_id 資料欄時，可能會違反此規則。

若要強制執行規則，請在版本檢視員工上建立下列觸發器。您可以在 SQL*Plus 或 SQL Developer 的「工作表」中輸入 CREATE TRIGGER 敘述句。或者，您可以使用 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 可防止其他使用者在觸發程式查詢表格工作時變更表格工作。在查詢期間防止工作變更是必要的，因為非封鎖讀取會使觸發程式在觸發程式變更員工時，無法「查看」其他使用者對工作所做的變更 (並防止這些使用者「查看」觸發程式對員工所做的變更)。

另一種防止查詢期間工作變更的方法，是在 SELECT 陳述式中包含 FOR UPDATE 子句。不過，SELECT FOR UPDATE 會限制並行執行超過 SHARE MODE 中的 LOCK TABLE 工作。

LOCK TABLE jobs IN SHARE MODE 可防止其他使用者變更工作，但無法在共用模式本身鎖定工作。職務變更可能比員工的變更要早得多。因此，鎖定共用模式的工作會比以獨佔模式鎖定單一工作資料列來提供更多的並行性。

另請參閱：

Oracle Database Development Guide，瞭解鎖定 SHARE MODE 表格的相關資訊
Oracle Database PL/SQL Language Reference，瞭解 SELECT FOR UPDATE 的相關資訊
「建立觸發程式」
「教學課程：顯示 employees_pkg 子程式的運作方式」以瞭解 employees_aiufer 觸發程式的運作方式

建立觸發程式以強制執行第二個商業規則

第二個商業規則是：如果工作 j 的員工有薪資 s ，則您無法將 j 的薪資下限變更為大於 s 的值，或將 j 的薪資上限變更為小於 s 的值。(若要這麼做，將現有資料變成無效。)

更新工作表格的 min_salary 或 max_salary 資料欄時，可能會違反此規則。

若要強制執行規則，請在版本檢視工作上建立下列觸發程式。您可以在 SQL*Plus 或 SQL Developer 的「工作表」中輸入 CREATE TRIGGER 敘述句。或者，您可以使用 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 可防止其他使用者在觸發程式查詢時變更表格員工。在查詢期間防止員工發生變更，因為非封鎖讀取會阻止觸發程式在觸發程式變更工作時「查看」其他使用者對員工所做的變更 (並防止這些使用者「查看」觸發程式對工作的變更)。

對於此觸發程式，SELECT FOR UPDATE 不是 LOCK TABLE IN SHARE MODE 的替代選項。當您嘗試變更此職務的薪資範圍時，此觸發器必須防止其他使用者將薪資變更為超出新範圍。因此，觸發程式必須鎖定員工表格中具有此 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 套裝程式與類型參照，瞭解 DBMS_LOCK 套裝程式的相關資訊
「建立觸發程式」
「教學課程 : 顯示 admin_pkg 子程式的運作方式」

建立序列

注意：您必須以使用者 app_data 的身分連線到 Oracle Database。

若要建立為新部門和新員工產生唯一主索引鍵的順序，請使用以下陳述式 (依任一順序)。您可以在 SQL*Plus 或 SQL Developer 的「工作表」中輸入敘述句。或者，您也可以使用 SQL Developer 工具「建立序列」來建立序列。

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.phone_number 與 SQL 函數取得 employees.country_code 與 employees.phone_number，以及從 HR.JOB_HISTORY 取得 employees.job_start_date 的純量子查詢，載入含有 HR.EMPLOYEES 與 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
 /

結果：

 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 工具「新增外來索引鍵」來新增限制條件。

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

另請參閱： " Tutorial: Adding Constraints to Existing Tables "

將綱要物件上的權限授予使用者

注意：您必須以使用者 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 敘述句的相關資訊