存儲過程與觸發(fā)器課件

第10章存儲過程與觸發(fā)器第10章存儲過程與觸發(fā)器1本章內(nèi)容10.1存儲過程概述10.2存儲過程的創(chuàng)立與使用10.3觸發(fā)器概述10.4觸發(fā)器的創(chuàng)立與使用10.5事務(wù)處理10.6SQLServer的鎖機(jī)制本章內(nèi)容10.1存儲過程概述210.1存儲過程概述存儲過程是SQLServer效勞器上一組預(yù)編譯的Transact-SQL語句，用于完成某項任務(wù)，它可以承受參數(shù)、返回狀態(tài)值和參數(shù)值，并且可以嵌套調(diào)用。10.1存儲過程概述存儲過程是SQLServer效勞310.1存儲過程概述SQLServer存儲過程的類型包括：系統(tǒng)存儲過程用戶定義存儲過程臨時存儲過程擴(kuò)展存儲過程1.存儲過程的類型10.1存儲過程概述SQLServer存儲過程的類型包括410.1存儲過程概述(1)系統(tǒng)存儲過程

是指由系統(tǒng)提供的存儲過程，主要存儲在master數(shù)據(jù)庫中并以sp_為前綴，它從系統(tǒng)表中獲取信息，從而為系統(tǒng)管理員管理SQLServer提供支持。通過系統(tǒng)存儲過程，SQLServer中的許多管理性或信息性的活動(例如使用sp_depends、sp_helptexts可以了解數(shù)據(jù)數(shù)據(jù)庫對象、數(shù)據(jù)庫信息)都可以順利有效地完成。盡管系統(tǒng)存儲過程被放在master數(shù)據(jù)庫中，仍可以在其他數(shù)據(jù)庫中對其進(jìn)展調(diào)用(調(diào)用時，不必在存儲過程名前加上數(shù)據(jù)庫名)。當(dāng)創(chuàng)立一個新數(shù)據(jù)庫時，一些系統(tǒng)存儲過程會在新數(shù)據(jù)庫中被自動創(chuàng)立。10.1存儲過程概述(1)系統(tǒng)存儲過程是指由系統(tǒng)提供的存510.1存儲過程概述(2)用戶定義存儲過程是由用戶創(chuàng)立并能完成某一特定功能(例如查詢用戶所需數(shù)據(jù)信息)的存儲過程。它處于用戶創(chuàng)立的數(shù)據(jù)庫中，存儲過程名前沒有前綴sp_。10.1存儲過程概述(2)用戶定義存儲過程是由用戶創(chuàng)立并能610.1存儲過程概述(3)臨時存儲過程臨時存儲過程與臨時表類似，分為局部臨時存儲過程和全局臨時存儲過程，且可以分別向該過程名稱前面添加“#〞或“##〞前綴表示?！?〞表示本地臨時存儲過程，“##〞表示全局臨時存儲過程。使用臨時存儲過程必須創(chuàng)立本地連接，當(dāng)SQLServer關(guān)閉后，這些臨時存儲過程將自動被刪除。由于SQLServer支持重新使用執(zhí)行方案，所以連接到SQLServer2000的應(yīng)用程序應(yīng)使用sp_executesql系統(tǒng)存儲過程，而不使用臨時存儲過程。10.1存儲過程概述(3)臨時存儲過程臨時存儲過程與臨時表710.1存儲過程概述(4)擴(kuò)展存儲過程擴(kuò)展存儲過程是SQLServer可以動態(tài)裝載和執(zhí)行的動態(tài)鏈接庫(DLL)。當(dāng)擴(kuò)展存儲過程加載到SQLServer中，它的使用方法與系統(tǒng)存儲過程一樣。擴(kuò)展存儲過程只能添加到master數(shù)據(jù)庫中，其前綴是xp_。10.1存儲過程概述(4)擴(kuò)展存儲過程擴(kuò)展存儲過程是SQL810.1存儲過程概述2.存儲過程的功能特點SQLServer的存儲過程可實現(xiàn)以下功能：(1)接收輸入?yún)?shù)并以輸出參數(shù)的形式為調(diào)用過程或批處理返回多個值。(2)包含執(zhí)行數(shù)據(jù)庫操作的編程語句，包括調(diào)用其他過程。(3)為調(diào)用過程或批處理返回一個狀態(tài)值，以表示成功或失敗(及失敗原因)。10.1存儲過程概述2.存儲過程的功能特點SQLSer910.1存儲過程概述存儲過程具有以下優(yōu)點(1)模塊化編程。(2)快速執(zhí)行。(3)減少網(wǎng)絡(luò)通信量。(4)提供平安機(jī)制。(5)保證操作一致性。10.1存儲過程概述存儲過程具有以下優(yōu)點(1)模塊化編程。1010.2存儲過程的創(chuàng)建與使用10.2存儲過程的創(chuàng)建與使用1.使用企業(yè)管理器創(chuàng)立存儲過程存儲過程創(chuàng)建10.2存儲過程的創(chuàng)建與使用10.2存儲過程的創(chuàng)建與使112.使用向?qū)?chuàng)建存儲過程2.使用向?qū)?chuàng)建存儲過程123.使用CREATEPROCEDURE語句創(chuàng)立存儲過程使用CREATEPROCEDURE語句創(chuàng)立存儲過程應(yīng)該考慮以下幾個方面：(1)在一個批處理中，CREATEPROCEDURE語句不能與其他SQL語句合并在一起。(2)數(shù)據(jù)庫所有者具有默認(rèn)的創(chuàng)立存儲過程的權(quán)限，它可把該權(quán)限傳遞給其他的用戶。(3)存儲過程作為數(shù)據(jù)庫對象其命名必須符合標(biāo)識符的命名規(guī)那么。(4)只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)立屬于當(dāng)前數(shù)據(jù)庫的存儲過程。3.使用CREATEPROCEDURE語句創(chuàng)立存儲過程使13創(chuàng)立存儲過程語句的語法格式如下：CREATE

PROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[,...n]創(chuàng)立存儲過程語句的語法格式如下：CREATEPROC[ED14例10-1創(chuàng)立存儲過程，從表goods和表goods_classification的聯(lián)接中返回商品名、商品類別、單價。CREATEPROCEDUREgoods_infoASSELECTgoods_name,classification_name,unit_price FROMgoodsgINNERJOINgoods_classificationgc ONg.classification_id=gc.classification_id存儲過程創(chuàng)立后，存儲過程的名稱存放在sysobject表中，文本存放在syscomments表中。例10-1創(chuàng)立存儲過程，從表goods和表goods_c15執(zhí)行存儲過程執(zhí)行存儲過程的語法格式：[[EXEC[UTE]]{[@return_status=]

procedure_name[;number]|@procedure_name_var}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]][,...n][WITHRECOMPILE]10.2存儲過程的創(chuàng)建與使用執(zhí)行存儲過程執(zhí)行存儲過程的語法格式：10.2存儲過程的創(chuàng)建16例如，執(zhí)行例10-1的存儲過程goods_info在SQL查詢分析器中輸入命令：EXECgoods_info運行的結(jié)果：例如，執(zhí)行例10-1的存儲過程goods_info在SQL查1710.2存儲過程的創(chuàng)建與使用修改存儲過程1.使用企業(yè)管理器修改存儲過程2.使用ALTERPROCEDURE語句修改存儲過程ALTERPROCEDURE的語法規(guī)那么是：ALTERPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...n]10.2存儲過程的創(chuàng)建與使用修改存儲過程1.使用企業(yè)管理18查看存儲過程的文本信息SELECTo.id,c.textFROMsysobjectsoINNERJOINsyscommentscONo.id=c.idWHEREo.type='P'AND='procedure_name'GO查看存儲過程的文本信息SELECTo.id,c.text1910.2存儲過程的創(chuàng)建與使用刪除存儲過程

1.使用企業(yè)管理器刪除存儲過程2.使用DROPPROCEDURE刪除存儲過程DROPPROCEDURE的語法如下：DROPPROCEDURE

{procedure_name}[,...n]例如刪除例10-1創(chuàng)建的存儲過程goods_info：DROPPROCEDURE

goods_infoGO10.2存儲過程的創(chuàng)建與使用刪除存儲過程1.使用企業(yè)管理2010.2存儲過程的創(chuàng)建與使用存儲過程參數(shù)與狀態(tài)值存儲過程和調(diào)用者之間通過參數(shù)交換數(shù)據(jù)，可以按輸入的參數(shù)執(zhí)行，也可由參數(shù)輸出執(zhí)行結(jié)果。調(diào)用者通過存儲過程返回的狀態(tài)值對存儲過程進(jìn)展管理。1.參數(shù)存儲過程的參數(shù)在創(chuàng)立過程時聲明。SQLServer支持兩類參數(shù)：輸入?yún)?shù)和輸出參數(shù)。(1)輸入?yún)?shù)輸入?yún)?shù)允許調(diào)用程序為存儲過程傳送數(shù)據(jù)值。要定義存儲過程的輸入?yún)?shù)，必須在CREATEPROCEDURE語句中聲明一個或多個變量及類型。10.2存儲過程的創(chuàng)建與使用存儲過程參數(shù)與狀態(tài)值存儲過程和21例10-3創(chuàng)立帶參數(shù)的存儲過程，從表employee、sell_order、goods、goods_classification的連接中返回輸入的員工名、該員工銷售的商品名、商品類別、銷售量等信息。CREATEPROC

sell_info

@employee_namevarchar(20)ASSELECTemployee_name,goods_name,classification_name,order_numFROMemployeee

INNERJOINsell_orders

ONe.employee_id=s.employee_idJOINgoodsg

ONg.goods_id=s.goods_idJOINgoods_classificationgcONgc.classification_id=g.classification_idWHEREemployee_nameLIKE@employee_name例10-3創(chuàng)立帶參數(shù)的存儲過程，從表employee、s22執(zhí)行存儲過程sell_info以EXECsell_info'東方牧'參數(shù)值可以包含通配符“%”，例如，查找所有姓“錢”的員工的銷售情況可以使用以下命令： EXECsell_info'錢%'執(zhí)行存儲過程sell_info以參數(shù)值可以包含通配符“%”，23存儲過程執(zhí)行時，參數(shù)可以由位置標(biāo)識，也可以由名字標(biāo)識。例如，定義一個具有3個參數(shù)的存儲過程：CREATEPROCmyproc@val1int,@val2int,@val3intAS...參數(shù)以位置傳遞：EXECmyproc10,20,15參數(shù)以名字傳遞，每個值由對應(yīng)的參數(shù)名引導(dǎo)：EXECmyproc@val2=20,@val1=10,@val3=15存儲過程中參數(shù)的標(biāo)識存儲過程執(zhí)行時，參數(shù)可以由位置標(biāo)識，也可以由名字標(biāo)識。例如，24(2)輸出參數(shù)輸出參數(shù)允許存儲過程將數(shù)據(jù)值或游標(biāo)變量傳回調(diào)用程序。OUTPUT關(guān)鍵字用以指出能返回到調(diào)用它的批處理或過程中的參數(shù)。為了使用輸出參數(shù)，在CREATEPROCEDURE和EXECUTE語句中都必須使用OUTPUT關(guān)鍵字。(2)輸出參數(shù)輸出參數(shù)允許存儲過程將數(shù)據(jù)值或游標(biāo)變量傳回調(diào)用25例10-4創(chuàng)立存儲過程price_goods，通過輸入?yún)?shù)在goods表中查找商品，以輸出參數(shù)獲取商品單價。CREATEPROCprice_goods@goods_namevarchar(80)=NULL,

@price_goodsrealOUTPUTASSELECT@price_goods=unit_priceFROMgoodsWHEREgoods_name=@goods_name例10-4創(chuàng)立存儲過程price_goods，通過輸入?yún)?6執(zhí)行price_goods存儲過程的代碼如下：DECLARE@pricerealEXEC

price_goods'CanonLBP2900',@priceOUTPUTSELECT@priceEXECUTE語句還需要關(guān)鍵字OUTPUT以允許參數(shù)值返回給變量。執(zhí)行price_goods存儲過程的代碼如下：EXECUTE27(1)用RETURN語句定義返回值存儲過程可以返回整型狀態(tài)值，表示過程是否成功執(zhí)行，或者過程失敗的原因。如果存儲過程沒有顯式設(shè)置返回代碼的值，那么SQLServer返回代碼為0，表示成功執(zhí)行；假設(shè)返回-1~-99之間的整數(shù)，表示沒有成功執(zhí)行。也可以使用RETURN語句，用大于0或小于-99的整數(shù)來定義自己的返回狀態(tài)值，以表示不同的執(zhí)行結(jié)果。2.返回存儲過程的狀態(tài)(2)捕獲返回狀態(tài)值使用以下語句接收返回的狀態(tài)值EXECUTE@status_var=procedure_name(1)用RETURN語句定義返回值2.返回存儲過程的狀態(tài)(28例10-5創(chuàng)立存儲過程，輸入商品類別，返回各種商品名稱。在存儲過程中，用值15表示用戶沒有提供參數(shù)；值-l01表示沒有輸入商品類別；值0表示過程運行沒有出錯。CREATEPROCcl_goods@cl_namevarchar(40)=NULLASIF@cl_name=NULLRETURN15IFNOTEXISTS(SELECT*FROMgoods_classificationWHEREclassification_name=@cl_name)RETURN-101SELECTg.goods_nameFROMgoods_classificationgc,goodsgWHEREgc.classification_id=g.classification_idANDgc.classification_name=@cl_nameRETURN0例10-5創(chuàng)立存儲過程，輸入商品類別，返回各種商品名稱。29DECLARE@return_statusintEXEC@return_status=cl_goods'筆記本計算機(jī)'IF@return_status=15SELECT'語法錯誤'ELSE

IF@return_status=-101SELECT'沒有找到該商品類別'例10-5的存儲過程cl_goods執(zhí)行時使用以下語句：DECLARE@return_statusint例10-30批處理是包含一個或多個Transact-SQL語句的組，從應(yīng)用程序一次性地發(fā)送到SQLServer執(zhí)行。SQLServer將批處理語句編譯成一個可執(zhí)行單元，此單元稱為執(zhí)行方案，每次執(zhí)行其中的一條語句。函數(shù)是由一個或多個Transact-SQL語句組成的子程序，可用于封裝代碼以便重新使用。存儲過程是一組SQL語句的集合，類似于程序的模塊。它與函數(shù)不同，因為它不返回取代其名稱的值，也不能直接用在表達(dá)式中。批處理、函數(shù)、存儲過程的區(qū)別存儲過程、批處理文件和函數(shù)都是由一個或多個Transact-SQL語句組成的，通常可以完成一系列的操作，但是它們的適用范圍和產(chǎn)生的效果是不同的，使用時應(yīng)當(dāng)認(rèn)真分析，區(qū)別對待。批處理是包含一個或多個Transact-SQL語句的組，從應(yīng)31觸發(fā)器是一種特殊類型的存儲過程，用于實現(xiàn)比較復(fù)雜的數(shù)據(jù)約束。觸發(fā)器主要是通過事件進(jìn)展觸發(fā)而被執(zhí)行的，而存儲過程可以通過過程名字直接調(diào)用。當(dāng)對某一表進(jìn)展UPDATE、INSERT、DELETE操作時，SQLServer就會自動執(zhí)行觸發(fā)器所定義的SQL語句，從而確保對數(shù)據(jù)的處理必須符合由這些SQL語句所定義的規(guī)那么。觸發(fā)器的主要作用就是能夠?qū)崿F(xiàn)由主鍵和外鍵所不能保證的參照完整性和數(shù)據(jù)的一致性。10.3觸發(fā)器概述觸發(fā)器是一種特殊類型的存儲過程，用于實現(xiàn)比較復(fù)雜的數(shù)據(jù)約束。32觸發(fā)器的其它功能強(qiáng)化約束：觸發(fā)器能夠?qū)崿F(xiàn)比CHEAK語句更為復(fù)雜的約束。跟蹤變化：觸發(fā)器可以偵測數(shù)據(jù)庫內(nèi)的操作，從而不允許數(shù)據(jù)庫中不經(jīng)許可的指定更新和變化。級聯(lián)運行：觸發(fā)器可以偵測數(shù)據(jù)庫內(nèi)的操作，并自動地級聯(lián)影響整個數(shù)據(jù)庫地各項內(nèi)容。存儲過程的調(diào)用：為了響應(yīng)數(shù)據(jù)庫更新，觸發(fā)器可以調(diào)用一個或多個存儲過程，甚至可以通過外部過程的調(diào)用而在DBMS本身之外進(jìn)展操作。觸發(fā)器的其它功能33名稱：觸發(fā)器有一個符合標(biāo)志符命名規(guī)那么的名稱。定義的目標(biāo)：觸發(fā)器必須定義在表或視圖上。觸發(fā)條件：是UPDATE、INSERT，還是DELETE語句。觸發(fā)邏輯：觸發(fā)之后如何處理。觸發(fā)器的四要素名稱：觸發(fā)器有一個符合標(biāo)志符命名規(guī)那么的名稱。觸發(fā)器的四要素341.使用企業(yè)管理器創(chuàng)立觸發(fā)器創(chuàng)立觸發(fā)器10.4觸發(fā)器的創(chuàng)建與使用1.使用企業(yè)管理器創(chuàng)立觸發(fā)器創(chuàng)立觸發(fā)器10.4觸發(fā)器的35存儲過程與觸發(fā)器課件362.使用CREATETRIGGER語句創(chuàng)立觸發(fā)器CREATETRIGGER語句的語法格式如下：CREATETRIGGERtrigger_nameON{table_name|view}[WITHENCRYPTION]{FOR|AFTER|INSTEADOF} --觸發(fā)器的類型{[

INSERT][,][

UPDATE][,][DELETE]}ASsql_statement[...n]2.使用CREATETRIGGER語句創(chuàng)立觸發(fā)器CREA37觸發(fā)器的類型AFTER觸發(fā)器該觸發(fā)器會在觸發(fā)INSERT、UPDATE或DELETE動作之后執(zhí)行。該觸發(fā)器只有在已插入一行或是多行和所有約束已被處理且通過后才觸發(fā)。該觸發(fā)器只能在表上定義，可以為表的同一操作定義多個觸發(fā)器。INSTEADOF觸發(fā)器該觸發(fā)器代替觸發(fā)動作進(jìn)展激活，即表示不執(zhí)行其所定義的操作(INSERT、UPDATE、DELETE)而僅執(zhí)行觸發(fā)器本身。兩者的區(qū)別AFTER觸發(fā)器是告訴SQL語句執(zhí)行了INSERT、UPDATE或DELETE操作后干什么(操作已經(jīng)做了，數(shù)據(jù)已經(jīng)變動了)。INSTEADOF觸發(fā)器是告訴當(dāng)執(zhí)行INSERT、UPDATE或者DELETE操作時用什么別的操作來代替(操作不做，而是做另外一種觸發(fā)器定義的操作)。觸發(fā)器的類型AFTER觸發(fā)器兩者的區(qū)別38例10-6在employee表上創(chuàng)立一個DELETE類型的觸發(fā)器，該觸發(fā)器的名稱為tr_employee.(1)創(chuàng)立觸發(fā)器tr_employee CREATETRIGGERtr_employeeONemployee FORDELETE AS DECLARE@msgvarchar(50) SELECT@msg=STR(@@ROWCOUNT)+'個員工被刪除' SELECT@msg RETURN例10-6在employee表上創(chuàng)立一個DELETE類型39(2)執(zhí)行觸發(fā)器tr_employee觸發(fā)器不能通過名字來執(zhí)行，而是在相應(yīng)的SQL語句被執(zhí)行時自動觸發(fā)的。例如執(zhí)行以下DELETE語句：DELETEFROMemployeeWHEREemployee_name='劉葉'(2)執(zhí)行觸發(fā)器tr_employee40在觸發(fā)器的執(zhí)行過程中，SQLServer建立和管理兩個臨時的虛擬表：Deleted表和Inserted表。這兩個表包含了在激發(fā)觸發(fā)器的操作中插入或刪除的所有記錄?？梢杂眠@一特性來測試某些數(shù)據(jù)修改的效果，以及設(shè)置觸發(fā)器操作的條件。這兩個特殊表可供用戶瀏覽，但是用戶不能直接改變表中的數(shù)據(jù)。在執(zhí)行INSERT或UPDATE語句之后所有被添加或被更新的記錄都會存儲在Inserted表中。在執(zhí)行DELETE或UPDATE語句時，從觸發(fā)程序表中被刪除的行會發(fā)送到Deleted表。對于更新操作，SQLServer先將要進(jìn)展修改的記錄存儲到Deleted表中，然后再將修改后的數(shù)據(jù)復(fù)制到Inserted表以及觸發(fā)程序表。3.Deleted表和Inserted表在觸發(fā)器的執(zhí)行過程中，SQLServer建立和管理兩個臨時41Deleted表和Inserted表在執(zhí)行觸發(fā)程序時的情況Transact-SQL語句Inserted表Deleted表INSERT所要添加的行空UPDATE新的行舊的行DELETE空刪除的行Deleted表和Inserted表在執(zhí)行觸發(fā)程序時的情況T42例10-7為表customer創(chuàng)立一個名為test_tr的觸發(fā)器，當(dāng)執(zhí)行添加、更新或刪除時，激活該觸發(fā)器。創(chuàng)立test_tr觸發(fā)器：CREATETRIGGERtest_trONcustomerFORINSERT,UPDATE,DELETEASSELECT*FROMinsertedSELECT*FROMdeletedcustomer表執(zhí)行以下插入操作：INSERTINTOcustomer(customer_id,customer_name,telephone)VALUES('12346','張三','1234567')例10-7為表customer創(chuàng)立一個名為test_tr4310.4觸發(fā)器的創(chuàng)建與使用刪除觸發(fā)器1.使用企業(yè)管理器刪除觸發(fā)器10.4觸發(fā)器的創(chuàng)建與使用刪除觸發(fā)器1.使用企業(yè)管理器刪442.使用DROPTRIGGER刪除指定觸發(fā)器刪除觸發(fā)器語句的語法格式如下：DROPTRIGGERtrigger_name[,...n]例如，刪除例10-6的觸發(fā)器tr_employee：DROPTRIGGERtr_employee2.使用DROPTRIGGER刪除指定觸發(fā)器刪除觸發(fā)器語45修改觸發(fā)器通過企業(yè)管理器、系統(tǒng)存儲過程或Transact_SQL語句，可以修改觸發(fā)器的名字和正文。10.4觸發(fā)器的創(chuàng)建與使用1.使用sp_rename系統(tǒng)存儲過程修改觸發(fā)器的名字：語法格式為：sp_rename

oldname,newname2.使用企業(yè)管理器修改觸發(fā)器的正文3.使用ALTERTRIGGER語句修改觸發(fā)器修改觸發(fā)器通過企業(yè)管理器、系統(tǒng)存儲過程或Transact_S46ALTERTRIGGER修改觸發(fā)器的語法如下：ALTERTRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}ASsql_statement[...n]ALTERTRIGGER修改觸發(fā)器的語法如下：47ALTERTRIGGERtr_employeeONemployeeFORINSERTASDECLARE@msgvarchar(50)SELECT@msg=STR(@@ROWCOUNT)+'個員工數(shù)據(jù)被插入'SELECT@msgRETURN例如，將例10-6的觸發(fā)器tr_employee修改為INSERT操作后進(jìn)展。對employee表執(zhí)行以下插入語句： INSERTemployee(employee_id,employee_name)VALUES('E016','王五')激活I(lǐng)NSERT觸發(fā)器tr_employee，顯示信息如下： 1個員工數(shù)據(jù)被插入ALTERTRIGGERtr_employeeONe48事務(wù)(Transaction)是SQLServer中的一個邏輯工作單元，該單元將被作為一個整體進(jìn)展處理。事務(wù)可以是一組SQL語句、一條SQL語句或整個程序，在通常情況下，一個應(yīng)用程序里包含多個事務(wù)。事務(wù)保證連續(xù)多個操作必須全部執(zhí)行成功，否那么必須立即回復(fù)到未執(zhí)行任何操作的狀態(tài)，即執(zhí)行事務(wù)的結(jié)果要么全部將數(shù)據(jù)所要執(zhí)行的操作完成，要么全部數(shù)據(jù)都不修改。10.5事務(wù)處理事務(wù)(Transaction)是SQLServer中的一個49例如，企業(yè)取消了倉儲部，需要將“倉儲部〞從department表中刪除，而employee表中的部門編號與倉儲部相對應(yīng)的員工也應(yīng)刪除。因此，兩個表都需要修改，這種修改只能通過兩條語句DELETE進(jìn)展。假設(shè)倉儲部編號為D004，第一條DELETE語句修改department表為： DELETEFROMdepartmentWHEREdepartment_id='D004'第二條DELETE語句修改employee表為： DELETEFROMemployeeWHEREdepartment_id='D004'因此，必須保證這兩條DELETE語句同時執(zhí)行，或都不執(zhí)行。這時可以使用數(shù)據(jù)庫中的事務(wù)技術(shù)來實現(xiàn)。1．事務(wù)的由來在SQLServer中，使用DELETE或UPDATE語句對數(shù)據(jù)庫進(jìn)行更新時一次只能操作一個表，這會帶來數(shù)據(jù)庫的數(shù)據(jù)不一致的問題。例如，企業(yè)取消了倉儲部，需要將“倉儲部〞從departmen502．事務(wù)屬性事務(wù)是網(wǎng)絡(luò)數(shù)據(jù)庫中邏輯操作的根本單位，事務(wù)的ACID屬性必不可少。由于事務(wù)作為一個邏輯工作單元，當(dāng)事務(wù)執(zhí)行遇到錯誤時，將取消事務(wù)所做的修改。一個邏輯單元必須具有4個屬性：原子性(Atomicity)一致性(Consistency)隔離性(Isolation)持久性(Durability)這些屬性稱為ACID。2．事務(wù)屬性事務(wù)是網(wǎng)絡(luò)數(shù)據(jù)庫中邏輯操作的根本單位，事務(wù)的AC51事務(wù)的ACID屬性原子性(Atomicity)事務(wù)必須是工作的最小單位，即原子單元，對于其數(shù)據(jù)的修改，要么全都執(zhí)行，要么全都不執(zhí)行。一致性(Consistency)事務(wù)在完成后，必須使所有的數(shù)據(jù)都保持一致性狀態(tài)。在相關(guān)數(shù)據(jù)庫中，事務(wù)必須遵守數(shù)據(jù)庫的約束和規(guī)那么，以保持所有數(shù)據(jù)的完整性。事務(wù)完畢時，所有的內(nèi)部數(shù)據(jù)構(gòu)造都必須是正確的。隔離性(Isolation)一個事務(wù)所作的修改必須與任何其他并發(fā)事務(wù)所作的修改隔離。事務(wù)查看數(shù)據(jù)時數(shù)據(jù)所處的狀態(tài)，要么是另一并發(fā)事務(wù)修改它之前的狀態(tài)，要么是另一事務(wù)修改它之后的狀態(tài)，事務(wù)不會查看中間狀態(tài)的數(shù)據(jù)。這稱為可串行性，因為它能夠重新裝載起始數(shù)據(jù)，并且重播一系列事務(wù)，以使數(shù)據(jù)完畢時的狀態(tài)與原始事務(wù)執(zhí)行的狀態(tài)一樣。持久性(Durability)事務(wù)完成后，它對于系統(tǒng)的影響是永久的。該修改即使出現(xiàn)系統(tǒng)故障也將一直保持。事務(wù)的ACID屬性原子性(Atomicity)523．事務(wù)模式應(yīng)用程序主要通過指定事務(wù)啟動和完畢的時間來控制事務(wù)。這可以使用Transact-SQL語句來控制事務(wù)的啟動和完畢。系統(tǒng)還必須能夠正確處理那些在事務(wù)完成之前便中止事務(wù)的錯誤。事務(wù)是在連接層進(jìn)展管理的。當(dāng)事務(wù)在一個連接上啟動時，在該連接上執(zhí)行的所有Transact-SQL語句在該事務(wù)完畢之前都是該事務(wù)的一局部。引入事務(wù)后，所有對數(shù)據(jù)庫的操作就是以事務(wù)為邏輯單位進(jìn)展。一個事務(wù)最終只有兩種狀態(tài)：提交狀態(tài)〔事務(wù)被正常執(zhí)行〕和未提交狀態(tài)〔事務(wù)必須被回滾〕。所以事務(wù)概念的引入解決了不可預(yù)見的用戶操作的發(fā)生。3．事務(wù)模式應(yīng)用程序主要通過指定事務(wù)啟動和完畢的時間來控制事53SQLServer以3種事務(wù)模式管理事務(wù)(1)自動提交事務(wù)模式：每條單獨的語句都是一個事務(wù)。在此模式下，每條Transact-SQL語句在成功執(zhí)行完成后，都被自動提交，如果遇到錯誤，那么自動回滾該語句。該模式為系統(tǒng)默認(rèn)的事務(wù)管理模式。(2)顯式事務(wù)模式：該模式允許用戶定義事務(wù)的啟動和完畢。事務(wù)以BEGINTRANSACTION語句顯式開場，以COMMIT或ROLLBACK語句顯式完畢。(3)隱性事務(wù)模式：在當(dāng)前事務(wù)完成提交或回滾后，新事務(wù)自動啟動。隱性事務(wù)不需要使用BEGINTRANSACTION語句標(biāo)識事務(wù)的開場，但需要以COMMIT或ROLLBACK語句來提交或回滾事務(wù)。提示：日常管理和開發(fā)應(yīng)用系統(tǒng)時推薦使用顯式事務(wù)模式。SQLServer以3種事務(wù)模式管理事務(wù)541．啟動和完畢事務(wù)啟動事務(wù)語句的語法格式如下〔定義事務(wù)〕：BEGINTRAN[SACTION][transaction_name|@tran_name_variable[WITHMARK['description']]]完畢事務(wù)語句的語法格式如下〔提交事務(wù)〕：COMMIT[TRAN[SACTION][transaction_name|@tran_name_variable]]SQLServer按事務(wù)模式進(jìn)行事務(wù)管理，設(shè)置事務(wù)啟動和結(jié)束的時間，正確處理事務(wù)結(jié)束之前產(chǎn)生的錯誤。1．啟動和完畢事務(wù)SQLServer按事務(wù)模式進(jìn)行事務(wù)管55例10-8建立一個顯式事務(wù)以顯示Sales數(shù)據(jù)庫的employee表的數(shù)據(jù)。BEGINTRANSACTIONSELECT*FROMemployeeCOMMITTRANSACTION本例創(chuàng)建的事務(wù)以BEGINTRANSACTION語句開始，以COMMITTRANSACTION語句結(jié)束。例10-8建立一個顯式事務(wù)以顯示Sales數(shù)據(jù)庫的empl56DECLARE@transaction_namevarchar(32)SELECT@transaction_name='tran_delete'BEGINTRANSACTION@transaction_nameDELETEFROMdepartmentWHEREdepartment_id='D004'DELETEFROMemployeeWHEREdepartment_id='D004'COMMITTRANSACTION

tran_delete例10-9建立一個顯式命名事務(wù)以刪除department表的“倉儲部〞記錄行。本例命名了一個tran_delete事務(wù)，該事務(wù)用于department表的“倉儲部”記錄行及相關(guān)數(shù)據(jù)。DECLARE@transaction_namevarc57CREATETABLEimp_tran(numchar(2)NOTNULL,cnamechar(6)NOTNULL)GOSETIMPLICIT_TRANSACTIONSON--啟動隱性事務(wù)模式GO--第一個事務(wù)由INSERT語句啟動INSERTINTOimp_tranVALUES('01','Zhang')INSERTINTOimp_tranVALUES('02','Wang')COMMITTRANSACTION--提交第一個隱性事務(wù)GO--第二個隱式事務(wù)由SELECT語句啟動SELECTCOUNT(*)FROMimp_tranINSERTINTOimp_tranVALUES('03','Li')SELECT*FROMimp_tranCOMMITTRANSACTION--提交第二個隱性事務(wù)GOSETIMPLICIT_TRANSACTIONSOFF--關(guān)閉隱性事務(wù)模式GO例10-10隱性事務(wù)處理過程。CREATETABLEimp_tran例10-10隱性58事務(wù)回滾使用ROLLBACKTRANSACTION語句實現(xiàn)，其語法格式如下：ROLLBACK[TRAN[SACTION][transaction_name|@tran_name_variable|savepoint_name|@savepoint_variable]]其中，savepoint_name用于指定回滾到某一指定位置的標(biāo)記名稱，@savepoint_variable為存放該標(biāo)記名稱的變量。2．事務(wù)回滾當(dāng)事務(wù)執(zhí)行過程中遇到錯誤時，該事務(wù)修改的所有數(shù)據(jù)都恢復(fù)到事務(wù)開始時的狀態(tài)或某個指定的位置，事務(wù)占用的資源將被釋放。這個操作過程叫事務(wù)回滾(TransactionRollback)。事務(wù)回滾使用ROLLBACKTRANSACTION語句實現(xiàn)59例10-11使用ROLLBACKTRANSACTION語句標(biāo)識事務(wù)完畢。BEGINTRANSACTIONUPDATEgoodsSETstock_quantity=stock_quantity-5WHEREgoods_id='G00006'

INSERTINTOsell_order(order_id1,goods_id,order_num,order_date)VALUES('S00005','G00006',5,getdate())ROLLBACKTRANSACTION例10-11使用ROLLBACKTRANSACTION語60事務(wù)回滾到指定位置如果要讓事務(wù)回滾到指定位置，那么需要在事務(wù)中設(shè)定保存點(SavePoint)。所謂保存點是指定其所在位置之前的事務(wù)語句，不能回滾的語句，即此語句前面的操作被視為有效。其語法格式如下：SAVETRAN[SACTION]{savepoint_name|@savepoint_variable}對于長事務(wù)，可以在事務(wù)中設(shè)置若干個保存點。保存點好比是對事務(wù)做上若干個標(biāo)記，這樣回滾事務(wù)就不必回滾整個事務(wù)，而是可以回滾到指定的保存點。事務(wù)回滾到指定位置如果要讓事務(wù)回滾到指定位置，那么需要在事務(wù)61BEGINTRANSACTIONmy_transaction_deleteDELETEFROMdepartmentWHEREdepartment_id='D005'SAVETRANSACTIONafter_delete --設(shè)置保存點UPDATEemployeeSETdepartment_id='D001'WHEREdepartment_id='D005'IF(@@error=0OR@@rowcount=0) --判斷是否出錯或無記錄BEGINROLLBACKTRANSACTIONafter_delete --如果出錯回滾到保存點COMMITTRANSACTIONmy_transaction_delete--出錯回滾后提交ENDELSECOMMITTRANSACTIONmy_transaction_delete--未出錯進(jìn)展提交GO例10-12刪除倉儲部，再將倉儲部的職工劃分到總經(jīng)理辦。BEGINTRANSACTIONmy_transacti62CREATETRIGGERtrig_uptabONgoodsFORUPDATEASSAVETRANSACTIONtran_uptab--設(shè)置保存點INSERTINTOnewgoodsSELECT*FROMinsertedIF(@@error<>0) --IF語句控制回滾操作BEGIN

ROLLBACKTRANSACTIONtran_uptabEND例10-13為表goods定義觸發(fā)器trig_uptab，如果goods表更新數(shù)據(jù)，那么把新數(shù)據(jù)復(fù)制到表newgoods中，假設(shè)出錯，那么取消復(fù)制操作。CREATETRIGGERtrig_uptabONg63和BEGIN…END語句類似，BEGINTRANSACTION和COMMITTRANSACTION語句也可以進(jìn)展嵌套，即事務(wù)可以嵌套執(zhí)行。3．事務(wù)嵌套和BEGIN…END語句類似，BEGINTRANSACTI64CREATETABLEemployee_tran(numchar(2)NOTNULL,cnamechar(6)NOTNULL)GOBEGINTRANSACTIONTran1 --@@TRANCOUNT為1INSERTINTOemployee_tranVALUES('01','Zhang')

BEGINTRANSACTIONTran2 --@@TRANCOUNT為2INSERTINTOemployee_tranVALUES('02','Wang')

BEGINTRANSACTIONTran3 --@@TRANCOUNT為3PRINT@@TRANCOUNTINSERTINTOemployee_tranVALUES('03','Li')

COMMITTRANSACTIONTran3 --@@TRANCOUNT為2PRINT@@TRANCOUNT

COMMITTRANSACTIONTran2 --@@TRANCOUNT為1PRINT@@TRANCOUNTCOMMITTRANSACTIONTran1 --@@TRANCOUNT為0PRINT@@TRANCOUNT例10-14提交事務(wù)。運行結(jié)果：3210CREATETABLEemployee_tran例10-65鎖(Lock)作為一種平安機(jī)制，用于控制多個用戶的并發(fā)操作，以防止用戶讀取正在由其他用戶更改的數(shù)據(jù)或者多個用戶同時修改同一數(shù)據(jù)，從而確保事務(wù)完整性和數(shù)據(jù)庫一致性。鎖機(jī)制用于解決并發(fā)事務(wù)對同一資源或者數(shù)據(jù)的競爭，從而可能導(dǎo)致的數(shù)據(jù)不一致問題。雖然SQLServer會自動強(qiáng)制執(zhí)行鎖，但是用戶可以通過對鎖進(jìn)展了解并在應(yīng)用程序中自定義鎖來設(shè)計出更有效率的應(yīng)用程序。10.6SQLServer的鎖機(jī)制鎖(Lock)作為一種平安機(jī)制，用于控制多個用戶的并發(fā)操作，66當(dāng)對一個數(shù)據(jù)源加鎖后，此數(shù)據(jù)源就有了一定的訪問限制，稱對此數(shù)據(jù)源進(jìn)展了“鎖定〞。SQLServer有多種粒度鎖，允許一個事務(wù)鎖定不同類型的資源。①數(shù)據(jù)行(Row)：數(shù)據(jù)頁中的單行數(shù)據(jù)。②索引行(Key)：索引頁中的單行數(shù)據(jù)，即索引的鍵值。③頁(Page)：頁是SQLServer存取數(shù)據(jù)的根本單位，其大小為8KB。④擴(kuò)展盤區(qū)(Extent)：一個盤區(qū)由8個連續(xù)的頁組成。⑤表(Table)。⑥數(shù)據(jù)庫(Database)。當(dāng)對一個數(shù)據(jù)源加鎖后，此數(shù)據(jù)源就有了一定的訪問限制，稱對此數(shù)67SQLServer使用不同的鎖模式鎖定資源，這些鎖模式確定了并發(fā)事務(wù)訪問資源的方式。(1)共享鎖(SharedLock)(2)排它鎖(ExclusiveLock)(3)更新鎖(UpdateLock)SQLServer使用不同的鎖模式鎖定資源，這些鎖模式確定68從程序員的角度，鎖可以分為以下兩種類型：(1)樂觀鎖(OptimisticLock)。樂觀鎖假定在處理數(shù)據(jù)時，不需要在應(yīng)用程序的代碼中做任何事情就可以直接在記錄上加鎖，即完全依靠數(shù)據(jù)庫來管理鎖的工作。一般情況下，當(dāng)執(zhí)行事務(wù)處理時，SQLServer會自動對事務(wù)處理范圍內(nèi)更新到的表做鎖定。(2)悲觀鎖(PessimisticLock)。悲觀鎖需要程序員直接收理數(shù)據(jù)或?qū)ο笊系募渔i處理，并負(fù)責(zé)獲取、共享和放棄正在使用的數(shù)據(jù)上的任何鎖。從程序員的角度，鎖可以分為以下兩種類型：(1)樂觀鎖(O69隔離(Isolation)是計算機(jī)平安技術(shù)中的概念，其本質(zhì)上是一種封鎖機(jī)制。它是指自動數(shù)據(jù)處理系統(tǒng)中的用戶和資源的相關(guān)牽制關(guān)系，也就是用戶和進(jìn)程彼此分開，且和操作系統(tǒng)的保護(hù)控制也分開來。事務(wù)準(zhǔn)備承受不一致數(shù)據(jù)的級別稱為隔離級別(IsolationLevel)。隔離級別是一個事務(wù)必須與其他事務(wù)進(jìn)展隔離的程度。較低的隔離級別可以增加并發(fā)，但代價是降低數(shù)據(jù)的正確性。相反，較高的隔離級別可以確保數(shù)據(jù)的正確性，但可能對并發(fā)產(chǎn)生負(fù)面影響。應(yīng)用程序要求的隔離級別確定了SQLServer使用的鎖行為。隔離(Isolation)是計算機(jī)平安技術(shù)中的概念，其本質(zhì)上70(1)提交讀(READCOMMITTED)。它是SQLServer的默認(rèn)級別。在此隔離級別下，SELECT語句不會也不能返回尚未提交(Committed)的數(shù)據(jù)(即臟數(shù)據(jù))。(2)未提交讀(READUNCOMMITTED)。與提交讀隔離級別相反，它允許讀取臟數(shù)據(jù)，即已經(jīng)被其他用戶修改但尚未提交的數(shù)據(jù)。它是最低的事務(wù)隔離級別，僅可保證不讀取物理損壞的數(shù)據(jù)。(3)可重復(fù)讀(REPEATABLEREAD)。在此隔離級別下，用SELECT語句讀取的數(shù)據(jù)在整個語句執(zhí)行過程中不會被更改。此選項會影響系統(tǒng)的效能，非必要情況最好不用此隔離級別。(4)可串行讀(SERIALIZABLE)。將共享鎖保持到事務(wù)完成，而不是不管事務(wù)是否完成都在不再需要所需的表或數(shù)據(jù)頁時就立即釋放共享鎖。它是最高的事務(wù)隔離級別，事務(wù)之間完全隔離。SQLServer支持以下4種隔離級別(1)提交讀(READCOMMITTED)。它是SQL71使用SETTRANSACTIONISOLATIONLEVEL語句設(shè)置會話的隔離級別其語法格式如下：SETTRANSACTIONISOLATIONLEVEL{READCOMMITTED|READUNCOMMITTED|REPEATABLEREAD|SERIALIZABLE}一次只能設(shè)置一個選項。使用SETTRANSACTIONISOLATIONLE721．用企業(yè)管理器查看和終止鎖1．用企業(yè)管理器查看和終止鎖73系統(tǒng)存儲過程sp_lock的語法格式如下：sp_lockspidspid數(shù)據(jù)類型為int，如果不指定spid，那么顯示所有的鎖。2．用系統(tǒng)存儲過程sp_lock查看鎖例： USEmasterEXECsp_lock

例： USEmasterEXECsp_lock52系統(tǒng)存儲過程sp_lock的語法格式如下：2．用系統(tǒng)存儲過程74死鎖(Deadlocking)是在多用戶或多進(jìn)程狀況下，為使用同一資源而產(chǎn)生的無法解決的爭用狀態(tài)。死鎖會造成資源的大量浪費，甚至?xí)瓜到y(tǒng)崩潰。因此，在SQLServer2000中，通常由鎖監(jiān)視器線程自動定期對死鎖進(jìn)展檢測。當(dāng)識別死鎖后，SQLServer自動設(shè)置一個事務(wù)完畢死鎖進(jìn)程。SQLServer2000提供了自動發(fā)現(xiàn)和解除死鎖的機(jī)制，除非特殊情況，DBA不用手工干預(yù)鎖的授予和死鎖的解除。SQLServer解決死鎖的原那么是“犧牲一個比兩個都死強(qiáng)〞，即挑出一個進(jìn)程作為犧牲者，將其事務(wù)回滾，并向執(zhí)行此進(jìn)程的程序發(fā)送編號為1205的錯誤信息。雖然死鎖不能完全防止，但可以使死鎖的數(shù)量減至最少。死鎖(Deadlocking)是在多用戶或多進(jìn)程狀況下，為使75防止死鎖的途徑防止死鎖的途徑就是不能讓滿足死鎖條件的情況發(fā)生，為此，用戶需要遵循以下原那么：(1)盡量防止并發(fā)地執(zhí)行涉及到修改數(shù)據(jù)的語句。(2)要求每個事務(wù)一次就將所有要使用的數(shù)據(jù)全部加鎖，否那么就不予執(zhí)行。(3)預(yù)先規(guī)定一個封鎖順序，所有的事務(wù)都必須按這個順序?qū)?shù)據(jù)執(zhí)行封鎖。例如，不同的過程在事務(wù)內(nèi)部對對象的更新執(zhí)行順序應(yīng)盡量保持一致。(4)每個事務(wù)的執(zhí)行時間不可太長，對程序段長的事務(wù)可考慮將其分割為幾個事務(wù)。防止死鎖的途徑防止死鎖的途徑就是不能讓滿足死鎖條件的情況發(fā)生76(1)存儲過程是一組SQL語句和流程控制語句的集合，以一個名字存儲并作為一個單元處理。存儲過程用于完成某項任務(wù)，它可以接受參數(shù)、返回狀態(tài)值和參數(shù)值，并且實現(xiàn)嵌套調(diào)用。(2)觸發(fā)器就其本質(zhì)而言是一種特殊的存儲過程，有3種類型：插入觸發(fā)器、更新觸發(fā)器和刪除觸發(fā)器。(3)創(chuàng)建、刪除、查看、修改存儲過程和觸發(fā)器可以使用企業(yè)管理器或Transact-SQL語句。(4)存儲過程和觸發(fā)器的各種信息的查看、修改還可以使用系統(tǒng)存儲過程sp_helptext、sp_rename、sp_helptrigger、sp_depends實現(xiàn)。(5)事務(wù)是一個操作序列，它包含了一組數(shù)據(jù)庫操作命令，所有的命令作為一個整體一起向系統(tǒng)提交或撤消操作請求，即要么都執(zhí)行，要么都不執(zhí)行。(6)鎖是在多用戶環(huán)境下對資源訪問的一種限制。當(dāng)對一個數(shù)據(jù)源加鎖后，此數(shù)據(jù)源就有了一定的訪問限制。(7)事務(wù)與鎖也是保證數(shù)據(jù)完整性和正確性的機(jī)制，可以確保數(shù)據(jù)能夠正確地被存儲、修改，而不會造成數(shù)據(jù)在存儲或修改過程中因事故或其他用戶的中斷而導(dǎo)致的數(shù)據(jù)不完整。本章小結(jié)(1)存儲過程是一組SQL語句和流程控制語句的集合，以一個名77第10章存儲過程與觸發(fā)器第10章存儲過程與觸發(fā)器78本章內(nèi)容10.1存儲過程概述10.2存儲過程的創(chuàng)立與使用10.3觸發(fā)器概述10.4觸發(fā)器的創(chuàng)立與使用10.5事務(wù)處理10.6SQLServer的鎖機(jī)制本章內(nèi)容10.1存儲過程概述7910.1存儲過程概述存儲過程是SQLServer效勞器上一組預(yù)編譯的Transact-SQL語句，用于完成某項任務(wù)，它可以承受參數(shù)、返回狀態(tài)值和參數(shù)值，并且可以嵌套調(diào)用。10.1存儲過程概述存儲過程是SQLServer效勞8010.1存儲過程概述SQLServer存儲過程的類型包括：系統(tǒng)存儲過程用戶定義存儲過程臨時存儲過程擴(kuò)展存儲過程1.存儲過程的類型10.1存儲過程概述SQLServer存儲過程的類型包括8110.1存儲過程概述(1)系統(tǒng)存儲過程

是指由系統(tǒng)提供的存儲過程，主要存儲在master數(shù)據(jù)庫中并以sp_為前綴，它從系統(tǒng)表中獲取信息，從而為系統(tǒng)管理員管理SQLServer提供支持。通過系統(tǒng)存儲過程，SQLServer中的許多管理性或信息性的活動(例如使用sp_depends、sp_helptexts可以了解數(shù)據(jù)數(shù)據(jù)庫對象、數(shù)據(jù)庫信息)都可以順利有效地完成。盡管系統(tǒng)存儲過程被放在master數(shù)據(jù)庫中，仍可以在其他數(shù)據(jù)庫中對其進(jìn)展調(diào)用(調(diào)用時，不必在存儲過程名前加上數(shù)據(jù)庫名)。當(dāng)創(chuàng)立一個新數(shù)據(jù)庫時，一些系統(tǒng)存儲過程會在新數(shù)據(jù)庫中被自動創(chuàng)立。10.1存儲過程概述(1)系統(tǒng)存儲過程是指由系統(tǒng)提供的存8210.1存儲過程概述(2)用戶定義存儲過程是由用戶創(chuàng)立并能完成某一特定功能(例如查詢用戶所需數(shù)據(jù)信息)的存儲過程。它處于用戶創(chuàng)立的數(shù)據(jù)庫中，存儲過程名前沒有前綴sp_。10.1存儲過程概述(2)用戶定義存儲過程是由用戶創(chuàng)立并能8310.1存儲過程概述(3)臨時存儲過程臨時存儲過程與臨時表類似，分為局部臨時存儲過程和全局臨時存儲過程，且可以分別向該過程名稱前面添加“#〞或“##〞前綴表示?！?〞表示本地臨時存儲過程，“##〞表示全局臨時存儲過程。使用臨時存儲過程必須創(chuàng)立本地連接，當(dāng)SQLServer關(guān)閉后，這些臨時存儲過程將自動被刪除。由于SQLServer支持重新使用執(zhí)行方案，所以連接到SQLServer2000的應(yīng)用程序應(yīng)使用sp_executesql系統(tǒng)存儲過程，而不使用臨時存儲過程。10.1存儲過程概述(3)臨時存儲過程臨時存儲過程與臨時表8410.1存儲過程概述(4)擴(kuò)展存儲過程擴(kuò)展存儲過程是SQLServer可以動態(tài)裝載和執(zhí)行的動態(tài)鏈接庫(DLL)。當(dāng)擴(kuò)展存儲過程加載到SQLServer中，它的使用方法與系統(tǒng)存儲過程一樣。擴(kuò)展存儲過程只能添加到master數(shù)據(jù)庫中，其前綴是xp_。10.1存儲過程概述(4)擴(kuò)展存儲過程擴(kuò)展存儲過程是SQL8510.1存儲過程概述2.存儲過程的功能特點SQLServer的存儲過程可實現(xiàn)以下功能：(1)接收輸入?yún)?shù)并以輸出參數(shù)的形式為調(diào)用過程或批處理返回多個值。(2)包含執(zhí)行數(shù)據(jù)庫操作的編程語句，包括調(diào)用其他過程。(3)為調(diào)用過程或批處理返回一個狀態(tài)值，以表示成功或失敗(及失敗原因)。10.1存儲過程概述2.存儲過程的功能特點SQLSer8610.1存儲過程概述存儲過程具有以下優(yōu)點(1)模塊化編程。(2)快速執(zhí)行。(3)減少網(wǎng)絡(luò)通信量。(4)提供平安機(jī)制。(5)保證操作一致性。10.1存儲過程概述存儲過程具有以下優(yōu)點(1)模塊化編程。8710.2存儲過程的創(chuàng)建與使用10.2存儲過程的創(chuàng)建與使用1.使用企業(yè)管理器創(chuàng)立存儲過程存儲過程創(chuàng)建10.2存儲過程的創(chuàng)建與使用10.2存儲過程的創(chuàng)建與使882.使用向?qū)?chuàng)建存儲過程2.使用向?qū)?chuàng)建存儲過程893.使用CREATEPROCEDURE語句創(chuàng)立存儲過程使用CREATEPROCEDURE語句創(chuàng)立存儲過程應(yīng)該考慮以下幾個方面：(1)在一個批處理中，CREATEPROCEDURE語句不能與其他SQL語句合并在一起。(2)數(shù)據(jù)庫所有者具有默認(rèn)的創(chuàng)立存儲過程的權(quán)限，它可把該權(quán)限傳遞給其他的用戶。(3)存儲過程作為數(shù)據(jù)庫對象其命名必須符合標(biāo)識符的命名規(guī)那么。(4)只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)立屬于當(dāng)前數(shù)據(jù)庫的存儲過程。3.使用CREATEPROCEDURE語句創(chuàng)立存儲過程使90創(chuàng)立存儲過程語句的語法格式如下：CREATE

PROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[,...n]創(chuàng)立存儲過程語句的語法格式如下：CREATEPROC[ED91例10-1創(chuàng)立存儲過程，從表goods和表goods_classification的聯(lián)接中返回商品名、商品類別、單價。CREATEPROCEDUREgoods_infoASSELECTgoods_name,classification_name,unit_price FROMgoodsgINNERJOINgoods_classificationgc ONg.classification_id=gc.classification_id存儲過程創(chuàng)立后，存儲過程的名稱存放在sysobject表中，文本存放在syscomments表中。例10-1創(chuàng)立存儲過程，從表goods和表goods_c92執(zhí)行存儲過程執(zhí)行存儲過程的語法格式：[[EXEC[UTE]]{[@return_status=]

procedure_name[;number]|@procedure_name_var}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]][,...n][WITHRECOMPILE]10.2存儲過程的創(chuàng)建與使用執(zhí)行存儲過程執(zhí)行存儲過程的語法格式：10.2存儲過程的創(chuàng)建93例如，執(zhí)行例10-1的存儲過程goods_info在SQL查詢分析器中輸入命令：EXECgoods_info運行的結(jié)果：例如，執(zhí)行例10-1的存儲過程goods_info在SQL查9410.2存儲過程的創(chuàng)建與使用修改存儲過程1.使用企業(yè)管理器修改存儲過程2.使用ALTERPROCEDURE語句修改存儲過程ALTERPROCEDURE的語法規(guī)那么是：ALTERPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...n]10.2存儲過程的創(chuàng)建與使用修改存儲過程1.使用企業(yè)管理95查看存儲過程的文本信息SELECTo.id,c.textFROMsysobjectsoINNERJOINsyscommentscONo.id=c.idWHEREo.type='P'AND='procedure_name'GO查看存儲過程的文本信息SELECTo.id,c.text9610.2存儲過程的創(chuàng)建與使用刪除存儲過程

1.使用企業(yè)管理器刪除存儲過程2.使用DROPPROCEDURE刪除存儲過程DROPPROCEDURE的語法如下：DROPPROCEDURE

{procedure_name}[,...n]例如刪除例10-1創(chuàng)建的存儲過程goods_info：DROPPROCEDURE

goods_infoGO10.2存儲過程的創(chuàng)建與使用刪除存儲過程1.使用企業(yè)管理9710.2存儲過程的創(chuàng)建與使用存儲過程參數(shù)與狀態(tài)值存儲過程和調(diào)用者之間通過參數(shù)交換數(shù)據(jù)，可以按輸入的參數(shù)執(zhí)行，也可由參數(shù)輸出執(zhí)行結(jié)果。調(diào)用者通過存儲過程返回的狀態(tài)值對存儲過程進(jìn)展管理。1.參數(shù)存儲過程的參數(shù)在創(chuàng)立過程時聲明。SQLServer支持兩類參數(shù)：輸入?yún)?shù)和輸出參數(shù)。(1)輸入?yún)?shù)輸入?yún)?shù)允許調(diào)用程序為存儲過程傳送數(shù)據(jù)值。要定義存儲過程的輸入?yún)?shù)，必須在CREATEPROCEDURE語句中聲明一個或多個變量及類型。10.2存儲過程的創(chuàng)建與使用存儲過程參數(shù)與狀態(tài)值存儲過程和98例10-3創(chuàng)立帶參數(shù)的存儲過程，從表employee、sell_order、goods、goods_classification的連接中返回輸入的員工名、該員工銷售的商品名、商品類別、銷售量等信息。CREATEPROC

sell_info

@employee_namevarchar(20)ASSELECTemployee_name,goods_name,classification_name,order_numFROMemployeee

INNERJOINsell_orders

ONe.employee_id=s.employee_idJOINgoodsg

ONg.goods_id=s.goods_idJOINgoods_classificationgcONgc.classification_id=g.classification_idWHEREemployee_nameLIKE@employee_name例10-3創(chuàng)立帶參數(shù)的存儲過程，從表employee、s99執(zhí)行存儲過程sell_info以EXECsell_info'東方牧'參數(shù)值可以包含通配符“%”，例如，查找所有姓“錢”的員工的銷售情況可以使用以下命令： EXECsell_info'錢%'執(zhí)行存儲過程sell_info以參數(shù)值可以包含通配符“%”，100存儲過程執(zhí)行時，參數(shù)可以由位置標(biāo)識，也可以由名字標(biāo)識。例如，定義一個具有3個參數(shù)的存儲過程：CREATEPROCmyproc@val1int,@val2int,@val3intAS...參數(shù)以位置傳遞：EXECmyproc10,20,15參數(shù)以名字傳遞，每個值由對應(yīng)的參數(shù)名引導(dǎo)：EXECmyproc@val2=20,@val1=10,@val3=15存儲過程中參數(shù)的標(biāo)識存儲過程執(zhí)行時，參數(shù)可以由位置標(biāo)識，也可以由名字標(biāo)識。例如，101(2)輸出參數(shù)輸出參數(shù)允許存儲過程將數(shù)據(jù)值或游標(biāo)變量傳回調(diào)用程序。OUTPUT關(guān)鍵字用以指出能返回到調(diào)用它的批處理或過程中的參數(shù)。為了使用輸出參數(shù)，在CREATEPROCEDURE和EXECUTE語句中都必須使用OUTPUT關(guān)鍵字。(2)輸出參數(shù)輸出參數(shù)允許存儲過程將數(shù)據(jù)值或游標(biāo)變量傳回調(diào)用102例10-4創(chuàng)立存儲過程price_goods，通過輸入?yún)?shù)在goods表中查找商品，以輸出參數(shù)獲取商品單價。CREATEPROCprice_goods@goods_namevarchar(80)=NULL,

@price_goodsrealOUTPUTASSELECT@price_goods=unit_priceFROMgoodsWHEREgoods_name=@goods_name例10-4創(chuàng)立存儲過程price_goods，通過輸入?yún)?03執(zhí)行price_goods存儲過程的代碼如下：DECLARE@pricerealEXEC

price_goods'CanonLBP2900',@priceOUTPUTSELECT@priceEXECUTE語句還需要關(guān)鍵字OUTPUT以允許參數(shù)值返回給變量。執(zhí)行price_goods存儲過程的代碼如下：EXECUTE104(1)用RETURN語句定義返回值存儲過程可以返回整型狀態(tài)值，表示過程是否成功執(zhí)行，或者過程失敗的原因。如果存儲過程沒有顯式設(shè)置返回代碼的值，那么SQLServer返回代碼為0，表示成功執(zhí)行；假設(shè)返回-1~-99之間的整數(shù)，表示沒有成功執(zhí)行。也可以使用RETURN語句，用大于0或小于-99的整數(shù)來定義自己的返回狀態(tài)值，以表示不同的執(zhí)行結(jié)果。2.返回存儲過程的狀態(tài)(2)捕獲返回狀態(tài)值使用以下語句接收返回的狀態(tài)值EXECUTE@status_var=procedure_name(1)用RETURN語句定義返回值2.返回存儲過程的狀態(tài)(105例10-5創(chuàng)立存儲過程，輸入商品類別，返回各種商品名稱。在存儲過程中，用值15表示用戶沒有提供參數(shù)；值-l01表示沒有輸入商品類別；值0表示過程運行沒有出錯。CREATEPROCcl_goods@cl_namevarchar(40)=NULLASIF@cl_name=NULLRETURN15IFNOTEXISTS(SELECT*FROMgoods_classificationWHEREclassification_name=@cl_name)RETURN-101SELECTg.goods_nameFROMgoods_classificationgc,goodsgWHEREgc.classification_id=g.classification_idANDgc.classification_name=@cl_nameRETURN0例10-5創(chuàng)立存儲過程，輸入商品類別，返回各種商品名稱。106DECLARE@return_statusintEXEC@return_status=cl_goods'筆記本計算機(jī)'IF@return_status=15SELECT'語法錯誤'ELSE

IF@return_status=-101SELECT'沒有找到該商品類別'例10-5的存儲過程cl_goods執(zhí)行時使用以下語句：DECLARE@return_statusint例10-107批處理是包含一個或多個Transact-SQL語句的組，從應(yīng)用程序一次性地發(fā)送到SQLServer執(zhí)行。SQLServer將批處理語句編譯成一個可執(zhí)行單元，此單元稱為執(zhí)行方案，每次執(zhí)行其中的一條語句。函數(shù)是由一個或多個Transact-SQL語句組成的子程序，可用于封裝代碼以便重新使用。存儲過程是一組SQL語句的集合，類似于程序的模塊。它與函數(shù)不同，因為它不返回取代其名稱的值，也不能直接用在表達(dá)式中。批處理、函數(shù)、存儲過程的區(qū)別存儲過程、批處理文件和函數(shù)都是由一個或多個Transact-SQL語句組成的，通?？梢酝瓿梢幌盗械牟僮?，但是它們的適用范圍和產(chǎn)生的效果是不同的，使用時應(yīng)當(dāng)認(rèn)真分析，區(qū)別對待。批處理是包含一個或多個Transact-SQL語句的組，從應(yīng)108觸發(fā)器是一種特殊類型的存儲過程，用于實現(xiàn)比較復(fù)雜的數(shù)據(jù)約束。觸發(fā)器主要是通過事件進(jìn)展觸發(fā)而被執(zhí)行的，而存儲過程可以通過過程名字直接調(diào)用。當(dāng)對某一表進(jìn)展UPDATE、INSERT、DELETE操作時，SQLServer就會自動執(zhí)行觸發(fā)器所定義的SQL語句，從而確保對數(shù)據(jù)的處理必須符合由這些SQL語句所定義的規(guī)那么。觸發(fā)器的主要作用就是能夠?qū)崿F(xiàn)由主鍵和外鍵所不能保證的參照完整性和數(shù)據(jù)的一致性。10.3觸發(fā)器概述觸發(fā)器是一種特殊類型的存儲過程，用于實現(xiàn)比較復(fù)雜的數(shù)據(jù)約束。109觸發(fā)器的其它功能強(qiáng)化約束：觸發(fā)器能夠?qū)崿F(xiàn)比CHEAK語句更為復(fù)雜的約束。跟蹤變化：觸發(fā)器可以偵測數(shù)據(jù)庫內(nèi)的操作，從而不允許數(shù)據(jù)庫中不經(jīng)許可的指定更新和變化。級聯(lián)運行：觸發(fā)器可以偵測數(shù)據(jù)庫內(nèi)的操作，并自動地級聯(lián)影響整個數(shù)據(jù)庫地各項內(nèi)容。存儲過程的調(diào)用：為了響應(yīng)數(shù)據(jù)庫更新，觸發(fā)器可以調(diào)用一個或多個存儲過程，甚至可以通過外部過程的調(diào)用而在DBMS本身之外進(jìn)展操作。觸發(fā)器的其它功能110名稱：觸發(fā)器有一個符合標(biāo)志符命名規(guī)那么的名稱。定義的目標(biāo)：觸發(fā)器必須定義在表或視圖上。觸發(fā)條件：是UPDATE、INSERT，還是DELETE語句。觸發(fā)邏輯：觸發(fā)之后如何處理。觸發(fā)器的四要素名稱：觸發(fā)器有一個符合標(biāo)志符命名規(guī)那么的名稱。觸發(fā)器的四要素1111.使用企業(yè)管理器創(chuàng)立觸發(fā)器創(chuàng)立觸發(fā)器10.4觸發(fā)器的創(chuàng)建與使用1.使用企業(yè)管理器創(chuàng)立觸發(fā)器創(chuàng)立觸發(fā)器10.4觸發(fā)器的112存儲過程與觸發(fā)器課件1132.使用CREATETRIGGER語句創(chuàng)立觸發(fā)器CREATETRIGGER語句的語法格式如下：CREATETRIGGERtrigger_nameON{table_name|view}[WITHENCRYPTION]{FOR|AFTER|INSTEADOF} --觸發(fā)器的類型{[

INSERT][,][

UPDATE][,][DELETE]}ASsql_statement[...n]2.使用CREATE