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

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

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

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

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

條件表達式

語句塊1[ELSE

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

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

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

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

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

<語句塊>

[BREAK] … [CONTINUE]

… END9.1.5流程控制語句4．等待語句

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

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

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

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

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

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

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

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

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

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