第7章事務(wù)、存儲(chǔ)過程、觸發(fā)器和游標(biāo)

1、7.1 事事 務(wù)務(wù)7.2 存儲(chǔ)過程存儲(chǔ)過程7.3 觸發(fā)器及其用途觸發(fā)器及其用途7.4 游標(biāo)游標(biāo)1、事務(wù)的概念、事務(wù)的概念事務(wù)是一個(gè)用戶定義的完整的工作單元，事務(wù)是一個(gè)用戶定義的完整的工作單元，一個(gè)事務(wù)內(nèi)的所有語句被作為整體執(zhí)行，要一個(gè)事務(wù)內(nèi)的所有語句被作為整體執(zhí)行，要么全部執(zhí)行，要么全部不執(zhí)行。么全部執(zhí)行，要么全部不執(zhí)行。原子性：原子性：事務(wù)是數(shù)據(jù)庫的邏輯工作單位，事事務(wù)是數(shù)據(jù)庫的邏輯工作單位，事務(wù)中的操作要么都做，要么都不做。務(wù)中的操作要么都做，要么都不做。 一致性：一致性：事務(wù)執(zhí)行的結(jié)果必須是使數(shù)據(jù)庫從事務(wù)執(zhí)行的結(jié)果必須是使數(shù)據(jù)庫從一個(gè)一致性狀態(tài)變到另一個(gè)一致性狀態(tài)。一個(gè)一致性狀態(tài)變到另一

2、個(gè)一致性狀態(tài)。隔離性：隔離性：一事務(wù)的執(zhí)行不能被其它事務(wù)干擾一事務(wù)的執(zhí)行不能被其它事務(wù)干擾 持續(xù)性（永久性）：持續(xù)性（永久性）：指事務(wù)一旦提交，則其指事務(wù)一旦提交，則其對(duì)數(shù)據(jù)庫中數(shù)據(jù)的改變就應(yīng)該是永久的對(duì)數(shù)據(jù)庫中數(shù)據(jù)的改變就應(yīng)該是永久的 SQL Server 的事務(wù)模式可分為的事務(wù)模式可分為顯式事務(wù)顯式事務(wù)、隱式事務(wù)隱式事務(wù)和和自動(dòng)事務(wù)自動(dòng)事務(wù)三種。三種。1) 顯式事務(wù)顯式事務(wù)顯式事務(wù)是指由用戶執(zhí)行顯式事務(wù)是指由用戶執(zhí)行T-sqlT-sql事務(wù)語句而定義的事事務(wù)語句而定義的事務(wù)，這類事務(wù)又稱做用戶定義事務(wù)。定義事務(wù)的語句包括：務(wù)，這類事務(wù)又稱做用戶定義事務(wù)。定義事務(wù)的語句包括：BEGIN TR

3、ANSACTION:標(biāo)識(shí)一個(gè)事務(wù)的開始，即啟標(biāo)識(shí)一個(gè)事務(wù)的開始，即啟動(dòng)事務(wù)。動(dòng)事務(wù)。COMMIT TRANSACTION、COMMIT WORK：標(biāo)標(biāo)識(shí)一個(gè)事務(wù)的結(jié)束，事務(wù)內(nèi)所修改的數(shù)據(jù)被永久保存到數(shù)識(shí)一個(gè)事務(wù)的結(jié)束，事務(wù)內(nèi)所修改的數(shù)據(jù)被永久保存到數(shù)據(jù)庫中。據(jù)庫中。ROLLBACK TRANSACTION、ROLLBACK WORK：標(biāo)識(shí)一個(gè)事務(wù)的結(jié)束，說明事務(wù)執(zhí)行過程中遇到標(biāo)識(shí)一個(gè)事務(wù)的結(jié)束，說明事務(wù)執(zhí)行過程中遇到錯(cuò)誤，事務(wù)內(nèi)所修改的數(shù)據(jù)被回滾到事務(wù)執(zhí)行前的狀態(tài)。錯(cuò)誤，事務(wù)內(nèi)所修改的數(shù)據(jù)被回滾到事務(wù)執(zhí)行前的狀態(tài)。2) 隱式事務(wù)隱式事務(wù)在隱式事務(wù)模式下，在當(dāng)前事務(wù)提交或回滾后，在隱式事務(wù)模式下

4、，在當(dāng)前事務(wù)提交或回滾后，SQL Server自動(dòng)自動(dòng)開始下一個(gè)事務(wù)。所以，隱式事務(wù)不需要使用開始下一個(gè)事務(wù)。所以，隱式事務(wù)不需要使用BEGIN TRANSACTION語句啟動(dòng)事務(wù)，而只需要用戶使用語句啟動(dòng)事務(wù)，而只需要用戶使用ROLLBACK TRANSACTION、ROLLBACK WORK、COMMIT TRANSACTION、COMMIT WORK等語句提交或回滾事務(wù)。在提交或回滾后，等語句提交或回滾事務(wù)。在提交或回滾后，SQL Server自動(dòng)開自動(dòng)開始下一個(gè)事務(wù)。始下一個(gè)事務(wù)。執(zhí)行執(zhí)行SET IMPLICIT_TRANSACTIONS ONSET IMPLICIT_TRANSACT

5、IONS ON語句可使語句可使SQL ServerSQL Server進(jìn)入隱進(jìn)入隱式事務(wù)模式。式事務(wù)模式。在隱式事務(wù)模式下，當(dāng)執(zhí)行下面任意一個(gè)語句時(shí)，可使在隱式事務(wù)模式下，當(dāng)執(zhí)行下面任意一個(gè)語句時(shí)，可使SQL Server重新啟動(dòng)一個(gè)事務(wù)：重新啟動(dòng)一個(gè)事務(wù)： 所有所有CREATE語句語句 ALTER TABLE 所有所有DROP語句語句 TRUNCATE TABLE GRANT REVOKE INSERT UPDATE DELETE SELECT OPEN FETCH需要關(guān)閉隱式事務(wù)模式時(shí)，調(diào)用需要關(guān)閉隱式事務(wù)模式時(shí)，調(diào)用SET語句關(guān)閉語句關(guān)閉IMPLICIT_TRANSACTIONS 連接選

6、項(xiàng)即可。連接選項(xiàng)即可。3) 自動(dòng)事務(wù)模式自動(dòng)事務(wù)模式在自動(dòng)事務(wù)模式下，當(dāng)一個(gè)語句被成功執(zhí)行后，它被在自動(dòng)事務(wù)模式下，當(dāng)一個(gè)語句被成功執(zhí)行后，它被自動(dòng)提交，而當(dāng)它執(zhí)行過程中產(chǎn)生錯(cuò)誤時(shí)，被自動(dòng)回滾。自動(dòng)提交，而當(dāng)它執(zhí)行過程中產(chǎn)生錯(cuò)誤時(shí)，被自動(dòng)回滾。自動(dòng)事務(wù)模式是自動(dòng)事務(wù)模式是SQL Server的默認(rèn)事務(wù)管理模式，的默認(rèn)事務(wù)管理模式，當(dāng)與當(dāng)與SQL ServerSQL Server建立連接后，直接進(jìn)入自動(dòng)事務(wù)模式，直到使建立連接后，直接進(jìn)入自動(dòng)事務(wù)模式，直到使用用BEGIN TRANSACTIONBEGIN TRANSACTION語句開始一個(gè)顯式事務(wù)，或者打開語句開始一個(gè)顯式事務(wù)，或者打開IMPL

7、ICIT_TRANSACTIONS IMPLICIT_TRANSACTIONS 連接選項(xiàng)進(jìn)入隱式事務(wù)模式為止。連接選項(xiàng)進(jìn)入隱式事務(wù)模式為止。而當(dāng)顯式事務(wù)被提交或而當(dāng)顯式事務(wù)被提交或IMPLICIT_TRANSACTIONS 被被關(guān)閉后，關(guān)閉后，SQL Server又進(jìn)入自動(dòng)事務(wù)管理模式。又進(jìn)入自動(dòng)事務(wù)管理模式。BEGIN TRAN demoSELECT * FROM StudentINSERT INTO Student VALUES(9711112,張三張三, )SELECT * FROM StudentROLLBACK - 回滾整個(gè)事務(wù)回滾整個(gè)事務(wù)或：或：COMMIT - 提交事務(wù)提交事務(wù)下

8、面例子說明自動(dòng)事務(wù)模式下各語句的執(zhí)行情況和下面例子說明自動(dòng)事務(wù)模式下各語句的執(zhí)行情況和SQL ServerSQL Server對(duì)批的處理：對(duì)批的處理：-SQL Server處于自動(dòng)事務(wù)管理模式處于自動(dòng)事務(wù)管理模式Use pubsGoCreate table TB_transaction1(col1 INT PRIMARY KEY, col2 datetime)GoINSERT TB_transaction1 VALUES(1,GETDATE()INSERT TB_transaction1 VALUES(1,GETDATE()-違反約束違反約束GoSELECT times=1,* from TB

9、_transaction1GoBegin tran-進(jìn)入顯示事務(wù)模式進(jìn)入顯示事務(wù)模式INSERT TB_transaction1 VALUES(2,GETDATE()SELECT times=2,* from TB_transaction1INSERT TB_transaction1 VALUE(3,GETDATE()-語法錯(cuò)誤語法錯(cuò)誤Rollback GoSELECT times=2,* from TB_transaction1-此時(shí)，又重新進(jìn)入自動(dòng)事務(wù)模式此時(shí)，又重新進(jìn)入自動(dòng)事務(wù)模式GoSET IMPLICIT_TRANSACTIONS ON進(jìn)入隱含事務(wù)模式進(jìn)入隱含事務(wù)模式goinsert

10、 TB_transaction1 VALUES(3,GETDATE()insert TB_transaction1 VALUES(4,GETDATE()rollbackgoselect * from TB_transaction1delete from TB_transaction1 rollbackselect * from TB_transaction1set implicit_transactions offdelete from TB_transaction1 rollback-此時(shí)該命令不成功，因?yàn)橐呀?jīng)進(jìn)入自動(dòng)事務(wù)模式此時(shí)該命令不成功，因?yàn)橐呀?jīng)進(jìn)入自動(dòng)事務(wù)模式go在大型分布式數(shù)據(jù)庫應(yīng)

11、用程序中，對(duì)數(shù)據(jù)庫的并發(fā)訪問操作是在大型分布式數(shù)據(jù)庫應(yīng)用程序中，對(duì)數(shù)據(jù)庫的并發(fā)訪問操作是一個(gè)普遍存在的問題。一個(gè)普遍存在的問題。SQL Server使用資源鎖定的方法管理用戶的使用資源鎖定的方法管理用戶的并發(fā)操作。如果在用戶并發(fā)訪問期間沒有鎖定數(shù)據(jù)庫資源，用戶操并發(fā)操作。如果在用戶并發(fā)訪問期間沒有鎖定數(shù)據(jù)庫資源，用戶操作相同的數(shù)據(jù)時(shí)可能會(huì)產(chǎn)生一些意想不到的問題。這些問題包括：作相同的數(shù)據(jù)時(shí)可能會(huì)產(chǎn)生一些意想不到的問題。這些問題包括：。丟失修改或被覆蓋。丟失修改或被覆蓋。讀臟數(shù)據(jù)。讀臟數(shù)據(jù)。不能重復(fù)讀：。不能重復(fù)讀：一個(gè)事物多次訪問同一行數(shù)據(jù)而每次所讀取的一個(gè)事物多次訪問同一行數(shù)據(jù)而每次所讀取的

12、數(shù)據(jù)是不同的。數(shù)據(jù)是不同的?；糜白x：?；糜白x：是指一個(gè)事務(wù)多次讀取一定范圍內(nèi)的數(shù)據(jù)行，而前是指一個(gè)事務(wù)多次讀取一定范圍內(nèi)的數(shù)據(jù)行，而前后兩次所讀取的數(shù)據(jù)行是不同后兩次所讀取的數(shù)據(jù)行是不同為了避免產(chǎn)生并發(fā)訪問問題，為了避免產(chǎn)生并發(fā)訪問問題，SQL Server使用使用不不同類型的鎖對(duì)資源進(jìn)行鎖定，從而限制在一個(gè)事務(wù)讀同類型的鎖對(duì)資源進(jìn)行鎖定，從而限制在一個(gè)事務(wù)讀取數(shù)據(jù)期間其他事務(wù)鎖執(zhí)行的操作類型，即對(duì)事務(wù)進(jìn)取數(shù)據(jù)期間其他事務(wù)鎖執(zhí)行的操作類型，即對(duì)事務(wù)進(jìn)行隔離。行隔離。不同的并發(fā)訪問問題可以通過設(shè)置不同的事不同的并發(fā)訪問問題可以通過設(shè)置不同的事務(wù)隔離級(jí)別加以解決。事務(wù)的隔離級(jí)別控制一個(gè)事務(wù)務(wù)隔離級(jí)

13、別加以解決。事務(wù)的隔離級(jí)別控制一個(gè)事務(wù)與其他事務(wù)的隔離程度，它決定該事務(wù)在讀取數(shù)據(jù)時(shí)與其他事務(wù)的隔離程度，它決定該事務(wù)在讀取數(shù)據(jù)時(shí)對(duì)資源所使用的鎖類型。對(duì)資源所使用的鎖類型。SQL-92標(biāo)準(zhǔn)定義了以下標(biāo)準(zhǔn)定義了以下4種隔離級(jí)別：種隔離級(jí)別：未提交讀：未提交讀：這是這是4種隔離級(jí)別中限制最低的級(jí)別，它僅能保種隔離級(jí)別中限制最低的級(jí)別，它僅能保證證SQL Server不讀取物理損壞的數(shù)據(jù)。在這種隔離級(jí)別下，不發(fā)出共不讀取物理損壞的數(shù)據(jù)。在這種隔離級(jí)別下，不發(fā)出共享鎖，也不接受排它鎖，事務(wù)可以對(duì)數(shù)據(jù)執(zhí)行未提交讀或臟讀；在事享鎖，也不接受排它鎖，事務(wù)可以對(duì)數(shù)據(jù)執(zhí)行未提交讀或臟讀；在事務(wù)結(jié)束前可以更改數(shù)

14、據(jù)集內(nèi)的數(shù)值，行也可以出現(xiàn)在數(shù)據(jù)集中或從數(shù)務(wù)結(jié)束前可以更改數(shù)據(jù)集內(nèi)的數(shù)值，行也可以出現(xiàn)在數(shù)據(jù)集中或從數(shù)據(jù)集消失。據(jù)集消失。提交讀：提交讀：它要求在讀取數(shù)據(jù)時(shí)控制共享鎖以避免發(fā)生臟讀，它要求在讀取數(shù)據(jù)時(shí)控制共享鎖以避免發(fā)生臟讀，但數(shù)據(jù)可在事務(wù)結(jié)束前更改，這可能產(chǎn)生不能重復(fù)讀或幻影讀問題。但數(shù)據(jù)可在事務(wù)結(jié)束前更改，這可能產(chǎn)生不能重復(fù)讀或幻影讀問題?？芍貜?fù)讀：可重復(fù)讀：鎖定查詢中使用的所有數(shù)據(jù)以防止其他用戶更新，鎖定查詢中使用的所有數(shù)據(jù)以防止其他用戶更新，但是其他用戶可以將新的幻影行插入到數(shù)據(jù)集中，新插入的幻影行將但是其他用戶可以將新的幻影行插入到數(shù)據(jù)集中，新插入的幻影行將出現(xiàn)在當(dāng)前事物的后續(xù)讀取結(jié)

15、果集中?？芍貜?fù)讀能夠避免產(chǎn)生臟讀和出現(xiàn)在當(dāng)前事物的后續(xù)讀取結(jié)果集中?？芍貜?fù)讀能夠避免產(chǎn)生臟讀和非重復(fù)讀問題，但仍可能導(dǎo)致幻影讀問題。非重復(fù)讀問題，但仍可能導(dǎo)致幻影讀問題。可串行讀：可串行讀：這是事務(wù)隔離的最高級(jí)別，它使事務(wù)之間完全隔這是事務(wù)隔離的最高級(jí)別，它使事務(wù)之間完全隔離，所以將導(dǎo)致并發(fā)級(jí)別較低。在這種隔離級(jí)別下，離，所以將導(dǎo)致并發(fā)級(jí)別較低。在這種隔離級(jí)別下，SQL Server在數(shù)在數(shù)據(jù)集上放置一個(gè)范圍鎖，以防止其他用戶在事務(wù)完成之前更新數(shù)據(jù)集據(jù)集上放置一個(gè)范圍鎖，以防止其他用戶在事務(wù)完成之前更新數(shù)據(jù)集或向數(shù)據(jù)集內(nèi)插入數(shù)據(jù)行，從而避免出現(xiàn)臟讀、非重復(fù)讀或幻影讀等或向數(shù)據(jù)集內(nèi)插入數(shù)據(jù)行，從

16、而避免出現(xiàn)臟讀、非重復(fù)讀或幻影讀等并發(fā)問題。并發(fā)問題。調(diào)用調(diào)用Transact-SQL中的中的SET TRANSACTION INOLATION LEVEL語句可以調(diào)整事務(wù)的隔離級(jí)別，以控制由該連接所發(fā)出的所語句可以調(diào)整事務(wù)的隔離級(jí)別，以控制由該連接所發(fā)出的所有有SELECT語句的默認(rèn)事務(wù)鎖定行為。該語句的語法格式為：語句的默認(rèn)事務(wù)鎖定行為。該語句的語法格式為： SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED |READ COMMITTED |REPEATED READ |SERIALIZATION SQL Server中有關(guān)事務(wù)的處理語句有：

17、中有關(guān)事務(wù)的處理語句有：命令名命令名作用作用格式格式BEGIN TRANSACTION說明一個(gè)事務(wù)開始說明一個(gè)事務(wù)開始 BEGIN TRANsaction COMMIT TRANSACTION說明一個(gè)事務(wù)結(jié)束，它的作說明一個(gè)事務(wù)結(jié)束，它的作用是提交或確認(rèn)事務(wù)已經(jīng)完用是提交或確認(rèn)事務(wù)已經(jīng)完成成 COMMIT TRANsaction SAVE TRANSACTION用于在事務(wù)中設(shè)置一個(gè)保存用于在事務(wù)中設(shè)置一個(gè)保存點(diǎn)，目的是在撤消事務(wù)時(shí)可點(diǎn)，目的是在撤消事務(wù)時(shí)可以只撤消部分事務(wù)，以提高以只撤消部分事務(wù)，以提高系統(tǒng)的效率系統(tǒng)的效率 SAVE TRANsaction ROLLBACK TRANSACTI

18、ON 說明要撤消事務(wù)，即撤消在說明要撤消事務(wù)，即撤消在該事務(wù)中對(duì)數(shù)據(jù)庫所做的更該事務(wù)中對(duì)數(shù)據(jù)庫所做的更新操作，使數(shù)據(jù)庫回退到新操作，使數(shù)據(jù)庫回退到BEGIN TRANSACTION或或保存點(diǎn)之前的狀態(tài)保存點(diǎn)之前的狀態(tài) ROLLBACK TRANsaction | 保存點(diǎn)提供了一種機(jī)制，用于回滾部分事務(wù)。保存點(diǎn)提供了一種機(jī)制，用于回滾部分事務(wù)。在應(yīng)用程序中，使用在應(yīng)用程序中，使用SAVE TRANSACTION savepoint_name語句設(shè)置保存點(diǎn)，使用語句設(shè)置保存點(diǎn)，使用ROLLBACK TRANSACTION savepoint_name將事務(wù)回滾到保存將事務(wù)回滾到保存點(diǎn)。點(diǎn)。關(guān)于嵌套

19、事務(wù)：關(guān)于嵌套事務(wù)：說明：說明：在定義一個(gè)事務(wù)時(shí)，在定義一個(gè)事務(wù)時(shí)，BEGIN TRANSACTION語語句應(yīng)與句應(yīng)與COMMIT TRANSACTION語句或語句或ROLLBACK TRANSACTION成對(duì)出現(xiàn)。在成對(duì)出現(xiàn)。在SQL Server中，顯示事務(wù)定義中，顯示事務(wù)定義語句可以嵌套語句可以嵌套.一個(gè)嵌套的事務(wù)是一系列子事務(wù)一個(gè)嵌套的事務(wù)是一系列子事務(wù)ti的集合的集合, T=t1,t2,tn,這些子事務(wù)中的每一個(gè)又可以是擁有它自己的這些子事務(wù)中的每一個(gè)又可以是擁有它自己的事務(wù)事務(wù).T能夠決定子事務(wù)能夠決定子事務(wù)ti的啟動(dòng)和終止的啟動(dòng)和終止,反過來反過來,如果如果T中的一個(gè)中的一個(gè)子事

20、務(wù)子事務(wù)ti終止終止,它強(qiáng)制它強(qiáng)制T終止終止;如拖如拖ti提交提交,這一動(dòng)作并不能使這一動(dòng)作并不能使ti成成為永久的為永久的,如果如果T終止終止(回滾回滾),那么那么ti的提交將被撤消的提交將被撤消.也就是說，也就是說，SQL Server忽略內(nèi)部事務(wù)的提交，根據(jù)最外忽略內(nèi)部事務(wù)的提交，根據(jù)最外部事務(wù)結(jié)束時(shí)采取的操作，將提交或者回滾事務(wù)。如果提交部事務(wù)結(jié)束時(shí)采取的操作，將提交或者回滾事務(wù)。如果提交外部事務(wù)，則內(nèi)層嵌套的事務(wù)也會(huì)提交。如果回滾外部事務(wù)，外部事務(wù)，則內(nèi)層嵌套的事務(wù)也會(huì)提交。如果回滾外部事務(wù)，則不論此前是否提交過內(nèi)層事務(wù)，所有內(nèi)層事務(wù)都將回滾。則不論此前是否提交過內(nèi)層事務(wù)，所有內(nèi)層事

21、務(wù)都將回滾。但實(shí)際上只有最外層的但實(shí)際上只有最外層的BEGIN TRANSACTION語語句和句和COMMIT TRANSACTION語句才能建立和提交事語句才能建立和提交事務(wù)；在回滾事務(wù)時(shí)，也只能使用最外層定義的事務(wù)名或務(wù)；在回滾事務(wù)時(shí)，也只能使用最外層定義的事務(wù)名或存儲(chǔ)點(diǎn)標(biāo)記，而不能使用內(nèi)層定義的事務(wù)名。事務(wù)嵌套存儲(chǔ)點(diǎn)標(biāo)記，而不能使用內(nèi)層定義的事務(wù)名。事務(wù)嵌套常用在存儲(chǔ)過程或觸發(fā)器內(nèi)，它們可以使用常用在存儲(chǔ)過程或觸發(fā)器內(nèi)，它們可以使用BEGIN TRANSACTION 。COMMIT TRANSACTION對(duì)來對(duì)來相互調(diào)用。相互調(diào)用。例例1、Use pubsgoBEGIN TRANSACT

22、ION demoSELECT * FROM discountsINSERT discountsVALUES(demo1,null,null,null,20.0)SAVE TRANSACTION save_demoINSERT discountsVALUES(demo2,null,null,null,20.0)SELECT * FROM discountsROLLBACK TRANSACTION save_demo回滾部分事務(wù)回滾部分事務(wù)SELECT * FROM discountsROLLBACK TRANSACTION回滾整個(gè)事務(wù)回滾整個(gè)事務(wù)SELECT * FROM discounts例例

23、2、事務(wù)嵌套、事務(wù)嵌套u(yù)se pubsgoBEGIN TRANSACTION demoSELECT * FROM discountsINSERT discountsVALUES(demo1,null,null,null,20.0)begin TRANSACTION demo1INSERT discountsVALUES(demo2,null,null,null,20.0)SELECT * FROM discountsROLLBACK TRANSACTION demo1SELECT * FROM discountsROLLBACK TRANSACTION-出錯(cuò)，因?yàn)橄鄳?yīng)事務(wù)已經(jīng)被回滾出錯(cuò)，因?yàn)橄?/p>

24、應(yīng)事務(wù)已經(jīng)被回滾SELECT * FROM discounts例例3、USE pubsGOCREATE TABLE TB_transaction2(col1 int IDENTITY, col2 char(10)GOSET IMPLICIT_TRANSACTIONS ONGOINSERT TB_ transaction2 VALUES(row1)INSERT TB_ transaction2 VALUES(row2)GoCOMMIT TRANSACTIONPRINT 第一個(gè)隱式事務(wù)所插入的數(shù)據(jù)第一個(gè)隱式事務(wù)所插入的數(shù)據(jù):SELECT * FROM TB_ transaction2GOINSER

25、T TB_ transaction2 VALUES(row3)GOPRINT 第一、二個(gè)隱式事務(wù)所插入的第一、二個(gè)隱式事務(wù)所插入的數(shù)據(jù)：數(shù)據(jù)：SELECT * FROM TB_ transaction2GOROLLBACK TRANSACTIONGOSET IMPLICIT_TRANSACTION OFFGOPRINT 回滾第二個(gè)隱式事務(wù)后剩下的回滾第二個(gè)隱式事務(wù)后剩下的數(shù)據(jù)：數(shù)據(jù)：SELECT * FROM TB_ transaction2GO7.2.1 創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程7.2.2 執(zhí)行存儲(chǔ)過程執(zhí)行存儲(chǔ)過程7.2.3 存儲(chǔ)過程的返回值和狀態(tài)信息存儲(chǔ)過程的返回值和狀態(tài)信息7.2.4 查

26、看和修改存儲(chǔ)過程查看和修改存儲(chǔ)過程7.27.2.5 5 重命名和刪除存儲(chǔ)過程重命名和刪除存儲(chǔ)過程7.2.6 系統(tǒng)存儲(chǔ)過程系統(tǒng)存儲(chǔ)過程SQL Server提供了一種方法，它可以將一些固定提供了一種方法，它可以將一些固定的操作集中起來由的操作集中起來由SQL Server數(shù)據(jù)庫服務(wù)器來完成，以數(shù)據(jù)庫服務(wù)器來完成，以實(shí)現(xiàn)某個(gè)任務(wù)，這種方法就是實(shí)現(xiàn)某個(gè)任務(wù)，這種方法就是存儲(chǔ)過程存儲(chǔ)過程。存儲(chǔ)過程存儲(chǔ)過程是是 SQL 語句和流程控制語句的預(yù)編譯集語句和流程控制語句的預(yù)編譯集合，以一個(gè)名稱存儲(chǔ)并作為一個(gè)單元處理。存儲(chǔ)過程存合，以一個(gè)名稱存儲(chǔ)并作為一個(gè)單元處理。存儲(chǔ)過程存儲(chǔ)在數(shù)據(jù)庫內(nèi)，可由應(yīng)用程序調(diào)用執(zhí)行，

27、而且允許用戶儲(chǔ)在數(shù)據(jù)庫內(nèi)，可由應(yīng)用程序調(diào)用執(zhí)行，而且允許用戶聲明變量、有條件執(zhí)行以及其它強(qiáng)大的編程功能。聲明變量、有條件執(zhí)行以及其它強(qiáng)大的編程功能。 在在SQL Server中存儲(chǔ)過程分為兩類：即系統(tǒng)提供中存儲(chǔ)過程分為兩類：即系統(tǒng)提供的存儲(chǔ)過程和用戶自定義的存儲(chǔ)過程。的存儲(chǔ)過程和用戶自定義的存儲(chǔ)過程。 在在SQL Server中，可以使用三種方法創(chuàng)建存儲(chǔ)中，可以使用三種方法創(chuàng)建存儲(chǔ)過程過程 ：使用創(chuàng)建存儲(chǔ)過程向?qū)?chuàng)建存儲(chǔ)過程。使用創(chuàng)建存儲(chǔ)過程向?qū)?chuàng)建存儲(chǔ)過程。利用利用SQL Server 企業(yè)管理器創(chuàng)建存儲(chǔ)過程。企業(yè)管理器創(chuàng)建存儲(chǔ)過程。使用使用Transact-SQL語句中的語句中的CREAT

28、E PROCEDURE命令創(chuàng)建存儲(chǔ)過程。命令創(chuàng)建存儲(chǔ)過程。 所有的輸入?yún)?shù)以及傳給調(diào)用者的輸出參所有的輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)。數(shù)。被執(zhí)行的針對(duì)數(shù)據(jù)庫的操作語句，包括調(diào)被執(zhí)行的針對(duì)數(shù)據(jù)庫的操作語句，包括調(diào)用其它存儲(chǔ)過程的語句。用其它存儲(chǔ)過程的語句。返回給調(diào)用者的狀態(tài)值，以指明調(diào)用是成返回給調(diào)用者的狀態(tài)值，以指明調(diào)用是成功還是失敗。功還是失敗。在企業(yè)管理器中，在企業(yè)管理器中，選擇工具菜單中的向選擇工具菜單中的向?qū)нx項(xiàng)，選擇導(dǎo)選項(xiàng)，選擇“向?qū)驅(qū)А保ㄈ鐖D（如圖8-1所示），所示），則出現(xiàn)歡迎使用創(chuàng)建則出現(xiàn)歡迎使用創(chuàng)建存儲(chǔ)過程向?qū)?duì)話框，存儲(chǔ)過程向?qū)?duì)話框，如圖如圖8-2所示。根據(jù)所示。根據(jù)

29、圖圖8-2-圖圖8-7提示可提示可完成創(chuàng)建存儲(chǔ)過程。完成創(chuàng)建存儲(chǔ)過程。圖圖8-1 新建新建SQL Server組組圖圖8-2 選擇創(chuàng)建存儲(chǔ)過程向?qū)нx擇創(chuàng)建存儲(chǔ)過程向?qū)D圖8-3 歡迎使用創(chuàng)建存儲(chǔ)過程向?qū)?duì)話框歡迎使用創(chuàng)建存儲(chǔ)過程向?qū)?duì)話框圖圖8-4 選擇數(shù)據(jù)庫對(duì)話框選擇數(shù)據(jù)庫對(duì)話框 圖圖8-5 選擇數(shù)據(jù)庫對(duì)象對(duì)話框選擇數(shù)據(jù)庫對(duì)象對(duì)話框圖圖8-6 完成創(chuàng)建存儲(chǔ)過程向?qū)?duì)話框完成創(chuàng)建存儲(chǔ)過程向?qū)?duì)話框圖圖8-7 編輯存儲(chǔ)過程屬性對(duì)話框編輯存儲(chǔ)過程屬性對(duì)話框圖圖8-8 編輯存儲(chǔ)過程編輯存儲(chǔ)過程SQL對(duì)話框?qū)υ捒颍ǎ┰冢ǎ┰赟QL Server企業(yè)管理器中，選擇指定的服企業(yè)管理器中，選擇指定的服務(wù)器和

30、數(shù)據(jù)庫，用右鍵單擊要?jiǎng)?chuàng)建存儲(chǔ)過程的數(shù)據(jù)庫，務(wù)器和數(shù)據(jù)庫，用右鍵單擊要?jiǎng)?chuàng)建存儲(chǔ)過程的數(shù)據(jù)庫，在彈出的快捷菜單中選擇在彈出的快捷菜單中選擇“新建新建”選項(xiàng)，再選擇下一級(jí)選項(xiàng)，再選擇下一級(jí)菜單中的菜單中的“存儲(chǔ)過程存儲(chǔ)過程”選項(xiàng)，如圖選項(xiàng)，如圖8-9所示；或者用右所示；或者用右鍵單擊存儲(chǔ)過程圖標(biāo)，從彈出的快捷菜單中選擇鍵單擊存儲(chǔ)過程圖標(biāo)，從彈出的快捷菜單中選擇“新建新建存儲(chǔ)過程存儲(chǔ)過程”選項(xiàng)，如圖選項(xiàng)，如圖8-10所示。均會(huì)出現(xiàn)創(chuàng)建存儲(chǔ)過所示。均會(huì)出現(xiàn)創(chuàng)建存儲(chǔ)過程對(duì)話框，如圖程對(duì)話框，如圖8-11所示。所示。（）在文本框中可以輸入創(chuàng)建存儲(chǔ)過程的（）在文本框中可以輸入創(chuàng)建存儲(chǔ)過程的T_SQL語句，單擊

31、語句，單擊“檢查語法檢查語法”，則可以檢查語法是否正確；，則可以檢查語法是否正確；單擊單擊“確定確定”按鈕，即可保存該存儲(chǔ)過程。如果要設(shè)置按鈕，即可保存該存儲(chǔ)過程。如果要設(shè)置權(quán)限，單擊權(quán)限，單擊“權(quán)限權(quán)限”按鈕，如圖按鈕，如圖8-12所示。所示。圖圖8-9 選擇新建存儲(chǔ)過程對(duì)話框（選擇新建存儲(chǔ)過程對(duì)話框（1）圖圖8-10 選擇新建存儲(chǔ)過程對(duì)話框（選擇新建存儲(chǔ)過程對(duì)話框（2）圖圖8-11 新建存儲(chǔ)過程對(duì)話框新建存儲(chǔ)過程對(duì)話框圖圖8-12 設(shè)置權(quán)限對(duì)話框設(shè)置權(quán)限對(duì)話框創(chuàng)建存儲(chǔ)過程前，應(yīng)該考慮下列幾個(gè)事項(xiàng)：創(chuàng)建存儲(chǔ)過程前，應(yīng)該考慮下列幾個(gè)事項(xiàng)： 不能將不能將 CREATE PROCEDURE 語句與

32、其它語句與其它 SQL 語句組合到單個(gè)批處理中。語句組合到單個(gè)批處理中。創(chuàng)建存儲(chǔ)過程的權(quán)限默認(rèn)屬于數(shù)據(jù)庫所有者，創(chuàng)建存儲(chǔ)過程的權(quán)限默認(rèn)屬于數(shù)據(jù)庫所有者，該所有者可將此權(quán)限授予其他用戶。該所有者可將此權(quán)限授予其他用戶。存儲(chǔ)過程是數(shù)據(jù)庫對(duì)象，其名稱必須遵守標(biāo)識(shí)存儲(chǔ)過程是數(shù)據(jù)庫對(duì)象，其名稱必須遵守標(biāo)識(shí)符規(guī)則。符規(guī)則。只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建存儲(chǔ)過程。只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建存儲(chǔ)過程。一個(gè)存儲(chǔ)過程的最大尺寸為一個(gè)存儲(chǔ)過程的最大尺寸為128M。CREATE PROC EDURE 存儲(chǔ)過程名存儲(chǔ)過程名 ；版本號(hào)；版本號(hào) ( 參數(shù)名參數(shù)名 數(shù)據(jù)類型數(shù)據(jù)類型 VARYING =default OUTPUT ，)W

33、ITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFOR REPLICATION AS SQL語句語句1）：為新建立的存儲(chǔ)過程名稱，它必須遵守為新建立的存儲(chǔ)過程名稱，它必須遵守T-SQL標(biāo)識(shí)符標(biāo)識(shí)符命名規(guī)則，且在一個(gè)數(shù)據(jù)庫中，存儲(chǔ)過程名必須唯一。命名規(guī)則，且在一個(gè)數(shù)據(jù)庫中，存儲(chǔ)過程名必須唯一。2） ；版本號(hào)；版本號(hào) :用來區(qū)分一組同名存儲(chǔ)過程中的不同版本。同名存儲(chǔ)用來區(qū)分一組同名存儲(chǔ)過程中的不同版本。同名存儲(chǔ)過程只需調(diào)用一次過程只需調(diào)用一次DROP PROCEDURE即可全部刪除。即可全部刪除。2）：存儲(chǔ)過程的參數(shù)有兩種類型：輸入?yún)?shù)和輸出參數(shù)存儲(chǔ)過程的

34、參數(shù)有兩種類型：輸入?yún)?shù)和輸出參數(shù). Cursor類型只能用做輸出參數(shù)，使用該數(shù)據(jù)類型時(shí)，必須同時(shí)使用類型只能用做輸出參數(shù)，使用該數(shù)據(jù)類型時(shí)，必須同時(shí)使用VARYING和和OUTPUT。3）OUTPUT選項(xiàng)選項(xiàng)用來聲明存儲(chǔ)過程的返回參數(shù)，其值可以返回給調(diào)用來聲明存儲(chǔ)過程的返回參數(shù)，其值可以返回給調(diào)用它的用它的EXECUTE語句，語句，text、ntext和和image類型參數(shù)可用做存儲(chǔ)過類型參數(shù)可用做存儲(chǔ)過程的返回參數(shù)。程的返回參數(shù)。4）=default用來為存儲(chǔ)過程參數(shù)設(shè)置默認(rèn)值。用來為存儲(chǔ)過程參數(shù)設(shè)置默認(rèn)值。5）：可以包含任意數(shù)量的可以包含任意數(shù)量的T-SQL語句，它定義存儲(chǔ)過程語句，它定

35、義存儲(chǔ)過程所執(zhí)行的操作。定義的文本不能超過所執(zhí)行的操作。定義的文本不能超過128MB。6）WITH RECOMPILE選項(xiàng)要求選項(xiàng)要求SQL Server不要在緩存不要在緩存中保存存儲(chǔ)過程的執(zhí)行計(jì)劃，而在每次執(zhí)行時(shí)都重新對(duì)它進(jìn)行中保存存儲(chǔ)過程的執(zhí)行計(jì)劃，而在每次執(zhí)行時(shí)都重新對(duì)它進(jìn)行編譯。編譯。7）WITH ENCRYPTION選項(xiàng)要求對(duì)存儲(chǔ)在選項(xiàng)要求對(duì)存儲(chǔ)在syscomments系統(tǒng)表中的存儲(chǔ)過程定義文本進(jìn)行加密。系統(tǒng)表中的存儲(chǔ)過程定義文本進(jìn)行加密。8）FOR REPLICATION選項(xiàng)說明該存儲(chǔ)過程只能在復(fù)制選項(xiàng)說明該存儲(chǔ)過程只能在復(fù)制過程中執(zhí)行，但這種類型的存儲(chǔ)過程不能在訂閱服務(wù)器上執(zhí)行。

36、過程中執(zhí)行，但這種類型的存儲(chǔ)過程不能在訂閱服務(wù)器上執(zhí)行。不能與不能與WITH RECOMPILE同時(shí)使用。同時(shí)使用。比如，我們創(chuàng)建一個(gè)最簡單的存儲(chǔ)過程比如，我們創(chuàng)建一個(gè)最簡單的存儲(chǔ)過程: CREATE PROCedure getSTU_ISASSELECT * FROM Student where sdept=ISCREATE PROCedure getSTU( department varchar(20)ASSELECT * FROM Student where sdept=department若我們使用帶參數(shù)的存儲(chǔ)過程若我們使用帶參數(shù)的存儲(chǔ)過程,則可用一個(gè)存儲(chǔ)過程實(shí)現(xiàn)則可用一個(gè)存儲(chǔ)過程實(shí)現(xiàn)

37、對(duì)不同系的學(xué)生數(shù)據(jù)的查詢：對(duì)不同系的學(xué)生數(shù)據(jù)的查詢： 創(chuàng)建存儲(chǔ)過程中的創(chuàng)建存儲(chǔ)過程中的還可以含有流程控制還可以含有流程控制等語句。存儲(chǔ)過程可以嵌套，即在一個(gè)存儲(chǔ)過程中可以等語句。存儲(chǔ)過程可以嵌套，即在一個(gè)存儲(chǔ)過程中可以調(diào)用另外一個(gè)存儲(chǔ)過程。存儲(chǔ)過程一般用來完成數(shù)據(jù)查調(diào)用另外一個(gè)存儲(chǔ)過程。存儲(chǔ)過程一般用來完成數(shù)據(jù)查詢和數(shù)據(jù)處理操作，所以在存儲(chǔ)過程中詢和數(shù)據(jù)處理操作，所以在存儲(chǔ)過程中不可以使用創(chuàng)建不可以使用創(chuàng)建數(shù)據(jù)庫對(duì)象等語句數(shù)據(jù)庫對(duì)象等語句。這類語句如下：。這類語句如下： .SET SHOWPLAN_TEXT.SET SHOWPLAN_ALLCREATE TABLECREATE VIEWCREA

38、TE DEFAULTCREATE RULECREATE TRIGGERCREATE PROCEDURE 執(zhí)行存儲(chǔ)過程的語句：執(zhí)行存儲(chǔ)過程的語句：EXECute = =| 例如、調(diào)用上面的過程，查詢數(shù)學(xué)系學(xué)生的信息的語句為：例如、調(diào)用上面的過程，查詢數(shù)學(xué)系學(xué)生的信息的語句為： EXECUTE getSTU MA或者：或者：EXECUTE getSTU department=MA或者：或者：DECLARE dept CHAR(20) SELECT dept=MA EXECUTE getSTU dept無論什么時(shí)候執(zhí)行存儲(chǔ)過程，總要返回一個(gè)無論什么時(shí)候執(zhí)行存儲(chǔ)過程，總要返回一個(gè)結(jié)果結(jié)果碼碼，用以指示

39、存儲(chǔ)過程的執(zhí)行狀態(tài)。用以指示存儲(chǔ)過程的執(zhí)行狀態(tài)。如果存儲(chǔ)過程執(zhí)行成功，返回的結(jié)果碼是如果存儲(chǔ)過程執(zhí)行成功，返回的結(jié)果碼是0；如果存儲(chǔ)過程執(zhí)行失敗，返回的結(jié)果碼一般是一如果存儲(chǔ)過程執(zhí)行失敗，返回的結(jié)果碼一般是一個(gè)個(gè)負(fù)數(shù)負(fù)數(shù)，它和失敗的類型有關(guān)。，它和失敗的類型有關(guān)。我們?cè)趧?chuàng)建存儲(chǔ)過程時(shí)，也可以定義自己的狀態(tài)我們?cè)趧?chuàng)建存儲(chǔ)過程時(shí)，也可以定義自己的狀態(tài)碼和錯(cuò)誤信息。比如：碼和錯(cuò)誤信息。比如： /*查詢工資大于查詢工資大于salary的員工信息的員工信息*/CREATE PROCedure getemp (salary)ASIF salary=NULLRETURN 13IF NOT EXISTS(SE

40、LECT*FROM職工職工 WHERE 工資工資salary)RETURN-103SELECT*FROM職工職工WHERE工資工資salary 其調(diào)用語句可以是：其調(diào)用語句可以是：DECLARE return_status intExecute return_status=getemp 1200GoIf return_status =13 print “必須提供一個(gè)數(shù)值作參數(shù)！必須提供一個(gè)數(shù)值作參數(shù)！”Else If return_status = -103 print “沒有滿足條件的記錄！沒有滿足條件的記錄！”以下存儲(chǔ)過程用來查詢學(xué)生的平均成績：以下存儲(chǔ)過程用來查詢學(xué)生的平均成績：CREA

41、TE PROCEDURE GET_AVG(Snumber char(8),savg tinyint OUTPUT)ASIF Snumber = NULL RETURN -1IF NOT EXISTS (SELECT * FROM SC WHERE sno=Snumber) return 2Select savg=avg(grade)From scWhere sno=Snumber其調(diào)用語句為：其調(diào)用語句為：DECLARE Snum char(8),saverage tinying,re_status intSELECT Snum=95002Execute re_status=GET_AVG S

42、num,saverage OUTPUTIF. 系統(tǒng)存儲(chǔ)過程系統(tǒng)存儲(chǔ)過程sp_procoption可以將存儲(chǔ)過程設(shè)置為自動(dòng)可以將存儲(chǔ)過程設(shè)置為自動(dòng)執(zhí)行方式執(zhí)行方式,使之在使之在SQL Server每次啟動(dòng)時(shí)自動(dòng)執(zhí)行，其語法每次啟動(dòng)時(shí)自動(dòng)執(zhí)行，其語法格式為：格式為： sp_procoption procName=procedure ,optionName=option ,optionValue=value其中：其中：procedure為需要自動(dòng)執(zhí)行的存儲(chǔ)過程名稱。為需要自動(dòng)執(zhí)行的存儲(chǔ)過程名稱。option和和value參數(shù)分別為待設(shè)置的選項(xiàng)名稱及其狀態(tài)。參數(shù)分別為待設(shè)置的選項(xiàng)名稱及其狀態(tài)。Opti

43、on唯一取值為唯一取值為startup,它設(shè)置存儲(chǔ)過程的自動(dòng)執(zhí)行狀態(tài)。它設(shè)置存儲(chǔ)過程的自動(dòng)執(zhí)行狀態(tài)。Value的取值為的取值為true和和false、或、或on 和和off。注意：注意：1 1）設(shè)置為自動(dòng)執(zhí)行的存儲(chǔ)過程不要返回任何結(jié)果集合。設(shè)置為自動(dòng)執(zhí)行的存儲(chǔ)過程不要返回任何結(jié)果集合。因?yàn)樗鼈冇上到y(tǒng)自動(dòng)執(zhí)行，無法處理其返回結(jié)果。因?yàn)樗鼈冇上到y(tǒng)自動(dòng)執(zhí)行，無法處理其返回結(jié)果。2 2）SQL ServerSQL Server配置選項(xiàng)配置選項(xiàng)scan for stratupscan for stratup控制控制SQL SQL ServerServer每次啟動(dòng)時(shí)是否查找和執(zhí)行系統(tǒng)中標(biāo)識(shí)為自動(dòng)執(zhí)行每次啟

44、動(dòng)時(shí)是否查找和執(zhí)行系統(tǒng)中標(biāo)識(shí)為自動(dòng)執(zhí)行的存儲(chǔ)過程。只有打開該選項(xiàng)時(shí)，自動(dòng)執(zhí)行存儲(chǔ)過程才能的存儲(chǔ)過程。只有打開該選項(xiàng)時(shí)，自動(dòng)執(zhí)行存儲(chǔ)過程才能真正自動(dòng)執(zhí)行。真正自動(dòng)執(zhí)行。存儲(chǔ)過程被創(chuàng)建之后，它的名字就存儲(chǔ)在系統(tǒng)存儲(chǔ)過程被創(chuàng)建之后，它的名字就存儲(chǔ)在系統(tǒng)表表sysobjects中，它的源代碼存放在系統(tǒng)表中，它的源代碼存放在系統(tǒng)表syscomments中。可以使用使用企業(yè)管理器或系統(tǒng)中。可以使用使用企業(yè)管理器或系統(tǒng)存儲(chǔ)過程來查看用戶創(chuàng)建的存儲(chǔ)過程。存儲(chǔ)過程來查看用戶創(chuàng)建的存儲(chǔ)過程。在企業(yè)管理器中，打開指定的服務(wù)器和數(shù)據(jù)庫在企業(yè)管理器中，打開指定的服務(wù)器和數(shù)據(jù)庫項(xiàng)，選擇要?jiǎng)?chuàng)建存儲(chǔ)過程的數(shù)據(jù)庫，單擊存儲(chǔ)過程

45、項(xiàng)，選擇要?jiǎng)?chuàng)建存儲(chǔ)過程的數(shù)據(jù)庫，單擊存儲(chǔ)過程文件夾，此時(shí)在右邊的頁框中顯示該數(shù)據(jù)庫的所有文件夾，此時(shí)在右邊的頁框中顯示該數(shù)據(jù)庫的所有存儲(chǔ)過程。用右鍵單擊要查看的存儲(chǔ)過程，從彈出存儲(chǔ)過程。用右鍵單擊要查看的存儲(chǔ)過程，從彈出的快捷菜單中選擇屬性選項(xiàng)，此時(shí)便可以看到存儲(chǔ)的快捷菜單中選擇屬性選項(xiàng)，此時(shí)便可以看到存儲(chǔ)過程的源代碼，同時(shí)還可以做修改，如下圖所示。過程的源代碼，同時(shí)還可以做修改，如下圖所示。 ALTER PROC EDURE 存儲(chǔ)過程名存儲(chǔ)過程名 ；版本號(hào)；版本號(hào) ( 參數(shù)名參數(shù)名 數(shù)據(jù)類型數(shù)據(jù)類型 VARYING =default OUTPUT ，)WITH RECOMPILE|ENCRY

46、PTION|RECOMPILE,ENCRYPTIONFOR REPLICATION AS SQL語句語句1. 重命名存儲(chǔ)過程重命名存儲(chǔ)過程修改存儲(chǔ)過程的名稱可以使用系統(tǒng)存儲(chǔ)過程修改存儲(chǔ)過程的名稱可以使用系統(tǒng)存儲(chǔ)過程sp_rename，其語法形式如下：，其語法形式如下：sp_rename 原存儲(chǔ)過程名稱，新存儲(chǔ)過程名稱原存儲(chǔ)過程名稱，新存儲(chǔ)過程名稱另外，通過企業(yè)管理器也可以修改存儲(chǔ)過程的名另外，通過企業(yè)管理器也可以修改存儲(chǔ)過程的名稱。稱。 刪除存儲(chǔ)過程可以使用刪除存儲(chǔ)過程可以使用DROP命令，命令，DROP命令命令可以將一個(gè)或者多個(gè)存儲(chǔ)過程或者存儲(chǔ)過程組從當(dāng)可以將一個(gè)或者多個(gè)存儲(chǔ)過程或者存儲(chǔ)過程

47、組從當(dāng)前數(shù)據(jù)庫中刪除，其語法形式如下：前數(shù)據(jù)庫中刪除，其語法形式如下：drop procedure procedure ,n當(dāng)然，利用企業(yè)管理器也可以很方便地刪除存當(dāng)然，利用企業(yè)管理器也可以很方便地刪除存儲(chǔ)過程。儲(chǔ)過程。 例如：例如： drop procedure getempA. 使用帶有復(fù)雜使用帶有復(fù)雜 SELECT 語句的存儲(chǔ)過程：查語句的存儲(chǔ)過程：查詢學(xué)生的考試成績?cè)儗W(xué)生的考試成績CREATE PROCEDURE student_grade1AS SELECT Sname, Cname,Grade FROM Student s INNER JOIN sc ON s.sno = sc.s

48、no INNER JOIN course c ON o = o執(zhí)行：執(zhí)行：EXEC student_grade1B. 使用帶有參數(shù)的存儲(chǔ)過程：查詢某個(gè)學(xué)生某門課程使用帶有參數(shù)的存儲(chǔ)過程：查詢某個(gè)學(xué)生某門課程的考試成績的考試成績CREATE PROCEDURE student_grade2 student_name char(10), course_name char(20) AS SELECT Sname, Cname,Grade FROM Student s INNER JOIN sc ON s.sno = sc.sno INNER JOIN course c ON o = o WHERE

49、sname = student_name AND cname = course_name 1. 按參數(shù)位置傳遞值按參數(shù)位置傳遞值EXEC student_grade2 劉晨劉晨, 數(shù)據(jù)庫數(shù)據(jù)庫2. 按參數(shù)名傳遞值按參數(shù)名傳遞值EXEC Student_grade2 student_name = 劉晨劉晨, course_name=數(shù)據(jù)庫數(shù)據(jù)庫C. 使用使用 OUTPUT 參數(shù)參數(shù) Create Procedure Proc1var1 int, var2 int, var3 int outputAs Set var3=var1*var2 執(zhí)行Declare m int, m2 int,m3 in

50、tSet m1=5Set m2=7Execute Proc1 m1,m2,m3 outputSelect m3 SQL Server提供了許多預(yù)定義的存儲(chǔ)過程，這提供了許多預(yù)定義的存儲(chǔ)過程，這些存儲(chǔ)過程存儲(chǔ)在些存儲(chǔ)過程存儲(chǔ)在master數(shù)據(jù)庫中（以數(shù)據(jù)庫中（以_sp為前綴），為前綴），提供了有效的查詢系統(tǒng)表的方法，以及許多系統(tǒng)管提供了有效的查詢系統(tǒng)表的方法，以及許多系統(tǒng)管理功能理功能 如：如：sp_help 、sp_helpdatabases作用：報(bào)告有關(guān)指定數(shù)據(jù)庫或所有數(shù)據(jù)庫的信息。作用：報(bào)告有關(guān)指定數(shù)據(jù)庫或所有數(shù)據(jù)庫的信息。語法：語法：sp_helpdb dbname= 數(shù)據(jù)庫名數(shù)據(jù)庫名

51、如果沒有指定數(shù)據(jù)庫名，則如果沒有指定數(shù)據(jù)庫名，則 sp_helpdb 報(bào)告報(bào)告 master.dbo.sysdatabases 中的所有數(shù)據(jù)庫。中的所有數(shù)據(jù)庫。 例例1. 返回返回pubs數(shù)據(jù)庫的信息數(shù)據(jù)庫的信息exec sp_helpdb pubs例例2. 返回有關(guān)所有數(shù)據(jù)庫的信息返回有關(guān)所有數(shù)據(jù)庫的信息exec sp_helpdb 作用：作用：列出駐留在列出駐留在 SQL Server 實(shí)例中的實(shí)例中的數(shù)據(jù)庫。數(shù)據(jù)庫。語法：語法：sp_databases返回系統(tǒng)中的全部數(shù)據(jù)庫名、數(shù)據(jù)庫大小返回系統(tǒng)中的全部數(shù)據(jù)庫名、數(shù)據(jù)庫大?。ㄒ裕ㄒ?KBKB為單位）以及標(biāo)記值（為單位）以及標(biāo)記值（SQL

52、ServerSQL Server的的此項(xiàng)值為此項(xiàng)值為NULLNULL） 作用：作用：返回當(dāng)前環(huán)境下可查詢對(duì)象的列表（任何可出現(xiàn)返回當(dāng)前環(huán)境下可查詢對(duì)象的列表（任何可出現(xiàn)在在 FROM 子句中的對(duì)象）。子句中的對(duì)象）。語法：語法：sp_tables table_type= “ 類型類型 ” 其中：其中：類型包括類型包括TABLE（用戶表）、（用戶表）、SYSTEM TABLE（系統(tǒng)表）和（系統(tǒng)表）和 VIEW（視圖）（視圖） 例例1：返回在當(dāng)前環(huán)境中可查詢的對(duì)象的列表：返回在當(dāng)前環(huán)境中可查詢的對(duì)象的列表EXEC sp_tables例例2：返回當(dāng)前數(shù)據(jù)庫中的所有用戶表：返回當(dāng)前數(shù)據(jù)庫中的所有用戶表

53、EXEC sp_tables table_type= TABLE 作用：作用：返回某個(gè)表的所有約束返回某個(gè)表的所有約束 語法：語法：sp_helpconstraint 表名表名 示例：示例：顯示顯示 authors 表的所有約束表的所有約束 EXEC sp_helpconstraint authors 作用：顯示當(dāng)前數(shù)據(jù)庫或數(shù)據(jù)庫中的某個(gè)表所使作用：顯示當(dāng)前數(shù)據(jù)庫或數(shù)據(jù)庫中的某個(gè)表所使用的磁盤空間以及保留的磁盤空間。用的磁盤空間以及保留的磁盤空間。語法：語法：sp_spaceused 表名表名 例例1：察看當(dāng)前數(shù)據(jù)庫的空間使用情況：察看當(dāng)前數(shù)據(jù)庫的空間使用情況 EXEC sp_spaceuse

54、d 例例2：查看：查看pubs數(shù)據(jù)庫的數(shù)據(jù)庫的titles表的空間信息表的空間信息 EXEC sp_spaceused titles 存儲(chǔ)過程（存儲(chǔ)過程（Stored Procedure）是一組為了完成特定功是一組為了完成特定功能的能的SQL 語句集，經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫。用戶通過指定語句集，經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫。用戶通過指定存儲(chǔ)過程的名字并給出參數(shù)（如果該存儲(chǔ)過程帶有參數(shù)）存儲(chǔ)過程的名字并給出參數(shù)（如果該存儲(chǔ)過程帶有參數(shù)）來執(zhí)行它。來執(zhí)行它。 在在SQL Server 的系列版本中存儲(chǔ)過程分為兩類：的系列版本中存儲(chǔ)過程分為兩類：系統(tǒng)系統(tǒng)提供的存儲(chǔ)過程提供的存儲(chǔ)過程和用戶自定義存儲(chǔ)過程。和用戶

55、自定義存儲(chǔ)過程。系統(tǒng)過程系統(tǒng)過程主要存儲(chǔ)在主要存儲(chǔ)在master 數(shù)據(jù)庫中并以數(shù)據(jù)庫中并以sp_為前綴，為前綴，并且系統(tǒng)存儲(chǔ)過程主要是從系統(tǒng)表中獲取信息，從而為系并且系統(tǒng)存儲(chǔ)過程主要是從系統(tǒng)表中獲取信息，從而為系統(tǒng)管理員管理統(tǒng)管理員管理SQL Server 提供支持。提供支持。用戶自定義存儲(chǔ)過程用戶自定義存儲(chǔ)過程是由用戶創(chuàng)建并能完成某一特定是由用戶創(chuàng)建并能完成某一特定功能（如查詢用戶所需數(shù)據(jù)信息）的存儲(chǔ)過程。在本章中功能（如查詢用戶所需數(shù)據(jù)信息）的存儲(chǔ)過程。在本章中所涉及到的存儲(chǔ)過程主要是指用戶自定義存儲(chǔ)過程。所涉及到的存儲(chǔ)過程主要是指用戶自定義存儲(chǔ)過程。 當(dāng)利用當(dāng)利用MS SQL Serve

56、r 創(chuàng)建一個(gè)應(yīng)用程序時(shí)，創(chuàng)建一個(gè)應(yīng)用程序時(shí)，Transaction-SQL 是一種主要的編程語言。若運(yùn)用是一種主要的編程語言。若運(yùn)用Transaction-SQL 來進(jìn)行編程，有兩種方法來進(jìn)行編程，有兩種方法:其一是，在本地存儲(chǔ)其一是，在本地存儲(chǔ)Transaction- SQL 程序，并創(chuàng)建應(yīng)程序，并創(chuàng)建應(yīng)用程序向用程序向SQL Server 發(fā)送命令來對(duì)結(jié)果進(jìn)行處理。發(fā)送命令來對(duì)結(jié)果進(jìn)行處理。其二是，可以把部分用其二是，可以把部分用Transaction-SQL 編寫的程序作編寫的程序作為為存儲(chǔ)過程存儲(chǔ)過程存儲(chǔ)在存儲(chǔ)在SQL Server 中，并創(chuàng)建應(yīng)用程序來調(diào)用存中，并創(chuàng)建應(yīng)用程序來調(diào)用存

57、儲(chǔ)過程，對(duì)數(shù)據(jù)結(jié)果進(jìn)行處理存儲(chǔ)過程能夠通過接收參數(shù)向儲(chǔ)過程，對(duì)數(shù)據(jù)結(jié)果進(jìn)行處理存儲(chǔ)過程能夠通過接收參數(shù)向調(diào)用者返回結(jié)果集，結(jié)果集的格式由調(diào)用者確定；返回狀態(tài)調(diào)用者返回結(jié)果集，結(jié)果集的格式由調(diào)用者確定；返回狀態(tài)值給調(diào)用者，指明調(diào)用是成功或是失?。话ㄡ槍?duì)數(shù)據(jù)庫的值給調(diào)用者，指明調(diào)用是成功或是失敗；包括針對(duì)數(shù)據(jù)庫的操作語句，并且可以在一個(gè)存儲(chǔ)過程中調(diào)用另一存儲(chǔ)過程。操作語句，并且可以在一個(gè)存儲(chǔ)過程中調(diào)用另一存儲(chǔ)過程。 存儲(chǔ)過程在被創(chuàng)建以后可以在程序中被多次調(diào)存儲(chǔ)過程在被創(chuàng)建以后可以在程序中被多次調(diào)用，而不必重新編寫該存儲(chǔ)過程的用，而不必重新編寫該存儲(chǔ)過程的SQL 語句。而且語句。而且數(shù)據(jù)庫專業(yè)人員

58、可隨時(shí)對(duì)存儲(chǔ)過程進(jìn)行修改，但對(duì)數(shù)據(jù)庫專業(yè)人員可隨時(shí)對(duì)存儲(chǔ)過程進(jìn)行修改，但對(duì)應(yīng)用程序源代碼毫無影響（因?yàn)閼?yīng)用程序源代碼只應(yīng)用程序源代碼毫無影響（因?yàn)閼?yīng)用程序源代碼只包含存儲(chǔ)過程的調(diào)用語句），從而極大地提高了程包含存儲(chǔ)過程的調(diào)用語句），從而極大地提高了程序的可移植性。序的可移植性。 如果某一操作包含大量的如果某一操作包含大量的Transaction-SQL 代碼代碼或分別被多次執(zhí)行，那么存儲(chǔ)過程要比批處理的執(zhí)或分別被多次執(zhí)行，那么存儲(chǔ)過程要比批處理的執(zhí)行速度快很多。因?yàn)榇鎯?chǔ)過程是預(yù)編譯的，在首次行速度快很多。因?yàn)榇鎯?chǔ)過程是預(yù)編譯的，在首次運(yùn)行一個(gè)存儲(chǔ)過程時(shí)，查詢優(yōu)化器對(duì)其進(jìn)行分析、運(yùn)行一個(gè)存儲(chǔ)過程

59、時(shí)，查詢優(yōu)化器對(duì)其進(jìn)行分析、優(yōu)化，并給出最終被存在系統(tǒng)表中的執(zhí)行計(jì)劃。而優(yōu)化，并給出最終被存在系統(tǒng)表中的執(zhí)行計(jì)劃。而批處理的批處理的Transaction- SQL 語句在每次運(yùn)行時(shí)都要進(jìn)語句在每次運(yùn)行時(shí)都要進(jìn)行編譯和優(yōu)化，因此速度相對(duì)要慢一些。行編譯和優(yōu)化，因此速度相對(duì)要慢一些。 對(duì)于同一個(gè)針對(duì)數(shù)據(jù)數(shù)據(jù)庫對(duì)象的操作（如查對(duì)于同一個(gè)針對(duì)數(shù)據(jù)數(shù)據(jù)庫對(duì)象的操作（如查詢、修改），如果這一操作所涉及到的詢、修改），如果這一操作所涉及到的 Transaction-SQL 語句被組織成一存儲(chǔ)過程，那么當(dāng)在客戶計(jì)算語句被組織成一存儲(chǔ)過程，那么當(dāng)在客戶計(jì)算機(jī)上調(diào)用該存儲(chǔ)過程時(shí)，網(wǎng)絡(luò)中傳送的只是該調(diào)用機(jī)上調(diào)用該

60、存儲(chǔ)過程時(shí)，網(wǎng)絡(luò)中傳送的只是該調(diào)用語句，否則將是多條語句，否則將是多條SQL 語句，從而大大增加了網(wǎng)語句，從而大大增加了網(wǎng)絡(luò)流量，降低網(wǎng)絡(luò)負(fù)載。絡(luò)流量，降低網(wǎng)絡(luò)負(fù)載。 系統(tǒng)管理員通過對(duì)執(zhí)行某一存儲(chǔ)過程的權(quán)限進(jìn)系統(tǒng)管理員通過對(duì)執(zhí)行某一存儲(chǔ)過程的權(quán)限進(jìn)行限制，從而能夠?qū)崿F(xiàn)對(duì)相應(yīng)的數(shù)據(jù)訪問權(quán)限的限行限制，從而能夠?qū)崿F(xiàn)對(duì)相應(yīng)的數(shù)據(jù)訪問權(quán)限的限制，避免非授權(quán)用戶對(duì)數(shù)據(jù)的訪問，保證數(shù)據(jù)的安制，避免非授權(quán)用戶對(duì)數(shù)據(jù)的訪問，保證數(shù)據(jù)的安全。全。存儲(chǔ)過程可以在系統(tǒng)啟動(dòng)時(shí)自動(dòng)執(zhí)行存儲(chǔ)過程可以在系統(tǒng)啟動(dòng)時(shí)自動(dòng)執(zhí)行,而不必在而不必在系統(tǒng)啟動(dòng)后再進(jìn)行手工操作系統(tǒng)啟動(dòng)后再進(jìn)行手工操作,大大方便了用戶的使用大大方便了用戶的