數據庫優化之創建存儲過程、觸發器

    存儲過程可加快查詢的執行速度，提高訪問數據的速度，幫助實現模塊化編程，保存一致性，提高安全性。觸發器是在對表進行插入、更新、刪除操作時自動執行的存儲過程，通常用于強制業務規則。

一、存儲過程

1. 為什么需要存儲過程

    從客戶端通過網絡向服務器發送SQL代碼并執行是不安全的，給***提供盜取數據的機會，如下圖所示，一個簡單的SQL注入過程

   從上圖可知，應用程序的執行過程是不安全的，主要有以下幾個方面：

（1）數據不安全，網絡傳送SQL代碼，容易被未授權者截獲

（2）每次提交SQL代碼都要經過語法編譯后在執行，影響應用程序的運行性能

（3）網絡流量大，對于反復執行的SQL代碼，在網絡上多次傳送，影響網絡傳輸量

2. 什么是存儲過程

    存儲過程是SQL語句和控制語句的預編譯集合，保存在數據庫中，可有應用程序調用執行，而且允許用戶聲明變量、邏輯控制語句及其他強大的編程功能。包含邏輯控制語句和數據操作語句，可以接收參數、輸出參數、返回單個或多個結果值及返回值

    使用存儲過程的優點：

（1）模塊化程序設計，只需創建一次，以后即可調用該存儲過程任意次

（2）執行速度快，效率高

（3）減少網絡流量

（4）具有良好的安全性

    存儲過程分為系統存儲過程和用戶自定義的存儲過程

3. 系統存儲過程

    是一組預編譯的T-SQL語句，提供了管理數據庫和更新表的機制，并充當從系統表中檢索信息的快捷方式

（1）常見的系統存儲過程

    系統存儲過程的名稱以“sp_”開頭，存放在Resource數據庫中

    使用存儲過程的語法如下：

exec  存儲過程名  [參數值]

例如：執行以下T-SQL語句

（2）常用的擴展存儲過程

    擴展存儲過程是SQL Server提供的各類系統存儲過程的一類，允許使用其他編程語言（如C#）創建外部存儲過程，通常以“xp_”開頭，以DDL形式單獨存在

    一個常用的擴展存儲過程為xp_cmdshell ,它可以完成DOS命令下的一些操作，如創建文件夾、列出文件夾。語法如下：

exec  xp_cmdshell  DOS命令  [no_output]

其中，no_output為可選參數，設置執行DOS命令后是否輸出返回信息

例如：在C盤下創建一個文件夾bank，并查看文件

4. 用戶自定義的存儲過程

    除了使用系統的存儲過程外，也可以創建自己的存儲過程。可以使用SSMS或T-SQL語句創建存儲過程

（1）使用SSMS創建存儲過程

（2）使用T-SQL語句創建存儲過程

    創建存儲過程的語法如下：

    刪除存儲過程的語法如下：

drop  proc  存儲過程名

案例：有以下兩個表，編寫存儲過程，實現網絡管理專業的平均分

觸發器

    觸發器是一種特殊的存儲過程，當表中數據發生更新時自動調用，以響應INSERT、UPDATE、DELETE語句

1. 什么是觸發器

    觸發器是對表進行插入、更新、刪除操作時自動執行的存儲過程，通常用于強制業務規則，可以定義比用CHECK約束更為復雜的約束。觸發器主要是通過事件觸發而被執行的，而存儲過程可以通過存儲過程名稱而被直接使用。

2. 觸發器的分類

INSERT觸發器：當向表中插入數據時觸發

UPDATE觸發器：當更新表中某列或多列時觸發

DELETE觸發器：當刪除表中記錄是觸發

3. deleted表和inserted表

    每個觸發器都有兩個特殊的邏輯表：刪除表和插入表。由系統管理，存儲在內存而不是數據庫中，因此，不允許用戶直接對其修改。它們只是臨時存放對表中數據行的修改信息，當觸發器工作完成，它們也被刪除。

4. 觸發器的作用

    主要作用是：實現由主鍵和外鍵所不能保證的復雜的參照完整性和數據的一致性，除此之外，還有以下幾種功能

（1）強化約束：實現比CHECK約束更為復雜的約束

（2）跟蹤變化：偵測數據庫內的操作，從而不允許未經許可的更新和變化

（3）級聯運行：偵測數據庫內的操作，并自動級聯影響整個數據庫的各項內容

5. 創建觸發器

    創建觸發器可使用SSMS或T-SQL語句

（1）使用SSMS創建觸發器

（2）使用T-SQL語句創建觸發器

    使用T-SQL語句創建觸發器的語法如下：

create  trigger  觸發器名          //創建的觸發器名稱
on  表名                            //在其上執行觸發器的表或視圖名稱
[with  encryption]                 //可選，防止將觸發器作為SQL Server復制的一部分發布
for  {[delete,insert,update]}         //關鍵字，至少指定一項，如果多項，由逗號分隔
as sql語句

案例：創建一個觸發器，當有人更改信息時，提示一條消息，并阻止操作

    如果需要修改觸發器，操作方法如下，在彈出的窗口修改T-SQL語句即可

創建觸發器時注意事項

（1）create trigger必須是批處理中的第一條語句，并只能應用到一個表中

（2）觸發器只能在當前數據庫中創建，但可以引用當前數據庫的外部對象

（3）在同一條create trigger語句中，可以為多種用戶操作（如DELETE）定義相同的觸發器操作