數(shù)據(jù)庫(kù)原理及應(yīng)用-SQL Server 2019高級(jí)應(yīng)用

SQLServer2019高級(jí)應(yīng)用目錄01T-SQL編程基礎(chǔ)02游標(biāo)03存儲(chǔ)過(guò)程04觸發(fā)器本章主要內(nèi)容本章主要闡述T-SQL的標(biāo)識(shí)符、運(yùn)算符、表達(dá)式、函數(shù)、變量與常量、流程控制語(yǔ)句等基礎(chǔ)知識(shí)，并結(jié)合實(shí)例探討T-SQL最常見(jiàn)的應(yīng)用，分析游標(biāo)的基本原理和使用方法，重點(diǎn)介紹使用T-SQL創(chuàng)建、管理存儲(chǔ)過(guò)程和觸發(fā)器。T-SQL編程基礎(chǔ)019.1.1T-SQL語(yǔ)法元素1．標(biāo)識(shí)符：用來(lái)標(biāo)識(shí)服務(wù)器、數(shù)據(jù)庫(kù)和數(shù)據(jù)庫(kù)對(duì)象。保留字不能作為標(biāo)識(shí)符。數(shù)據(jù)類型：用來(lái)定義數(shù)據(jù)對(duì)象。運(yùn)算符：表達(dá)式的組成部分。表達(dá)式：將標(biāo)識(shí)符、值和運(yùn)算符按一定的規(guī)則連接起來(lái)的有意義的式子。函數(shù)：可以有n個(gè)參數(shù)，并返回一個(gè)值或幾個(gè)值的集合。注釋：作為程序說(shuō)明性文字或調(diào)試程序時(shí)的輔助手段。保留關(guān)鍵字：定義、操作或訪問(wèn)數(shù)據(jù)庫(kù)。9.1.2常量在程序運(yùn)行過(guò)程中其值不會(huì)發(fā)生改變的量就是常量，又稱文字值或標(biāo)量值。在T-SQL程序設(shè)計(jì)中，常量的格式取決于它所表示的數(shù)據(jù)值的數(shù)據(jù)類型，主要包含字符串常量、數(shù)值常量、日期常量等。1．字符串常量字符串常量需要以單引號(hào)（'）括起來(lái)?！纠?.1】查詢教師表teacher中職稱為副教授的教師信息。SELECT*FROMteacherWHEREProf='副教授';9.1.2常量2．?dāng)?shù)值常量數(shù)值常量包含的內(nèi)容較多，所占用的存儲(chǔ)空間的大小不同，表示的數(shù)據(jù)范圍也各有不同。3．日期常量和字符串常量一樣，日期常量使用特定格式的字符日期值表示，用單引號(hào)（'）括起來(lái)?！纠?.2】查詢教師表teacher中入職時(shí)間在1996年以后的教師信息。SELECT*FROMteacherWHEREHiredate>'1996-12-31';9.1.3變量變量用于臨時(shí)存儲(chǔ)數(shù)據(jù)，是在語(yǔ)句之間傳遞數(shù)據(jù)的方式之一，是可以對(duì)其賦值并參與運(yùn)算的一個(gè)實(shí)體。變量分為局部變量和全局變量?jī)深悺?．全局變量全局變量是SQLServer2019系統(tǒng)內(nèi)部使用的變量，作用于任何程序。全局變量由系統(tǒng)定義和維護(hù)，用戶只能使用預(yù)先說(shuō)明及定義的全局變量，因此，全局變量對(duì)用戶而言是只讀的，用戶無(wú)法對(duì)它們進(jìn)行修改或管理。使用全局變量必須以兩個(gè)“@@”開(kāi)頭。9.1.3變量2．局部變量T-SQL中的局部變量是由用戶自定義和使用的變量，命名時(shí)不區(qū)分大小寫，但不能與全局變量的名稱相同。局部變量一般用于臨時(shí)存儲(chǔ)數(shù)據(jù)，方便在語(yǔ)句之間傳遞數(shù)值。局部變量的作用范圍有限，僅局限在一個(gè)批處理、存儲(chǔ)過(guò)程或觸發(fā)器中。局部變量以一個(gè)@為標(biāo)記，如@變量名。T-SQL中的局部變量遵從先聲明后使用的原則。用DECLARE語(yǔ)句聲明定義局部變量，其語(yǔ)法格式如下：DECLARE@變量名數(shù)據(jù)類型[(長(zhǎng)度)][,@變量名數(shù)據(jù)類型[(長(zhǎng)度)],…]9.1.3變量2．局部變量在對(duì)局部變量賦值時(shí)，可以選用SET命令或SELECT命令。其語(yǔ)法格式如下：SET@變量名=變量值SELECT@變量名=變量值[,@變量名=變量值…]

SET命令一次只能給一個(gè)局部變量賦值，SELECT命令可以一次性給多個(gè)局部變量賦值?！纠?.3】聲明一個(gè)長(zhǎng)度為6個(gè)字符的局部變量sno，并賦初值為'202001'。DECLARE@snonchar(6)SET@sno=‘202001’或SELECT@sno='202001'SELECT命令也可以直接將查詢的單值結(jié)果賦值給局部變量。9.1.4運(yùn)算符運(yùn)算符是一種符號(hào)，用來(lái)指定在一個(gè)或多個(gè)表達(dá)式中執(zhí)行的操作。SQLServer2019為用戶提供了豐富的運(yùn)算符，主要包括算術(shù)運(yùn)算符、賦值運(yùn)算符、位運(yùn)算符、比較運(yùn)算符、邏輯運(yùn)算符、字符串串聯(lián)運(yùn)算符、一元運(yùn)算符等。1．算術(shù)運(yùn)算符：雙目運(yùn)算符，對(duì)兩個(gè)表達(dá)式執(zhí)行算術(shù)運(yùn)算。賦值運(yùn)算符：等號(hào)（=），其為單目運(yùn)算符。位運(yùn)算符：用來(lái)在整型數(shù)據(jù)或二進(jìn)制數(shù)據(jù)（image類型除外）之間執(zhí)行位操作，是雙目運(yùn)算符。4.比較運(yùn)算符：又稱關(guān)系運(yùn)算符，用來(lái)比較兩個(gè)表達(dá)式之間的大小關(guān)系，也是雙目運(yùn)算符，通常用于構(gòu)造表達(dá)式。9.1.4運(yùn)算符5．邏輯運(yùn)算符：用來(lái)將多個(gè)邏輯表達(dá)式連接起來(lái)表達(dá)復(fù)雜的邏輯關(guān)系，運(yùn)算結(jié)果同樣返回布爾值（TRUE或FALSE）。6.字符串串聯(lián)運(yùn)算符：是雙目運(yùn)算符，用于將左右兩側(cè)的字符串串聯(lián)起來(lái)形成新的字符串。一元運(yùn)算符：+、-、～，都為單目運(yùn)算符，只對(duì)一個(gè)表達(dá)式進(jìn)行運(yùn)算。需注意兩點(diǎn)，一是運(yùn)算對(duì)象的數(shù)據(jù)類型要與選用的運(yùn)算符相匹配。例如進(jìn)行算術(shù)運(yùn)算時(shí)，參與數(shù)值運(yùn)算的兩個(gè)表達(dá)式必須是數(shù)值類型的數(shù)據(jù)或者能夠進(jìn)行算術(shù)運(yùn)算的其他數(shù)據(jù)類型。二是當(dāng)多個(gè)運(yùn)算符參與運(yùn)算時(shí)，要按照優(yōu)先級(jí)先后執(zhí)行，優(yōu)先級(jí)相同時(shí)從左到右依次求值，還可以使用括號(hào)改變優(yōu)先級(jí)。9.1.5流程控制語(yǔ)句流程控制語(yǔ)句是指那些用來(lái)控制程序執(zhí)行和流程分支的命令，流程控制語(yǔ)句主要用來(lái)控制SQL語(yǔ)句、語(yǔ)句塊或存儲(chǔ)過(guò)程的執(zhí)行流程，例如條件控制語(yǔ)句、循環(huán)語(yǔ)句等。流程控制語(yǔ)句可以實(shí)現(xiàn)程序的結(jié)構(gòu)性和邏輯性，從而幫助用戶實(shí)現(xiàn)現(xiàn)實(shí)世界中較為復(fù)雜的邏輯關(guān)系。

SQLServer2019提供以下6種流程控制語(yǔ)句：語(yǔ)句塊、選擇語(yǔ)句、循環(huán)語(yǔ)句、等待語(yǔ)句、轉(zhuǎn)移語(yǔ)句和返回語(yǔ)句。9.1.5流程控制語(yǔ)句1．語(yǔ)句塊語(yǔ)句塊由BEGIN…END語(yǔ)句構(gòu)成，能將多個(gè)T-SQL語(yǔ)句組合成一個(gè)程序塊，并將它們視為一個(gè)單元來(lái)處理。BEGIN…END語(yǔ)句的語(yǔ)法格式如下所示，在使用時(shí)可將其理解為其他程序設(shè)計(jì)語(yǔ)言中的復(fù)合語(yǔ)句。BEGIN

<SQL語(yǔ)句|SQL語(yǔ)句塊>END9.1.5流程控制語(yǔ)句2．選擇語(yǔ)句選擇語(yǔ)句可以有3種表現(xiàn)形式，第一種形式是IF…ELSE語(yǔ)句，它的語(yǔ)法格式如下所示：IF

條件表達(dá)式

語(yǔ)句塊1[ELSE

語(yǔ)句塊2]9.1.5流程控制語(yǔ)句2．選擇語(yǔ)句選擇語(yǔ)句的第二種形式是IF…EXISTS語(yǔ)句，其語(yǔ)法格式如下所示：IF [NOT]EXISTS(SELECT子查詢)

語(yǔ)句塊1[ELSE語(yǔ)句塊2]9.1.5流程控制語(yǔ)句2．選擇語(yǔ)句

選擇語(yǔ)句的第三種形式是CASE語(yǔ)句，CASE語(yǔ)句不是獨(dú)立語(yǔ)句，只用于允許使用表達(dá)式的位置。它又包括兩種格式，語(yǔ)法格式如下所示。格式一：

格式二：CASE<表達(dá)式>CASEWHEN<表達(dá)式>THEN<表達(dá)式>WHEN<表達(dá)式>THEN<表達(dá)式>……WHEN<表達(dá)式>THEN<表達(dá)式>WHEN<表達(dá)式>THEN<表達(dá)式>[ELSE<表達(dá)式>][ELSE<表達(dá)式>]ENDEND9.1.5流程控制語(yǔ)句3．循環(huán)語(yǔ)句當(dāng)程序中需要重復(fù)執(zhí)行某項(xiàng)操作時(shí)，就需要使用循環(huán)語(yǔ)句WHILE…CONTINUE…BREAK。WHILE語(yǔ)句通過(guò)循環(huán)條件表達(dá)式來(lái)設(shè)定一個(gè)循環(huán)條件，當(dāng)邏輯值為真時(shí)，重復(fù)執(zhí)行一個(gè)語(yǔ)句塊，否則退出循環(huán)，繼續(xù)執(zhí)行后續(xù)操作。其語(yǔ)法格式如下所示：9.1.5流程控制語(yǔ)句3．循環(huán)語(yǔ)句

WHILE<循環(huán)條件表達(dá)式> BEGIN

<語(yǔ)句塊>

[BREAK] … [CONTINUE]

… END9.1.5流程控制語(yǔ)句4．等待語(yǔ)句

WAITFOR語(yǔ)句用于暫停執(zhí)行SQL語(yǔ)句、程序塊或存儲(chǔ)過(guò)程等，直到所設(shè)定的時(shí)間已過(guò)或者所設(shè)定的時(shí)間已到才繼續(xù)執(zhí)行。其語(yǔ)法格式如下所示：WAITFOR{DELAY'time'|TIME'time'}9.1.5流程控制語(yǔ)句5．轉(zhuǎn)移語(yǔ)句

GOTO語(yǔ)句可以使程序直接跳轉(zhuǎn)到指定的位置開(kāi)始執(zhí)行，GOTO語(yǔ)句和指定位置之間的程序段將直接跳過(guò)，不再執(zhí)行。GOTO語(yǔ)句的語(yǔ)法形式相對(duì)簡(jiǎn)單，如下所示： GOTO label … label:9.1.5流程控制語(yǔ)句5．返回語(yǔ)句返回語(yǔ)句是指RETURN語(yǔ)句，后跟整型值。其語(yǔ)法格式如下：RETURN[integer_expression]參數(shù)integer_expression為返回的整型值。RETURN語(yǔ)句用于無(wú)條件地終止一個(gè)查詢、存儲(chǔ)過(guò)程或者批處理，其后的語(yǔ)句不再執(zhí)行。9.1.6常用函數(shù)函數(shù)是用來(lái)執(zhí)行一些特定功能并有返回值的一組T-SQL語(yǔ)句，每個(gè)函數(shù)都有一個(gè)名稱，在名稱之后有一對(duì)括號(hào)()，通常將這組T-SQL語(yǔ)句稱為函數(shù)體。SQLServer2019提供了兩類函數(shù)：系統(tǒng)內(nèi)置函數(shù)和用戶自定義函數(shù)。1．系統(tǒng)內(nèi)置函數(shù)常用的系統(tǒng)內(nèi)置函數(shù)包括轉(zhuǎn)換函數(shù)、字符串函數(shù)、日期函數(shù)、系統(tǒng)函數(shù)、數(shù)學(xué)函數(shù)等。由于這些函數(shù)是系統(tǒng)內(nèi)置的，在使用時(shí)用戶直接調(diào)用即可。9.1.6常用函數(shù)2．用戶自定義函數(shù)SQLServer2019不但提供了系統(tǒng)內(nèi)置函數(shù)，還允許用戶自定義函數(shù)，并將其納入數(shù)據(jù)庫(kù)對(duì)象中管理。用戶自定義函數(shù)往往實(shí)現(xiàn)一個(gè)獨(dú)立功能，方便用戶重復(fù)調(diào)用。用戶可以利用T-SQL命令來(lái)創(chuàng)建（CREATEFUNCTION）、修改（ALTERFUNCTION）和刪除（DROPFUNCTION）用戶自定義函數(shù)。根據(jù)函數(shù)返回值類型的不同，用戶自定義函數(shù)可分為標(biāo)量值函數(shù)和表值函數(shù)兩大類。其中，標(biāo)量值函數(shù)的返回值是單個(gè)數(shù)據(jù)值；表值函數(shù)又分為內(nèi)聯(lián)表值函數(shù)和多語(yǔ)句表值函數(shù)，它們均返回table類型的數(shù)據(jù)。9.1.6常用函數(shù)2．用戶自定義函數(shù)——標(biāo)量值函數(shù)使用T-SQL創(chuàng)建標(biāo)量值函數(shù)的語(yǔ)法為：CREATEFUNCTIONfunction_name([{@parameter_name[as]parameter_data_type[=default][READONLY]}[,…n]])RETURNSreturn_data_type[WITHENCRYPTION][AS]BEGINfunction_bodyreturnscalar_expressionEND9.1.6常用函數(shù)2．用戶自定義函數(shù)——標(biāo)量值函數(shù)相關(guān)參數(shù)的說(shuō)明如下：function_name：函數(shù)名，必須符合標(biāo)識(shí)符規(guī)則，應(yīng)見(jiàn)名知義，函數(shù)名后要加括號(hào)。@parameter_name：用戶自定義函數(shù)中的參數(shù)?？陕暶饕粋€(gè)或多個(gè)參數(shù)，以@開(kāi)頭，必須符合標(biāo)識(shí)符規(guī)則，多個(gè)參數(shù)間用逗點(diǎn)隔開(kāi)。parameter_data_type：參數(shù)的數(shù)據(jù)類型。default：參數(shù)的默認(rèn)值。如果定義了default值，則無(wú)須指定此參數(shù)的值即可執(zhí)行函數(shù)。READONLY：不能在函數(shù)定義中更新或修改參數(shù)。如果參數(shù)類型為用戶定義的表類型，則應(yīng)指定READONLY。9.1.6常用函數(shù)2．用戶自定義函數(shù)——標(biāo)量值函數(shù)return_data_type：用戶自定義函數(shù)的返回值類型。對(duì)于T-SQL函數(shù)，可以使用除timestamp數(shù)據(jù)類型之外的所有數(shù)據(jù)類型。WITHENCRYPTION：數(shù)據(jù)庫(kù)引擎會(huì)將CREATEFUNCTION語(yǔ)句的原始文本轉(zhuǎn)換為模糊格式。模糊代碼的輸出在任何目錄視圖中都不能直接顯示。對(duì)系統(tǒng)表或數(shù)據(jù)庫(kù)文件沒(méi)有訪問(wèn)權(quán)限的用戶不能檢索模糊文本。BEGIN和END之間定義函數(shù)體，該函數(shù)體中必須包括一條return語(yǔ)句，用于返回一個(gè)值。function_body：指定一系列定義函數(shù)值的T-SQL語(yǔ)句。scalar_expression：指定標(biāo)量值函數(shù)返回的標(biāo)量值?！纠?.11】自定義一個(gè)標(biāo)量值函數(shù)，判斷一個(gè)任意的三位數(shù)是否為水仙花數(shù)。如是，函數(shù)返回1，否則返回0，數(shù)值由用戶通過(guò)參數(shù)傳遞給函數(shù)。CREATEFUNCTIONsxhs(@n,int)RETURNSINTASBEGINDECLARE@xINT,@yINT,@zINT,@mINT,@signINTSET@x=@n/100SET@y=(@n-@x*100)/10SET@z=@n%10程序接下一頁(yè)【例9.11】自定義一個(gè)標(biāo)量值函數(shù)，判斷一個(gè)任意的三位數(shù)是否為水仙花數(shù)。如是，函數(shù)返回1，否則返回0，數(shù)值由用戶通過(guò)參數(shù)傳遞給函數(shù)。SET@m=@x*@x*@x+@y*@y*@y+@z*@z*@zIF(@n=@m)SET@sign=1ELSESET@sign=0RETURN@signEND9.1.6常用函數(shù)2．用戶自定義函數(shù)——內(nèi)聯(lián)表值函數(shù)使用T-SQL創(chuàng)建內(nèi)聯(lián)表值函數(shù)的語(yǔ)法為：CREATEFUNCTIONfunction_name([{@parameter_name[as]parameter_data_type[=default][READONLY]}[,…n]])RETURNSTABLE[WITHENCRYPTION][AS]RETURN(selectstatement)9.1.6常用函數(shù)2．用戶自定義函數(shù)——內(nèi)聯(lián)表值函數(shù)內(nèi)聯(lián)表值函數(shù)中參數(shù)的用法與標(biāo)量值函數(shù)相同。需要明確的是：內(nèi)聯(lián)表值函數(shù)沒(méi)有函數(shù)體；與標(biāo)量值函數(shù)不同的是，內(nèi)聯(lián)表值函數(shù)返回值是table類型數(shù)據(jù)，即是一個(gè)表，且表中的內(nèi)容由RETURN子句中的selectstatement決定?！纠?.12】設(shè)計(jì)內(nèi)聯(lián)表值函數(shù)，查詢teacher表中每位教師的姓名和職稱。CREATEFUNCTIONt1()RETURNSTABLEASRETURNSELECTTNO,ProfFROMteacher9.1.6常用函數(shù)2．用戶自定義函數(shù)——多語(yǔ)句表值函數(shù)多語(yǔ)句表值函數(shù)也稱為多聲明表值型函數(shù)，是標(biāo)量值函數(shù)和內(nèi)聯(lián)表值函數(shù)的結(jié)合體。同內(nèi)聯(lián)表值函數(shù)一樣，它的返回值也是一個(gè)表，但它和標(biāo)量值函數(shù)一樣有一個(gè)用BEGIN…END語(yǔ)句括起來(lái)的函數(shù)體，返回值的表中數(shù)據(jù)是由函數(shù)體中的語(yǔ)句插入的，可進(jìn)行多次查詢。創(chuàng)建多語(yǔ)句表值函數(shù)的語(yǔ)法結(jié)構(gòu)如下所示：9.1.6常用函數(shù)2．用戶自定義函數(shù)——多語(yǔ)句表值函數(shù)CREATEFUNCTIONfunction_name([{@parameter_name[as]parameter_data_type[=default][READONLY]}[,…n]])RETURNS@return_variableTABLE<table_type_definition>[WITHENCRYPTION][AS]BEGINfunction_bodyRETURNEND9.1.6常用函數(shù)2．用戶自定義函數(shù)——多語(yǔ)句表值函數(shù)多語(yǔ)句表值函數(shù)的語(yǔ)法格式中參數(shù)的使用與標(biāo)量值函數(shù)和內(nèi)聯(lián)表值函數(shù)相同。需要特別說(shuō)明的是：RETURNS@return_variableTABLE<table_type_definition>指明函數(shù)返回的是table類型的局部變量，且緊隨其后要對(duì)其進(jìn)行表結(jié)構(gòu)的定義。在多語(yǔ)句表值函數(shù)的函數(shù)體中必須包括一條不帶參數(shù)的RETURN語(yǔ)句用于返回表。游標(biāo)029.2游標(biāo)關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)的實(shí)質(zhì)是面向集合的，SELECT查詢之后返回的是結(jié)果集，即系列行數(shù)據(jù)的集合，在SQLServer中并沒(méi)有一種描述表中單一記錄的表達(dá)形式，除非使用WHERE子句限制只有一條記錄被選中。用戶在利用T-SQL設(shè)計(jì)程序時(shí)，可能會(huì)根據(jù)每條記錄值的不同分別采取不同的處理策略，也就是說(shuō)需要逐行對(duì)結(jié)果集進(jìn)行處理。因此，必須借助游標(biāo)進(jìn)行面向單條記錄的數(shù)據(jù)處理。9.2.1游標(biāo)的概念游標(biāo)是T-SQL支持的一種對(duì)象，是處理結(jié)果集的一種機(jī)制。游標(biāo)本質(zhì)是系統(tǒng)開(kāi)辟的一塊內(nèi)存區(qū)域，用來(lái)臨時(shí)保存SELECT等SQL語(yǔ)句執(zhí)行后返回的結(jié)果集。游標(biāo)允許應(yīng)用程序?qū)Σ樵冋Z(yǔ)句SELECT返回的結(jié)果集中的每一行進(jìn)行相同或不同的操作，而不是一次對(duì)整個(gè)結(jié)果集進(jìn)行同一種操作。它還能夠基于游標(biāo)位置而對(duì)表中數(shù)據(jù)進(jìn)行刪除或更新。此外，正是游標(biāo)把面向集合的數(shù)據(jù)庫(kù)管理系統(tǒng)和面向行的程序設(shè)計(jì)兩者聯(lián)系起來(lái)，使兩種數(shù)據(jù)處理方式能夠進(jìn)行溝通。9.2.2游標(biāo)的語(yǔ)法格式T-SQL游標(biāo)由DECLARECURSOR語(yǔ)法定義，主要用在T-SQL腳本、存儲(chǔ)過(guò)程和觸發(fā)器中。每個(gè)游標(biāo)必須有四個(gè)組成部分，這四個(gè)關(guān)鍵部分必須符合以下順序：首先是DECLARE游標(biāo)（聲明），其次是OPEN游標(biāo)（打開(kāi)），再次是從一個(gè)游標(biāo)中FETCH信息（推進(jìn)），最后是CLOSE、DELLOCATE游標(biāo)（關(guān)閉及釋放）。9.2.2游標(biāo)的語(yǔ)法格式1．聲明游標(biāo)使用DECLARE聲明一個(gè)游標(biāo)，主要包括以下內(nèi)容：游標(biāo)名稱、數(shù)據(jù)來(lái)源（表和列）、選取條件、屬性（僅讀或可修改）。其具體語(yǔ)法格式如下所示：DECLAREcursor_nameCURSORFORselect_statement[FORREADONLY|UPDATE[OFcolumn_name[,...n]]]9.2.2游標(biāo)的語(yǔ)法格式2.打開(kāi)游標(biāo)打開(kāi)游標(biāo)的語(yǔ)法格式相對(duì)簡(jiǎn)單：OPENcursor_name3．推進(jìn)游標(biāo)推進(jìn)游標(biāo)的語(yǔ)法格式如下：FETCH[NEXT|PRIOR|FIRST|LAST|]FROMcursor_name[into@variable_name[,...]]9.2.2游標(biāo)的語(yǔ)法格式4．關(guān)閉、釋放游標(biāo)關(guān)閉、釋放游標(biāo)的語(yǔ)法格式相對(duì)簡(jiǎn)單，如下所示：CLOSEcursor_name上述語(yǔ)句關(guān)閉游標(biāo)并釋放資源，如有需要可使用OPEN重啟游標(biāo)。DELLOCATEcursor_name游標(biāo)使用示例USETeachSystemGO--定義局部變量DECLARE@Cnonvarchar(6),@Cnamenvarchar(20)--定義游標(biāo)DECLARECrsCourseCURSORFORSELECTCNO,CNFROMcourseORDERBYCNO--打開(kāi)游標(biāo)OPENCrsCourse游標(biāo)使用示例--打開(kāi)首行數(shù)據(jù)FETCHNEXTFROMCrsCourseinto@Cno,@Cname--循環(huán)推進(jìn)WHILE@@FETCH_STATUS=0--通過(guò)游標(biāo)狀態(tài)構(gòu)造循環(huán)，逐行輸出

BEGINPRINT'課程號(hào):'+@Cno+'課程名稱:'+@CnameFETCHNEXTFROMCrsCourseINTO@Cno,@Cname--取下一行數(shù)據(jù)

ENDCLOSECrsCourse --關(guān)閉游標(biāo)DELLOCATECrsCourse--釋放游標(biāo)9.2.3游標(biāo)的局限游標(biāo)的優(yōu)點(diǎn)是可以方便地從一個(gè)結(jié)果集中循環(huán)遍歷數(shù)據(jù)再進(jìn)行操作。但是，正因?yàn)橛螛?biāo)可將結(jié)果集中的數(shù)據(jù)逐條取出進(jìn)行處理，從而增加了服務(wù)器的負(fù)擔(dān)。再者，使用游標(biāo)的效率遠(yuǎn)遠(yuǎn)沒(méi)有使用默認(rèn)的結(jié)果集的效率高。在默認(rèn)的結(jié)果集中，從客戶端發(fā)送到服務(wù)器的唯一一個(gè)數(shù)據(jù)包是包含需執(zhí)行語(yǔ)句的數(shù)據(jù)包。而在使用服務(wù)器的游標(biāo)時(shí)，每個(gè)FETCH語(yǔ)句都必須從客戶端發(fā)送到服務(wù)器，然后在服務(wù)器中將它解析并編譯為執(zhí)行計(jì)劃。因此，很多時(shí)候復(fù)雜和低效是沒(méi)有使用游標(biāo)的主要原因，除非要在SQLServer上進(jìn)行很復(fù)雜的數(shù)據(jù)操作。存儲(chǔ)過(guò)程039.3.1存儲(chǔ)過(guò)程的概念及特點(diǎn)1.存儲(chǔ)過(guò)程的概念

存儲(chǔ)過(guò)程（StoredProcedure），是一組為了完成特定功能的SQL語(yǔ)句集，由流程控制語(yǔ)句和SQL語(yǔ)句編寫，經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫(kù)中，用戶通過(guò)指定存儲(chǔ)過(guò)程的名字并給出參數(shù)（如果該存儲(chǔ)過(guò)程帶有參數(shù)）來(lái)執(zhí)行，并將返回值反饋給調(diào)用存儲(chǔ)過(guò)程的用戶。9.3.1存儲(chǔ)過(guò)程的概念及特點(diǎn)2.存儲(chǔ)過(guò)程的優(yōu)點(diǎn)當(dāng)需要在不同的應(yīng)用程序或平臺(tái)上執(zhí)行相同的函數(shù)或者封裝特定功能時(shí)，存儲(chǔ)過(guò)程是非常有用的。數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程可以看作是對(duì)編程中面向?qū)ο蠓椒ǖ哪M，它允許控制數(shù)據(jù)的訪問(wèn)方式。存儲(chǔ)過(guò)程具有以下優(yōu)點(diǎn)。（1）模塊化的程序設(shè)計(jì)，獨(dú)立修改，增強(qiáng)了代碼的重用性和共享性。（2）執(zhí)行效率高，一次編譯。（3）網(wǎng)絡(luò)流量大幅減少，減輕網(wǎng)絡(luò)負(fù)載。（4）提高數(shù)據(jù)庫(kù)安全性。9.3.1存儲(chǔ)過(guò)程的概念及特點(diǎn)2.存儲(chǔ)過(guò)程的分類SQLServer2019主要支持以下3種不同類型的存儲(chǔ)過(guò)程。（1）系統(tǒng)存儲(chǔ)過(guò)程用來(lái)執(zhí)行許多管理和信息活動(dòng)。用戶可在應(yīng)用程序中直接調(diào)用系統(tǒng)存儲(chǔ)過(guò)程，以sp_為前綴。（2）用戶自定義存儲(chǔ)過(guò)程用戶自定義存儲(chǔ)過(guò)程是指用戶根據(jù)應(yīng)用程序的需要，有效地利用存儲(chǔ)過(guò)程的優(yōu)點(diǎn)，將業(yè)務(wù)流程中相對(duì)穩(wěn)定、有獨(dú)立功能、高頻操作部分自定義成存儲(chǔ)過(guò)程。（3）擴(kuò)展存儲(chǔ)過(guò)程擴(kuò)展存儲(chǔ)過(guò)程是用戶可以使用外部程序語(yǔ)言編寫的存儲(chǔ)過(guò)程，用來(lái)擴(kuò)展SQLServer服務(wù)器功能，以xp_為前綴。9.3.2創(chuàng)建存儲(chǔ)過(guò)程SQLServer2019提供給用戶兩種方法創(chuàng)建存儲(chǔ)過(guò)程：其一是使用CREATEPROCEDURE語(yǔ)句創(chuàng)建；其二是使用對(duì)象資源管理器創(chuàng)建。數(shù)據(jù)庫(kù)所有者享有存儲(chǔ)過(guò)程的管理權(quán)，可通過(guò)授權(quán)機(jī)制把許可權(quán)授權(quán)給其他用戶。無(wú)論采取哪種方式創(chuàng)建存儲(chǔ)過(guò)程，均需確定存儲(chǔ)過(guò)程的3個(gè)組成部分：所有的輸入?yún)?shù)及傳給調(diào)用者的輸出參數(shù)；被執(zhí)行的針對(duì)數(shù)據(jù)庫(kù)的操作語(yǔ)句，包括調(diào)用其他存儲(chǔ)過(guò)程的語(yǔ)句；返回給調(diào)用者的狀態(tài)值，以指明調(diào)用是成功還是失敗。9.3.2創(chuàng)建存儲(chǔ)過(guò)程1．使用CREATEPROCEDURE語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程其語(yǔ)法格式如下所示：CREATEPROCEDUREprocedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...n]9.3.2創(chuàng)建存儲(chǔ)過(guò)程用CREATEPROCEDURE命令創(chuàng)建存儲(chǔ)過(guò)程，既可以創(chuàng)建不帶參數(shù)的存儲(chǔ)過(guò)程，也可以創(chuàng)建帶參數(shù)的存儲(chǔ)過(guò)程?！纠?.14】在TeachSystem數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)名稱為TeacherProc的不帶參數(shù)的存儲(chǔ)過(guò)程，該存儲(chǔ)過(guò)程的功能是從數(shù)據(jù)表teacher中查詢所有教授的教師信息。USETeachSystemGOCREATEPROCEDURETeacherProcASSELECT*FROMteacherWHEREProf='教授'9.3.2創(chuàng)建存儲(chǔ)過(guò)程【例9.15】對(duì)于學(xué)生選課管理而言，新增一條選課記錄是高頻操作。創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，完成向SC表中插入一條新選課記錄。USETeachSystemGOCREATEPROCEDUREInsertRecord(@sno nchar(6),@cno nchar(6),@scorenumeric(4))ASINSERTINTOSCVALUES(@sno,@cno,@score)GO9.3.2創(chuàng)建存儲(chǔ)過(guò)程【例9.16】創(chuàng)建和執(zhí)行帶輸出參數(shù)的存儲(chǔ)過(guò)程，統(tǒng)計(jì)選修某一門課程所有學(xué)生的平均成績(jī)，該門課程的課程編號(hào)由用戶輸入。USETeachSystemGOCREATEPROCEDUREAvgC(@cnonchar(6)OUTPUT,@avgnumeric(4)OUTPUT)AS程序接下一頁(yè)9.3.2創(chuàng)建存儲(chǔ)過(guò)程【例9.16】創(chuàng)建和執(zhí)行帶輸出參數(shù)的存儲(chǔ)過(guò)程，統(tǒng)計(jì)選修某一門課程所有學(xué)生的平均成績(jī)，該門課程的課程編號(hào)由用戶輸入。SELECT@cno=CNO,@avg=AVG(Score)FROMSCWHERECNO=@cnoGROUPBYCNOGO9.3.2創(chuàng)建存儲(chǔ)過(guò)程2．使用對(duì)象資源管理器創(chuàng)建存儲(chǔ)過(guò)程

在選定的數(shù)據(jù)庫(kù)下打開(kāi)“可編程性”選項(xiàng)，右擊“存儲(chǔ)過(guò)程”選項(xiàng)，在彈出的快捷菜單中選擇“新建存儲(chǔ)過(guò)程”命令。在新建的查詢窗口中可以看到關(guān)于創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)句模板，在其中添加相應(yīng)的內(nèi)容，單擊工具欄上的“執(zhí)行”按鈕即可。9.3.3查看、刪除、修改和重命名存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程作為數(shù)據(jù)庫(kù)的操作對(duì)象，包含刪除、修改和重命名操作，通過(guò)利用對(duì)象資源管理器創(chuàng)建存儲(chǔ)過(guò)程的例子，可以發(fā)現(xiàn)使用資源管理器進(jìn)行這些操作更為便利。當(dāng)然，使用T-SQL命令也可完成對(duì)存儲(chǔ)過(guò)程的編輯。1.查看存儲(chǔ)過(guò)程

用戶可以通過(guò)SQLServer提供的系統(tǒng)存儲(chǔ)過(guò)程sp_helptext來(lái)查看用戶自己創(chuàng)建的存儲(chǔ)過(guò)程的相關(guān)信息，其語(yǔ)法格式如下： sp_helptextprocedure_name2.刪除存儲(chǔ)過(guò)程 DROPPROCEDURE{procedure_name}[,...n]9.3.3查看、刪除、修改和重命名存儲(chǔ)過(guò)程3.修改存儲(chǔ)過(guò)程ALTERPROCEDUREprocedure_name[,number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[,...n]可以發(fā)現(xiàn)，修改存儲(chǔ)過(guò)程的語(yǔ)法和創(chuàng)建存儲(chǔ)過(guò)程時(shí)基本一致，只是關(guān)鍵字的使用上用ALTER代替了CREATE。9.3.3查看、刪除、修改和重命名存儲(chǔ)過(guò)程4.重命名存儲(chǔ)過(guò)程用戶可以通過(guò)ManagementStudio可視化界面快速重命名存儲(chǔ)過(guò)程。具體步驟如下。（1）打開(kāi)ManagementStudio，在左側(cè)對(duì)象資源管理器中依次展開(kāi)“數(shù)據(jù)庫(kù)”→“存儲(chǔ)過(guò)程所屬數(shù)據(jù)庫(kù)”→“可編程性”選項(xiàng)。（2）展開(kāi)“存儲(chǔ)過(guò)程”選項(xiàng)，根據(jù)名稱找到需要重命名的存儲(chǔ)過(guò)程。（3）右擊該存儲(chǔ)過(guò)程，在彈出的快捷菜單中選擇“重命名”命令，即可對(duì)該存儲(chǔ)過(guò)程的重命名。觸發(fā)器049.4.1觸發(fā)器的概念和工作原理1.觸發(fā)器的概念觸發(fā)器是一組T-SQL語(yǔ)句，是在對(duì)表進(jìn)行插入、更新或刪除操作時(shí)自動(dòng)執(zhí)行的存儲(chǔ)過(guò)程。與存儲(chǔ)過(guò)程相比，二者的不同點(diǎn)主要體現(xiàn)在：觸發(fā)器不能使用EXECUTE語(yǔ)句，由用戶使用T-SQL語(yǔ)句時(shí)自動(dòng)觸發(fā)，是自動(dòng)執(zhí)行的且不含參數(shù)。觸發(fā)器的特點(diǎn)是：它是一種在基本表被修改時(shí)自動(dòng)執(zhí)行的內(nèi)嵌過(guò)程，主要通過(guò)事件或動(dòng)作進(jìn)行觸發(fā)而被執(zhí)行；它不像存儲(chǔ)過(guò)程能通過(guò)名稱被直接調(diào)用，更不允許帶參數(shù)；它主要用于約束、默認(rèn)值和規(guī)則的完整性檢查，實(shí)施更加復(fù)雜的業(yè)務(wù)規(guī)則。9.4.1觸發(fā)器的概念和工作原理2.觸發(fā)器的分類SQLServer包括3種常規(guī)類型的觸發(fā)器：DML觸發(fā)器、DDL觸發(fā)器和登錄觸發(fā)器。（1）DML觸發(fā)器DML觸發(fā)器是作用在表或視圖上的一種觸發(fā)器，當(dāng)數(shù)據(jù)庫(kù)服務(wù)器中發(fā)生數(shù)據(jù)操作（插入、修改、刪除）事件時(shí)執(zhí)行相關(guān)操作。主要有以下3種。①INSERT觸發(fā)器：向表中插入數(shù)據(jù)時(shí)被觸發(fā)。②DELETE觸發(fā)器：從表中刪除數(shù)據(jù)時(shí)被觸發(fā)。③UPDATE觸發(fā)器：修改表中數(shù)據(jù)時(shí)被觸發(fā)。9.4.1觸發(fā)器的概念和工作原理（2）DDL觸發(fā)器DDL觸發(fā)器是當(dāng)服務(wù)器或者數(shù)據(jù)庫(kù)中發(fā)生以CREATE、DROP、ALTER開(kāi)頭的語(yǔ)句事件時(shí)被激活使用。使用DDL觸發(fā)器可以防止對(duì)數(shù)據(jù)架構(gòu)進(jìn)行某些更改或記錄數(shù)據(jù)中的更改及事件操作。（3）登錄觸發(fā)器

登錄觸發(fā)器是由登錄（LOGON）事件而激活的觸發(fā)器。登錄觸發(fā)器將在用戶身份驗(yàn)證階段完成之后且用戶會(huì)話實(shí)際建立之前被激發(fā)。如果身份驗(yàn)證失敗，將不激發(fā)登錄觸發(fā)器。9.4.1觸發(fā)器的概念和工作原理2.觸發(fā)器的工作原理

每個(gè)觸發(fā)器自動(dòng)在內(nèi)存中創(chuàng)建兩個(gè)特殊的表：插入表（Inserted表）和刪除表（Deleted表）。兩個(gè)表均為只讀的邏輯虛表，其表結(jié)構(gòu)與觸發(fā)器所作用的表的表結(jié)構(gòu)相同。用戶無(wú)權(quán)對(duì)其直接修改，觸發(fā)器工作完成后自動(dòng)刪除這兩個(gè)表。9.4.1觸發(fā)器的概念和工作原理2.觸發(fā)器的工作原理

Inserted表臨時(shí)保存插入或更新后的記錄行，可以利用此表檢查插入的數(shù)據(jù)是否滿足實(shí)際的業(yè)務(wù)需求。如果滿足，接受操作；如果不滿足，則向用戶報(bào)告錯(cuò)誤消息，并回滾操作。Deleted表臨時(shí)保存刪除或更新前的記錄行，可以利用此表檢查被刪除的數(shù)據(jù)是否滿足實(shí)際的業(yè)務(wù)需求。如果滿足，接受操作；如果不滿足，則向用戶報(bào)告錯(cuò)誤消息，并回滾操作。9.4.2創(chuàng)建觸發(fā)器SQLServer2019提供給用戶兩種方式創(chuàng)建觸發(fā)器：使用CREATETRIGGER語(yǔ)句或使用ManagementStudio可視化界面。1．創(chuàng)建DML觸發(fā)器-----使用CREATETRIGGER語(yǔ)句創(chuàng)建DML觸發(fā)器語(yǔ)法格式如下：CREATETRIGGERtrigger_nameON{table_name|view}[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}ASsql_statement[;]9.4.2創(chuàng)建觸發(fā)器【例9.17】使用觸發(fā)器級(jí)聯(lián)刪除，當(dāng)刪除課程表的某一課程信息時(shí)，級(jí)聯(lián)刪除其成績(jī)信息。CREATETRIGGERdel_cONcourseAFTERDELETE ASDELETEFROMSCWHERECNOIN(SELECTCNOFROMdeleted)9.4.2創(chuàng)建觸發(fā)器【例9.18】使用觸發(fā)器，當(dāng)更新SC表的成績(jī)信息時(shí)，要求成績(jī)的取值范圍為0～100。CREATETRIGGERup_scONSCAFTERUPDATEASIF(SELECTScoreFROMINSERTED)NOTBETWEEN0AND100BEGINPRINT'成績(jī)?nèi)≈捣秶鷳?yīng)在0～100！'ROLLBACKTRANSACTIONENDELSEPRINT'更新成功！'9.4.2創(chuàng)建觸發(fā)器1．創(chuàng)建DML觸發(fā)器-----使用ManagementStudio創(chuàng)建觸發(fā)器打開(kāi)ManagementStudio，在左側(cè)對(duì)象資源管理器中依次展開(kāi)“數(shù)據(jù)庫(kù)”→“當(dāng)前使用數(shù)據(jù)庫(kù)”，找到欲創(chuàng)建觸發(fā)器的表。展開(kāi)表選項(xiàng)，找到“觸發(fā)器”選項(xiàng)，右擊，在彈出的快捷菜單中選擇“新建觸發(fā)器…”命令。在右側(cè)工作區(qū)域看到該觸發(fā)器的語(yǔ)句模板，在其中添加相應(yīng)的內(nèi)容，單擊工具欄上的“執(zhí)行”按鈕即可。9.4.2創(chuàng)建觸發(fā)器2．創(chuàng)建DDL觸發(fā)器使用CREATETRIGGER語(yǔ)句創(chuàng)建DDL觸發(fā)器的語(yǔ)法格式如下：CREATETRIGGERtrigger_nameON{ALLSERVER|DATABASE}WITHENCRYPTION{FOR|AFTER|{event_type|event_group}ASsql_statement[;]9.4.2創(chuàng)建觸發(fā)器【例9.19】創(chuàng)建一個(gè)DDL觸發(fā)