SQL高級應用本章首先介紹Transact-SQL程序設計基礎：標識符、數據類型、各類運算符、變量、批處理、流程控制語句、函數等，然后介紹存儲過程、游標、觸發器的使用。SQL高級應用1

T-SQL程序設計基礎2存儲過程3游標4觸發器2存儲過程存儲過程是存儲于數據庫中的一組T-SQL語句。可將常用的或很復雜的工作，預先用SQL語句寫好并用一個指定的名稱存儲起來，那么以后要叫數據庫提供與已定義好的存儲過程的功能相同的服務時，只需調用execute，即可自動完成命令。存儲過程是一種數據庫對象，可以包含程序流、邏輯控制以及對數據庫的查詢，可以接受參數、輸出參數、返回單個或多個結果集以及狀態集，并可重用或嵌套。

存儲過程的優點代碼執行效率高模塊化編程數據查詢效率高安全性高減少網絡流量存儲過程的分類系統存儲過程用戶自定義的存儲過程。系統存儲過程系統存儲過程是由SQLServe系統創建的存儲過程，用戶可直接使用。系統存儲過程存儲在master數據庫中，以“sp_”開頭命名。系統存儲過程主要用于系統管理、用戶登錄管理、權限設置、數據庫對象管理、數據復制等操作。常用的系統存儲過程sp_help：報告有關數據庫對象（sysobjects表中列出的任何對象）、用戶定義數據類型或系統所提供的數據類型的信息。sp_addlogin：創建新的SQLServer登錄，該登錄允許用戶使用SQLServer身份驗證連接到SQLServer實例。Sp_password：為MicrosoftSQLServer登錄名添加或更改密碼。sp_cursor_list：報告當前為連接打開的服務器游標的屬性。Sp_adduser：向當前數據庫中添加新的用戶。sp_addrole：在當前數據庫中創建新的數據庫角色。sp_addrolemember：為當前數據庫中的數據庫角色添加數據庫用戶、數據庫角色、Windows登錄或Windows組。常用的系統存儲過程（續）sp_droplogin：刪除SQLServer登錄名。這樣將阻止使用該登錄名對SQLServer實例進行訪問。sp_dropuser：從當前數據庫中刪除數據庫用戶。sp_droprole：從當前數據庫中刪除數據庫角色。sp_droprolemember：從當前數據庫的SQLServer角色中刪除安全帳戶。sp_addtype：創建別名數據類型。例25使用存儲過程sp_help查看bank數據庫的信息。usestudentgoexecsp_helpgo用戶自定義存儲過程數據庫用戶可根據某一特定功能的需要，在用戶數據庫中由用戶創建的存儲過程存儲過程命名時不能以“sp_”開頭。2.2存儲過程的創建CREATEPROCEDUREprocedure_name

[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,…][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]AS

sql_statement語法說明procedure_name：新創建的存儲過程名稱，過程名必須符合標識符規則，且對于數據庫及其所有者必須唯一。;number：是可選的整數，用來對同名的過程分組，以便用一條DROPPROCEDURE語句即可將同組的過程一起除去。@parameter：過程中的參數。在CREATEPROCEDURE語句中可以聲明一個或多個參數。用戶必須在執行過程時提供每個所聲明參數的值（除非定義了該參數的默認值，或者該值設置為等于另一個參數）。使用@符號作為第一個字符來指定參數名稱。參數名稱必須符合標識符的規則。存儲過程最多可有2100個參數。data_type:參數的數據類型。除table之外的其他所有數據類型均可以用作存儲過程的參數。但是，cursor數據類型只能用于OUTPUT參數。如果指定cursor數據類型，則還必須指定VARYING和OUTPUT關鍵字。對于可以是cursor數據類型的輸出參數，沒有最大數目的限制。語法說明（續）VARYING：指定作為輸出參數支持的結果集，僅適用于游標參數。default：參數的默認值。如果定義了默認值，則不必指定該參數的值即可執行過程。默認值必須是常量或NULL。OUTPUT：表明參數是返回參數，可將信息返回給調用過程。{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}：RECOMPILE表明SQLServer不會緩存該過程的被引用的對象，該過程將在運行時重新編譯。ENCRYPTION表示SQLServer加密syscomments表中包含CREATEPROCEDURE語句文本的條目。AS：指定過程要執行的操作。sql_statement：過程中要包含的任意數目和類型的Transact-SQL語句。但有一些限制。2.3存儲過程的執行存儲過程一般不會自動執行，用戶可使用EXECUTE命令來直接執行存儲過程。

執行存儲過程必須具有執行該存儲過程的權限。如果存儲過程是批處理中的第一個語句，EXECUTE可以省略。

存儲過程的執行[EXEC[UTE]][@return_status=]{procedure_name[;number]|@procedure_name_var}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}][,…][WITHRECOMPILE]語法說明只能在當前數據庫中創建存儲過程。@return_status=：是一個可選的整形變量，用來保存存儲過程的返回狀態。該變量在用于EXECUTE語句之前必須在批處理、存儲過程或函數中聲明。procedure_name：要調用的存儲過程名稱。;number：可選的整數，具體含義同CREATEPROCEDURE中的;number。@procedure_name_var：是局部定義變量名，代表存儲過程名稱。@parameter，value：是過程參數及其值。語法說明（續）@variable：用來保存參數或返回參數的變量。OUTPUT：指定存儲過程必須返回一個參數。DEFAULT：根據過程的定義，提供參數的默認值。WITHRECOMPILE：可強制重新編譯存儲過程代碼，但消耗較多的系統資源。2.4存儲過程的查看和修改存儲過程的有關信息以及創建存儲的文本均被存儲在SQLServer數據庫中的系統表sysobjects和syscomments中，通過SELECT語句可直接查看存儲過程的定義。存儲過程的查看selectsysobjects.id,syscomments.textfromsysobjects,syscommentswheresysobjects.id=syscomments.idandsysobjects.type='P'='procedure_name'其中，procedure_name為要查看的存儲過程的名稱。ALTERPROCEDUREprocedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,…][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASsql_statement其中的參數和保留字的含義說明與CREATEPROCEDURE語句一致。存儲過程的修改存儲過程的刪除DROPPROCEDURE{procedure_name}[,…]語法說明：procedure_name：為要刪除的存儲過程或存儲過程組的名稱。存儲過程分組后，將無法刪除組內的單個存儲過程。刪除一個存儲過程將會把同組的所有存儲過程都刪除。例1創建一個不帶參數的存儲過程P1，用于檢索所有學生信息，并執行該存儲過程。--創建存儲過程P1usestudentgocreateprocedurep1asselect*fromstudentgo--執行存儲過程p1executep1

例27創建帶有一個輸入參數的存儲過程P2，用于檢索沒有參加考試某門課程考試的同學。--創建存儲過程P2CREATEPROCEDUREP2@kcmsvarchar(20)ASBEGIN

select*fromstudentwheresnoNOTIN(selectsnofromscorewherecnoIN(selectcnofromcoursewherecname=@kcms))END--執行存儲過程p2executep2‘數據結構'例28創建帶有一個輸入參數和一個輸出參數的存儲過程P3，輸入學生的學號，返回姓名。--創建存儲過程P3CREATEPROCEDUREP3@xhchar(10),@xmchar(10)OUTPUTASBEGINselect@xm=snamefromstudentwheresno=@xhEND--執行存儲過程p3declare@xmvarchar(10)executep1‘101@xmOUTPUTprint@xm例29創建帶有多個輸入參數和多個輸出參數的存儲過程P4，輸入學生的學號和課程號，返回姓名和成績。--創建存儲過程P4CREATEPROCEDUREP4@xhchar(10),@kchchar(10),@xmchar(10)OUTPUT,@cjintOUTPUTASBEGINselect@xm=snamefromstudentwheresno=@xhselect@cj=gradefromscorewheresno=@xhandcno=@kchEND--執行存儲過程p4declare@xmvarchar(10),@cjintexecutep1’101’,’201’,@xmOUTPUT,@cjOUTPUTprint@xmPrint@cj例29刪除例26、27、28創建的存儲過程p1、p2、p3。usestudentdropprocedurep1,p2,p3,p4練習(1)創建存儲過程，給定老師姓名，刪除學習該老師的選課記錄。存儲過程的參數為老師姓名，返回刪除記錄的個數。(2)創建存儲過程，給定老師姓名，把score表中該老師授課成績低于60分的改為0分。返回修改記錄的個數。(3)創建存儲過程，給定學號、課程號和成績，如果在score表中有該選課記錄，更新該選課的成績，如果沒有，則在score表中插入一條新記錄。數據更新成功，返回1，數據更新失敗，返回-1。3游標SQL語言與主語言在數據處理方式上不同，SQL語言是面向集合的，一條SQL語句可以產生或處理多條記錄；而主語言是面向單一記錄的，一次只能處理一條記錄。可借助于游標來進行面向單條記錄的數據處理，來協調這兩種不同的數據處理方式。3.1游標概述游標是一種數據結構，包括：游標結果集：由定義該游標的SELECT語句返回的行的集合。游標位置：指向這個集合中某一行的指針。游標可以逐行處理數據，具有以下優點通過游標允許程序對由SELECT產生的結果集的每一行執行相同或不同的操作。允許定位在結果集的特定行。允許結果集中的當前行被修改。允許由其他用戶修改的數據在結果集中是可見的。提供腳本、存儲過程和觸發器中使用的訪問結果集中的數據的T-SQL語句。SQLSERVER支持三種類型的游標Transact_SQL游標：由DECLARECURSOR語法定義，主要用在Transact_SQL腳本、存儲過程和觸發器中。Transact_SQL游標主要用在服務器上，由從客戶端發送給服務器的Transact_SQL語句或是批處理、存儲過程、觸發器中的Transact_SQL進行管理。API游標：支持在OLEDB，ODBC以及DB_library中使用游標函數，主要用在服務器上。客戶游標：主要是當在客戶機上緩存結果集時才使用。在客戶游標中，有一個缺省的結果集被用來在客戶機上緩存整個結果集。客戶游標僅支持靜態游標而非動態游標。客戶游標常常僅被用作服務器游標的輔助。使用游標的步驟定義游標打開游標從游標中獲取記錄關閉游標釋放游標3.2聲明游標DECLARE<cursor_name>CURSORFOR<select_statement>[FOR{READONLY|UPDATE[OFcolumn_name[,……n]]}]語法說明cursor_name:是所定義的游標名稱。select_statement：是定義游標結果集的標準SELECT語句，既可以是簡單查詢，也可以是復雜的連接查詢或嵌套查詢。FORREADONLY：指出游標的結果集是只讀的，不能修改。FORUPDATE[OFcolumn_name[,……n]]:指出游標的結果集是可以被修改的。如果指定OFcolumn_name[,……n]參數，則只允許修改所列出的列。如果在UPDATE中未指定列的列表，則可以更新所有列。定義游標僅僅是一條說明性語句，這時DBMS并不執行SELECT語句。

3.3打開游標OPEN<cursor_name>語法說明：打開游標實際上是執行相應的SELECT語句，并把指定表中的所有滿足查詢條件的記錄放到內存緩沖區中，可從全局變量@@CURSOR_ROWS中讀取游標結果集合中的行數。執行了“打開游標”后，游標處于活動狀態，指針指向查詢結果集的第一條記錄。3.4提取游標中的記錄FETCH[NEXT|PRIOR|FIRST|LAST]Cursor_name[INTO@variable_name[,…n]]語法說明：NEXT：向下移動。如果FETCHNEXT為對游標的第一次提取操作，則返回結果集中的第一行。NEXT為默認的游標提取選項。PRIOR：向前移動。如果FETCHPRIOR為對游標的第一次提取操作，則沒有行返回并且游標置于第一行之前。FIRST：提取游標中的第一行并將其作為當前行。語法說明（續）LAST：提取游標中的最后一行并將其作為當前行。INTO@variable_name[,…n]：將提取到的數據放到局部變量中。列表中的局部變量的個數及數據類型應與定義游標時的SELECT語句目標列一致。FETCH語句每次只能提取一行數據。因此FETCH語句通常用在一個循環結構中，通過循環執行FETCH語句逐條取出結果集中的記錄進行處理。FETCH語句的執行狀態保存在全局變量@@FETCH_STATUS中，有三種取值：0表示成功；－1表示失敗或此行不再結果集中；－2表示被提取的行不存在。3.5關閉游標CLOSEcursor_name語法說明：在游標使用結束后，應關閉游標，以釋放游標占用的緩沖區及其他資源。3.6釋放游標由于關閉游標時并沒有刪除游標，因此游標仍然占用系統資源。可使用DEALLOCATE語句來釋放游標所占用的系統資源，其基本格式如下：DEALLOCATEcursor_name

語法說明：cursor_name：為要釋放的游標名稱。例30定義一個查詢的游標，將score中不及格學生的姓名、課程名和成績查詢出來。--聲明變量declare@xmvarchar(20),@kcmcvarchar(20),@cjint--創建游標DECLARERetrive_gradeCURSORFORselectsname,cname,gradefromstudent,course,scorewherestudent.sno=o=oandgrade<60--打開游標OPEN

Retrive_grade--提取第一行數據FETCHNEXTFROMRetrive_gradeINTO@xm,@kcmc,@cj--打印表標題PRINT

'姓名課程名稱成績'PRINT'--------------------------------------------------'while@@FETCH_STATUS=0BEGIN

--打印當前行

PRINT@xm+@kcmc+CONVERT(varchar(5),@cj)--提取下一行數據

FETCHNEXTFROMRetrive_gradeINTO@xm,@kcmc,@cjEND--關閉游標CLOSE

Retrive_grade--釋放游標DEALLOCATE

Retrive_grade練習定義一個游標，查詢打印score中不及格學生的姓名、課程名和成績和績點。說明：績點的計算調用函數：js_jd(姓名,課程名稱)

打印格式：姓名課程名稱成績績點---------------------------------通過游標修改數據，UPDATE語句的語法形式UPDATEtable_nameWHERECURRENTOF

cursor_name其中：table_name：為需要修改的數據庫表名。cursor_name：為游標名。當游標基于多個表時，UPDATE語句只能修改一個基本表中的數據，其他表中的數據不受影響。例31定義一個游標，把績點低于2.0的課程的成績加10分，并保存到score表中。declare@xmvarchar(20),@kcmcvarchar(20),@cjint,@jddecimal(5,1)--定義游標DECLARE

Update_gradeCURSORFORselectsname,cname,gradefromstudent,course,scorewherestudent.sno=o=o

FORUPDATEOFgrade--打開游標OPENUpdate_grade--提取第一行數據FETCH

NEXTFROMUpdate_gradeINTO@xm,@kcmc,@cjselect@jd=dbo.js_jd(@xm,@kcmc)–計算績點-提取數據while@@FETCH_STATUS=0BEGIN--處理績點低于2.0IF@jd<2.0UPDATEscoreSETgrade=grade+10

WHERECURRENTOFUpdate_gradeFETCHNEXTFROMUpdate_gradeINTO@xm,@kcmc,@cjselect@jd=dbo.js_jd(@xm,@kcmc)END--關閉游標CLOSEUpdate_grade--釋放游標DEALLOCATEUpdate_grade通過游標刪除數據，DELETE語句的語法形式DELETEFROMtable_nameWHERECURRENTOF

cursor_name其中：table_name：為需要修改的數據庫表名。cursor_name：為游標名。當游標基于多個數據表時，DELETE語句一次只能刪除一個基本表中的數據，其他基本表中的數據不受影響。例32定義游標，將score表中績點為0的信息刪除。declare@xmvarchar(20),@kcmcvarchar(20),@cjint,@jddecimal(5,1)--定義游標DECLARE

Delete_gradeCURSORFORselectsname,cname,gradefromstudent,course,scorewherestudent.sno=o=o

FORUPDATE--打開游標OPENDelete_grade--提取第一行數據FETCH

NEXTFROMDelete_gradeINTO@xm,@kcmc,@cjselect@jd=dbo.js_jd(@xm,@kcmc)–計算績點-提取數據while@@FETCH_STATUS=0BEGIN--刪除績點為0的記錄IF@jd=0DELETEFROMscore

WHERECURRENTOFDelete_gradeFETCHNEXTFROMDelete_gradeINTO@xm,@kcmc,@cjselect@jd=dbo.js_jd(@xm,@kcmc)END--關閉游標CLOSEDelete_grade--釋放游標DEALLOCATEDelete_grade練習創建一個存儲過程P8，查詢打印給定學生姓名的選課信息,并計算其平均績點。說明：績點的計算調用函數：js_jd(姓名,課程名稱)

打印格式：XXX同學的成績表課程名稱成績績點---------------------------------XXXXXX.X---------------------------------

平均績點：X.X4觸發器觸發器是一種在使用UPDATE、INSERT或DELETE命令對指定表中的數據進行修改時，由數據庫管理系統自動執行的內嵌程序，用來保證數據的一致性和完整性。觸發器與表緊密相連，可以看作是表定義的一部分。觸發器可以建立在一個用戶定義的表或視圖上，但不能建立在臨時表或系統表上。觸發器基于一個表創建，但可以操作多個表。觸發器作為一個獨立的單元被執行，被看作一個事務。如果在執行觸發器的過程中發生了錯誤，則整個事務將會自動回滾。觸發器是一種特殊的存儲過程，但它與存儲過程不同，存儲過程的存在獨立于表，而觸發器和表緊密結合；存儲過程可以通過存儲過程名稱而被直接調用，觸發器則是通過事件進行觸發而自動被執行的。觸發器的優點觸發器是自動執行的。觸發器可以通過數據庫中的相關表進行層疊更改，實現多個表之間數據的一致性和完整性。觸發器可以強制比用check約束定義的約束更為復雜的約束。觸發器可以引用其他表中的列。一個表中的多個同類觸發器（INSERT、UPDATE、DELETE）運行采取多個不同的對策，以相應同一個修改語句。觸發器可以評估數據修改前后的表狀態，并根據其差異采取對策。SQLServer觸發器的分類AFTER觸發器：又稱后觸發器。這種觸發器在數據變動完成后才被觸發。可以對變動的數據進行檢查，如果發現錯誤，將拒絕接受或回滾變動的數據。只能在表上定義。在同一個數據表中可以創建多個after觸發器。默認為after觸發器。for同after。INSTEADOF觸發器：前觸發器。其在數據變動前被觸發，并取代變動數據的操作（update、insert、delete），而去執行觸發器定義的操作。可在表或視圖上定義，每個update、insert、delete語句最多可定義一個insteadof觸發器。觸發器的組成事件：事件是指對數據庫的插入、刪除、修改等操作。觸發器在這些事件發生時將開始工作。條件：觸發器將測試條件是否成立。如果條件成立，就執行相應的動作；否則什么也不做。動作：如果條件滿足，則執行這些動作。Inserted表和deleted表在結構上類似于定義觸發器的表。由系統自動創建和管理。可使用這兩個臨時駐留內存的表測試某些數據修改的效果以及設置觸發器操作的條件；但是不能直接對表中的數據進行更改。Deleted表用于存儲delete和update語句所影響的行的備份。在執行delete或update語句時，行從表中刪除，并轉移到deleted表中。Deleted表和基表通常沒有相同的行。Inserted表用于存儲insert和update語句所影響的行的備份。在一個插入或更新事務處理中，新建行被同時添加到inserted表和基表中。Inserted表中的行是基表中新行的備份。在對具有觸發器的表（觸發器表）進行操作時，其操作過程如下：執行insert操作：插入到觸發器表中的新行被插入到inserted表中。執行delete操作：從觸發器表中刪除的行被插入到deleted表中。執行update操作：先從觸發器表中刪除舊行，然后再插入新行。其中被刪除的舊行被插入到deleted表中，插入的新行被插入到inserted表中。創建觸發器CREATE

TRIGGER<trigger_name>ON<table|view>[WITHENCRYPTION]

{

{

{

FOR|AFTER|INSTEADOF

}{[DELETE][,][INSERT][,][UPDATE]}

AS

sql_statement[...n]

}

}

語法說明：Trigger_name：觸發器的名稱。Table|view：是在其上執行觸發器的表或視圖。Withencryption：加密syscomments表中包含createtrigger語句文本的條目。After：指定觸發器只有在觸發sql語句中指定的所有操作都已成功執行后才激發。如果僅指定for，則是默認設置。不能在視圖上定義after觸發器。Insteadof：指定執行觸發器而不是執行觸發sql語句，從而替代觸發語句的操作。在表或視圖上，每個insert、update、delete語句最多可以定義一個insteadof觸發器。