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

存儲過程與觸發(fā)器第1頁，共26頁，2023年，2月20日，星期四9.1存儲過程問題要把完成某功能的SQL做成類似C語言的函數(shù)，供需要時調(diào)用，如何做？1.什么是存儲過程？存儲過程是SQLServer服務(wù)器上一組預(yù)編譯的Transact-SQL語句，它們被集合在一起用于完成某項任務(wù)，它可以接受參數(shù)、返回參數(shù)值，并且可以嵌套調(diào)用。9.1.1存儲過程概述第2頁，共26頁，2023年，2月20日，星期四9.1.1存儲過程概述2.存儲過程的功能特點模塊化編程創(chuàng)建一個存儲過程存放在數(shù)據(jù)庫中后，就可以被其他程序反復使用。執(zhí)行速度快存儲過程第一次被執(zhí)行后，就駐留在內(nèi)存中。以后執(zhí)行就省去了重新分析、優(yōu)化、編譯的過程。減少網(wǎng)絡(luò)通信量有了存儲過程后，在網(wǎng)絡(luò)上只要一條語句就能執(zhí)行一個存儲過程。保證系統(tǒng)安全性通過隔離和加密的方法提高了數(shù)據(jù)庫的安全性，通過授權(quán)可以讓用戶只能執(zhí)行存儲過程而不能直接訪問數(shù)據(jù)庫對象。第3頁，共26頁，2023年，2月20日，星期四3.SQLServer存儲過程的類型包括：系統(tǒng)存儲過程用戶定義存儲過程臨時存儲過程擴展存儲過程9.1.1存儲過程概述第4頁，共26頁，2023年，2月20日，星期四系統(tǒng)存儲過程：是指由系統(tǒng)提供的存儲過程，主要存儲在master數(shù)據(jù)庫中并以sp_為前綴，。通過系統(tǒng)存儲過程，SQLServer中的許多管理性或信息性的活動都可以完成(例如使用sp_depends、sp_helptext可以了解數(shù)據(jù)庫對象、數(shù)據(jù)庫信息)。盡管系統(tǒng)存儲過程被放在master數(shù)據(jù)庫中，仍可以在其他數(shù)據(jù)庫中對其進行調(diào)用(調(diào)用時，不必在存儲過程名前加上數(shù)據(jù)庫名)。用戶自定義存儲過程：是由用戶創(chuàng)建并能完成某一特定功能(例如查詢用戶所需數(shù)據(jù)信息)的存儲過程。它處于用戶創(chuàng)建的數(shù)據(jù)庫中，存儲過程名前沒有前綴sp_。9.1.1存儲過程概述第5頁，共26頁，2023年，2月20日，星期四存儲過程和視圖的比較視圖存儲過程語句只能是SELECT語句可以包含程序流、邏輯以及SELECT語句輸入、返回結(jié)果不能接受參數(shù)，只能返回結(jié)果集可以有輸入輸出參數(shù)，也可以有返回值典型應(yīng)用多個表的連接查詢完成某個特定的較復雜的任務(wù)9.1.1存儲過程概述第6頁，共26頁，2023年，2月20日，星期四1.創(chuàng)建和執(zhí)行不帶參數(shù)的存儲過程（1）使用企業(yè)管理器創(chuàng)建存儲過程（2）使用CREATEPROCEDURE語句創(chuàng)建存儲過程CREATEPROC[EDURE][所有者.]存儲過程名

ASSQL語句[,...n]執(zhí)行：

[EXEC[UTE]]存儲過程名9.1.2存儲過程的創(chuàng)建與使用第7頁，共26頁，2023年，2月20日，星期四【例1】創(chuàng)建并執(zhí)行存儲過程proc_reader，查詢讀者姓名及其所在單位名稱。CREATEPROCproc_readerASSELECTReader.ReaderName,Department.DepartmentNameFROMReader,DepartmentWHEREReader.DepartmentNo=Department.DepartmentNoGOEXECproc_reader存儲過程創(chuàng)建后，存儲過程的名稱存放在sysobject表中，文本存放在syscomments表中。9.1.2存儲過程的創(chuàng)建與使用第8頁，共26頁，2023年，2月20日，星期四2.創(chuàng)建和執(zhí)行帶參數(shù)的存儲過程（1）使用CREATEPROCEDURE創(chuàng)建帶參數(shù)存儲過程

CREATEPROC[EDURE][所有者.]存儲過程名

[@參數(shù) 數(shù)據(jù)類型[=default][OUTPUT]][,...n][WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION][FORREPLICATION]ASSQL語句[,...n]（2）執(zhí)行帶參數(shù)存儲過程[EXEC[UTE]][@整型變量=]存儲過程名|@存儲過程變量[[@參數(shù)=]參量值|@變量[OUTPUT]|[DEFAULT]][,...n][WITHRECOMPLILE]9.1.2存儲過程的創(chuàng)建與使用第9頁，共26頁，2023年，2月20日，星期四在SQL查詢分析器中輸入命令：

DECLARE@readernochar(10)EXECproc_bookborrowed@readerno='101‘運行的結(jié)果：CREATEPROCproc_bookborrowed(@readernochar(10))ASSELECTReader.ReaderName,Borrow.BookNo,Book.BooknameFROMReader,Borrow,BookWHEREReader.ReaderNo=Borrow.ReaderNoANDBook.BookNo=Borrow.BookNoANDReader.ReaderNo=@readerno【例2】創(chuàng)建一個帶有輸入?yún)?shù)的存儲過程proc_bookborrowed，查詢指定編號的讀者所借圖書信息。9.1.2存儲過程的創(chuàng)建與使用第10頁，共26頁，2023年，2月20日，星期四【例3】創(chuàng)建一個帶有輸入和輸出參數(shù)的存儲過程proc_booknumber，查詢指定編號的讀者所借圖書的冊數(shù)。輸入?yún)?shù)為@readerno，將其默認值設(shè)置為“101”。CREATEPROCproc_booknumber(@readernochar(10)='101',@booknumberintOUTPUT)ASSELECTReaderName,@booknumber=COUNT(BookNo)FROMReaderINNERJOINBorrowONReader.ReaderNo=Borrow.ReaderNoWHEREReader.ReaderNo=@readerno在SQL查詢分析器中輸入命令：

DECLARE@readernochar(10),@booknumberintEXECproc_booknumber'203',@booknumberOUTPUTEXECproc_booknumberDEFAULT,@booknumberOUTPUT

9.1.2存儲過程的創(chuàng)建與使用第11頁，共26頁，2023年，2月20日，星期四執(zhí)行時，參數(shù)可以由位置標識，也可以由名字標識。例如，定義一個具有3個參數(shù)的存儲過程：CREATEPROCmyproc@val1int,@val2int,@val3intAS...參數(shù)以位置傳遞：EXECmyproc10,20,15參數(shù)以名字傳遞，每個值由對應(yīng)的參數(shù)名引導：EXECmyproc@val2=20,@val1=10,@val3=15按名字傳遞參數(shù)比按位置傳遞參數(shù)具有更大的靈活性。但是，按位置傳遞參數(shù)卻具有更快的速度。9.1.2存儲過程的創(chuàng)建與使用第12頁，共26頁，2023年，2月20日，星期四（1）使用系統(tǒng)存儲過程查看和修改存儲過程查看創(chuàng)建存儲過程的文本

sp_helptextprocedure_name查看存儲過程一般信息

sp_helpprocedure_name查看存儲過程引用表情況

sp_dependsprocedure_name存儲過程列表

sp_stored_procedures9.1.3查看、修改和刪除存儲過程1.使用企業(yè)管理器查看、修改和刪除存儲過程2.使用T-SQL語句查看、修改和刪除存儲過程第13頁，共26頁，2023年，2月20日，星期四EXECsp_helptextproc_bookborrowed--查看存儲過程的定義EXECsp_dependsproc_bookborrowed--查看存儲過程的相關(guān)性EXECsp_helpproc_bookborrowed--查看存儲過程的參數(shù)第14頁，共26頁，2023年，2月20日，星期四（2）使用ALTERPROCEDURE語句修改存儲過程ALTERPROC[EDURE][所有者.]存儲過程名[;整數(shù)][@參數(shù) 數(shù)據(jù)類型[=default][OUTPUT][,...n][WITHRECOMPILE|ENCRYPTION]ASSQL語句[,...n]（3）刪除存儲過程DROPPROC[EDURE][所有者.]存儲過程名[,...n]9.1.3查看、修改和刪除存儲過程第15頁，共26頁，2023年，2月20日，星期四【例4】修改例8-2中的存儲過程，要求加密存儲過程的定義文本，查詢指定姓名的讀者所借圖書信息。ALTERPROCproc_bookborrowed(@readernamevarchar(60))WITHENCRYPTIONASSELECTReader.ReaderName,Borrow.BookNo,Book.BookNameFROMReader,Borrow,BookWHEREReader.ReaderNo=Borrow.ReaderNoandBook.BookNo=Borrow.BookNoandReader.ReaderName=@readername執(zhí)行存儲過程，查詢讀者“王明”借閱圖書的信息。DECLARE@readernamevarchar(60)EXECproc_bookborrowed@readername='王明'使用系統(tǒng)存儲過程sp_helptext來查看存儲過程proc_bookborrowed的定義，在查詢分析器中輸入下述語句：EXECsp_helptextproc_bookborrowed第16頁，共26頁，2023年，2月20日，星期四帶輸出參數(shù)的存儲過程舉例CREATEPROCEDUREMathTutor@m1smallint,@m2smallint,@resultsmallintOUTPUTASSET@result=@m1*@m2DECLARE@answersmallintEXECUTEMathTutor5,6,@answerOUTPUTSELECT'Theresultis:',@answerTheresultis:30存儲過程的結(jié)果執(zhí)行存儲過程創(chuàng)建存儲過程第17頁，共26頁，2023年，2月20日，星期四9.2.1觸發(fā)器概述觸發(fā)器是一種特殊類型的存儲過程。觸發(fā)器主要是通過事件進行觸發(fā)而被執(zhí)行的。當對某一表進行UPDATE、INSERT、DELETE操作時，SQLServer就會自動執(zhí)行觸發(fā)器所定義的SQL語句，從而確保對數(shù)據(jù)的處理必須符合由這些SQL語句所定義的規(guī)則。觸發(fā)器是除了約束、默認值、規(guī)則外，用來維護數(shù)據(jù)完整性的另一種手段。9.2觸發(fā)器第18頁，共26頁，2023年，2月20日，星期四在觸發(fā)器的執(zhí)行過程中，SQLServer建立和管理兩個臨時的虛擬表：Deleted表和Inserted表。這兩個表包含了在激發(fā)觸發(fā)器的操作中插入或刪除的所有記錄。INSERT觸發(fā)器工作原理執(zhí)行插入操作時激活觸發(fā)器，將插入的元組拷貝到邏輯表insertedDELETE觸發(fā)器工作原理執(zhí)行刪除操作時激活觸發(fā)器，將刪除的元組保存在邏輯表deletedUPDATE觸發(fā)器工作原理執(zhí)行更新操作時激活觸發(fā)器，把要被更新的數(shù)據(jù)移入deleted表，更新數(shù)據(jù)插入到表inserted觸發(fā)器工作原理9.2.1觸發(fā)器概述第19頁，共26頁，2023年，2月20日，星期四9.2.2創(chuàng)建觸發(fā)器CreateTrigger

觸發(fā)器名

ON

表名

[WITHENCRYPTION]

FOR{[Delete][,][Insert][,][Update]}

ASSQL語句組第20頁，共26頁，2023年，2月20日，星期四【例5】創(chuàng)建一個觸發(fā)器，當圖書管理系統(tǒng)數(shù)據(jù)庫的Reader表中插入新數(shù)據(jù)成功后，利用觸發(fā)器產(chǎn)生提示信息“成功插入一條記錄”--創(chuàng)建觸發(fā)器CREATETRIGGERtr_readerONReaderFORINSERTASDECLARE@errintSELECT@err=@@errorIF(@err=0)BEGINPRINT'成功插入一條記錄'ENDRETURN--測試觸發(fā)器INSERTintoReader(ReaderNO,ReaderName)Values('301','馬帥')會顯示“成功插入一條記錄”9.2.2創(chuàng)建觸發(fā)器第21頁，共26頁，2023年，2月20日，星期四【例6】在圖書管理系統(tǒng)數(shù)據(jù)庫的Reader表和Borrow表之間具有參照關(guān)系，要求當刪除Reader表中的記錄時，激活觸發(fā)器tr_Delete，在Borrow表中也刪除相匹配的記錄行。CREATETRIGGERtr_DeleteONReaderFORDELETEASDECLARE@delcountINTDECLARE@readnoCHAR(10)SELECT@delcount=COUNT(*)FROMdeletedIF@delcount>0BEGIN--從臨時表deleted中獲取要刪除的讀者編號SELECT@readno=ReaderNoFROMdeleted--從Borrow表中刪除該員工的銷售記錄

DELETEFROMBorrowWHEREReaderNo=@readnoEND--測試觸發(fā)器INSERTintoBorrowValues('301','01001','2010-01-13','2010-3-13')DELETEFROMReaderWHEREReaderNo='301'第22頁，共26頁，2023年，2月20日，星期四DELETEFROMSTUDENTWHERESno=‘101’CREATETRIGGERtrg_Student_DeleteONStudentFORDELETEASdeletefromgradewhereSno=(selectSnofromdeleted)studentgrade使用觸發(fā)器維護數(shù)據(jù)完整性（刪除）第23頁，共26頁，2023年，2月20日，星期四9.2.3管理觸發(fā)器（1）查看觸發(fā)器信息：查看觸發(fā)器一般信息

sp_help‘觸發(fā)器名稱’ 查看觸發(fā)器的定義

sp_helptext‘觸發(fā)器名稱’