亚洲欧美日本韩国,亚洲AV日韩综合一区,久久乐国产精品亚洲综合

用約束和觸發器實施商業規則

在商界，我們的確通常需要確保始終實施某些規則。例如，參與項目的雇員必須被雇用。或者想要某些事件有計劃地發生。例如，如果銷售員售出一批商品，則應增加其傭金。

DB2 通用數據庫為此提供了一套有用的方法。 唯一約束是禁止在表的一列或多列中出現重復值的規則。 參考完整性約束確保在整個指定的表中數據一致性。 表檢查約束是一些條件，它們定義為表定義的一部分，限制一列或多列中使用的值。觸發器允許您定義一組操作，這些操作通過對指定的表進行刪除、插入或更新操作來執行或觸發。觸發器可用于寫入其他表、修改輸入值以及發布警報信息。

第一節提供關鍵字的概念性概述。接著，通過示例和圖表進一步探討參考完整性、約束以及觸發器。

關鍵字

關鍵字是可用來標識或存取特定行的一組列。

由不止一列組成的關鍵字稱為組合關鍵字。在具有組合關鍵字的表中，組合關鍵字中各列的排序不受這些列在表中排序的約束。

唯一關鍵字

唯一關鍵字被定義為它的任何值都不相同。唯一關鍵字的列不能包含空值。在執行 INSERT 和 UPDATE 語句期間，數據庫管理程序強制執行該約束。一個表可以有多個唯一關鍵字。唯一關鍵字是可選的，并且可在 CREATE TABLE 或 ALTER TABLE 語句中定義。

主關鍵字

主關鍵字是一種唯一關鍵字，表定義的一部分。一個表不能有多個主關鍵字，并且主關鍵字的列不能包含空值。主關鍵字是可選的，并且可在 CREATE TABLE 或 ALTER TABLE 語句中定義。

外部關鍵字

外部關鍵字在參考約束的定義中指定。一個表可以有零個或多個外部關鍵字。如果組合外部關鍵字的值的任何部分為空，則該值為空。外部關鍵字是可選的，并且可在 CREATE TABLE 語句或 ALTER TABLE 語句中定義。

唯一約束

唯一約束確保關鍵字的值在表中是唯一的。唯一約束是可選的，并且可以通過使用指定 PRIMARY KEY 或 UNIQUE 子句的 CREATE TABLE 或 ALTER TABLE 語句來定義唯一約束。例如，可在一個表的雇員編號列上定義一個唯一約束，以確保每個雇員有唯一的編號。

通過定義唯一約束和外部關鍵字，可以定義表與表之間的關系，從而實施某些商業規則。唯一關鍵和外部關鍵字約束的組合通常稱為參考完整性約束。外部關鍵字所引用的唯一約束稱為父關鍵字。外部關鍵字表示特定的父關鍵字，或與特定的父關鍵字相關。例如，某規則可能規定每個雇員（EMPLOYEE 表）必須屬于某現存的部門（DEPARTMENT 表）。因此，將 EMPLOYEE 表中的“部門號”定義為外部關鍵字，而將 DEPARTMENT 表中的“部門號”定義為主關鍵字。下列圖表提供參考完整性約束的直觀說明。

圖 4. 外部約束和主約束定義關系并保護數據

表檢查約束

表檢查約束指定對于表的每行都要進行判定的條件。可對個別列指定檢查約束。可使用 CREATE 或 ALTER TABLE 語句添加檢查約束。

下列語句創建具有下列約束的表：

部門編號的值必須在范圍 10 至 100 內
雇員的職務只能為下列之一： "Sales"、"Mgr"或"Clerk"
1986 年之前雇用的每個雇員的工資必須超過 $40,500。

 
     CREATE TABLE EMP
           (ID           SMALLINT NOT NULL,
            NAME         VARCHAR(9),
            DEPT         SMALLINT CHECK (DEPT BETWEEN 10 AND 100),
            JOB          CHAR(5)   CHECK (JOB IN ('Sales', 'Mgr', 'Clerk')),
            HIREDATE     DATE,
            SALARY       DECIMAL(7,2),
            COMM         DECIMAL(7,2),
            PRIMARY KEY (ID),
            CONSTRAINT YEARSAL CHECK (YEAR(HIREDATE) >= 1986 OR SALARY > 40500) )

僅當條件判定為假時才會違反約束。例如，如果插入行的 DEPT 為空值，則插入繼續進行而不出錯，盡管 DEPT 的值應該象約束中定義的那樣在 10 和 100 之間。

下列語句將一個約束添加至名為 COMP 的 EMPLOYEE 表中，該約束為雇員的總報酬必須超過 $15,000：

 
     ALTER TABLE EMP
        ADD CONSTRAINT COMP CHECK (SALARY + COMM > 15000)

將檢查表中現存的行以確保這些行不違反新約束。可通過使用如下的 SET CONSTRAINTS 語句將此檢查延期：

     SET CONSTRAINTS FOR EMP OFF
     ALTER TABLE EMP ADD CONSTRAINT COMP CHECK (SALARY + COMM > 15000)
     SET CONSTRAINTS FOR EMP IMMEDIATE CHECKED

首先使用 SET CONSTRAINTS 語句以延期對表的約束檢查。然后可將一個或多個約束添加至表而不檢查這些約束。接著再次發出 SET CONSTRAINTS 語句，反過來將約束檢查打開并執行任何延期的約束檢查。

觸發器

一個觸發器定義一組操作，這組操作通過修改指定基表中數據的操作來激活。

可使用觸發器來執行對輸入數據的驗證；自動生成新插入行的值；為了交叉引用而讀取其他表；為了審查跟蹤而寫入其他表；或通過電子郵件信息支持警報。使用觸發器將導致應用程序開發及商業規則的全面實施更快速并且應用程序和數據的維護更容易。

DB2 通用數據庫支持幾種類型的觸發器。可定義觸發器在 DELETE、INSERT 或 UPDATE 操作之前或之后激活。每個觸發器包括一組稱為觸發操作的 SQL 語句，這組語句可包括一個可選的搜索條件。

可進一步定義后觸發器以對每一行都執行觸發操作，或對語句執行一次觸發操作，而前觸發器總是對每一行都執行觸發操作。

在 INSERT、UPDATE 或 DELETE 語句之前使用觸發器，以便在執行觸發操作之前檢查某些條件，或在將輸入值存儲在表中之前更改輸入值。使用后觸發器，以便在必要時傳播值或執行其他任務，如發送信息等，這些任務可能是觸發器操作所要求的。

下列示例說明了前觸發器和后觸發器的使用。考慮一個記錄并跟蹤股票價格波動的應用程序。該數據庫包含兩個表，CURRENTQUOTE 和 QUOTEHISTORY，定義如下：

 
     CREATE TABLE CURRENTQUOTE
     (SYMBOL VARCHAR(10),
      QUOTE DECIMAL(5,2),
      STATUS VARCHAR(9))
     
     CREATE TABLE  QUOTEHISTORY
     (SYMBOL VARCHAR(10),
      QUOTE DECIMAL(5,2),
      TIMESTAMP TIMESTAMP)

當使用如下語句更新 CURRENTQUOTE 的 QUOTE 列時：

 
     UPDATE CURRENTQUOTE
        SET QUOTE = 68.5
        WHERE SYMBOL = 'IBM'

應更新 CURRENTQUOTE 的 STATUS 列以反映股票是否：

在升值
處于本年度的新高
在下跌
處于本年度的新低
價位穩定

這通過使用下列前觸發器來實現：

(1)

     CREATE TRIGGER STOCK_STATUS
        NO CASCADE BEFORE UPDATE OF QUOTE ON CURRENTQUOTE
        REFERENCING NEW AS NEWQUOTE OLD AS OLDQUOTE
     FOR EACH ROW MODE DB2SQL

(2)

      SET NEWQUOTE.STATUS =

(3)

         CASE

(4)

            WHEN NEWQUOTE.QUOTE >=
                      (SELECT MAX(QUOTE)
                          FROM QUOTEHISTORY         
                          WHERE SYMBOL = NEWQUOTE.SYMBOL                 
                          AND YEAR(TIMESTAMP) = YEAR(CURRENT DATE) )     
            THEN 'High'

(5)

          WHEN NEWQUOTE.QUOTE <=                             
                      (SELECT MIN(QUOTE)
                          FROM QUOTEHISTORY
                          WHERE SYMBOL = NEWQUOTE.SYMBOL                       
                          AND YEAR(TIMESTAMP) = YEAR(CURRENT DATE) )      
          THEN 'Low'

(6)

          WHEN NEWQUOTE.QUOTE > OLDQUOTE.QUOTE
             THEN 'Rising'
          WHEN NEWQUOTE.QUOTE < OLDQUOTE.QUOTE                       
             THEN 'Dropping'
          WHEN NEWQUOTE.QUOTE = OLDQUOTE.QUOTE
             THEN 'Steady'
     END

(1): 此代碼塊將名為 STOCK_STATUS 的觸發器定義為一個應該在更新 CURRENTQUOTE 表的 QUOTE 列之前激活的觸發器。第二行指定，在將 CURRENTQUOTE 表的實際更新所引起的任何更改應用于數據庫之前，要應用觸發操作。第二行也意味著觸發操作將不會激活任何其他觸發器。第三行指定一些名稱，必須將這些名稱作為列名的限定符用于新值 (NEWQUOTE) 和舊值 (OLDQUOTE)。用這些相關名（NEWQUOTE 和 OLDQUOTE）限定的列名稱為轉換變量。第四行表示應對每一行都執行觸發操作。
(2): 這標記此觸發器的觸發操作中第一個也是唯一的一個 SQL 語句的開始。 SET 轉換變量語句在一個觸發器中用來將值賦給表的行中的列，該表正在由激活該觸發器的語句進行更新。此語句正將一個值賦給 CURRENTQUOTE 表的 STATUS 列。
(3): 該賦值語句右邊使用的表達式為 CASE 表達式。 CASE 表達式擴充為 END 關鍵字。
(4): 第一種情況檢查新報價 (NEWQUOTE.QUOTE) 是否超過當前日歷年度中股票符號的最高價。子查詢正在使用由跟在后面的后觸發器更新的 QUOTEHISTORY 表。
(5): 第二種情況檢查新報價 (NEWQUOTE.QUOTE) 是否小于當前日歷年度中股票符號的最低價。子查詢正在使用由跟在后面的后觸發器更新的 QUOTEHISTORY 表。
(6): 最后三種情況將新報價 (NEWQUOTE.QUOTE) 與表 (OLDQUOTE.QUOTE) 中的報價比較，以確定新報價是大于、小于還是等于舊報價。 SET 轉換變量語句在此處結束。

除了更新 CURRENTQUOTE 表中的項之外，還需要通過將新報價連同時間戳記一起復制到 QUOTEHISTORY 表中來創建一個審查記錄。這通過使用下列后觸發器來實現：

(1)

     CREATE TRIGGER RECORD_HISTORY
     AFTER UPDATE OF QUOTE ON CURRENTQUOTE
     REFERENCING NEW AS NEWQUOTE
     FOR EACH ROW MODE DB2SQL
     BEGIN ATOMIC

(2)

     INSERT INTO QUOTEHISTORY
        VALUES (NEWQUOTE.SYMBOL, NEWQUOTE.QUOTE, CURRENT TIMESTAMP);
     END

(1)

此代碼塊將命名為 RECORD_HISTORY 的觸發器定義為應該在更新 CURRENTQUOTE 表的 QUOTE 列之后激活的觸發器。第三行指定應該作為列名的限定符用于新值 (NEWQUOTE) 的名稱。第四行表示應對每一行都執行觸發操作。

(2)

此觸發器的觸發操作包括單個 SQL 語句，該語句使用已更新的行中的數據（NEWQUOTE.SYMBOL 和 NEWQUOTE.QUOTE）和當前的時間戳記將該行插入 QUOTEHISTORY 表。

CURRENT TIMESTAMP 是包含時間戳記的專用寄存器。專用寄存器中提供了列表和解釋。

連接

從兩個或更多個表中組合數據的過程稱為連接表。數據庫管理程序從指定的表中形成行的所有組合。對于每個組合，它都測試連接條件。連接條件是帶有一些約束的搜索條件。有關約束的列表，參考 SQL Reference。

注意：連接條件涉及的列的數據類型不必相同；然而，這些數據類型必須相容。計算連接條件的方式與計算其他搜索條件的方式相同，并且使用相同的比較規則。

如果未指定連接條件，則返回在 FROM 子句中列出的表中行的所有組合，即使這些行可能完全不相關。該結果稱為這兩個表的交叉積。

本節中的示例基于下面兩個表。這兩個表只是樣本數據庫中表的簡化形式，在樣本數據庫中并不存在。這兩個表一般用來概述關于連接的重點。 SAMP_STAFF 列出未作為合同工雇用的雇員的姓名以及這些雇員的職務說明，而 SAMP_PROJECT 則列出雇員（合同工和全職人員）的姓名以及這些雇員所參與的項目。

這些表如下：

下列示例產生兩個表的交叉積。因未指定連接條件，所以給出了行的所有組合：

     SELECT SAMP_PROJECT.NAME,
            SAMP_PROJECT.PROJ, SAMP_STAFF.NAME, SAMP_STAFF.JOB
        FROM SAMP_PROJECT, SAMP_STAFF

此語句產生下列結果：

     NAME       PROJ   NAME       JOB
     ---------- ------ ---------- --------
     Haas       AD3100 Haas       PRES
     Thompson   PL2100 Haas       PRES
     Walker     MA2112 Haas       PRES
     Lutz       MA2111 Haas       PRES
     Haas       AD3100 Thompson   MANAGER
     Thompson   PL2100 Thompson   MANAGER
     Walker     MA2112 Thompson   MANAGER
     Lutz       MA2111 Thompson   MANAGER
     Haas       AD3100 Lucchessi  SALESREP
     Thompson   PL2100 Lucchessi  SALESREP
     Walker     MA2112 Lucchessi  SALESREP
     Lutz       MA2111 Lucchessi  SALESREP
     Haas       AD3100 Nicholls   ANALYST
     Thompson   PL2100 Nicholls   ANALYST
     Walker     MA2112 Nicholls   ANALYST
     Lutz       MA2111 Nicholls   ANALYST

兩個主要的連接類型是內連接和外連接。到目前為止，所有示例中使用的都是內連接。內連接只保留交叉積中滿足連接條件的那些行。如果某行在一個表中存在，但在另一個表中不存在，則結果表中不包括該信息。

下列示例產生兩個表的內連接。該內連接列出分配給某個項目的全職雇員信息：

     SELECT SAMP_PROJECT.NAME,
            SAMP_PROJECT.PROJ, SAMP_STAFF.NAME, SAMP_STAFF.JOB
        FROM SAMP_PROJECT, SAMP_STAFF
        WHERE SAMP_STAFF.NAME = SAMP_PROJECT.NAME

或者，也可以指定如下內連接：

     SELECT SAMP_PROJECT.NAME,
            SAMP_PROJECT.PROJ, SAMP_STAFF.NAME, SAMP_STAFF.JOB
        FROM SAMP_PROJECT INNER JOIN SAMP_STAFF
          ON SAMP_STAFF.NAME = SAMP_PROJECT.NAME

結果是：

     NAME       PROJ   NAME       JOB
     ---------- ------ ---------- --------
     Haas       AD3100 Haas       PRES
     Thompson   PL2100 Thompson   MANAGER

注意：該內連接的結果由右表和左表中姓名列的值匹配的行組成－ 'Haas' 和 'Thompson' 都包括在列出所有全職雇員的 SAMP_STAFF 表中以及列出分配給某個項目的專職和合同雇員的 SAMP_PROJECT 表中。

外連接是內連接和左表和／或右表中未包括內連接中的那些行的并置。當對兩個表執行外連接時，可任意將一個表指定為左表而將另一個表指定為右表。外連接有三種類型：

左外連接包括內連接和左表中未包括在內連接中的那些行。
右外連接包括內連接和右表中未包括在內連接中的那些行。
全外連接包括內連接以及左表和右表中未包括在內連接中的行。

使用 SELECT 語句來指定要顯示的列。在 FROM 子句中，列出后跟關鍵字 LEFT OUTER JOIN、RIGHT OUTER JOIN 或 FULL OUTER JOIN 的第一個表的名稱。接著需要指定后跟 ON 關鍵字的第二個表。在 ON 關鍵字后面，指定表示要連接的表之間關系的連接條件。

在下列示例中，將 SAMP_STAFF 指定為右表，而 SAMP_PROJECT 則被指定為左表。通過使用 LEFT OUTER JOIN，列出所有全職和合同雇員（在 SAMP_PROJECT 中列出）的姓名和項目編號，如果是全職雇員（在 SAMP_STAFF 中列出），還列出這些雇員的職位：

     SELECT SAMP_PROJECT.NAME, SAMP_PROJECT.PROJ,
            SAMP_STAFF.NAME, SAMP_STAFF.JOB
        FROM SAMP_PROJECT LEFT OUTER JOIN SAMP_STAFF
          ON SAMP_STAFF.NAME = SAMP_PROJECT.NAME

此語句產生下列結果：

     NAME       PROJ                 NAME       JOB
     ---------- -------------------- ---------- --------------------
     Haas       AD3100               Haas       PRES
     Lutz       MA2111               -          -
     Thompson   PL2100               Thompson   MANAGER
     Walker     MA2112               -          -

所有列中都具有值的那些行是該內連接的結果。這些都是滿足連接條件的行： 'Haas' 和 'Thompson' 既在 SAMP_PROJECT（左表）中列出又在 SAMP_STAFF（右表）中列出。對于不滿足連接條件的行，右表的列上出現空值： 'Lutz' 和 'Walker' 都是在 SAMP_PROJECT 表中列出的合同雇員，因而未在 SAMP_STAFF 表中列出。注意：左表中的所有行都包括在結果集中。

在下一個示例中，將 SAMP_STAFF 指定為右表而 SAMP_PROJECT 則被指定為左表。通過使用 RIGHT OUTER JOIN 列出所有專職雇員（在 SAMP_STAFF 中列出）的姓名和工作職位，如果將這些雇員分配給了某個項目（在 SAMP_PROJECT 中列出），還列出他們的項目編號：

     SELECT SAMP_PROJECT.NAME,
            SAMP_PROJECT.PROJ, SAMP_STAFF.NAME, SAMP_STAFF.JOB
        FROM SAMP_PROJECT RIGHT OUTER JOIN SAMP_STAFF
          ON SAMP_STAFF.NAME = SAMP_PROJECT.NAME

結果為：

    NAME       PROJ                 NAME       JOB
    ---------- -------------------- ---------- --------------------
    Haas       AD3100               Haas       PRES
    -          -                    Lucchessi  SALESREP
    -          -                    Nicholls   ANALYST
    Thompson   PL2100               Thompson   MANAGER

象在左外連接中一樣，所有列中都具有值的那些行是內連接的結果。這些都是滿足連接條件的行： 'Haas'和'Thompson'既在 SAMP_PROJECT（左表）中列出又在 SAMP_STAFF（右表）中列出。對于不滿足連接條件的行，右表的列上出現空值： 'Lucchessi'和'Nicholls'都是未分配項目的專職雇員。雖然他們在 SAMP_STAFF 中列出，但未在 SAMP_PROJECT 中列出。注意：右表中的所有行都包括在結果集中。

下一個示例對 SAMP_PROJECT 表和 SAMP_STAFF 表使用 FULL OUTER JOIN。該示例列出所有專職雇員（包括未分配項目的雇員）和合同雇員的姓名：

     SELECT SAMP_PROJECT.NAME, SAMP_PROJECT.PROJ,
            SAMP_STAFF.NAME, SAMP_STAFF.JOB
        FROM SAMP_PROJECT FULL OUTER JOIN SAMP_STAFF
          ON SAMP_STAFF.NAME = SAMP_PROJECT.NAME

結果為：

     NAME       PROJ                 NAME       JOB
     ---------- -------------------- ---------- --------------------
     Haas       AD3100               Haas       PRES
     -          -                    Lucchessi  SALESREP
     -          -                    Nicholls   ANALYST
     Thompson   PL2100               Thompson   MANAGER
     Lutz       MA2111               -          -
     Walker     MA2112               -          -

此結果包括左外連接、右外連接以及內連接。列出所有專職雇員和合同雇員。正如左外連接和右外連接一樣，對于不滿足連接條件的值，相應列中出現空值。 SAMP_STAFF 和 SAMP_PROJECT 中的每一行都包括在結果集中。

新用戶注冊刷新評論列表


只有注冊用戶登錄后才能發表評論。




網站導航: 博客園 IT新聞 Chat2DB C++博客博問管理
相關文章: Oracle中按拼音排序 Mysql 集群簡介和配置 Oracle管理及常用基礎腳本 Oracle database link Oracle內存分配 Solaris10 x86 u1 下安裝 Oracle 10g Release 2 SQL優化 oracle常用命令(我的筆記) 手工創建數據庫持久對象原生數據庫查詢語言（中文版）——預覽

Vincent.Chan‘s Blog

導航

公告

留言簿(11)

隨筆分類

文章分類

隨筆檔案

文章檔案

相冊

閱讀排行榜

評論排行榜

常用鏈接

統計

積分與排名

網站

最新評論

高級 SQL(轉載：約束＋觸發器)

用約束和觸發器實施商業規則

關鍵字

唯一關鍵字

主關鍵字

外部關鍵字

唯一約束

參考完整性約束

表檢查約束

觸發器

連接

復雜查詢

ROLLUP 和 CUBE 查詢

遞歸查詢