資料庫 SQL Server 進階概念

Rule

定義

對一欄位填入資料時 , 必需符合某條件才能填入 , 此條件就稱為 Rule

好處

一個 Rule 可以供給多個表格使用 , 如檢查年齡 , E-Mail 等檢查的法則都可以寫成 Rule ,
只要任一表格有這些欄位 , 就可以套用已定義好的 Rule , 以進行欄位檢查 .

語法

CREATE RULE rule
AS condition_expression

範例

CREATE RULE rIDNOFormat
AS @yourIDNO LIKE '[A-Z][1-2][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

注意

Rule 建立之後 , 還要用與欲套用此 Rule 的欄位 bind 才會發揮作用 . 可使用
sp_bindrule rIDNOFormat , '使用者資料.身份證字號' 以進行 bind , 使用
sp_unbindrule '使用者資料.身份證字號' 可以 unbind rule

User Defined Type

定義

內定資料形態以外的資料形態

好處

達成許多表格定義的一致 , 比如說 , 可自定地址的形態為 address nvarchar(40) , 以後就可以使用 address
這個形態 , 避免 A 表格中定義的地址為 nvarchar(50) , 而B 表格中卻定義為 nvarchar(40)

語法

sp_addtype [@typename =] type,
[@phystype =] system_data_type
[, [@nulltype =] 'null_type']

範例

-- 自定一名為 address 的形態
sp_addtype address 'nvarchar(40)','NOT NULL'
-- 刪除名為 address 的形態
sp_droptype address

View

定義

將 SELECT 的結果暫時表現成一個虛擬資料表 ,供使用者可以操作此虛擬資料表 . 此虛擬資料表稱為 View .

好處

優點	說明
可讀性提升	把複雜的表格關係用 View 表現 , 較容易閱讀
保密性提升	針對不同使用者可產生不同權限設定的 View
程式維護方便	程式若都對 View 做存取 , 當 Table 的架構改變時 , 無需改變程式 , 只要改變 View 即可

語法

CREATE VIEW view_name [(column [,...n])]
[WITH ENCRYPTION]
AS
select_statement
[WITH CHECK OPTION]

範例

CREATE VIEW vProduct_Stock_Reorder
WITH ENCRYPTION
AS
SELECT 品名,數量 from 產品存貨 WHERE 數量<10

注意

(1) SELECT 子句中不可使用 ORDER BY , COMPUTE , COMPUTE BY .
(2) INSERT 資料到 View 時 , 要特別注意 , 不允許NULL的欄位是否已設好預設值 .

Trigger

定義

當表格有 INSERT , UPDATE , DELETE 的動作時 , 可設定一程序對這些動作做出反應 , 此程序就稱為 Trigger

好處

(1) 檢查對資料表的更改動作是否合適 ( Check 會先檢查 , 然後才輪到 Trigger )
(2) 進行相關工作 . 如一筆定單被下訂單的人刪除了 , 經由 Trigger 可將相關表格中的資料一併刪除
(3) 發出通知 . 比如說 , 有新訂單來時 , 可經由 Trigger 發 Mail 給相關的人

語法

CREATE TRIGGER trigger_name
ON table
[WITH ENCRYPTION]
{
    {FOR { [DELETE] [,] [INSERT] [,] [UPDATE] }
        [WITH APPEND]
        [NOT FOR REPLICATION]
        AS
            sql_statement [...n]
    }
    |
    {FOR { [INSERT] [,] [UPDATE] }
        [WITH APPEND]
        [NOT FOR REPLICATION]
        AS
        {    IF UPDATE (column)
            [{AND | OR} UPDATE (column)]
                [...n]
            | IF (COLUMNS_UPDATED() {bitwise_operator} updated_bitmask)
                { comparison_operator} column_bitmask [...n]
        }
            sql_statement [ ...n]
    }
}

範例

CREATE TRIGGER tNewOrderInsert
ON 訂單資料
WITH ENCRYPTION
    FOR INSERT
        AS
            IF (SELECT 訂購數量 FROM inserted ) > 100
                EXEC xp_sendmail 'jhhsu','有人下了一筆大訂單'

注意

(1) 當Trigger發生時 , 會產生兩個暫時性的表格

	inserted	deleted
新增(INSERT)	存放新增的記錄
修改(UPDATE)	存放要更新的記錄	存放更新後的記錄
刪除(DELETE)		存放要刪除的記錄

(2) 當Trigger發生時 , 資料已更改 , 但尚未 commit , 故可以 ROLLBACK TRANSACTION

(3) 使用 RAISERROR 來傳回錯誤訊息 , 舉例如下

CREATE TRIGGER 檢查定單訂購數量
ON 訂單
FOR DELETE
AS
    IF ISNULL((SELECT SUM(數量) FROM 訂單) ,0)<1000
    BEGIN
        ROLLBACK TRANSACTION
        RAISERROR('訂貨數量不得少於 1000',16,1)
    END

(4)通常對於 UPDATE 的 Trigger 會使用 @@ROWCOUNT 來判斷是否要做

Store Procedure

定義

將一連串的 SQL 事先寫好 , 存成一個物件 , 以後只要下 EXEC 物件名 , 就可以執行一連串的 SQL ,
此物件就稱為 Store Procedure.

好處

(1) 執行效率高 . 因為 Store Procedure 的 SQL 會事先編譯過 , 顯然比單純的 SQL 執行速度來的快
(2) 重複使用 .
(3) 提高安全性 . 有些表格是使用者只能看到一部份的內容 , 而修改的能力也必須受到限制 , 此時我們通常
會使用 View 創造一個使用者可以看到的虛擬表格 , 而以特定的 Store Procedure 為這些使用者提供修改的服務 .

語法

CREATE PROC[EDURE] procedure_name [;number]
    [
        {@parameter data_type} [VARYING] [= default] [OUTPUT]
    ]
    [,...n]
[WITH
    {
        RECOMPILE
        | ENCRYPTION
        | RECOMPILE, ENCRYPTION
    }
]
[FOR REPLICATION]
AS
    sql_statement [...n]

範例

CREATE PROCEDURE usp是否為工作人員
@員工編號 nvarchar(10)
@事業部門 nvarchar(4) OUTPUT
AS
    DECLARE @工作 int
    SET NOCOUNT
    SET @事業部門='0002'
    SELECT @工作⁼工作 FROM WK使用人員 WHERE 員工編號=@員工編號
    IF @@ROWCOUNT=0
        RETURN @工作
    ELSE
        RETURN 0

使用

EXEC usp是否為工作人員 '3106' , '0001'

注意

(1) 系統Store procedure名稱開頭為 sp_ , 擴充Store Procedure名稱開頭為 xp_ , 加起來有數百個
(2) Store Procedure 的傳回種類有三種

傳回值種類	如何利用傳回值
RETURN n	EXEC @ret=usp是否為工作人員 '3106' '0001'
在參數列中指定有 OUTPUT 的參數	SET @事業部門='0001' EXEC usp是否為工作人員 '3106' @事業部門 OUTPUT
RAISERROR('不具有管理者權限',16,1)	檢查 @@ERROR 的值
執行預存程序所產生的通知訊息

(3) Store Procedure 的參數有時使用者可能忘了提供或是根本無法提供 , 此時的 Store Procedure 應該如以下的寫法

CREATE PROCEDURE usp新增OP員工
@員工號碼 nvarchar(20),
@登入帳號 nvarchar(20)='templogin',
@登入密碼 nvarchar(20)='temppasswd',
@員工姓名 nvarchar(20)='tempname'
AS
INSERT INTO OP員工(員工號碼,登入帳號,登入密碼,員工姓名) VALUES(@員工號碼,@登入帳號,@登入密碼,@員工姓名)

Transaction

定義

對多個 Table 的操作可能發生某些 Table 成功而某些 Table 失敗 , 若要確保所有的 Table 的一致性
則必須使用 Transaction . 若所有參與 Transaction 的 Table 都操作成功了 , 此 Transaction 才會被 Submit
若有任何一個 Table 的操作不成功 , 則必須 Rollback .

好處

(1) 可確保多個 Table 資料的一致 , 不使用 Transaction 在更動多個 Table 時可能會造成有些更動
有些則沒有更動 .

語法

整個 Transaction 是由 BEGIN TRANSACTION 開始 , 直到 COMMIT TRANSACTION 或 ROLLBACK TRANSACTION
才結束.

範例

BEGIN TRANSACTION
    UPDATE 員工資料 SET 員工姓名='jhhsu' WHERE 員工編號='881019'
    IF @@ERROR > 0 OR @@ROWCOUNT <> 1
    GOTO NeedRollback

    UPDATE 員工親屬 SET 員工姓名='jhhsu' WHERE 員工編號='881019'

NeddRollback:
IF @@ERROR > 0 OR @@ROWCOUNT <> 1
    BEGIN
        IF @@TRANCOUNT = 1
            ROLLBACK TRANSACTION
        ELSE
            COMMIT TRANSACTION
        RETURN 1   -- 傳回 1 表示失敗
    END
ELSE
    BEGIN
        COMMIT TRANSACTION
        RETURN 0   -- 傳回 0 表示成功
    END

注意

(1) 在 Transaction 中的每一項操作結束後都必須檢查 @@ERROR 及 @@ROWCOUNT
(2) 若在 Store Procedure 中要使用 Transaction , 則應該要使用巢狀的 Transaction , 利用 @@TRANCOUNT
     來判斷是在那一層 Transaction . @@TRANSCOUNT 會在遇到 BEGIN TRANSACTION 時加 1 , 遇到
       COMMIT TRANSACTION 時減 1 , 而在遇到 ROLLBACK TRANSACTION 時則歸 0 .
(3) 在 Transaction 中所使用的資料 , 必須要適當的與其它的 Transaction 做隔離 , 因此應該視需求設定不同
       的隔離等級 . 隔離等級有下列數種 :

隔離等級	不會讀到別人修改中的資料	已讀取的資料不被更改	使用到的Table不會被更改
Read Uncommitted	否	否	否
Read Committed	是	否	否
Repeatable Read	是	是	否
Serializable	是	是	是

所謂 " 不會讀到別人修改中的資料 " 是指 : 別人已更改 , 但尚未 Commit 的資料 , 不能被讀取 .

使用

SET TRANSACTION ISOLATION LEVEL
{
        READ COMMITTED
        | READ UNCOMMITTED
        | REPEATABLE READ
        | SERIALIZABLE
}

以更改隔離等級