13.1 存儲(chǔ)過程

　　13.1.1 存儲(chǔ)過程基本概念

　　1、 在關(guān)系數(shù)據(jù)庫中，SQL語言是應(yīng)用程序和數(shù)據(jù)庫管理之間的主要編程接口;

　　2、 使用SQL語言編寫代碼時(shí)，可用兩種方法存儲(chǔ)和執(zhí)行代碼：

　　(1) 在客戶端存儲(chǔ)代碼，并創(chuàng)建向數(shù)據(jù)庫管理系統(tǒng)發(fā)送SQL命令，并處理返回結(jié)果的應(yīng)用程序;

　　(2) 將這些發(fā)送的SQL語句存儲(chǔ)在數(shù)據(jù)庫管理系統(tǒng)中，這些存儲(chǔ)在數(shù)據(jù)庫管理系統(tǒng)中的SQL語句就是存儲(chǔ)過程，然后再創(chuàng)建執(zhí)行存儲(chǔ)過程并處理返回結(jié)果的應(yīng)用程序。

　　3、 使用存儲(chǔ)過程的好處：

　　(1) 模塊化程序設(shè)計(jì)：只需創(chuàng)建一次存儲(chǔ)過程并將其存儲(chǔ)在數(shù)據(jù)庫中，以后就可以在應(yīng)用程序中多次調(diào)用存儲(chǔ)過程;

　　(2) 提高性能：系統(tǒng)在創(chuàng)建存儲(chǔ)過程時(shí)對(duì)其進(jìn)行分析和優(yōu)化，并在第一次執(zhí)行時(shí)進(jìn)行語法檢查和編譯，編譯好的代碼放入內(nèi)存中，以后再執(zhí)行此存儲(chǔ)過程時(shí)，只需直接執(zhí)行內(nèi)存中的代碼，從而提高代碼的執(zhí)行效率;

　　(3) 減少網(wǎng)絡(luò)流量：一個(gè)需要數(shù)百行SQL代碼完成的操作現(xiàn)在只需一條執(zhí)行存儲(chǔ)過程的代碼即可實(shí)現(xiàn)，因此，不再需要在網(wǎng)絡(luò)中發(fā)送這些多語句;

　　(4) 可作為安全機(jī)制使用：

　　13.1.2 創(chuàng)建和執(zhí)行存儲(chǔ)過程

　　1、 創(chuàng)建存儲(chǔ)過程的SQL語句為：CREATE PROCEDURE，語法格式為：

　　CREATE PROCEDURE 存儲(chǔ)過程名

　　[{@ 參數(shù)名 數(shù)據(jù)類型}[=default][OUTPUT]

　　][,…n]

　　AS

　　SQL語句[…n]

　　其中：

　　(1) default:表示參數(shù)的默認(rèn)值。如果定義了默認(rèn)值，則在執(zhí)行存儲(chǔ)過程時(shí)，可以不必指定該參數(shù)的值，默認(rèn)值必須是常量或NULL;

　　(2) OUTPUT：表明參數(shù)是輸出參數(shù)，該選項(xiàng)的值可以返回給存儲(chǔ)過程的調(diào)用者。

　　2、執(zhí)行存儲(chǔ)過程的SQL語句是EXECUTE，語法格式：

　　CREATE EXECUTE 存儲(chǔ)過程名 [實(shí)參[，OUTPUT][，…n]]

　　1、 執(zhí)行有多個(gè)輸入?yún)��?shù)的存儲(chǔ)過程時(shí)，參數(shù)的傳遞方式有兩種：

　　(1) 按參數(shù)位置傳遞值：指執(zhí)行存儲(chǔ)過程的EXEC語句中的實(shí)參的排列順序必須與定義存儲(chǔ)過程時(shí)定義的參數(shù)的順序一致;

　　(2) 按參數(shù)名傳遞值：指執(zhí)行存儲(chǔ)過程的EXEC語句中要指明定義存儲(chǔ)過程時(shí)指定的參數(shù)的名字以及此參數(shù)的值，而不關(guān)心參數(shù)的定義順序。

　　2、 注意：

　　(1) 在執(zhí)行有輸出參數(shù)的存儲(chǔ)過程時(shí)，執(zhí)行語句中的變量名的后邊要加上OUTPUT修飾符;

　　(2) 在調(diào)用有輸出參數(shù)的存儲(chǔ)過程時(shí)，與輸出參數(shù)對(duì)應(yīng)的是一個(gè)變量，此變量用于保存輸出參數(shù)返回的結(jié)果;

　　13.2 用戶自定義函數(shù)

　　13.2.1 基本概念

　　1、 用戶定義函數(shù)可以擴(kuò)展數(shù)據(jù)操作的功能，它在概念上類似于一般的程序設(shè)計(jì)語言中定義的函數(shù)。

　　13.2.2 創(chuàng)建和調(diào)用標(biāo)量函數(shù)

　　標(biāo)量函數(shù)—返回單個(gè)數(shù)據(jù)值的函數(shù);

　　1、定義標(biāo)量函數(shù)

　　CREATE FUNCTION [擁有者名.] 函數(shù)名

　　([{@ 參數(shù)名[AS]標(biāo)量數(shù)據(jù)類型[=default]}[,…n]])

　　RETURNS 返回值類型

　　[AS]

　　BEGIN

　　函數(shù)體

　　RETURN 標(biāo)量表達(dá)式

　　END

　　(1) 同存儲(chǔ)過程一樣，函數(shù)的參數(shù)也可以有默認(rèn)值。

　　(2) 標(biāo)量表達(dá)式：指定標(biāo)量函數(shù)返回的標(biāo)量值。

　　2、 調(diào)用標(biāo)量函數(shù)：

　　當(dāng)調(diào)用標(biāo)量函數(shù)時(shí)，必須提供至少由兩部分組成的名稱：函數(shù)擁有者名和函數(shù)名。可在任何允許出現(xiàn)表達(dá)式的SQL語句中調(diào)用標(biāo)量函數(shù)，只要類型一致;

　　13.2.3 創(chuàng)建和調(diào)用內(nèi)嵌表值函數(shù)

　　1、 創(chuàng)建內(nèi)嵌表值函數(shù)，其語法為：

　　CREATE FUNCTION [擁有者名.] 函數(shù)名

　　([{@ 參數(shù)名[AS]標(biāo)量數(shù)據(jù)類型[=default]}[,…n]])

　　RETURNS 返回變量 TABLE <表定義>

　　[AS]

　　BEGIN

　　函數(shù)體

　　RETURN

　　END

　　<表定義>：：=({列定義|表約束}[,…n])

　　2、調(diào)用多語句表值函數(shù)

　　多語句表值函數(shù)的返回值是一個(gè)表，因此對(duì)多語句表值函數(shù)的使用也是放在SELECT語句的FROM子句中。

　　13.3 觸發(fā)器

　　13.3.1 觸發(fā)器基本概念

　　1、 觸發(fā)器是一種特殊的存儲(chǔ)過程，其特殊性在于它不需要由用戶來調(diào)用，而是當(dāng)用戶對(duì)表中的數(shù)據(jù)進(jìn)行UPDATE、INSERT或DELETE操作時(shí)自動(dòng)觸發(fā)執(zhí)行;

　　2、 觸發(fā)器常用于下列場(chǎng)合：

　　(1) 完成比CHECK的約束更復(fù)雜的數(shù)據(jù)約束;

　　(2) 為保證數(shù)據(jù)庫性能而維護(hù)的非規(guī)范化數(shù)據(jù);

　　(3) 實(shí)現(xiàn)復(fù)雜的業(yè)務(wù)規(guī)則，可使業(yè)務(wù)的處理任務(wù)自動(dòng)進(jìn)行。

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

　　1、 創(chuàng)建觸發(fā)器的語句：CREATE TRIGGER，語法為：

　　CREATE TRIGGER 觸發(fā)器名稱

　　ON {表名| 視圖名}

　　[WITH ENCRYPTION]

　　{FOR |AFTER|INSTEAD OF}{[INSERT][，][DELETE][，][UPDATE]}

　　AS

　　[{IF UPDATE(column)…}]

　　SQL語句

　　(1) 觸發(fā)器名稱在數(shù)據(jù)庫中必須是惟一的;

　　(2) ON子句用于指定在其上執(zhí)行觸發(fā)器的表;

　　(3) AFTER：指定觸發(fā)器只有在引發(fā)的SQL語句中指定的操作都已成功執(zhí)行，并且所有的約束檢查也成功完成后，才執(zhí)行此觸發(fā)器，這種觸發(fā)器稱為后觸發(fā)型觸發(fā)器;

　　(4) FOR：作用同AFTER;

　　(5) INSTEAD OF：指定執(zhí)行觸發(fā)器而不是執(zhí)行引發(fā)觸發(fā)器執(zhí)行的SQL語句，從而替代觸發(fā)語句的操作，這種觸發(fā)器稱為前觸發(fā)型觸發(fā)器;

　　(6) INSTERT、DELETE或UPDATE是引發(fā)觸發(fā)器執(zhí)行的操作，若同時(shí)指定多個(gè)操作，則各操作之間用逗號(hào)分隔;

　　2、 創(chuàng)建觸發(fā)器時(shí)，需要注意：

　　(1) 在一個(gè)表上可以建立多個(gè)名稱不同、類型各異的觸發(fā)器，每個(gè)觸發(fā)器可由三個(gè)操作引發(fā);

　　(2) 大部分Transact-SQL語句都可用在觸發(fā)器中，但也有一些限制;

　　(3) 在觸發(fā)器定義中，可以使用IF UPDATE子句測(cè)試在INSERT和UPDATE語句中是否對(duì)指定字段有影響;

　　(4) 通常不要在觸發(fā)器中返回任何結(jié)果。

　　3、 創(chuàng)建后觸發(fā)型觸發(fā)器

　　使用FOR或AFTER選項(xiàng)定義的觸發(fā)器為后觸發(fā)的觸發(fā)器，即只有在引發(fā)觸發(fā)器執(zhí)行語句中指定的操作都已完成執(zhí)行，并且所有的約束檢查也成功完成后，才執(zhí)行的觸發(fā)器;

　　4、 創(chuàng)建前觸發(fā)型觸發(fā)器

　　使用INSTEAD OF 選項(xiàng)定義的觸發(fā)器為前觸發(fā)型觸發(fā)器，在該觸發(fā)器中，指定執(zhí)行觸發(fā)器而不是執(zhí)行引發(fā)觸發(fā)器執(zhí)行的SQL語句，從而替代引發(fā)語句的操作。

　　13.4 查看、修改及刪除對(duì)象

　　13.4.1 查看對(duì)象

　　對(duì)于創(chuàng)建好的存儲(chǔ)過程、函數(shù)可通過企業(yè)管理器和查詢分析器查看這些對(duì)象的代碼;

　　13.4.2 修改對(duì)象

　　1、 修改存儲(chǔ)過程：ALTER PROCEDURE

　　ALTER PROC 存儲(chǔ)過程名

　　[{@參數(shù)名 數(shù)據(jù)類型}[=default][OUTPUT]][,…n]

　　AS

　　SQL語句[…n]

　　修改與定義的語句基本一致，只將CREARE PROC 改成ALTER PROC;

　　2、修改用戶自定義函數(shù)：

　　修改與定義的語句基本一致，只將CREARE FUNCTION 改成ALTER FUNCTION;

　　3、修改觸發(fā)器：

　　修改與定義的語句基本一致，只將CREARE TRIGGER 改成ALTER TRIGGER;

　　13.4.3 刪除對(duì)象

　　1、 刪除存儲(chǔ)過程：

　　DROP PROCEDURE {存儲(chǔ)過程名}[，…n];

　　2、刪除用戶自定義函數(shù)

　　DROP FUNCTION {[擁有者名.]函數(shù)名}[,…n]

　　3、刪除觸發(fā)器

　　DROP TRIGGER {觸發(fā)器名}[,…n]