第10章存儲過程和觸發(fā)器課件

第10章存儲過程和觸發(fā)器10.1存儲過程的概念10.2建立和執(zhí)行存儲過程10.3存儲過程的管理與維護(hù)10.4觸發(fā)器的概念10.5創(chuàng)建和應(yīng)用DML觸發(fā)器10.6觸發(fā)器的管理與維護(hù)10.7DDL觸發(fā)器10.8事務(wù)1/9/20231第10章存儲過程和觸發(fā)器10.1存儲過程的概念1/9/本章重點存儲過程的概念、分類存儲過程的創(chuàng)建、執(zhí)行觸發(fā)器的概念、分類觸發(fā)器的創(chuàng)建、觸發(fā)DELETED與INSERTED表1/9/20232本章重點存儲過程的概念、分類1/9/20232本章難點有參存儲過程的創(chuàng)建、執(zhí)行觸發(fā)器的觸發(fā)時機DELETED與INSERTED表1/9/20233本章難點有參存儲過程的創(chuàng)建、執(zhí)行1/9/2023310.1存儲過程的概念10．1．1基本概念存儲過程是一組編譯在單個執(zhí)行計劃中的Transact-SQL語句，將一些固定的操作集中起來交給SQLServer數(shù)據(jù)庫服務(wù)器完成，以實現(xiàn)某個任務(wù)。10．1．2存儲過程的優(yōu)點（1）與其他應(yīng)用程序共享應(yīng)用程序邏輯，因而確保了數(shù)據(jù)訪問和修改的一致性。（2）防止數(shù)據(jù)庫中表的細(xì)節(jié)暴露給用戶。（3）提供安全機制。（4）改進(jìn)性能。（5）減少網(wǎng)絡(luò)流量。1/9/2023410.1存儲過程的概念10．1．1基本概念1/9/210．1．3存儲過程的類型存儲過程分為三類：系統(tǒng)提供的存儲過程系統(tǒng)提供的存儲過程系統(tǒng)存儲過程的名字都以“sp_”為前綴例：sp_bindefault，sp_bindrule，sp_help，sp_helpdb,sp_helpindex等用戶定義的存儲過程用戶定義的存儲過程是由用戶為完成某一特定功能而編寫的存儲過程。存儲在創(chuàng)建時的數(shù)據(jù)庫中。擴展存儲過程：擴展存儲過程是用來調(diào)用操作系統(tǒng)提供的功能。1/9/2023510．1．3存儲過程的類型1/9/20235系統(tǒng)存儲過程說明sp_databases列出服務(wù)器上的所有數(shù)據(jù)庫。sp_helpdb報告有關(guān)指定數(shù)據(jù)庫或所有數(shù)據(jù)庫的信息sp_renamedb更改數(shù)據(jù)庫的名稱sp_tables返回當(dāng)前環(huán)境下可查詢的對象的列表sp_columns回某個表列的信息sp_help查看某個表的所有信息sp_helpconstraint查看某個表的約束sp_helpindex查看某個表的索引sp_stored_procedures列出當(dāng)前環(huán)境中的所有存儲過程。sp_password添加或修改登錄帳戶的密碼。sp_helptext顯示默認(rèn)值、未加密的存儲過程、用戶定義的存儲過程、觸發(fā)器或視圖的實際文本。1/9/20236系統(tǒng)存儲過程說明sp_databases列出服務(wù)器上的所有數(shù)EXECsp_databasesEXECsp_renamedb'Northwind','Northwind1'USEstuDBGOEXECsp_tablesEXECsp_columnsstuInfoEXECsp_helpstuInfoEXECsp_helpconstraintstuInfoEXECsp_helpindexstuMarksEXECsp_helptext'view_stuInfo_stuMarks'EXECsp_stored_procedures

修改數(shù)據(jù)庫的名稱(單用戶訪問)列出當(dāng)前系統(tǒng)中的數(shù)據(jù)庫當(dāng)前數(shù)據(jù)庫中查詢的對象的列表返回某個表列的信息查看表stuInfo的信息查看表stuInfo的約束查看表stuMarks的索引查看視圖的語句文本查看當(dāng)前數(shù)據(jù)庫中的存儲過程演示：常用的存儲過程1/9/20237EXECsp_databases修改數(shù)據(jù)庫的名稱(單用戶訪10.2建立和執(zhí)行存儲過程簡單存儲過程類似于將一組SQL語句起個名字，然后就可以在需要時反復(fù)調(diào)用。復(fù)雜一些的則要有輸入和輸出參數(shù)。10．2．1創(chuàng)建和執(zhí)行簡單存儲過程創(chuàng)建存儲過程的基本語法如下：CREATEPROC存儲過程名[WITHENCRYPTION][WITHRECOMPILE]ASSQL語句其中各參數(shù)如下：[WITHENCRYPTION]：對存儲過程進(jìn)行加密。[WITHRECOMPILE]：對存儲過程重新編譯。1/9/2023810.2建立和執(zhí)行存儲過程簡單存儲【例10.1】創(chuàng)建一個無參數(shù)的存儲過程，在SALES數(shù)據(jù)庫中,創(chuàng)建存儲過程xs,查詢銷售編號,商品名稱,數(shù)量。CREATEPROCEDURExsASSELECT銷售編號,商品名稱,sell.數(shù)量as銷售數(shù)量FROMgoods,sellWHEREgoods.商品編號=sell.商品編號1/9/20239【例10.1】創(chuàng)建一個無參數(shù)的存儲過程，在SALES數(shù)據(jù)庫中10．2．2存儲過程的執(zhí)行執(zhí)行存儲過程的基本語法如下：EXEC[UTE]存儲過程名同時EXECUTE命令除了可以執(zhí)行存儲過程外還可以執(zhí)行存放Transact-SQL語句的字符串變量，或直接執(zhí)行Transact-SQL語句字符串。此時EXECUTE語句的語法格式如下。EXECUTE({@字符串變量|[N]'SQL語句字符串'}[+...n]其中“@字符串變量”是局部字符串變量名，最大值為服務(wù)器的可用內(nèi)存。[N]'SQL語句字符串'的語句字符串是一個由SQL語句構(gòu)成的字符串常量。如果包含N，則該字符串將解釋為nvarchar數(shù)據(jù)類型。EXECxs1/9/20231010．2．2存儲過程的執(zhí)行EXECxs1/9/202【例10.2】建立一個批處理，查詢相應(yīng)表中的信息。DECLARE@tab_namevarchar(20)SET@tab_name='xs'EXECUTE('SELECT*FROM'+@tab_name)1/9/202311【例10.2】建立一個批處理，查詢相應(yīng)表中的信息。DECLA10．2．3帶輸入?yún)?shù)的存儲過程1．建立存儲過程一個存儲過程可以帶一個或多個參數(shù)，輸入?yún)?shù)是指由調(diào)用程序向存儲過程傳遞的參數(shù)，它們在創(chuàng)建存儲過程語句中被定義，在執(zhí)行存儲過程中給出相應(yīng)的參數(shù)值。聲明帶輸入?yún)?shù)的存儲過程的語法格式如下：CREATEPROCEDURE存儲過程名@參數(shù)名數(shù)據(jù)類型[=默認(rèn)值][，…n][WITHENCRYPTION][WITHRECOMPILE]ASSQL語句1/9/20231210．2．3帶輸入?yún)?shù)的存儲過程1/9/202312其中“@參數(shù)名”和定義局部變量一樣，必須以符號@為前綴，要指定數(shù)據(jù)類型，多個參數(shù)定義要用“，”隔開。在執(zhí)行存儲過程時該參數(shù)將由指定的參數(shù)值來代替，如果執(zhí)行時未提供該參數(shù)的參數(shù)值，則使用時須定義默認(rèn)值（默認(rèn)值可以是常量或空值null），否則將產(chǎn)生錯誤。1/9/202313其中“@參數(shù)名”和定義局部變量一樣，必須以符號@為前綴，要指

【例10.3】創(chuàng)建一個帶輸入?yún)?shù)的存儲過程PROC_GOODS,查詢指定員工所進(jìn)商品信息CREATEPROCproc_goods@員工編號char(6)=‘1001’AsSelect商品編號,商品名稱,生產(chǎn)廠商,進(jìn)貨價,零售價,數(shù)量,進(jìn)貨時間fromgoodsWhere進(jìn)貨員工編號=@員工編號

1/9/202314

【例10.3】創(chuàng)建一個帶輸入?yún)?shù)的存儲過程PROC_G2．執(zhí)行存儲過程在執(zhí)行存儲過程的語句中，有兩種方式傳遞參數(shù)值，分別是使用參數(shù)名傳遞參數(shù)值和按參數(shù)位置傳遞參數(shù)值。使用參數(shù)名傳遞參數(shù)值，是通過語句“@參數(shù)名=參數(shù)值”給參數(shù)傳遞值。當(dāng)存儲過程含有多個輸入?yún)?shù)時，對數(shù)值可以按任意順序給出，對于允許空值和具有默認(rèn)值的輸入?yún)?shù)可以不給參數(shù)值，其語法格式為：EXECUTE存儲過程名[@參數(shù)名=參數(shù)值][，…n]按參數(shù)位置傳遞參數(shù)值，不顯式地給出“@參數(shù)名”，而是按照參數(shù)定義的順序給出參數(shù)值。按位置傳遞參數(shù)時，也可以忽略允許為空值和有默認(rèn)值的參數(shù)，但不能因此破壞輸入?yún)?shù)的指定順序。必要時使用關(guān)鍵字“DEFAULT”作為參數(shù)值的占位。EXECproc_goods@員工編號=‘1002’或EXECproc_goods‘1002’1/9/2023152．執(zhí)行存儲過程EXECproc_goods@員工10．2．4帶輸出參數(shù)的存儲過程如果我們需要從存儲過程中返回一個或多個值，可以通過在創(chuàng)建存儲過程的語句中定義輸出參數(shù)來實現(xiàn)，為了使用輸出參數(shù)，需要在創(chuàng)建存儲過程的命令中使用OUTPUT關(guān)鍵字。聲明帶輸出參數(shù)的存儲過程的語法格式如下：CREATEPROCEDURE存儲過程名@參數(shù)名數(shù)據(jù)類型[VARYING][=默認(rèn)值]OUTPUT[，…n][WITHENCRYPTION][WITHRECOMPILE]ASSQL語句1/9/20231610．2．4帶輸出參數(shù)的存儲過程如果我注意：OUTPUT變量必須在定義存儲過程和使用該變量時都進(jìn)行定義。定義時的參數(shù)名和調(diào)用時的變量名不一定相同，不過數(shù)據(jù)類型和參數(shù)的位置必須匹配。1/9/202317注意：OUTPUT變量必須在定義存儲過程和使用該變量時都進(jìn)行【例10.5】創(chuàng)建一個帶有輸入和輸出函數(shù)的存儲過程proc_gno,查詢指定廠商指定名稱的商品對應(yīng)的商品編號CREATEPROCproc_gno@商品名稱varchar(20),@生產(chǎn)廠商varchar(30),@商品編號intoutputAsSelect@商品編號=商品編號FromgoodsWhere商品名稱=@商品名稱and生產(chǎn)廠商=@生產(chǎn)廠商

1/9/202318【例10.5】創(chuàng)建一個帶有輸入和輸出函數(shù)的存儲過程proc_執(zhí)行存儲過程Declare@商品編號intExecproc_gno‘打印機’,’惠普公司’,@商品編號outputPrint‘該商品編號為:’+cast(@商品編號aschar(6))1/9/202319執(zhí)行存儲過程Declare@商品編號int1/9/2010.3存儲過程的管理與維護(hù)10．3．1查看存儲過程的定義信息在SQLServerManagementStudio的“對象資源管理器”中，可以在要查看信息的存儲過程上單擊鼠標(biāo)右鍵，在快捷菜單中選擇“屬性”，彈出“存儲過程屬性”窗口.也可以通過系統(tǒng)存儲過程sp_helptext查看存儲過程的定義；通過sp_help查看存儲過程的參數(shù)；通過sp_depends查看存儲過程的相關(guān)性。1/9/20232010.3存儲過程的管理與維護(hù)10．3．1查看存儲過程【例10.7】在SQLServerManagementStudio服務(wù)器中新建查詢，使用系統(tǒng)存儲過程，查看例10.5中所創(chuàng)建存儲過程的定義、參數(shù)和相關(guān)性。EXECUTEsp_helptextproc_gnoEXECUTEsp_helpproc_gnoEXECUTEsp_dependsproc_gno1/9/202321【例10.7】在SQLServerManagement10．3．2存儲過程的重編譯存儲過程所采用的執(zhí)行計劃，只在編譯時優(yōu)化生成，以后便駐留在高速緩存中。當(dāng)用戶對數(shù)據(jù)庫新增了索引或其他影響數(shù)據(jù)庫邏輯結(jié)構(gòu)的更改后，已編譯的存儲過程執(zhí)行計劃可能會失去效率。通過對存儲過程進(jìn)行重新編譯，可以重新優(yōu)化存儲過程的執(zhí)行計劃。SQLServer為用戶提供了3種重新編譯的方法。1/9/20232210．3．2存儲過程的重編譯存儲過程1．在創(chuàng)建存儲過程時設(shè)定在創(chuàng)建存儲過程時，使用WITHRECOMPILE子句時SQLServer不將該存儲過程的查詢計劃保存在緩存中，而是在每次運行時重新編譯和優(yōu)化，并創(chuàng)建新的執(zhí)行計劃。2．在執(zhí)行存儲過程時設(shè)定通過在執(zhí)行存儲過程時設(shè)定重新編譯，可以讓SQLServer在執(zhí)行存儲過程時重新編譯該存儲過程，這一次執(zhí)行完成后，新的執(zhí)行計劃又被保存在緩存中。這樣用戶就可以根據(jù)需要進(jìn)行重新編譯。EXEC存儲過程名RECOMPILE3．通過系統(tǒng)存儲過程設(shè)定重編譯通過系統(tǒng)存儲過程sp_recompile設(shè)定重新編譯標(biāo)記，使存儲過程在下次運行時重新編譯。其語法格式如下：EXECsp_recompile數(shù)據(jù)庫對象1/9/2023231/9/20232310．3．3修改和刪除存儲過程1．修改存儲過程存儲過程的修改是由ALTER語句來完成的，基本語法如下：ALTERPROCEDURE存儲過程名[WITHENCRYPTION][WITHRECOMPILE]ASSQL語句1/9/20232410．3．3修改和刪除存儲過程1．修改存儲過程1/9/2【例10.7】修改例10.1的存儲過程,對其進(jìn)行加密alterPROCEDURExsWITHENCRYPTIONASSELECT銷售編號,商品名稱,sell.數(shù)量as銷售數(shù)量FROMgoods,sellWHEREgoods.商品編號=sell.商品編號1/9/202325【例10.7】修改例10.1的存儲過程,對其進(jìn)行加密alt2．刪除存儲過程存儲過程的刪除是通過DROP語句來實現(xiàn)的，在SQLServerManagementStudio的“對象資源管理器”中也同樣可以進(jìn)行刪除。命令方式刪除存儲過程的方法也很簡單.語法格式:DROPPROCEDURE存儲過程名【例10.8】刪除例10.1中創(chuàng)建的存儲過程alterPROCEDURExs1/9/2023262．刪除存儲過程alterPROCEDURExs1/張三李四王五趙二王三宋二劉五插入刪除觸發(fā)器觸發(fā)趙二退休

趙二員工表退休員工表觸發(fā)器是在對表進(jìn)行插入、更新或刪除操作時自動執(zhí)行的存儲過程觸發(fā)器通常用于強制業(yè)務(wù)規(guī)則觸發(fā)器是一種高級約束，可以定義比用CHECK約束更為復(fù)雜的約束可執(zhí)行復(fù)雜的SQL語句（if/while/case）可引用其它表中的列觸發(fā)器定義在特定的表上，與表相關(guān)自動觸發(fā)執(zhí)行不能直接調(diào)用是一個事務(wù)（可回滾）1/9/202327張三李四王五趙二王三宋二劉五插入刪除觸發(fā)器觸發(fā)趙二退休趙二張三開戶1000元,李四開戶1元

10.4觸發(fā)器的概念10．4．1基本概念2、為什么需要觸發(fā)器為什么需要觸發(fā)器(TRIGGER)呢？典型的應(yīng)用就是銀行的取款機系統(tǒng)帳戶信息表bank

交易信息表transInfo

張三取錢200

問題：沒有自動修改張三的余額最優(yōu)的解決方案就是采用觸發(fā)器：它是一種特殊的存儲過程也具備事務(wù)的功能它能在多表之間執(zhí)行特殊的業(yè)務(wù)規(guī)則1/9/202328張三開戶1000元,李四開戶1元10.4觸發(fā)器的概念10．4．2觸發(fā)器的功能SQLServer2005提供了兩種方法來保證數(shù)據(jù)的有效性和完整性：約束和觸發(fā)器。觸發(fā)器的常用功能如下。（1）完成更復(fù)雜的數(shù)據(jù)約束：觸發(fā)器可以實現(xiàn)比約束更為復(fù)雜的數(shù)據(jù)約束。（2）檢查SQL所做的操作是否允許：觸發(fā)器可以檢查SQL所做的操作是否被允許。1/9/20232910．4．2觸發(fā)器的功能1/9/202329（3）修改其他數(shù)據(jù)表里的數(shù)據(jù)：當(dāng)一個SQL語句對數(shù)據(jù)表進(jìn)行操作的時候，觸發(fā)器可以根據(jù)SQL語句的操作情況來對另一個數(shù)據(jù)表進(jìn)行操作。（4）調(diào)用更多的存儲過程：約束是不能調(diào)用存儲過程的，但觸發(fā)器本身就是一種存儲過程，而存儲過程是可以嵌套調(diào)用的，所以觸發(fā)器也可能調(diào)用一個或多個存儲過程。（5）返回自定義的錯誤信息：約束只能通過標(biāo)準(zhǔn)的系統(tǒng)錯誤信息來傳遞錯誤信息，如果應(yīng)用程序要求使用自定義信息和較為復(fù)雜的錯誤處理，則必須使用觸發(fā)器。（6）更改原本要操作的SQL語句：觸發(fā)器可以修改原本要操作的SQL語句。（7）防止數(shù)據(jù)表結(jié)構(gòu)更改或數(shù)據(jù)表被刪除：為了保護(hù)已經(jīng)建立好的數(shù)據(jù)表，觸發(fā)器可以在接收到以DROP或ALTER開頭的語句后，不對數(shù)據(jù)表的結(jié)構(gòu)做任何操作。1/9/202330（3）修改其他數(shù)據(jù)表里的數(shù)據(jù)：當(dāng)一個SQL語句對數(shù)據(jù)表進(jìn)行操10．4．3觸發(fā)器的類型1.DML觸發(fā)器DML觸發(fā)器是當(dāng)數(shù)據(jù)庫服務(wù)器中發(fā)生數(shù)據(jù)操縱語言DML事件時執(zhí)行的特殊存儲過程，如INSERT、UPDATE等。2.DDL觸發(fā)器DDL觸發(fā)器是當(dāng)數(shù)據(jù)庫服務(wù)器中發(fā)生數(shù)據(jù)定義語言DDL事件時執(zhí)行的特殊存儲過程，如CREATE、ALTER等。1/9/20233110．4．3觸發(fā)器的類型1.DML觸發(fā)器1/9/2010.5創(chuàng)建和應(yīng)用DML觸發(fā)器10．5．1DML觸發(fā)器的類型1．AFTER觸發(fā)器這類觸發(fā)器是在記錄已經(jīng)改變之后，才會被激活執(zhí)行，它主要是用于記錄變更后的處理或檢查，一旦發(fā)現(xiàn)錯誤，也可以用ROLLBACKTRANSACTION語句來回滾本次的操作。2．INSTEADOF觸發(fā)器這類觸發(fā)器一般是用來取代原本要進(jìn)行的操作，在記錄變更之前發(fā)生的，它并不去執(zhí)行原來的SQL語句里的操作，而是去執(zhí)行觸發(fā)器本身所定義的操作。1/9/20233210.5創(chuàng)建和應(yīng)用DML觸發(fā)器10．5．1DML觸發(fā)10．5．2DML觸發(fā)器的工作原理在SQLServer2005中，為每個DML觸發(fā)器定義了兩個特殊的表，一個是Inserted表，一個是Deleted表。

修改操作inserted表deleted表增加(INSERT)記錄存放新增的記錄------刪除(DELETE)記錄-----存放被刪除的記錄修改(UPDATE)記錄存放更新后的記錄存放更新前的記錄1/9/20233310．5．2DML觸發(fā)器的工作原理修改操作inserteInserted表里存放的是更新前的記錄：對于插入記錄操作來說，Inserted表里存儲的是要插入的數(shù)據(jù)；對于更新記錄的操作來說，Inserted表里存放的是要更新的記錄。Deleted表里存放的是更新后的記錄：對于更新記錄操作來說，Deleted表里存放的是更新前的記錄；對于刪除記錄操作來說，Deleted表里存儲的是被刪除的舊記錄。1/9/202334Inserted表里存放的是更新前的記錄10．5．3創(chuàng)建DML觸發(fā)器的注意事項（1）CREATETRIGGER語句必須是批處理中的第一個語句，該語句后面的所有語句都被解釋為CREATETRIGGER語句定義的一部分。（2）創(chuàng)建DML觸發(fā)器的權(quán)限默認(rèn)分配給表的所有者，且不能將該權(quán)限轉(zhuǎn)授給其他用戶。（3）DML可以引用當(dāng)前數(shù)據(jù)庫以外的對象，但只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建DML觸發(fā)器。（4）不能對系統(tǒng)表或臨時表創(chuàng)建DML觸發(fā)器（5）對于含有DELETE或UPDATE操作定義的外鍵表，不能定義INSTEADOF觸發(fā)器。1/9/20233510．5．3創(chuàng)建DML觸發(fā)器的注意事項（1）CREATE10．5．3創(chuàng)建觸發(fā)器CREATETRIGGER觸發(fā)器名ON{表|視圖}[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]}[NOTFORREPLICATION]AS[{IFUPDATE(列名)[{AND|OR}UPDATE(列名)][…n]}]SQL語句1/9/20233610．5．3創(chuàng)建觸發(fā)器CREATETRIGGER觸（1）WITHENCRYPTION。加密CREATETRIGGER語句文本的條目。（2）FOR|AFTER。FOR與AFTER同義，指定觸發(fā)器只有在觸發(fā)器SQL語句中指定的所有操作都已成功后才激發(fā)。所有的引用級聯(lián)操作和約束檢查也必須成功完成后，才能執(zhí)行此觸發(fā)器，即為后觸發(fā)。（3）INSTEADOF。指定執(zhí)行觸發(fā)器而不執(zhí)行造成觸發(fā)的SQL語句，從而替代造成觸發(fā)的語句。在表或視圖上，每個INSERT、UPDATE或DELETE語句只能定義一個INSTEADOF觸發(fā)器，即替代觸發(fā)。（4）[INSERT][,][UPDATE][,][DELETE]。是指定在表上執(zhí)行哪些數(shù)據(jù)修改語句時將激活觸發(fā)器的關(guān)鍵字。必須至少指定一個選項。在觸發(fā)器定義中允許使用任意順序組合的這些關(guān)鍵字。當(dāng)進(jìn)行觸發(fā)條件的操作時（INSERT、UPDATE或DELETE），將執(zhí)行SQL語句中指定的觸發(fā)器操作。1/9/2023371/9/202337（5）NOTFORREPLICATION。表示當(dāng)復(fù)制進(jìn)程更改觸發(fā)器所涉及的表時，不要執(zhí)行該觸發(fā)器。（6）IFUPDATE(列名)。測試在指定的列上進(jìn)行的INSERT或UPDATE操作，不能用于DELETE操作，可以指定多列。因為已經(jīng)在ON子句中指定了表名，所以在IFUPDATE子句中的列名前不要包含表名。若要測試在多個列上進(jìn)行的INSERT或UPDATE操作，要分別單獨地指定UPDATE(列名)子句。在INSERT操作中IFUPDATE將返回TRUE值。1/9/202338（5）NOTFORREPLICATION。表示當(dāng)復(fù)制進(jìn)程1．UPDATE觸發(fā)器【例10.12】創(chuàng)建一個觸發(fā)器,當(dāng)有人試圖更新SALES數(shù)據(jù)庫中g(shù)oods表的商品編號或進(jìn)貨員工編號時,利用觸發(fā)器產(chǎn)生提示信息.并取消修改操作.--創(chuàng)建觸發(fā)器CREATETRIGGERupdate_trigONgoodsFORupdateASIFUPDATE(商品編號)ORUPDATE(進(jìn)貨員工編號)BEGINRAISERROR('商品編號或進(jìn)貨員工編號不能進(jìn)行修改!',7,2)ROLLBACKTRANSACTIONEND1/9/2023391．UPDATE觸發(fā)器CREATETRIGGERup--測試觸發(fā)器UpdategoodsSet商品編號=120Where數(shù)量=81/9/202340--測試觸發(fā)器Updategoods1/9/2023402．INSERT觸發(fā)器【例10.11】創(chuàng)建一個AFTERINSERT觸發(fā)器,當(dāng)在SALES數(shù)據(jù)庫的employees表中插入一條新員工記錄時,如果不是”采購部”,”財務(wù)部”,”銷售部”或”庫存部”的員工,則撤消該插入操作,并返回出錯消息.--創(chuàng)建觸發(fā)器1/9/2023412．INSERT觸發(fā)器--創(chuàng)建觸發(fā)器1/9/202341CREATETRIGGERinsert_trigONemployeesFORinsertASDeclare@departvarchar(16)Select@depart=employees.部門fromemployees,insertedWhereemployees.編號=inserted.編號If@depart<>'采購部'or@depart<>'財務(wù)部'or@depart<>'銷售部'or@depart<>'庫存部'BEGINROLLBACKTRANSACTIONRAISERROR('不能插入非本公司設(shè)定部門的員工信息!',16,10)END1/9/202342CREATETRIGGERinsert_trig1/--測試觸發(fā)器Insertemployess(編號,姓名,性別,部門,電話,地址)Values(‘1511’,’楊過’,’人事部’,’1201200’,’.南昌市廣場南路156號’)1/9/202343--測試觸發(fā)器Insertemployess(編號,姓名3．DELETE觸發(fā)器【例10.13】在sales數(shù)據(jù)庫的emplyoees表和sell表之間具有邏輯上的主外鍵關(guān)系,要求當(dāng)刪除或更新員工記錄的時候,要觸發(fā)觸發(fā)器update_delete_trig,在sell表中也刪除或更新相對應(yīng)的記錄行。--創(chuàng)建觸發(fā)器1/9/2023443．DELETE觸發(fā)器1/9/202344CREATETRIGGERupdate_delete_trigONemployeesFORupdate,deleteASDeclare@delcountintDeclare@empidchar(6)--更新Ifupdate(編號)BeginUpdatesellSet售貨員工編號=(select編號frominserted)Where售貨員工編號in(select編號fromdeleted)End1/9/202345CREATETRIGGERupdate_delete_--刪除Select@delcount=count(*)fromdeletedIf@delcount>0BeginSelect@empid=編號fromdeletedDeletefromsellwhere售貨員工編號=@empidEND1/9/202346--刪除1/9/2023466．觸發(fā)器案例分析1(1)問題：解決上述的銀行取款問題：當(dāng)向交易信息表(transInfo)中插入一條交易信息時，我們應(yīng)自動更新對應(yīng)帳戶的余額。(2)分析：在交易信息表上創(chuàng)建INSERT觸發(fā)器從inserted臨時表中獲取插入的數(shù)據(jù)行根據(jù)交易類型(transType)字段的值是存入/支取，增加/減少對應(yīng)帳戶的余額。1/9/2023476．觸發(fā)器案例分析1(1)問題：解決上述的銀行取款問CREATETRIGGERtrig_transInfoONtransInfo

FORINSERT

ASDECLARE@typechar(4),@outMoneyMONEYDECLARE@myCardIDchar(10),@balanceMONEYSELECT@type=transType,@outMoney=transMoney，@myCardID=cardIDFROMinsertedIF(@type='支取')UPDATEbankSETcurrentMoney=currentMoney-@outMoneyWHEREcardID=@myCardIDELSEUPDATEbankSETcurrentMoney=currentMoney+@outMoneyWHEREcardID=@myCardID…..GO從inserted表中獲取交易類型、交易金額等根據(jù)交易類型，減少或增加對應(yīng)卡號的余額(3)實現(xiàn)方法1/9/202348CREATETRIGGERtrig_transInfo6．觸發(fā)器案例分析2如果你是圖書管數(shù)據(jù)庫管理員,現(xiàn)在有Student學(xué)生表和BorrowRecord學(xué)生借書記錄表,現(xiàn)有兩需求要你去實現(xiàn)其功能:(1)、如果我更改了學(xué)生的學(xué)號,我希望他的借書記錄仍然與這個學(xué)生相關(guān)(也就是同時更改借書記錄表的學(xué)號)(2)、如果該學(xué)生已經(jīng)畢業(yè)，我希望刪除他的學(xué)號的同時，也刪除它的借書記錄。

1/9/2023496．觸發(fā)器案例分析2如果你是圖書管數(shù)據(jù)庫管理分析如何實現(xiàn)功能1:A在哪張表中創(chuàng)建觸發(fā)器?STUDENTB通過什么事件觸發(fā)?UPDATEC事件觸發(fā)后所要做的事情?ifUpdate(StudentID)BEIGINUpdateBorrowRecord

SetStudentID=i.StudentID

FromBorrowRecordbr,Deleted

d,Insertedi

END1/9/202350分析如何實現(xiàn)功能1:1/9/202350分析如何實現(xiàn)功能2:A在哪張表中創(chuàng)建觸發(fā)器?STUDENTB通過什么事件觸發(fā)?deleteC事件觸發(fā)后所要做的事情?

DeleteBorrowRecord

FromBorrowRecordbr,Deltedd

Wherebr.StudentID=d.StudentID

1/9/202351分析如何實現(xiàn)功能2:1/9/2023515．INSTEADOF觸發(fā)器如果視圖的數(shù)據(jù)來自于多個基表，則必須使用INSTAEDOF觸發(fā)器支持引用表中的數(shù)據(jù)的插入、更新和刪除操作。如果視圖的列為以下幾種情況之一：基表中的計算列基表中的標(biāo)識列具有timestamp數(shù)據(jù)類型的基表列該視圖的INSERT語句必須為這些列指定值，INSTEADOF觸發(fā)器在構(gòu)成將值插入基表的INSERT語句時，會忽略指定的值。1/9/2023525．INSTEADOF觸發(fā)器1/9/20235210.6觸發(fā)器的管理與維護(hù)10．6．1查看觸發(fā)器的定義信息10．6．2修改和刪除觸發(fā)器1．修改觸發(fā)器ALTERTRIGGER觸發(fā)器名ON{表|視圖}[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]}[NOTFORREPLICATION]AS[{IFUPDATE(列名)[{AND|OR}UPDATE(列名)][…n]}]SQL語句1/9/20235310.6觸發(fā)器的管理與維護(hù)1/9/202353【例10.13】修改例10.12的觸發(fā)器,對其進(jìn)行加密.ALTERTRIGGERupdate_trigONgoodsWITHENCRYPTIONFORupdateASIFUPDATE(商品編號)ORUPDATE(進(jìn)貨員工編號)BEGINRAISERROR('商品編號或進(jìn)貨員工編號不能進(jìn)行修改!',7,2)ROLLBACKTRANSACTIONEND1/9/202354【例10.13】修改例10.12的觸發(fā)器,對其進(jìn)行加密.AL2．刪除觸發(fā)器使用DROPTRIGGER<觸發(fā)器名>命令，即可刪除觸發(fā)器。【例10.13】刪除例10.12創(chuàng)建的觸發(fā)器DROPTRIGGERupdate_trig1/9/2023552．刪除觸發(fā)器DROPTRIGGERupdate_tr10．6．3禁止或啟用觸發(fā)器使用以下語句可以禁用或啟用指定表上的某些觸發(fā)器或所有觸發(fā)器。禁止和啟用觸發(fā)器的語法格式如下。ALTERTABLE表名{ENABLE|DISABLE}TRIGGER{ALL|觸發(fā)器名[,…n]}1/9/20235610．6．3禁止或啟用觸發(fā)器使用以下語【例10.14】禁用或啟用例10.11創(chuàng)建的觸發(fā)器--禁用觸發(fā)器ALTERTABLEemployeesDISABLETRIGGERinsert_trig--啟用觸發(fā)器ALTERTABLEemployeesENABLETRIGGERinsert_trig1/9/202357【例10.14】禁用或啟用例10.11創(chuàng)建的觸發(fā)器ALTER10.7DDL觸發(fā)器一般來說，在以下幾種情況下可以使用DDL觸發(fā)器。（1）防止數(shù)據(jù)庫架構(gòu)進(jìn)行某些修改。（2）防止數(shù)據(jù)庫或數(shù)據(jù)表被誤操作而刪除。（3）希望數(shù)據(jù)庫發(fā)生某種情況以響應(yīng)數(shù)據(jù)庫架構(gòu)中的更改。（4）要記錄數(shù)據(jù)庫架構(gòu)的更改或事件。僅在運行DDL觸發(fā)器的DDL語句后，DDL觸發(fā)器才會激發(fā)。DDL觸發(fā)器無法作為INSTEADOF觸發(fā)器而使用。1/9/20235810.7DDL觸發(fā)器一般來說，在以下10．7．1創(chuàng)建DDL觸發(fā)器創(chuàng)建DDL觸發(fā)器的語法格式如下：CREATETRIGGER觸發(fā)器名ON{服務(wù)器|數(shù)據(jù)庫}[WITHENCRYPTION]{FOR|AFTER}{DDL語句名稱}ASSQL語句1/9/20235910．7．1創(chuàng)建DDL觸發(fā)器創(chuàng)建DDL觸發(fā)器的語法格式【例10.16】建立用于保護(hù)數(shù)據(jù)庫sales中的數(shù)據(jù)表不被刪除的觸發(fā)器。CREATETRIGGERdis_drop_tableONsalesFORDROP_TABLEASBEGINRAISERROR('對不起，sales數(shù)據(jù)庫中的表不能刪除',16,10)END1/9/202360【例10.16】建立用于保護(hù)數(shù)據(jù)庫sales中的數(shù)據(jù)表不被刪10．7．2查看和修改DDL觸發(fā)器（1）作用在當(dāng)前SQLServer服務(wù)器上的DDL觸發(fā)器所在的位置,選擇所在的SQLServer服務(wù)器上，定位到“服務(wù)器對象”中的“觸發(fā)器”，在“摘要”對話框中就可以看到所有作用在當(dāng)前SQLServer服務(wù)器上的DDL觸發(fā)器。（2）作用在當(dāng)前數(shù)據(jù)庫中的DDL觸發(fā)器所在位置在SQLServer服務(wù)器上，通過“數(shù)據(jù)庫”選擇所在的數(shù)據(jù)庫，然后定位到“可編程性”中的“數(shù)據(jù)庫觸發(fā)器”，在摘要對話框中就可以看到所有的當(dāng)前數(shù)據(jù)庫中的DDL觸發(fā)器。1/9/20236110．7．2查看和修改DDL觸發(fā)器1/9/20236110.8事務(wù)10．8．1基本概念事務(wù)是作為單個邏輯工作單元執(zhí)行的一系列操作。這一系列操作或者都被執(zhí)行，或者都不被執(zhí)行。1/9/20236210.8事務(wù)10．8．1基本概念1/9/202362事務(wù)作為一個邏輯工作單元有4個屬性，稱為ACID（原子性、一致性、隔離性和持久性）屬性。（1）原子性。事務(wù)必須是原子工作單元，對于其數(shù)據(jù)修改，要么全都執(zhí)行，要么全都不執(zhí)行。（2）一致性。事務(wù)在完成時，必須使所有的數(shù)據(jù)都保持一致狀態(tài)。在相關(guān)數(shù)據(jù)庫中，所有規(guī)則都應(yīng)用于事務(wù)的修改，以保持所有數(shù)據(jù)的完整性。事務(wù)結(jié)束時，所有的內(nèi)部數(shù)據(jù)結(jié)構(gòu)都必須是正確的。（3）隔離性。由并發(fā)事務(wù)所做的修改必須與任何其他并發(fā)事務(wù)所做的修改隔離。保證事務(wù)查看數(shù)據(jù)時數(shù)據(jù)所處的狀態(tài)，只能是一并發(fā)事務(wù)修改它之前的狀態(tài)或者是另一事務(wù)修改它之后的狀態(tài)，而不能查看中間狀態(tài)的數(shù)據(jù)。（4）持久性。事務(wù)完成之后對系統(tǒng)的影響是永久的。1/9/2023631/9/202363SQLServer有以下3種事務(wù)模式。（1）自動提交事務(wù)。這是SQLServer的默認(rèn)模式。每個單獨的SQL語句都是一個事務(wù)，并在其完成后提交。不必指定任何語句控制事務(wù)。（2）顯式事務(wù)。每個事務(wù)均以BEGINTRANSACTION語句顯式開始，以COMMIT或ROLLBACK語句顯式結(jié)束。（3）隱性事務(wù)。通過API函數(shù)或Transact-SQL的SETIMPLICIT_TRANSACTIONON語句，將隱性事務(wù)模式設(shè)置為打開。這樣在前一個事務(wù)結(jié)束時新事務(wù)隱式啟動，但每個事務(wù)仍以COMMIT或ROLLBACK語句顯式結(jié)束。1/9/202364SQLServer有以下3種事務(wù)模式。1/9/20236410．8．2事務(wù)應(yīng)用事務(wù)組織結(jié)構(gòu)的一般形式如下。（1）定義一個事務(wù)的開始：BEGINTRANSACTION。（2）提交一個事務(wù)：COMMITTRANSACTION。（3）回滾事務(wù)：ROLLBACKTRANSACTION。

BEGINTRANSACTION代表一個事務(wù)的開始點，事務(wù)執(zhí)行到COMMITTRANSACTION提交語句后結(jié)束事務(wù)，完成對數(shù)據(jù)庫所做的永久改動。如果遇上錯誤用ROLLBACKTRANSACTION語句可以撤消所有改動。1/9/20236510．8．2事務(wù)應(yīng)用事務(wù)組織結(jié)構(gòu)的一般形式如下。1/9/如果在事務(wù)活動時由于任何原因（如客戶端應(yīng)用程序終止；客戶端計算機關(guān)閉或重新啟動；客戶端網(wǎng)絡(luò)連接中斷等）中斷了客戶端和SQLServer實例之間的通信，SQLServer實例將在收到網(wǎng)絡(luò)或操作系統(tǒng)發(fā)出的中斷通知時自動回滾事務(wù)。在所有這些錯誤情況下，將回滾任何未完成的事務(wù)以保護(hù)數(shù)據(jù)的完整性和一致性。1/9/202366如果在事務(wù)活動時由于任何原因（如客戶端應(yīng)本章小結(jié)1.存儲過程是一種數(shù)據(jù)庫對象，是存儲在服務(wù)器上的一組預(yù)定義的SQL語句集合。創(chuàng)建存儲過程并將編譯好的版本存儲在高速緩存中，可以加快程序的執(zhí)行效率。存儲過程可以有輸入、輸出參數(shù)，可以返回結(jié)果集以及返回值。通過本章的學(xué)習(xí)，應(yīng)掌握各種存儲過程的創(chuàng)建、執(zhí)行、修改和刪除方法。1/9/202367本章小結(jié)1.存儲過程是一種數(shù)據(jù)庫對象，是存儲在服務(wù)器上的一本章小結(jié)2.觸發(fā)器是一種特殊的存儲過程，當(dāng)有INSERT、UPDATE和DELETE操作影響到觸發(fā)器所保護(hù)的數(shù)據(jù)時，觸發(fā)器就會自動觸發(fā)執(zhí)行。觸發(fā)器主要用于加強業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性，能實現(xiàn)比CHECK約束更復(fù)雜的檢查，一般在使用觸發(fā)器之前應(yīng)優(yōu)先考慮使用約束，只在必要的時候才使用觸發(fā)器。1/9/202368本章小結(jié)2.觸發(fā)器是一種特殊的存儲過程，當(dāng)有INSERT、習(xí)題P2161、2、3、5、6、71/9/202369習(xí)題P2161/9/202369第10章存儲過程和觸發(fā)器10.1存儲過程的概念10.2建立和執(zhí)行存儲過程10.3存儲過程的管理與維護(hù)10.4觸發(fā)器的概念10.5創(chuàng)建和應(yīng)用DML觸發(fā)器10.6觸發(fā)器的管理與維護(hù)10.7DDL觸發(fā)器10.8事務(wù)1/9/202370第10章存儲過程和觸發(fā)器10.1存儲過程的概念1/9/本章重點存儲過程的概念、分類存儲過程的創(chuàng)建、執(zhí)行觸發(fā)器的概念、分類觸發(fā)器的創(chuàng)建、觸發(fā)DELETED與INSERTED表1/9/202371本章重點存儲過程的概念、分類1/9/20232本章難點有參存儲過程的創(chuàng)建、執(zhí)行觸發(fā)器的觸發(fā)時機DELETED與INSERTED表1/9/202372本章難點有參存儲過程的創(chuàng)建、執(zhí)行1/9/2023310.1存儲過程的概念10．1．1基本概念存儲過程是一組編譯在單個執(zhí)行計劃中的Transact-SQL語句，將一些固定的操作集中起來交給SQLServer數(shù)據(jù)庫服務(wù)器完成，以實現(xiàn)某個任務(wù)。10．1．2存儲過程的優(yōu)點（1）與其他應(yīng)用程序共享應(yīng)用程序邏輯，因而確保了數(shù)據(jù)訪問和修改的一致性。（2）防止數(shù)據(jù)庫中表的細(xì)節(jié)暴露給用戶。（3）提供安全機制。（4）改進(jìn)性能。（5）減少網(wǎng)絡(luò)流量。1/9/20237310.1存儲過程的概念10．1．1基本概念1/9/210．1．3存儲過程的類型存儲過程分為三類：系統(tǒng)提供的存儲過程系統(tǒng)提供的存儲過程系統(tǒng)存儲過程的名字都以“sp_”為前綴例：sp_bindefault，sp_bindrule，sp_help，sp_helpdb,sp_helpindex等用戶定義的存儲過程用戶定義的存儲過程是由用戶為完成某一特定功能而編寫的存儲過程。存儲在創(chuàng)建時的數(shù)據(jù)庫中。擴展存儲過程：擴展存儲過程是用來調(diào)用操作系統(tǒng)提供的功能。1/9/20237410．1．3存儲過程的類型1/9/20235系統(tǒng)存儲過程說明sp_databases列出服務(wù)器上的所有數(shù)據(jù)庫。sp_helpdb報告有關(guān)指定數(shù)據(jù)庫或所有數(shù)據(jù)庫的信息sp_renamedb更改數(shù)據(jù)庫的名稱sp_tables返回當(dāng)前環(huán)境下可查詢的對象的列表sp_columns回某個表列的信息sp_help查看某個表的所有信息sp_helpconstraint查看某個表的約束sp_helpindex查看某個表的索引sp_stored_procedures列出當(dāng)前環(huán)境中的所有存儲過程。sp_password添加或修改登錄帳戶的密碼。sp_helptext顯示默認(rèn)值、未加密的存儲過程、用戶定義的存儲過程、觸發(fā)器或視圖的實際文本。1/9/202375系統(tǒng)存儲過程說明sp_databases列出服務(wù)器上的所有數(shù)EXECsp_databasesEXECsp_renamedb'Northwind','Northwind1'USEstuDBGOEXECsp_tablesEXECsp_columnsstuInfoEXECsp_helpstuInfoEXECsp_helpconstraintstuInfoEXECsp_helpindexstuMarksEXECsp_helptext'view_stuInfo_stuMarks'EXECsp_stored_procedures

修改數(shù)據(jù)庫的名稱(單用戶訪問)列出當(dāng)前系統(tǒng)中的數(shù)據(jù)庫當(dāng)前數(shù)據(jù)庫中查詢的對象的列表返回某個表列的信息查看表stuInfo的信息查看表stuInfo的約束查看表stuMarks的索引查看視圖的語句文本查看當(dāng)前數(shù)據(jù)庫中的存儲過程演示：常用的存儲過程1/9/202376EXECsp_databases修改數(shù)據(jù)庫的名稱(單用戶訪10.2建立和執(zhí)行存儲過程簡單存儲過程類似于將一組SQL語句起個名字，然后就可以在需要時反復(fù)調(diào)用。復(fù)雜一些的則要有輸入和輸出參數(shù)。10．2．1創(chuàng)建和執(zhí)行簡單存儲過程創(chuàng)建存儲過程的基本語法如下：CREATEPROC存儲過程名[WITHENCRYPTION][WITHRECOMPILE]ASSQL語句其中各參數(shù)如下：[WITHENCRYPTION]：對存儲過程進(jìn)行加密。[WITHRECOMPILE]：對存儲過程重新編譯。1/9/20237710.2建立和執(zhí)行存儲過程簡單存儲【例10.1】創(chuàng)建一個無參數(shù)的存儲過程，在SALES數(shù)據(jù)庫中,創(chuàng)建存儲過程xs,查詢銷售編號,商品名稱,數(shù)量。CREATEPROCEDURExsASSELECT銷售編號,商品名稱,sell.數(shù)量as銷售數(shù)量FROMgoods,sellWHEREgoods.商品編號=sell.商品編號1/9/202378【例10.1】創(chuàng)建一個無參數(shù)的存儲過程，在SALES數(shù)據(jù)庫中10．2．2存儲過程的執(zhí)行執(zhí)行存儲過程的基本語法如下：EXEC[UTE]存儲過程名同時EXECUTE命令除了可以執(zhí)行存儲過程外還可以執(zhí)行存放Transact-SQL語句的字符串變量，或直接執(zhí)行Transact-SQL語句字符串。此時EXECUTE語句的語法格式如下。EXECUTE({@字符串變量|[N]'SQL語句字符串'}[+...n]其中“@字符串變量”是局部字符串變量名，最大值為服務(wù)器的可用內(nèi)存。[N]'SQL語句字符串'的語句字符串是一個由SQL語句構(gòu)成的字符串常量。如果包含N，則該字符串將解釋為nvarchar數(shù)據(jù)類型。EXECxs1/9/20237910．2．2存儲過程的執(zhí)行EXECxs1/9/202【例10.2】建立一個批處理，查詢相應(yīng)表中的信息。DECLARE@tab_namevarchar(20)SET@tab_name='xs'EXECUTE('SELECT*FROM'+@tab_name)1/9/202380【例10.2】建立一個批處理，查詢相應(yīng)表中的信息。DECLA10．2．3帶輸入?yún)?shù)的存儲過程1．建立存儲過程一個存儲過程可以帶一個或多個參數(shù)，輸入?yún)?shù)是指由調(diào)用程序向存儲過程傳遞的參數(shù)，它們在創(chuàng)建存儲過程語句中被定義，在執(zhí)行存儲過程中給出相應(yīng)的參數(shù)值。聲明帶輸入?yún)?shù)的存儲過程的語法格式如下：CREATEPROCEDURE存儲過程名@參數(shù)名數(shù)據(jù)類型[=默認(rèn)值][，…n][WITHENCRYPTION][WITHRECOMPILE]ASSQL語句1/9/20238110．2．3帶輸入?yún)?shù)的存儲過程1/9/202312其中“@參數(shù)名”和定義局部變量一樣，必須以符號@為前綴，要指定數(shù)據(jù)類型，多個參數(shù)定義要用“，”隔開。在執(zhí)行存儲過程時該參數(shù)將由指定的參數(shù)值來代替，如果執(zhí)行時未提供該參數(shù)的參數(shù)值，則使用時須定義默認(rèn)值（默認(rèn)值可以是常量或空值null），否則將產(chǎn)生錯誤。1/9/202382其中“@參數(shù)名”和定義局部變量一樣，必須以符號@為前綴，要指

【例10.3】創(chuàng)建一個帶輸入?yún)?shù)的存儲過程PROC_GOODS,查詢指定員工所進(jìn)商品信息CREATEPROCproc_goods@員工編號char(6)=‘1001’AsSelect商品編號,商品名稱,生產(chǎn)廠商,進(jìn)貨價,零售價,數(shù)量,進(jìn)貨時間fromgoodsWhere進(jìn)貨員工編號=@員工編號

1/9/202383

【例10.3】創(chuàng)建一個帶輸入?yún)?shù)的存儲過程PROC_G2．執(zhí)行存儲過程在執(zhí)行存儲過程的語句中，有兩種方式傳遞參數(shù)值，分別是使用參數(shù)名傳遞參數(shù)值和按參數(shù)位置傳遞參數(shù)值。使用參數(shù)名傳遞參數(shù)值，是通過語句“@參數(shù)名=參數(shù)值”給參數(shù)傳遞值。當(dāng)存儲過程含有多個輸入?yún)?shù)時，對數(shù)值可以按任意順序給出，對于允許空值和具有默認(rèn)值的輸入?yún)?shù)可以不給參數(shù)值，其語法格式為：EXECUTE存儲過程名[@參數(shù)名=參數(shù)值][，…n]按參數(shù)位置傳遞參數(shù)值，不顯式地給出“@參數(shù)名”，而是按照參數(shù)定義的順序給出參數(shù)值。按位置傳遞參數(shù)時，也可以忽略允許為空值和有默認(rèn)值的參數(shù)，但不能因此破壞輸入?yún)?shù)的指定順序。必要時使用關(guān)鍵字“DEFAULT”作為參數(shù)值的占位。EXECproc_goods@員工編號=‘1002’或EXECproc_goods‘1002’1/9/2023842．執(zhí)行存儲過程EXECproc_goods@員工10．2．4帶輸出參數(shù)的存儲過程如果我們需要從存儲過程中返回一個或多個值，可以通過在創(chuàng)建存儲過程的語句中定義輸出參數(shù)來實現(xiàn)，為了使用輸出參數(shù)，需要在創(chuàng)建存儲過程的命令中使用OUTPUT關(guān)鍵字。聲明帶輸出參數(shù)的存儲過程的語法格式如下：CREATEPROCEDURE存儲過程名@參數(shù)名數(shù)據(jù)類型[VARYING][=默認(rèn)值]OUTPUT[，…n][WITHENCRYPTION][WITHRECOMPILE]ASSQL語句1/9/20238510．2．4帶輸出參數(shù)的存儲過程如果我注意：OUTPUT變量必須在定義存儲過程和使用該變量時都進(jìn)行定義。定義時的參數(shù)名和調(diào)用時的變量名不一定相同，不過數(shù)據(jù)類型和參數(shù)的位置必須匹配。1/9/202386注意：OUTPUT變量必須在定義存儲過程和使用該變量時都進(jìn)行【例10.5】創(chuàng)建一個帶有輸入和輸出函數(shù)的存儲過程proc_gno,查詢指定廠商指定名稱的商品對應(yīng)的商品編號CREATEPROCproc_gno@商品名稱varchar(20),@生產(chǎn)廠商varchar(30),@商品編號intoutputAsSelect@商品編號=商品編號FromgoodsWhere商品名稱=@商品名稱and生產(chǎn)廠商=@生產(chǎn)廠商

1/9/202387【例10.5】創(chuàng)建一個帶有輸入和輸出函數(shù)的存儲過程proc_執(zhí)行存儲過程Declare@商品編號intExecproc_gno‘打印機’,’惠普公司’,@商品編號outputPrint‘該商品編號為:’+cast(@商品編號aschar(6))1/9/202388執(zhí)行存儲過程Declare@商品編號int1/9/2010.3存儲過程的管理與維護(hù)10．3．1查看存儲過程的定義信息在SQLServerManagementStudio的“對象資源管理器”中，可以在要查看信息的存儲過程上單擊鼠標(biāo)右鍵，在快捷菜單中選擇“屬性”，彈出“存儲過程屬性”窗口.也可以通過系統(tǒng)存儲過程sp_helptext查看存儲過程的定義；通過sp_help查看存儲過程的參數(shù)；通過sp_depends查看存儲過程的相關(guān)性。1/9/20238910.3存儲過程的管理與維護(hù)10．3．1查看存儲過程【例10.7】在SQLServerManagementStudio服務(wù)器中新建查詢，使用系統(tǒng)存儲過程，查看例10.5中所創(chuàng)建存儲過程的定義、參數(shù)和相關(guān)性。EXECUTEsp_helptextproc_gnoEXECUTEsp_helpproc_gnoEXECUTEsp_dependsproc_gno1/9/202390【例10.7】在SQLServerManagement10．3．2存儲過程的重編譯存儲過程所采用的執(zhí)行計劃，只在編譯時優(yōu)化生成，以后便駐留在高速緩存中。當(dāng)用戶對數(shù)據(jù)庫新增了索引或其他影響數(shù)據(jù)庫邏輯結(jié)構(gòu)的更改后，已編譯的存儲過程執(zhí)行計劃可能會失去效率。通過對存儲過程進(jìn)行重新編譯，可以重新優(yōu)化存儲過程的執(zhí)行計劃。SQLServer為用戶提供了3種重新編譯的方法。1/9/20239110．3．2存儲過程的重編譯存儲過程1．在創(chuàng)建存儲過程時設(shè)定在創(chuàng)建存儲過程時，使用WITHRECOMPILE子句時SQLServer不將該存儲過程的查詢計劃保存在緩存中，而是在每次運行時重新編譯和優(yōu)化，并創(chuàng)建新的執(zhí)行計劃。2．在執(zhí)行存儲過程時設(shè)定通過在執(zhí)行存儲過程時設(shè)定重新編譯，可以讓SQLServer在執(zhí)行存儲過程時重新編譯該存儲過程，這一次執(zhí)行完成后，新的執(zhí)行計劃又被保存在緩存中。這樣用戶就可以根據(jù)需要進(jìn)行重新編譯。EXEC存儲過程名RECOMPILE3．通過系統(tǒng)存儲過程設(shè)定重編譯通過系統(tǒng)存儲過程sp_recompile設(shè)定重新編譯標(biāo)記，使存儲過程在下次運行時重新編譯。其語法格式如下：EXECsp_recompile數(shù)據(jù)庫對象1/9/2023921/9/20232310．3．3修改和刪除存儲過程1．修改存儲過程存儲過程的修改是由ALTER語句來完成的，基本語法如下：ALTERPROCEDURE存儲過程名[WITHENCRYPTION][WITHRECOMPILE]ASSQL語句1/9/20239310．3．3修改和刪除存儲過程1．修改存儲過程1/9/2【例10.7】修改例10.1的存儲過程,對其進(jìn)行加密alterPROCEDURExsWITHENCRYPTIONASSELECT銷售編號,商品名稱,sell.數(shù)量as銷售數(shù)量FROMgoods,sellWHEREgoods.商品編號=sell.商品編號1/9/202394【例10.7】修改例10.1的存儲過程,對其進(jìn)行加密alt2．刪除存儲過程存儲過程的刪除是通過DROP語句來實現(xiàn)的，在SQLServerManagementStudio的“對象資源管理器”中也同樣可以進(jìn)行刪除。命令方式刪除存儲過程的方法也很簡單.語法格式:DROPPROCEDURE存儲過程名【例10.8】刪除例10.1中創(chuàng)建的存儲過程alterPROCEDURExs1/9/2023952．刪除存儲過程alterPROCEDURExs1/張三李四王五趙二王三宋二劉五插入刪除觸發(fā)器觸發(fā)趙二退休

趙二員工表退休員工表觸發(fā)器是在對表進(jìn)行插入、更新或刪除操作時自動執(zhí)行的存儲過程觸發(fā)器通常用于強制業(yè)務(wù)規(guī)則觸發(fā)器是一種高級約束，可以定義比用CHECK約束更為復(fù)雜的約束可執(zhí)行復(fù)雜的SQL語句（if/while/case）可引用其它表中的列觸發(fā)器定義在特定的表上，與表相關(guān)自動觸發(fā)執(zhí)行不能直接調(diào)用是一個事務(wù)（可回滾）1/9/202396張三李四王五趙二王三宋二劉五插入刪除觸發(fā)器觸發(fā)趙二退休趙二張三開戶1000元,李四開戶1元

10.4觸發(fā)器的概念10．4．1基本概念2、為什么需要觸發(fā)器為什么需要觸發(fā)器(TRIGGER)呢？典型的應(yīng)用就是銀行的取款機系統(tǒng)帳戶信息表bank

交易信息表transInfo

張三取錢200

問題：沒有自動修改張三的余額最優(yōu)的解決方案就是采用觸發(fā)器：它是一種特殊的存儲過程也具備事務(wù)的功能它能在多表之間執(zhí)行特殊的業(yè)務(wù)規(guī)則1/9/202397張三開戶1000元,李四開戶1元10.4觸發(fā)器的概念10．4．2觸發(fā)器的功能SQLServer2005提供了兩種方法來保證數(shù)據(jù)的有效性和完整性：約束和觸發(fā)器。觸發(fā)器的常用功能如下。（1）完成更復(fù)雜的數(shù)據(jù)約束：觸發(fā)器可以實現(xiàn)比約束更為復(fù)雜的數(shù)據(jù)約束。（2）檢查SQL所做的操作是否允許：觸發(fā)器可以檢查SQL所做的操作是否被允許。1/9/20239810．4．2觸發(fā)器的功能1/9/202329（3）修改其他數(shù)據(jù)表里的數(shù)據(jù)：當(dāng)一個SQL語句對數(shù)據(jù)表進(jìn)行操作的時候，觸發(fā)器可以根據(jù)SQL語句的操作情況來對另一個數(shù)據(jù)表進(jìn)行操作。（4）調(diào)用更多的存儲過程：約束是不能調(diào)用存儲過程的，但觸發(fā)器本身就是一種存儲過程，而存儲過程是可以嵌套調(diào)用的，所以觸發(fā)器也可能調(diào)用一個或多個存儲過程。（5）返回自定義的錯誤信息：約束只能通過標(biāo)準(zhǔn)的系統(tǒng)錯誤信息來傳遞錯誤信息，如果應(yīng)用程序要求使用自定義信息和較為復(fù)雜的錯誤處理，則必須使用觸發(fā)器。（6）更改原本要操作的SQL語句：觸發(fā)器可以修改原本要操作的SQL語句。（7）防止數(shù)據(jù)表結(jié)構(gòu)更改或數(shù)據(jù)表被刪除：為了保護(hù)已經(jīng)建立好的數(shù)據(jù)表，觸發(fā)器可以在接收到以DROP或ALTER開頭的語句后，不對數(shù)據(jù)表的結(jié)構(gòu)做任何操作。1/9/202399（3）修改其他數(shù)據(jù)表里的數(shù)據(jù)：當(dāng)一個SQL語句對數(shù)據(jù)表進(jìn)行操10．4．3觸發(fā)器的類型1.DML觸發(fā)器DML觸發(fā)器是當(dāng)數(shù)據(jù)庫服務(wù)器中發(fā)生數(shù)據(jù)操縱語言DML事件時執(zhí)行的特殊存儲過程，如INSERT、UPDATE等。2.DDL觸發(fā)器DDL觸發(fā)器是當(dāng)數(shù)據(jù)庫服務(wù)器中發(fā)生數(shù)據(jù)定義語言DDL事件時執(zhí)行的特殊存儲過程，如CREATE、ALTER等。1/9/202310010．4．3觸發(fā)器的類型1.DML觸發(fā)器1/9/2010.5創(chuàng)建和應(yīng)用DML觸發(fā)器10．5．1DML觸發(fā)器的類型1．AFTER觸發(fā)器這類觸發(fā)器是在記錄已經(jīng)改變之后，才會被激活執(zhí)行，它主要是用于記錄變更后的處理或檢查，一旦發(fā)現(xiàn)錯誤，也可以用ROLLBACKTRANSACTION語句來回滾本次的操作。2．INSTEADOF觸發(fā)器這類觸發(fā)器一般是用來取代原本要進(jìn)行的操作，在記錄變更之前發(fā)生的，它并不去執(zhí)行原來的SQL語句里的操作，而是去執(zhí)行觸發(fā)器本身所定義的操作。1/9/202310110.5創(chuàng)建和應(yīng)用DML觸發(fā)器10．5．1DML觸發(fā)10．5．2DML觸發(fā)器的工作原理在SQLServer2005中，為每個DML觸發(fā)器定義了兩個特殊的表，一個是Inserted表，一個是Deleted表。

修改操作inserted表deleted表增加(INSERT)記錄存放新增的記錄------刪除(DELETE)記錄-----存放被刪除的記錄修改(UPDATE)記錄存放更新后的記錄存放更新前的記錄1/9/202310210．5．2DML觸發(fā)器的工作原理修改操作inserteInserted表里存放的是更新前的記錄：對于插入記錄操作來說，Inserted表里存儲的是要插入的數(shù)據(jù)；對于更新記錄的操作來說，Inserted表里存放的是要更新的記錄。Deleted表里存放的是更新后的記錄：對于更新記錄操作來說，Deleted表里存放的是更新前的記錄；對于刪除記錄操作來說，Deleted表里存儲的是被刪除的舊記錄。1/9/2023103Inserted表里存放的是更新前的記錄10．5．3創(chuàng)建DML觸發(fā)器的注意事項（1）CREATETRIGGER語句必須是批處理中的第一個語句，該語句后面的所有語句都被解釋為CREATETRIGGER語句定義的一部分。（2）創(chuàng)建DML觸發(fā)器的權(quán)限默認(rèn)分配給表的所有者，且不能將該權(quán)限轉(zhuǎn)授給其他用戶。（3）DML可以引用當(dāng)前數(shù)據(jù)庫以外的對象，但只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建DML觸發(fā)器。（4）不能對系統(tǒng)表或臨時表創(chuàng)建DML觸發(fā)器（5）對于含有DELETE或UPDATE操作定義的外鍵表，不能定義INSTEADOF觸發(fā)器。1/9/202310410．5．3創(chuàng)建DML觸發(fā)器的注意事項（1）CREATE10．5．3創(chuàng)建觸發(fā)器CREATETRIGGER觸發(fā)器名ON{表|視圖}[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]}[NOTFORREPLICATION]AS[{IFUPDATE(列名)[{AND|OR}UPDATE(列名)][…n]}]SQL語句1/9/202310510．5．3創(chuàng)建觸發(fā)器CREATETRIGGER觸（1）WITHENCRYPTION。加密CREATETRIGGER語句文本的條目。（2）FOR|AFTER。FOR與AFTER同義，指定觸發(fā)器只有在觸發(fā)器SQL語句中指定的所有操作都已成功后才激發(fā)。所有的引用級聯(lián)操作和約束檢查也必須成功完成后，才能執(zhí)行此觸發(fā)器，即為后觸發(fā)。（3）INSTEADOF。指定執(zhí)行觸發(fā)器而不執(zhí)行造成觸發(fā)的SQL語句，從而替代造成觸發(fā)的語句。在表或視圖上，每個INSERT、UPDATE或DELETE語句只能定義一個INSTEADOF觸發(fā)器，即替代觸發(fā)。（4）[INSERT][,][UPDATE][,][DELETE]。是指定在表上執(zhí)行哪些數(shù)據(jù)修改語句時將激活觸發(fā)器的關(guān)鍵字。必須至少指定一個選項。在觸發(fā)器定義中允許使用任意順序組合的這些關(guān)鍵字。當(dāng)進(jìn)行觸發(fā)條件的操作時（INSERT、UPDATE或DELETE），將執(zhí)行SQL語句中指定的觸發(fā)器操作。1/9/20231061/9/202337（5）NOTFORREPLICATION。表示當(dāng)復(fù)制進(jìn)程更改觸發(fā)器所涉及的表時，不要執(zhí)行該觸發(fā)器。（6）IFUPDATE(列名)。測試在指定的列上進(jìn)行的INSERT或UPDATE操作，不能用于DELETE操作，可以指定多列。因為已經(jīng)在ON子句中指定了表名，所以在IFUPDATE子句中的列名前不要包含表名。若要測試在多個列上進(jìn)行的INSERT或UPDATE操作，要分別單獨地指定UPDATE(列名)子句。在INSERT操作中IFUPDATE將返回TRUE值。1/9/2023107（5）NOTFORREPLICATION。表示當(dāng)復(fù)制進(jìn)程1．UPDATE觸發(fā)器【例10.12】創(chuàng)建一個觸發(fā)器,當(dāng)有人試圖更新SALES數(shù)據(jù)庫中g(shù)oods表的商品編號或進(jìn)貨員工編號時,利用觸發(fā)器產(chǎn)生提示信息.并取消修改操作.--創(chuàng)建觸發(fā)器CREATETRIGGERupdate_trigONgoodsFORupdateASIFUPDATE(商品編號)ORUPDATE(進(jìn)貨員工編號)BEGINRAISERROR('商品編號或進(jìn)貨員工編號不能進(jìn)行修改!',7,2)ROLLBACKTRANSACTIONEND1/9/20231081．UPDATE觸發(fā)器CREATETRIGGERup--測試觸發(fā)器UpdategoodsSet商品編號=120Where數(shù)量=81/9/2023109--測試觸發(fā)器Updategoods1/9/2023402．INSERT觸發(fā)器【例10.11】創(chuàng)建一個AFTERINSERT觸發(fā)器,當(dāng)在SALES數(shù)據(jù)庫的employees表中插入一條新員工記錄時,如果不是”采購部”,”財務(wù)部”,”銷售部”或”庫存部”的員工,則撤消該插入操作,并返回出錯消息.--創(chuàng)建觸發(fā)器1/9/20231102．INSERT觸發(fā)器--創(chuàng)建觸發(fā)器1/9/202341CREATETRIGGERinsert_trigONemployeesFORinsertASDeclare@departvarchar(16)Select@depart=employees.部門fromemployees,insertedWhereemployees.編號=inserted.編號If@depart<>'采購部'or@depart<>'財務(wù)部'or@depart<>'銷售部'or@depar