SQL語(yǔ)言-SQL高級(jí)應(yīng)用(2)分析

SQL高級(jí)應(yīng)用本章首先介紹Transact-SQL程序設(shè)計(jì)基礎(chǔ)：標(biāo)識(shí)符、數(shù)據(jù)類型、各類運(yùn)算符、變量、批處理、流程控制語(yǔ)句、函數(shù)等，然后介紹存儲(chǔ)過(guò)程、游標(biāo)、觸發(fā)器的使用。SQL高級(jí)應(yīng)用1

T-SQL程序設(shè)計(jì)基礎(chǔ)2存儲(chǔ)過(guò)程3游標(biāo)4觸發(fā)器2存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程是存儲(chǔ)于數(shù)據(jù)庫(kù)中的一組T-SQL語(yǔ)句?？蓪⒊Ｓ玫幕蚝軓?fù)雜的工作，預(yù)先用SQL語(yǔ)句寫好并用一個(gè)指定的名稱存儲(chǔ)起來(lái)，那么以后要叫數(shù)據(jù)庫(kù)提供與已定義好的存儲(chǔ)過(guò)程的功能相同的服務(wù)時(shí)，只需調(diào)用execute，即可自動(dòng)完成命令。存儲(chǔ)過(guò)程是一種數(shù)據(jù)庫(kù)對(duì)象，可以包含程序流、邏輯控制以及對(duì)數(shù)據(jù)庫(kù)的查詢，可以接受參數(shù)、輸出參數(shù)、返回單個(gè)或多個(gè)結(jié)果集以及狀態(tài)集，并可重用或嵌套。

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

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

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

執(zhí)行存儲(chǔ)過(guò)程必須具有執(zhí)行該存儲(chǔ)過(guò)程的權(quán)限。如果存儲(chǔ)過(guò)程是批處理中的第一個(gè)語(yǔ)句，EXECUTE可以省略。

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

例27創(chuàng)建帶有一個(gè)輸入?yún)?shù)的存儲(chǔ)過(guò)程P2，用于檢索沒(méi)有參加考試某門課程考試的同學(xué)。--創(chuàng)建存儲(chǔ)過(guò)程P2CREATEPROCEDUREP2@kcmsvarchar(20)ASBEGIN

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

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

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

Retrive_grade--提取第一行數(shù)據(jù)FETCHNEXTFROMRetrive_gradeINTO@xm,@kcmc,@cj--打印表標(biāo)題PRINT

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

--打印當(dāng)前行

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

FETCHNEXTFROMRetrive_gradeINTO@xm,@kcmc,@cjEND--關(guān)閉游標(biāo)CLOSE

Retrive_grade--釋放游標(biāo)DEALLOCATE

Retrive_grade練習(xí)定義一個(gè)游標(biāo)，查詢打印score中不及格學(xué)生的姓名、課程名和成績(jī)和績(jī)點(diǎn)。說(shuō)明：績(jī)點(diǎn)的計(jì)算調(diào)用函數(shù)：js_jd(姓名,課程名稱)

打印格式：姓名課程名稱成績(jī)績(jī)點(diǎn)---------------------------------通過(guò)游標(biāo)修改數(shù)據(jù)，UPDATE語(yǔ)句的語(yǔ)法形式UPDATEtable_nameWHERECURRENTOF

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

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

FORUPDATEOFgrade--打開游標(biāo)OPENUpdate_grade--提取第一行數(shù)據(jù)FETCH

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

WHERECURRENTOFUpdate_gradeFETCHNEXTFROMUpdate_gradeINTO@xm,@kcmc,@cjselect@jd=dbo.js_jd(@xm,@kcmc)END--關(guān)閉游標(biāo)CLOSEUpdate_grade--釋放游標(biāo)DEALLOCATEUpdate_grade通過(guò)游標(biāo)刪除數(shù)據(jù)，DELETE語(yǔ)句的語(yǔ)法形式DELETEFROMtable_nameWHERECURRENTOF

cursor_name其中：table_name：為需要修改的數(shù)據(jù)庫(kù)表名。cursor_name：為游標(biāo)名。當(dāng)游標(biāo)基于多個(gè)數(shù)據(jù)表時(shí)，DELETE語(yǔ)句一次只能刪除一個(gè)基本表中的數(shù)據(jù)，其他基本表中的數(shù)據(jù)不受影響。例32定義游標(biāo)，將score表中績(jī)點(diǎn)為0的信息刪除。declare@xmvarchar(20),@kcmcvarchar(20),@cjint,@jddecimal(5,1)--定義游標(biāo)DECLARE

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

FORUPDATE--打開游標(biāo)OPENDelete_grade--提取第一行數(shù)據(jù)FETCH

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

WHERECURRENTOFDelete_gradeFETCHNEXTFROMDelete_gradeINTO@xm,@kcmc,@cjselect@jd=dbo.js_jd(@xm,@kcmc)END--關(guān)閉游標(biāo)CLOSEDelete_grade--釋放游標(biāo)DEALLOCATEDelete_grade練習(xí)創(chuàng)建一個(gè)存儲(chǔ)過(guò)程P8，查詢打印給定學(xué)生姓名的選課信息,并計(jì)算其平均績(jī)點(diǎn)。說(shuō)明：績(jī)點(diǎn)的計(jì)算調(diào)用函數(shù)：js_jd(姓名,課程名稱)

打印格式：XXX同學(xué)的成績(jī)表課程名稱成績(jī)績(jī)點(diǎn)---------------------------------XXXXXX.X---------------------------------

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

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

{

{

{

FOR|AFTER|INSTEADOF

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

AS

sql_statement[...n]

}

}

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