1、SQL Server 存儲過程和觸發器主要內容主要內容lTransact-SQL編程基礎l事務管理l游標 l存儲過程l觸發器 1 Transact-SQL編程基礎編程基礎 l標識符、運算符、變量l函數l流程控制語句lGOTO語句lRETURN語句l打印輸出語句l注釋語句標識符標識符l標識符是由用戶定義的有意義的可識別的字符序列，通常用來標識服務器、數據庫、數據庫對象、常量、變量等 l命名標識符時必須遵循以下規則 l第一個字符必須是下列字符之一： l字母、下劃線(_)、或者# l后續字符可以是：l字母、數字、_、#、$、等符號 l不能使用SQL中的關鍵字和運算符，不允許嵌入空格或其它特殊字符 運

2、算符運算符l算術運算符l+ ，- ，*， / ，%l賦值運算符l=l比較運算符l= ， ，= ，= ，l邏輯運算符land ，or ，notlBetweenand，In，Like，Existsl連接運算符l+ 用于字符串數據的連接運算符的優先順序運算符的優先順序 l*、/、%l+、-l=、=、=、lnotlandlbetween、in、like、orl= 算術運算符算術運算符比較運算符比較運算符邏輯運算符邏輯運算符賦值運算符賦值運算符變量變量 l局部變量l由用戶定義和使用，可賦值并參與運算l作用域 批處理、觸發器、存儲過程l聲明 declare 變量名 數據類型,l賦值 select 變量名=

3、值 或 SET 局部變量名=表達式【例【例1】DECLARE x float, var char(8) 該定義語句定義了變量該定義語句定義了變量x是浮點實型，變量是浮點實型，變量var是長度為是長度為8的定長字符數據類型。的定長字符數據類型。 【例2】定義變量c_code和score，并分別用SET和SELECT語句為它們賦值，然后查詢所有課程號為c_code,且成績小于score的學生成績信息。DECLARE DECLARE c_codec_code char(6), score char(6), score Numeric(3,1)Numeric(3,1)SET SET c_codec_c

4、ode=130001=130001SELECT score=80SELECT score=80 SELECT SELECT * * FROM FROM T_GradeT_Grade WHERE WHERE CourseCodeCourseCode=c_codec_code AND AND GradescoreGrade=3 BEGIN SET text=你選了你選了+CAST(cn AS char(2) /* CAST函數將函數將cn的值轉換為長度為的值轉換為長度為2的字符數據的字符數據*/ SET text=text+門課。很好，你完成了任務！門課。很好，你完成了任務！ ENDELSE BE

5、GIN SET text=你選了你選了+CAST(cn AS char(2) SET text=text+門課。選課太少，加油！門課。選課太少，加油！ ENDSELECT text AS 選課提示選課提示多分支的選擇語句多分支的選擇語句-CASEl簡單簡單 CASE 函數形式：將某個表達式與一組函數形式：將某個表達式與一組簡單表達式進行比較以確定結果。簡單表達式進行比較以確定結果。lCASE 搜索函數形式：計算一組條件表達式搜索函數形式：計算一組條件表達式以確定結果以確定結果簡單CASE函數l語法格式：語法格式： CASE 輸入表達式輸入表達式 WHEN 情況表達式情況表達式 THEN 結果表

6、達式結果表達式 . ELSE 結果表達式結果表達式 ENDl說明：說明：1)當表達式的值與某個情況表達式的值相等時，返回相當表達式的值與某個情況表達式的值相等時，返回相應結果表達式的值應結果表達式的值 。 2)如果全部都不相等，則返回如果全部都不相等，則返回 ELSE中的結果表達式，中的結果表達式，若沒有若沒有ELSE子句，則返回子句，則返回NULL值。值。 【例例5 5】查詢查詢0707級女同學的住校情況。級女同學的住校情況。SELECT StudentName AS 姓名姓名, CASE LiveInDorm WHEN 0 THEN 未住校未住校 WHEN 1 THEN 住校住校 END

7、AS 是否住校是否住校 FROM T_StudentWHERE Sex=女女 AND LEFT(StudentCode,2)=07字段：是否住校的表達CASE 搜索函數搜索函數語法格式：語法格式： CASE WHEN 條件表達式條件表達式 THEN 結果表達式結果表達式 . ELSE結果表達式結果表達式 END【例例6 6】統計每個學生平均成績并劃分等級統計每個學生平均成績并劃分等級。SELECT SELECT StudentCodeStudentCode AS AS 學號學號, STR(AVG(Grade),5,2) AS , STR(AVG(Grade),5,2) AS 平均成績平均成績,

8、 CASE CASE WHEN WHEN AVG(GradeAVG(Grade)=90 THEN A)=90 THEN A WHEN WHEN AVG(GradeAVG(Grade)=80 THEN B)=80 THEN B WHEN WHEN AVG(GradeAVG(Grade)=70 THEN C)=70 THEN C WHEN WHEN AVG(GradeAVG(Grade)=60 THEN D)=60 THEN D WHEN WHEN AVG(GradeAVG(Grade)60 THEN E)60 THEN E END AS END AS 等級等級 FROM FROM T_Grade

9、T_Grade GROUP BY GROUP BY StudentCodeSELECTStudentCodeSELECT流程控制語句流程控制語句2BEGINEND語句l定義語句塊：l多條T-SQL語句使用BEGINEND組合起來形成一個語句塊。l可以嵌套使用l語法形式為 BEGIN SQL語句1 SQL語句2 END 流程控制語句流程控制語句3WHILECONTINUEBREAK語句l條件循環語句lContinue可以使程序跳過其后面的語句，回到while循環的第一行命令;lBreak使程序完全跳出循環，結束while語句。l語法形式為 WHILE BREAK或CONTINUE 結束結束整個整

10、個循環循環結束結束本次本次循環循環while breakwhile continue【例例8 8】計算計算1010！。 DECLARE p int, i smallint, text varchar(100)SET p=1SET i=1WHILE i=10 BEGIN SET p= p*i SET i=i+1 ENDSET text=12310=+CAST(p AS char(10)SELECT text AS 計算結果GOTOGOTO語句語句 GOTO語句的語法形式為 l跳轉語句，跳轉到標有標識符指定位置 GOTO label label:【例9】利用利用GOTOGOTO語句求出從語句求出從

11、1 1加到加到5 5的總和的總和 RETURNRETURN語句語句 l終止語句，用于無條件終止一個查詢、存儲過程或者批處理。lRETURN語句的語法形式為 RETURN 整數 打印輸出語句打印輸出語句 可以顯示變量的值PRINT語句的語法如下 PRINT | | 例例10 利用利用PRINTPRINT語句判斷變量值的示例語句判斷變量值的示例 注釋語句注釋語句 （1）整塊注釋/* 注釋塊 */（2）從行的后部分注釋 語句 注釋事務管理事務管理B Beginegin Transaction Transaction 事務開始事務開始 SQLSQL語句組語句組 C Commit Transaction

12、 ommit Transaction 提交事務提交事務說明：說明：l用戶定義的事務也稱為顯式事務用戶定義的事務也稱為顯式事務l事務只用于數據修改語句事務只用于數據修改語句l可以在可以在commit transactioncommit transaction之前使用之前使用roolbackroolback transaction transaction取取消事務并撤銷對數據所作的任何改變，命令為：消事務并撤銷對數據所作的任何改變，命令為：roolbackroolback transaction transaction savepoint_namesavepoint_name lSavepoint

13、_nameSavepoint_name為保存點，是用戶放在事務中的一個標記，為保存點，是用戶放在事務中的一個標記，指明回滾點指明回滾點l事務管理通過確保要么完成整個事務，要么回滾事務，來確事務管理通過確保要么完成整個事務，要么回滾事務，來確保數據庫的一致性和可恢復性保數據庫的一致性和可恢復性例例1111 建立一個事務，建立一個事務，用來將用來將mag_deptmag_dept表中表中depiddepid為為1 1的記錄的的記錄的depmanagerdepmanager字段的值更改為字段的值更改為“王涓涓王涓涓” 例例1212 建立一個事務，建立一個事務，用來更改用來更改mag_deptmag_

14、dept表中表中depiddepid為為1 1以及以及depiddepid為為2 2的記錄的的記錄的depmanagerdepmanager字段字段的值，并回滾事務到保存點的值，并回滾事務到保存點存儲過程存儲過程 l存儲過程：可以將一些固定的操作集中起來由SQL Server數據庫服務器完成，以實現某個任務；存儲過程允許聲明變量、輸出參數、返回單個或者多個結果集以及返回值；l存儲過程存在于數據庫內，可由應用程序調用執行。l存儲過程分為兩類：l系統存儲過程（存儲在master數據庫中）l用戶自定義的存儲過程。l使用存儲過程有以下優點：1)可以在一個存儲過程中執行多條SQL語句；2)可多次調用；3

15、)創建時就在服務器端進行了編譯，節省SQL語句的運行時間；4)提供了安全機制，它限制了用戶訪問SQL語句的權利，只為特定用戶開放存儲過程。創建存儲過程創建存儲過程 常用存儲過程的語法格式：常用存儲過程的語法格式： CREATE PROCDURE 存儲過程名存儲過程名 形式參數形式參數 數據類型數據類型VARYING=默認值默認值OUTPUT AS SQL語句語句1 SQL語句語句n 說明：說明： l“形式參數形式參數”名稱必須符合標識符規則；名稱必須符合標識符規則；lOUTPUT表示該參數是可以返回的，可將信息返回調用者；表示該參數是可以返回的，可將信息返回調用者；l如果有多個參數，可以依次按

16、以上參數定義規則列出，用逗號如果有多個參數，可以依次按以上參數定義規則列出，用逗號“,”隔開。隔開。例例1313 建立一個名為建立一個名為“全部雇員全部雇員”的存儲過程，的存儲過程，用用來查詢來查詢mag_empmag_emp表的所有記錄表的所有記錄 例例14 建立一個名為建立一個名為“雇員查詢雇員查詢”的存儲過程，的存儲過程，查詢某種職務雇員的所有情況查詢某種職務雇員的所有情況 【例15】在School中創建一個的存儲過程proc_SearchStudent,查詢指定學生的選課情況。CREATE PROC CREATE PROC proc_SearchStudentproc_SearchSt

17、udent stcodestcode char(8) char(8)ASASSELECT SELECT T_Student.StudentName,T_Course.CourseNameT_Student.StudentName,T_Course.CourseNameFROM T_Student JOIN T_Grade JOIN T_CourseFROM T_Student JOIN T_Grade JOIN T_Course ON T_Grade.CourseCode=T_Course.CourseCode ON T_Grade.CourseCode=T_Course.CourseCode

18、 ON T_Student.StudentCode=T_Grade.StudentCode ON T_Student.StudentCode=T_Grade.StudentCodeWHERE WHERE T_Student.StudentCodeT_Student.StudentCode=scodescode 修改存儲過程修改存儲過程 修改存儲過程的語法為 ALTER PROCEDURE ； =OUTPUT AS 例例16 修改存儲過程修改存儲過程“雇員查詢雇員查詢”，用來，用來統計某一職務雇員的總人數統計某一職務雇員的總人數 運行存儲過程運行存儲過程 運行存儲過程的語法為 EXECUTE 存

19、儲過程名存儲過程名形參形參=實參值實參值|變量變量OUTPUT|DEFAULT說明：說明：1)“形參形參”是創建存儲過程時定義的形參名；是創建存儲過程時定義的形參名；2)“實參值實參值”是輸入參數的值；是輸入參數的值；3)“變量變量”表示用來保存參數或者返回參數的變量；表示用來保存參數或者返回參數的變量；OUTPUT表示表示指定參數為返回參數；指定參數為返回參數；4)DEFAULT表示使用該參數的默認值作為實參。表示使用該參數的默認值作為實參。5)如有多個參數，可依次按以上參數定義規則列出，用逗號如有多個參數，可依次按以上參數定義規則列出，用逗號“，”隔隔開。開。調用存儲過程調用存儲過程pro

20、c_Courseproc_Course的語句為：的語句為：EXEC EXEC proc_Courseproc_Course調用存儲過程調用存儲過程proc_SearchStudentproc_SearchStudent，查詢查詢0510110105101101學生的選課情況。學生的選課情況。EXEC EXEC proc_SearchStudentproc_SearchStudent 05101101 05101101或或EXEC proc_ EXEC proc_ SearchStudentSearchStudent scodescode=05101101=05101101采用采用“參數參數=值

21、值”的形的形式，各個參數的順序式，各個參數的順序可以任意排列可以任意排列。實參順序和定義時實參順序和定義時的參數順序一致。的參數順序一致。【例【例1717】在在SchoolSchool中創建一個的存儲過程中創建一個的存儲過程proc_SearchStudentAvgGradeproc_SearchStudentAvgGrade，查詢某個學生的選課數目查詢某個學生的選課數目和平均成績和平均成績。CREATE PROC CREATE PROC proc_SearchStudentAvgGradeproc_SearchStudentAvgGrade stcodestcode char(8), cha

22、r(8), stcountstcount intint OUTPUT,stavgOUTPUT,stavg intint OUTPUT OUTPUTASASSELECT SELECT stcountstcount= =COUNT(T_Grade.StudentCode),stavgCOUNT(T_Grade.StudentCode),stavg= =AVG(T_Grade.GradeAVG(T_Grade.Grade) )FROM FROM T_GradeT_GradeWHERE WHERE T_Grade.StudentCodeT_Grade.StudentCode=stcodestcodeG

23、ROUP BY GROUP BY T_Grade.StudentCodeT_Grade.StudentCode調用存儲過程調用存儲過程proc_SearchStudentAvgGradeproc_SearchStudentAvgGrade，查詢查詢0510110105101101學生的選課數目和平均成績學生的選課數目和平均成績。DECLARE DECLARE scountscount intint , ,savgsavg intint EXECEXEC proc_SearchStudentAvgGradeproc_SearchStudentAvgGrade 05101101,scount 05

24、101101,scount OUTPUT,savgOUTPUT,savg OUTPUT OUTPUT PRINT PRINT 學生學生+ 05101101+ 05101101+的選課數目為的選課數目為+CAST(scountCAST(scount AS AS char(2)+char(2)+門門 PRINT PRINT 學生學生+ 05101101+ 05101101+的平均成績為的平均成績為+STR(savg,5,2)+STR(savg,5,2)+分分 刪除存儲過程刪除存儲過程 刪除存儲過程的語法為 Drop Procedure 游標游標l問題要對查詢結果進行一行行的操作，如何做到？ 游游標

25、標引引例例以前：使用SELECT語句對表格進行查詢，返回的結果集包括所有滿足條件的行。思考：如果要求每次只顯示表格（例如上面的Course表）中的一行，該如何處理？這在將T-SQL嵌入到其他高級語言（如C、VC、Delphi等）的編程中經常用到。 游標概述游標概述l可以將游標看作一種特殊的指針，它可以指向與它相關聯的結果集中的任意一行，以便對當前位置的行進行處理。l游標提供了對一個結果集進行逐行處理的能力：1.在結果集中定位特定行2.從結果集的當前位置檢索行3.支持對結果集中當前位置的行進行數據處理（修改/刪除）使用游標的步驟使用游標的步驟l聲明游標l打開游標l處理數據（讀取/修改/刪除）可以

26、和其他T-SQL語句配合靈活使用l關閉游標（與打開游標配對）l刪除游標（與聲明游標配對，此時釋放分配給游標的所有資源）【問題】游標基本使用的例子相關語法格式相關語法格式l聲明游標DECLARE cursor_name CURSORDECLARE cursor_name CURSORFORWORD_ONLY|SCROLLFORWORD_ONLY|SCROLLFOR select_statementsFOR select_statementsl打開游標OPEN cursor_nameOPEN cursor_namel使用游標FETCH NEXT|PRIOR|FIRST|LASTFETCH NEXT

27、|PRIOR|FIRST|LASTFROM cursor Into variable_nameFROM cursor Into variable_namel關閉游標CLOSE cursor_nameCLOSE cursor_namel刪除游標DEALLOCATION cursor_nameDEALLOCATION cursor_name如果遍歷整個結果集？如果遍歷整個結果集？l使用fetch_status:l該全局變量/配置函數返回被最后FETCH語句執行的游標的狀態，返回類型為int：1.0：FETCH語句成功2.1:FETCH語句失敗或此行不在結果集中3.2：被提取的行不存在USE mas

28、terGOCREATE PROCEDURE sp_BuildIndexesASDECLARE TableName sysname, msg varchar(100), cmd varchar(100)DECLARE table_cur CURSOR FORSELECT name FROM sysobjects WHERE type=uOPEN table_curFETCH NEXT FROM table_cur INTO TableNameWHILE fetch_status = 0BEGINIF fetch_status = -2CONTINUESELECT msg = “Building

29、indexes for table”+TableName+”PRINT msgSELECT cmd = “DBCC DBREINDEX (”+TableName+”)”EXEC (cmd)PRINT “ “FETCH NEXT FROM table_cur INTO TableNameENDDEALLOCATE table_curGO觸發器觸發器l定義在表上的一個對象l一種特殊類型的存儲過程l不需要專門語句調用，通過事件進行觸發而被執行l當執行insert、delete和update語句時自動被觸發執行l觸發器可以在有數據操作時自動強制執行某些業務規則l觸發器執行的內容可以是報警、維護數據的完

30、整性、特殊的數據處理l觸發器可以用于完整性檢查，但比約束、默認值、規則等功能強大、靈活lSQL Server的兩種觸發器： （1）AFTER觸發器：數據被修改后觸發，對變動的數據進行檢查。（2）INSTEAD OF觸發器：數據修改之前被觸發，并取代修改數據的操作，轉去執行觸發器定義的操作。例如，執行完下面的語句后：例如，執行完下面的語句后： CREATE TRIGGER tri_StudentInsDel ON dbo.T_Student FOR INSERT, DELETE AS SELECT * FROM T_Student 當對表當對表T_Student的數據進行插入或刪除操的數據進行插

31、入或刪除操作時，觸發器作時，觸發器“tri_StudentUpdate”將會自動執行。將會自動執行。創建觸發器創建觸發器 創建觸發器時提供：觸發器名稱、定義觸發器的表、觸發器觸發事件、數據修改語句。創建觸發器的語法為CREATE TRIGGER 觸發器名觸發器名ON 表名表名|視圖名視圖名FOR INSERT,UPDATE,DELETEASSQL語句段語句段一個觸發器只能用于一個表兩個特殊的邏輯表：inserted、deleted例例1818 利用觸發器來保證利用觸發器來保證期刊采編系統數據庫中期刊采編系統數據庫中mag_infomag_info表的參照完整性表的參照完整性 【例【例1919】在在SchoolSchool數據庫的數據庫的T_StudentT_Student表上創建一個觸發器表上創建一個觸發器tri_StudentCodeUpdatetri_StudentCodeUpdate，當對學號列進行修改時，給出提示信息并當對學號列進行修改時，給出提示信息并取消修改操作。取消修改操作。 CREATE TRIGGER CREATE TRIGGER tri_StudentCodeUpdatetri_StudentCodeUpdateON ON T_StudentT_StudentFOR UPDATEFOR UPDATEAS