數(shù)據(jù)庫優(yōu)化之創(chuàng)建存儲過程、觸發(fā)器

    存儲過程可加快查詢的執(zhí)行速度，提高訪問數(shù)據(jù)的速度，幫助實(shí)現(xiàn)模塊化編程，保存一致性，提高安全性。觸發(fā)器是在對表進(jìn)行插入、更新、刪除操作時自動執(zhí)行的存儲過程，通常用于強(qiáng)制業(yè)務(wù)規(guī)則。

一、存儲過程

1. 為什么需要存儲過程

    從客戶端通過網(wǎng)絡(luò)向服務(wù)器發(fā)送SQL代碼并執(zhí)行是不安全的，給***提供盜取數(shù)據(jù)的機(jī)會，如下圖所示，一個簡單的SQL注入過程

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

（1）數(shù)據(jù)不安全，網(wǎng)絡(luò)傳送SQL代碼，容易被未授權(quán)者截獲

（2）每次提交SQL代碼都要經(jīng)過語法編譯后在執(zhí)行，影響應(yīng)用程序的運(yùn)行性能

（3）網(wǎng)絡(luò)流量大，對于反復(fù)執(zhí)行的SQL代碼，在網(wǎng)絡(luò)上多次傳送，影響網(wǎng)絡(luò)傳輸量

2. 什么是存儲過程

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

    使用存儲過程的優(yōu)點(diǎn)：

（1）模塊化程序設(shè)計(jì)，只需創(chuàng)建一次，以后即可調(diào)用該存儲過程任意次

（2）執(zhí)行速度快，效率高

（3）減少網(wǎng)絡(luò)流量

（4）具有良好的安全性

    存儲過程分為系統(tǒng)存儲過程和用戶自定義的存儲過程

3. 系統(tǒng)存儲過程

    是一組預(yù)編譯的T-SQL語句，提供了管理數(shù)據(jù)庫和更新表的機(jī)制，并充當(dāng)從系統(tǒng)表中檢索信息的快捷方式

（1）常見的系統(tǒng)存儲過程

    系統(tǒng)存儲過程的名稱以“sp_”開頭，存放在Resource數(shù)據(jù)庫中

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

exec  存儲過程名  [參數(shù)值]

例如：執(zhí)行以下T-SQL語句

（2）常用的擴(kuò)展存儲過程

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

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

exec  xp_cmdshell  DOS命令  [no_output]

其中，no_output為可選參數(shù)，設(shè)置執(zhí)行DOS命令后是否輸出返回信息

例如：在C盤下創(chuàng)建一個文件夾bank，并查看文件

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

    除了使用系統(tǒng)的存儲過程外，也可以創(chuàng)建自己的存儲過程?？梢允褂肧SMS或T-SQL語句創(chuàng)建存儲過程

（1）使用SSMS創(chuàng)建存儲過程

（2）使用T-SQL語句創(chuàng)建存儲過程

    創(chuàng)建存儲過程的語法如下：

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

drop  proc  存儲過程名

案例：有以下兩個表，編寫存儲過程，實(shí)現(xiàn)網(wǎng)絡(luò)管理專業(yè)的平均分

觸發(fā)器

    觸發(fā)器是一種特殊的存儲過程，當(dāng)表中數(shù)據(jù)發(fā)生更新時自動調(diào)用，以響應(yīng)INSERT、UPDATE、DELETE語句

1. 什么是觸發(fā)器

    觸發(fā)器是對表進(jìn)行插入、更新、刪除操作時自動執(zhí)行的存儲過程，通常用于強(qiáng)制業(yè)務(wù)規(guī)則，可以定義比用CHECK約束更為復(fù)雜的約束。觸發(fā)器主要是通過事件觸發(fā)而被執(zhí)行的，而存儲過程可以通過存儲過程名稱而被直接使用。

2. 觸發(fā)器的分類

INSERT觸發(fā)器：當(dāng)向表中插入數(shù)據(jù)時觸發(fā)

UPDATE觸發(fā)器：當(dāng)更新表中某列或多列時觸發(fā)

DELETE觸發(fā)器：當(dāng)刪除表中記錄是觸發(fā)

3. deleted表和inserted表

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

4. 觸發(fā)器的作用

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

（1）強(qiáng)化約束：實(shí)現(xiàn)比CHECK約束更為復(fù)雜的約束

（2）跟蹤變化：偵測數(shù)據(jù)庫內(nèi)的操作，從而不允許未經(jīng)許可的更新和變化

（3）級聯(lián)運(yùn)行：偵測數(shù)據(jù)庫內(nèi)的操作，并自動級聯(lián)影響整個數(shù)據(jù)庫的各項(xiàng)內(nèi)容

5. 創(chuàng)建觸發(fā)器

    創(chuàng)建觸發(fā)器可使用SSMS或T-SQL語句

（1）使用SSMS創(chuàng)建觸發(fā)器

（2）使用T-SQL語句創(chuàng)建觸發(fā)器

    使用T-SQL語句創(chuàng)建觸發(fā)器的語法如下：

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

案例：創(chuàng)建一個觸發(fā)器，當(dāng)有人更改信息時，提示一條消息，并阻止操作

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

創(chuàng)建觸發(fā)器時注意事項(xiàng)

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

（2）觸發(fā)器只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建，但可以引用當(dāng)前數(shù)據(jù)庫的外部對象

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