《數(shù)據(jù)庫及基礎(chǔ)》項(xiàng)目8 以程序方式處理學(xué)生信息管理數(shù)據(jù)表

以程序方式處理學(xué)生信息管理數(shù)據(jù)表任務(wù)8-1SQL編程基礎(chǔ)目錄Contents任務(wù)8-2創(chuàng)建與使用存儲過程任務(wù)8-3觸發(fā)器任務(wù)8-4事務(wù)、鎖的概念和應(yīng)用實(shí)訓(xùn)以程序方式處理數(shù)據(jù)表的數(shù)據(jù)任務(wù)8-1SQL編程基礎(chǔ)PART1任務(wù)assignments（一）SQL基礎(chǔ)（二）Transact-SQL的流程控制（三）常用函數(shù)（四）游標(biāo)SQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理【任務(wù)分析】設(shè)計(jì)人員要編寫存儲過程和存儲函數(shù)、觸發(fā)器及事務(wù)，首先要掌握SQL的語法規(guī)范及語言基礎(chǔ)?！菊n堂任務(wù)】熟悉SQL。?SQL的語法規(guī)范?SQL基礎(chǔ)?常用函數(shù)?游標(biāo)的基本操作SQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理（一）SQL基礎(chǔ)Transact-SQL是一系列操作數(shù)據(jù)庫及數(shù)據(jù)庫對象的命令語句，因此了解Transact-SQL語言基本語法和流程語句的構(gòu)成是必須的，Transact-SQL語言中除了關(guān)鍵字，主要包括常量和變量、表達(dá)式、運(yùn)算符、控制語句等。1．常量常量也稱為文字值或標(biāo)量值，是指程序運(yùn)行中其值始終不會改變的量。在Transact-SQL程序設(shè)計(jì)過程中，定義常量的格式取決于它所表示的值的數(shù)據(jù)類型。表8.1列出了SQLServer中的常量類型及常量表示說明。SQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理SQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理SQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理2．變量變量就是在程序執(zhí)行過程中，其值可以改變的量?？梢岳米兞縼泶鎯Τ绦驁?zhí)行過程中涉及的數(shù)據(jù)，如計(jì)算結(jié)果、用戶輸入的字符串以及對象的狀態(tài)等。變量由變量名和變量值構(gòu)成，其類型與常量一樣。變量名不能與命令和函數(shù)名相同，這里的變量和數(shù)學(xué)中變量的概念基本上一樣，可以隨時(shí)改變它對應(yīng)的數(shù)值。一些Transact-SQL系統(tǒng)函數(shù)的名稱以兩個(gè)@@符號開頭。在舊版SQLServer中，@@函數(shù)稱為全局變量，但它們不是變量，不具有等同于變量的行為。@@函數(shù)是系統(tǒng)函數(shù)，語法遵循函數(shù)規(guī)則。SQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理Transact-SQL局部變量是可以保存單個(gè)特定類型數(shù)據(jù)值的對象，是用戶自定義的變量，它的作用域從聲明變量的地方開始到聲明變量的批處理或存儲過程的結(jié)尾。局部變量用DECLARE語句聲明。語法格式如下。DECLARE{@variable_namedatatype}[,…n]參數(shù)說明如下。?@variable_name：局部變量名稱。用戶可以自定義符合SQLServer標(biāo)識符命名規(guī)則的名稱，但名稱首字符必須為@。?datatype：局部變量使用的數(shù)據(jù)類型，可以是指定系統(tǒng)提供的或用戶定義的數(shù)據(jù)類型和長度。對于數(shù)值變量，還指定精度和小數(shù)位數(shù)。對于XML類型的變量，可以指定一個(gè)可選的架構(gòu)集合。SQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理局部變量用DECLARE聲明后，都被賦予初值NULL。要給局部變量賦值，可以使用SET或SELECT語句，語法格式如下所示。SET@variable_name=expression[,…n]SELECT@variale_name=expression[,…n]其中，@variable_name是局部變量名，expression是任何有效的SQLServer表達(dá)式?！纠?.1】定義名為hello的局部變量，數(shù)據(jù)類型為char，長度為20，并為其賦值“hello,China！”。DECLARE@hellochar(20)SET@hello='hello,China!'【例8.2】定義名為student1的局部變量，使用查詢結(jié)果其賦值。DECLARE@student1char（8）SET@student1=(SELECTsnameFROMstudentWHEREsno=’20050101’)SQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理【例8.3】查詢student表中名字是例8.2中@student1值的學(xué)生信息。SELECTsno,snameFROMstudentWHEREsname=@student1【例8.4】查詢grademanager數(shù)據(jù)庫的student表中“系別”為“計(jì)算機(jī)”的學(xué)生信息。USEgrademanagerDECLARE@系別char(10)SET@系別='計(jì)算機(jī)'SELECTsno,sname,saddressFROMstudentWHEREsdept=@系別SQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理3．表達(dá)式在Transact-SQL中，表達(dá)式就是常量、變量、列名、復(fù)雜計(jì)算、運(yùn)算符和函數(shù)的組合。表達(dá)式通常都有返回值。與常量和變量一樣，表達(dá)式的值也具有某種數(shù)據(jù)類型。根據(jù)表達(dá)式值的類型，表達(dá)式可分為字符型表達(dá)式、數(shù)值型表達(dá)式和日期型表達(dá)式。表達(dá)式一般用在SELECT和SELECT語句的WHERE子句中。SQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理【例8.5】查詢學(xué)生的“學(xué)號”“平均成績”及“考生信息”3列，其中考生信息列由學(xué)生“姓名”“性別”“班級編號”和“年級”這些來自student表的數(shù)據(jù)組成。查詢結(jié)果按平均成績降序排列，使用表達(dá)式的SELECT查詢語句如下。SELECTA.sno,AVG(degree)AS'平均成績',sname+SPACE(6)+ssex+SPACE(4)+classno+'班'+SPACE(4)+left(classno,4)+'年級'AS'考生信息'FROMscAINNERJOINstudentBONA.sno=B.snoGROUPBYA.sno,sname,ssex,classnoORDERBY平均成績DESC在上述語句中同時(shí)使用了表別名、列別名、字符串連接運(yùn)算符、求平均值函數(shù)、系統(tǒng)字符串函數(shù)、內(nèi)連接和各種數(shù)據(jù)列等。SQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理（二）Transact-SQL的流程控制Transact-SQL的基本結(jié)構(gòu)是順序結(jié)構(gòu)、條件分支結(jié)構(gòu)和循環(huán)結(jié)構(gòu)。順序結(jié)構(gòu)是一種自然結(jié)構(gòu)，條件分支結(jié)構(gòu)和循環(huán)結(jié)構(gòu)需要根據(jù)程序的執(zhí)行情況調(diào)整和控制程序的執(zhí)行順序。在Transact-SQL中，流程控制語句就是用來控制程序執(zhí)行流程的語句，也稱流控制語句或控制流語句。SQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理1．BEGIN…ENDBEGIN…END語句用于將多個(gè)Transact-SQL語句組合為一個(gè)邏輯塊。語句塊允許嵌套。當(dāng)流程控制語句必須執(zhí)行一個(gè)包含兩條或兩條以上Transact-SQL語句的語句塊時(shí)，使用BEGIN…END。其語法格式如下。BEGIN{sql_statement|statement_block}END其中，sql_statement是使用語句塊定義的任何有效的Transact-SQL語句；statement_block是使用語句塊定義的任何有效Transact-SQL語句塊。SQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理2．IF…ELSEIF…ELSE語句用于指定Transact-SQL語句的執(zhí)行條件。如果條件為真，則執(zhí)行條件表達(dá)式后面的Transact-SQL語句。當(dāng)條件為假時(shí)，可以用ELSE關(guān)鍵字指定要執(zhí)行的Transact-SQL語句。該語句的語法格式如下。IFBoolean_expression{sql_statement|statement_block}ELSE{sql_statement|statement_block}其中，Boolean_expression是返回true或false的邏輯表達(dá)式。如果布爾表達(dá)式中含有SELECT語句，則必須用圓括號將SELECT語句括起來。SQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理【例8.6】使用IF…ELSE語句查詢計(jì)算機(jī)系的辦公地點(diǎn)，如果為空，則顯示“辦公地點(diǎn)不詳”，否則顯示其辦公地點(diǎn)。USEgrademanagerGOIF(SELECTofficeFROMdepartmentWHEREdeptname='計(jì)算機(jī)系')ISNULLBEGINPRINT'辦公地點(diǎn)不詳'SELECT*FROMdepartmentWHEREdeptname='計(jì)算機(jī)系'ENDELSESELECTofficeFROMdepartmentWHEREdeptname='計(jì)算機(jī)系'SQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理3．CASE語句CASE語句可根據(jù)表達(dá)式的真假來確定是否返回某個(gè)值，可以在表達(dá)式的任何位置使用這一關(guān)鍵字。使用CASE語句可以選擇多個(gè)分支。CASE語句兩種格式。（1）簡單格式：將某個(gè)表達(dá)式與一組簡單表達(dá)式進(jìn)行比較以確定結(jié)果。其語法格式如下。CASEinput_expressionWHENwhen_expressionTHENresult_expression[...n][ELSEelse_result_expression]END（2）搜索格式：計(jì)算一組布爾表達(dá)式以確定結(jié)果。其語法格式如下。CASEWHENBoolean_expressionTHENresult_expression[...n][ELSEelse_result_expression]ENDSQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理參數(shù)說明如下。①input_expression：使用簡單CASE格式時(shí)計(jì)算的表達(dá)式，可以是任何有效的表達(dá)式。②when_expression：用來與input_expression表達(dá)式比較的表達(dá)式，input_expression與每個(gè)when_expression表達(dá)式的數(shù)據(jù)類型必須相同，或者可以隱式轉(zhuǎn)換。③result_expression：表示當(dāng)input_expression=when_expression的取值為true時(shí)，需要返回的表達(dá)式。④else_result_expression：表示當(dāng)input_expression=when_expression的取值為false時(shí)，需要返回的表達(dá)式。⑤Boolean_expression：使用CASE搜索格式時(shí)計(jì)算的布爾表達(dá)式，可以是任何有效的布爾表達(dá)式。SQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理【例8.7】利用CASE語句查詢學(xué)生的考試成績，顯示成績的檔次（A~D）。USEgrademanagerGOSELECTsnameAS‘姓名’，degree=CASEWHENdegree>=90THEN‘A’WHENdegree>=75ANDdegree<90THEN‘B’WHENdegree>=60ANDdegree<75THEN‘C’WHENdegree<60ANDdegree>0THEN‘D’ENDFROMstudenta,scbWHEREa.sno=b.snoSQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理4．WHILE語句WHILE語句用于設(shè)置重復(fù)執(zhí)行Transact-SQL語句或語句塊的條件。當(dāng)指定的條件為真時(shí)，重復(fù)執(zhí)行循環(huán)語句。可以在循環(huán)體內(nèi)設(shè)置BREAK和CONTINUE關(guān)鍵字，以便控制循環(huán)語句的執(zhí)行過程，其語法格式如下。WHILEBoolean_expression{sql_statement|statement_block|BREAK|CONTINUE}SQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理參數(shù)說明如下。Boolean_expression：返回true或false的表達(dá)式。如果布爾表達(dá)式中含有SELECT語句，則必須用括號將SELECT語句括起來。sql_statement|statement_block：Transact-SQL語句或用語句塊定義的語句分組。若要定義語句塊，則使用控制流關(guān)鍵字BEGIN和END。BREAK：使程序從最內(nèi)層的WHILE循環(huán)中退出，執(zhí)行END關(guān)鍵字后面的任何語句，END關(guān)鍵字為循環(huán)結(jié)束標(biāo)記。CONTINUE：使WHILE循環(huán)重新開始執(zhí)行，忽略CONTINUE關(guān)鍵字后面的任何語句。SQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理【例8.8】使用WHILE語句求1～100之和。DECLARE@iint,@sumintSELECT@i=1,@sum=0WHILE@i<=100BEGINSET@sum=@sum+@iSET@i=@i+1ENDSELECT@sumSQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理5．注釋注釋是程序代碼中不被執(zhí)行的文本字符串，用于對代碼進(jìn)行說明或診斷。雙連字符(--)：用于單行或嵌套的注釋。用--插入的注釋由換行符終止，沒有最大長度限制。斜杠星型(/*…*/)：也稱塊注釋，可以插入單獨(dú)行中，也可以插入Transact-SQL語句中，支持嵌套注釋。多行注釋必須用/*和*/指明，沒有最大長度限制。SQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理（三）常用函數(shù)SQLServer為Transact-SQL提供了大量的系統(tǒng)函數(shù)，它們功能強(qiáng)大，方便易用。使用這些函數(shù)，可以極大地提高數(shù)據(jù)庫的管理效率。SQLServer的常用函數(shù)見表8.2。SQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理SQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理SQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理（四）游標(biāo)游標(biāo)(Cursor)是類似于C語言指針的結(jié)構(gòu)，在SQLServer中，它是一種數(shù)據(jù)訪問機(jī)制，允許用戶訪問單獨(dú)的數(shù)據(jù)行，而不是對整個(gè)行集進(jìn)行操作。在SQLServer中，游標(biāo)主要包括游標(biāo)結(jié)果集和游標(biāo)位置兩部分，游標(biāo)結(jié)果集是由定義游標(biāo)的SELECT語句返回行的集合，游標(biāo)位置則是指向這個(gè)結(jié)果集中某一行的指針。在使用游標(biāo)之前首先要聲明游標(biāo)，定義Transact-SQL服務(wù)器游標(biāo)的屬性，如游標(biāo)的滾動行為和用于生成游標(biāo)所操作結(jié)果集的查詢。聲明游標(biāo)的語法格式如下。DECLAREcursor_nameCURSORFORselect_statementSQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理參數(shù)說明如下。cursor_name：游標(biāo)定義的名稱，必須符合SQL標(biāo)識符規(guī)則。select_statement：是定義游標(biāo)結(jié)果集的標(biāo)準(zhǔn)SELECT語句?！纠?.10】在grademanager數(shù)據(jù)庫中為teacher表創(chuàng)建一個(gè)普通的游標(biāo)，名稱為T_cursor。DECLARET_cursorCURSORFORSELECT*FROMteacher聲明游標(biāo)后，就可對游標(biāo)進(jìn)行操作，主要包括打開游標(biāo)、檢索游標(biāo)、關(guān)閉游標(biāo)和釋放游標(biāo)。SQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理1．打開游標(biāo)使用游標(biāo)之前必須先打開游標(biāo)，打開游標(biāo)的語法格式如下。OPENcursor_name【例8.11】打開前面創(chuàng)建T_cursor游標(biāo)。OPENT_cursor2．檢索游標(biāo)打開游標(biāo)以后，就可以提取數(shù)據(jù)了。FETCH語句的功能是從游標(biāo)中將數(shù)據(jù)檢索出來，以便用戶能夠使用這個(gè)數(shù)據(jù)。檢索游標(biāo)的語法格式如下。FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]FROM]cursor_nameSQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理參數(shù)說明如下。NEXT：緊跟當(dāng)前行返回結(jié)果行，并且當(dāng)前行遞增為返回行。如果FETCHNEXT為對游標(biāo)的第一次提取操作，則返回結(jié)果集中的第一行。NEXT為默認(rèn)的游標(biāo)提取選項(xiàng)。PRIOR：返回緊鄰當(dāng)前行前面的結(jié)果行，并且當(dāng)前行遞減為返回行。如果FETCHPRIOR為對游標(biāo)的第一次提取操作，則沒有行返回并且游標(biāo)置于第一行之前。FIRST：返回游標(biāo)中的第一行并將其作為當(dāng)前行。LAST：返回游標(biāo)中的最后一行并將其作為當(dāng)前行。SQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理ABSOLUTE{n|@nvar}：n是整數(shù)常量，@nvar是smallint、tinyint或int類型的值。如果n或@nvar為正，則返回從游標(biāo)起始處開始向后的第n行，并將返回行變成新的當(dāng)前行。如果n或@nvar為負(fù)，則返回從游標(biāo)末尾處開始向前的第n行，并將返回行變成新的當(dāng)前行。如果n或@nvar為0，則不返回行。RELATIVE{n|@nvar}：n是整數(shù)常量，@nvar是smallint、tinyint或int類型的值。如果n或@nvar為正，則返回從當(dāng)前行開始向后的第n行，并將返回行變成新的當(dāng)前行。如果n或@nvar為負(fù)，則返回從當(dāng)前行開始向前的第n行，并將返回行變成新的當(dāng)前行。如果n或@nvar為0，則返回當(dāng)前行。在第一次提取游標(biāo)時(shí)，如果在將n或@nvar設(shè)置為負(fù)數(shù)或0的情況下指定FETCHRELATIVE，則不返回行。SQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理【例8.12】打開T_cursor游標(biāo)之后，使用FETCH語句檢索游標(biāo)中的可用數(shù)據(jù)。FETCHNEXTFROMT_cursorWHILE@@FETCH_STATUS=0BEGINFETCHNEXTFROMT_cursorENDGO上述語句中的@@FETCH_STATUS全局變量保存的就是FETCH操作的結(jié)果信息。如果其值為零，則表示有記錄檢索成功。如果值不為零，則FETCH語句由于某種原因操作失敗。SQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理3．關(guān)閉游標(biāo)打開游標(biāo)以后，SQLServer服務(wù)器會專門為游標(biāo)開辟一定的內(nèi)存空間，以存放游標(biāo)操作的數(shù)據(jù)結(jié)果集，同時(shí)游標(biāo)的使用也會根據(jù)具體情況對某些數(shù)據(jù)進(jìn)行封鎖。所以在不使用游標(biāo)時(shí)，一定要關(guān)閉游標(biāo)，以通知服務(wù)器釋放游標(biāo)占用的資源。關(guān)閉游標(biāo)的語法格式如下。CLOSEcursor_name【例8.13】關(guān)閉游標(biāo)T_cursor。CLOSET_cursorSQL編程基礎(chǔ)任務(wù)8-1任務(wù)1-1數(shù)據(jù)處理4．釋放游標(biāo)因?yàn)橛螛?biāo)結(jié)構(gòu)本身也會占用一定的計(jì)算機(jī)資源，所以使用完游標(biāo)后，為了回收被游標(biāo)占用的資源，應(yīng)該將游標(biāo)釋放。當(dāng)釋放最后的游標(biāo)引用時(shí)，組成該游標(biāo)的數(shù)據(jù)結(jié)構(gòu)由SQLServer釋放。釋放游標(biāo)的語法格式如下。DEALLOCATEcursor_name釋放完游標(biāo)以后，如果要重新使用這個(gè)游標(biāo)，就必須重新執(zhí)行聲明游標(biāo)的語句。PART2任務(wù)8-2創(chuàng)建與使用存儲過述任務(wù)assignments（一）存儲過程概述（二）創(chuàng)建存儲過程（三）執(zhí)行存儲過程（四）管理存儲過程標(biāo)創(chuàng)建與使用存儲過程任務(wù)8-2任務(wù)1-1數(shù)據(jù)處理【任務(wù)分析】為了提高訪問數(shù)據(jù)的速度與效率，可以使用存儲過程管理數(shù)據(jù)庫。【課堂任務(wù)】掌握存儲過程的概念及應(yīng)用。?存儲過程的概念?存儲過程的創(chuàng)建及管理?存儲過程中參數(shù)的使用?存儲過程的查看及刪除創(chuàng)建與使用存儲過程任務(wù)8-2任務(wù)1-1數(shù)據(jù)處理（一）存儲過程概述1．什么是存儲過程存儲過程(StoredProcedure)是在數(shù)據(jù)庫中定義的完成特定功能的SQL語句集合，經(jīng)編譯后存儲在數(shù)據(jù)庫服務(wù)器上。存儲過程可包含流程控制語句及各種SQL語句。存儲過程與其他編程語言中的構(gòu)造相似，它們可以接受輸入?yún)?shù)并以輸出參數(shù)的格式向調(diào)用程序返回單個(gè)或多個(gè)結(jié)果。創(chuàng)建與使用存儲過程任務(wù)8-2任務(wù)1-1數(shù)據(jù)處理2．使用存儲過程的優(yōu)點(diǎn)（1）存儲過程增強(qiáng)了SQL的功能和靈活性。存儲過程可以用流控制語句編寫，有很強(qiáng)的靈活性，可以完成復(fù)雜的判斷和較復(fù)雜的運(yùn)算。（2）存儲過程允許模塊化程序設(shè)計(jì)。存儲過程創(chuàng)建后，可以在程序中多次調(diào)用，而不必重新編寫該存儲過程的SQL語句，并且可以隨時(shí)修改存儲過程，毫不影響應(yīng)用程序源代碼。（3）存儲過程能實(shí)現(xiàn)較快的執(zhí)行速度。默認(rèn)情況下，在首次執(zhí)行存儲過程時(shí)將編譯存儲過程，并且創(chuàng)建一個(gè)執(zhí)行計(jì)劃，供以后的執(zhí)行重復(fù)使用，系統(tǒng)可以用更少的時(shí)間來處理存儲過程。創(chuàng)建與使用存儲過程任務(wù)8-2任務(wù)1-1數(shù)據(jù)處理（4）存儲過程減少了服務(wù)器/客戶端的網(wǎng)絡(luò)流量。存儲過程中的命令作為代碼的單個(gè)批處理執(zhí)行，當(dāng)在客戶計(jì)算機(jī)上調(diào)用存儲過程時(shí)，網(wǎng)絡(luò)中傳送的只是該調(diào)用語句，從而可以顯著減少服務(wù)器和客戶端之間的網(wǎng)絡(luò)流量。（5）存儲過程可作為一種安全機(jī)制來充分利用。多個(gè)用戶和客戶端程序可以通過存儲過程對基礎(chǔ)數(shù)據(jù)庫對象執(zhí)行操作，即使用戶和程序?qū)@些基礎(chǔ)對象沒有直接權(quán)限。在通過網(wǎng)絡(luò)調(diào)用存儲過程中，只有存儲過程的調(diào)用語句是可見的。因此，即使惡意用戶截獲網(wǎng)絡(luò)傳輸信息，也無法看到存儲過程本身涉及的表和數(shù)據(jù)庫對象名稱、嵌入的Transact-SQL語句及搜索關(guān)鍵數(shù)據(jù)等。創(chuàng)建與使用存儲過程任務(wù)8-2任務(wù)1-1數(shù)據(jù)處理（二）創(chuàng)建存儲過程在SQLServer系統(tǒng)中，可以使用SSMS管理工具和CREATEPROCEDURE語句創(chuàng)建存儲過程。需要強(qiáng)調(diào)的是，必須具有CREATEPROCEDURE權(quán)限才能創(chuàng)建存儲過程。1．使用SSMS創(chuàng)建存儲過程【例8.15】創(chuàng)建一個(gè)名稱為Proc_Stur的存儲過程，完成如下功能：在Students表中查詢男生的Sno、Sex、Sage這幾個(gè)字段的內(nèi)容。創(chuàng)建與使用存儲過程任務(wù)8-2任務(wù)1-1數(shù)據(jù)處理使用SSMS創(chuàng)建存儲過程的步驟如下。（1）啟動SSMS，在對象資源管理器中，連接到數(shù)據(jù)庫引擎的實(shí)例并展開該實(shí)例。（2）展開【數(shù)據(jù)庫】|【grademanager】|【可編程性】節(jié)點(diǎn)，用鼠標(biāo)右鍵單擊【存儲過程】節(jié)點(diǎn)，在快捷菜單中選擇【存儲過程】命令，如圖8.1所示。（3）在【查詢編輯器】中出現(xiàn)存儲過程編程模板，如圖8.1所示。在此模板的基礎(chǔ)上編寫創(chuàng)建存儲過程的語句。創(chuàng)建與使用存儲過程任務(wù)8-2任務(wù)1-1數(shù)據(jù)處理創(chuàng)建與使用存儲過程任務(wù)8-2任務(wù)1-1數(shù)據(jù)處理2．使用CREATEPROCEDURE語句創(chuàng)建存儲過程語法格式如下。CREATEPROCEDUREprocedure_name[{@parameterdata_type}[=default][OUTPUT][,…n]ASsql_statement[…n]參數(shù)說明如下。（1）procedure_name：存儲過程的名稱。（2）@parameter：過程中的參數(shù)。在CREATEPROCEDURE語句中可以聲明一個(gè)或者多個(gè)參數(shù)。每個(gè)過程的參數(shù)僅用于該過程本身；其他過程中可以使用相同的參數(shù)名稱。創(chuàng)建與使用存儲過程任務(wù)8-2任務(wù)1-1數(shù)據(jù)處理（3）data_type：參數(shù)的數(shù)據(jù)類型。（4）default：參數(shù)的默認(rèn)值。如果定義default值，則無需指定此參數(shù)的值即可執(zhí)行過程。默認(rèn)值必須是常量或者NULL。該常量值可以采用通配符的形式，也就是說，在將該參數(shù)傳遞到過程時(shí)使用LIKE關(guān)鍵字。（5）OUTPUT：指明參數(shù)是輸出參數(shù)。使用OUTPUT參數(shù)將值返回給過程的調(diào)用方。（6）<sql_statement>：要包含在過程中的一個(gè)或者多個(gè)Transact-SQL語句。創(chuàng)建與使用存儲過程任務(wù)8-2任務(wù)1-1數(shù)據(jù)處理【例8.16】創(chuàng)建一個(gè)基本存儲過程，從數(shù)據(jù)庫grademanager的student表中檢索出所有籍貫為“青島”的學(xué)生的學(xué)號、姓名、班級號及家庭地址等信息。USEgrademanagerGOCREATEPROCEDUREpro_學(xué)生信息ASSELECTsno,sname,classno,saddressFROMstudentWHEREsaddressLIKE'%青島%'ORDERBYsnoGO執(zhí)行存儲過程“pro_學(xué)生信息”，返回所有“青島”籍的學(xué)生信息。創(chuàng)建與使用存儲過程任務(wù)8-2任務(wù)1-1數(shù)據(jù)處理3．使用存儲過程參數(shù)SQLServer的存儲過程可以使用兩種類型的參數(shù)：輸入?yún)?shù)和輸出參數(shù)。參數(shù)用于在存儲過程以及應(yīng)用程序之間交換數(shù)據(jù)。?輸入?yún)?shù)允許用戶將數(shù)據(jù)值傳遞到存儲過程或者函數(shù)。?輸出參數(shù)允許存儲過程將數(shù)據(jù)值或者游標(biāo)變量傳遞給用戶。?每個(gè)存儲過程向用戶返回一個(gè)整數(shù)代碼，如果存儲過程沒有明顯設(shè)置返回代碼的值，則返回代碼為零。（1）輸入?yún)?shù)輸入?yún)?shù)，即在存儲過程中有一個(gè)條件，在執(zhí)行存儲過程時(shí)為這個(gè)條件指定值，通過存儲過程返回相應(yīng)的信息。使用輸入?yún)?shù)可以向同一存儲過程多次查找數(shù)據(jù)庫。創(chuàng)建與使用存儲過程任務(wù)8-2任務(wù)1-1數(shù)據(jù)處理【例8.17】創(chuàng)建一個(gè)存儲過程，用于返回grademanager數(shù)據(jù)庫中“計(jì)算機(jī)2”班的所有學(xué)生信息。建立一個(gè)性別參數(shù)為同一存儲過程指定不同的性別，來返回不同性別的學(xué)生信息。USEgrademanagerGOCREATEPROCEDUREpro_學(xué)生_性別_信息@性別NVARCHAR(10)ASSELECTsno,sname,ssex,classname,headerFROMstudentA,classBWHEREA.classno=B.classnoANDclassname='計(jì)算機(jī)2'ANDA.ssex=@性別GO創(chuàng)建與使用存儲過程任務(wù)8-2任務(wù)1-1數(shù)據(jù)處理“pro_學(xué)生_性別_信息”存儲過程使用一個(gè)字符串型參數(shù)“@性別”來執(zhí)行。執(zhí)行帶有輸入?yún)?shù)的存儲過程時(shí)，SQLServer提供了兩種傳遞參數(shù)的方式。①按位置傳遞。這種方式是在執(zhí)行過程的語句中，直接給出參數(shù)的值。當(dāng)有多個(gè)參數(shù)時(shí)，給出參數(shù)的順序與創(chuàng)建過程的語句中的參數(shù)一致，即參數(shù)傳遞的順序就是參數(shù)定義的順序。創(chuàng)建與使用存儲過程任務(wù)8-2任務(wù)1-1數(shù)據(jù)處理【例8.18】使用按位置傳遞方式執(zhí)行“pro_學(xué)生_性別_信息”存儲過程。EXECpro_學(xué)生_性別_信息'女'②通過參數(shù)名傳遞。這種方式是在執(zhí)行存儲過程的語句中，使用“參數(shù)名=參數(shù)值”的形式給出參數(shù)值。通過參數(shù)名傳遞的好處是，參數(shù)可以以任意順序給出。【例8.19】使用參數(shù)傳遞方式執(zhí)行“pro_學(xué)生_性別_信息”存儲過程。EXECpro_學(xué)生_性別_信息@性別='男'使用上述兩種傳遞參數(shù)的方式傳遞不同的參數(shù)并執(zhí)行存儲過程。創(chuàng)建與使用存儲過程任務(wù)8-2任務(wù)1-1數(shù)據(jù)處理（2）使用默認(rèn)參數(shù)值執(zhí)行存儲過程“pro_學(xué)生_性別_信息”時(shí)，如果沒有指定參數(shù)，系統(tǒng)運(yùn)行就會出錯；如果希望不給出參數(shù)時(shí)也能夠正確運(yùn)行，則可以給參數(shù)設(shè)置默認(rèn)值?！纠?.20】設(shè)置“pro_學(xué)生_性別_信息”存儲過程的狀態(tài)參數(shù)默認(rèn)值為“男”。USEgrademanagerGOCREATEPROCEDUREpro_學(xué)生_性別_信息@性別NVARCHAR(10)='男'ASSELECTsno,sname,ssex,classname,headerFROMstudentA,classBWHEREA.classno=B.classnoANDclassname='計(jì)算機(jī)2'ANDA.ssex=@性別GO為參數(shù)設(shè)置默認(rèn)值以后，再執(zhí)行存儲過程時(shí)，就可以不指定具體的參數(shù)創(chuàng)建與使用存儲過程任務(wù)8-2任務(wù)1-1數(shù)據(jù)處理（3）輸出參數(shù)定義輸出參數(shù)，可以從存儲過程中返回一個(gè)或者多個(gè)值。要使用輸出參數(shù)，就必須在CREATEPROCEDURE語句和EXECUTE語句中指定關(guān)鍵字OUTPUT。執(zhí)行存儲過程時(shí)，忽略O(shè)UTPUT關(guān)鍵字，存儲過程仍然會執(zhí)行但不返回值。【例8.21】創(chuàng)建一個(gè)名為pro_getteachername的存儲過程。它使用兩個(gè)參數(shù)，“@學(xué)生姓名”為輸入?yún)?shù)，用于指定要查詢的學(xué)生姓名，默認(rèn)參數(shù)值為“徐紅”；“@班主任”為輸出參數(shù)，用來返回該班班主任的姓名。創(chuàng)建與使用存儲過程任務(wù)8-2任務(wù)1-1數(shù)據(jù)處理USEgrademanagerGOCREATEPROCEDUREpro_getteachername@學(xué)生姓名NVARCHAR(20)='徐紅',@班主任NVARCHAR(20)OUTPUTASSELECT@班主任=B.headerFROMstudentA,classBWHEREA.classno=B.classnoANDsname=@學(xué)生姓名GO為了接收某一存儲過程的返回值，需要一個(gè)變量來存放返回參數(shù)的值，必須在該存儲過程的調(diào)用語句中，為這個(gè)變量加上OUTPUT關(guān)鍵字來聲明。創(chuàng)建與使用存儲過程任務(wù)8-2任務(wù)1-1數(shù)據(jù)處理（三）執(zhí)行存儲過程1．使用SSMS執(zhí)行存儲過程使用SSMS執(zhí)行存儲過程的步驟如下。（1）啟動SSMS，在對象資源管理器中，連接到數(shù)據(jù)庫引擎的實(shí)例并展開該實(shí)例。（2）展開【數(shù)據(jù)庫】|【grademanager】|【可編程性】|【存儲過程】節(jié)點(diǎn)，用鼠標(biāo)右鍵單擊要執(zhí)行的存儲過程（pro_學(xué)生信息），在快捷菜單中選擇【執(zhí)行存儲過程】命令，如圖8.6所示。（3）打開執(zhí)行該存儲過程的窗口，單擊【確定】按鈕，該存儲過程執(zhí)行完畢，如圖8.6所示。創(chuàng)建與使用存儲過程任務(wù)8-2任務(wù)1-1數(shù)據(jù)處理創(chuàng)建與使用存儲過程任務(wù)8-2任務(wù)1-1數(shù)據(jù)處理2．使用EXECUTE語句執(zhí)行存儲過程EXECUTE語句的語法格式如下。[[EXEC[UTE]]{[@return_status=]{procedure_name[;number]|@procedure_name_var}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}[,…n]下面執(zhí)行任務(wù)8-2中創(chuàng)建的3個(gè)存儲過程。因?yàn)榍懊鎰?chuàng)建的存儲過程“pro_學(xué)生信息”中沒有參數(shù)，所以可以直接使用EXEC語句來執(zhí)行。創(chuàng)建與使用存儲過程任務(wù)8-2任務(wù)1-1數(shù)據(jù)處理使用帶參數(shù)的存儲過程，需要在執(zhí)行過程中提供存儲過程的參數(shù)值?？梢允褂脙煞N方式來提供存儲過程的參數(shù)值。（1）直接方式。該方式在EXEC語句中直接為存儲過程的參數(shù)提供數(shù)據(jù)值，并且這些數(shù)據(jù)值的數(shù)量和順序與定義存儲過程時(shí)，參數(shù)的數(shù)據(jù)和順序相同。如果參數(shù)是字符類型或者日期類型，則還應(yīng)該將這些參數(shù)值使用引號引起來。例如，為前面創(chuàng)建的存儲過程“pro_學(xué)生_性別_信息”提供一個(gè)字符串?dāng)?shù)據(jù)為“女”，具體執(zhí)行情況如圖8.3所示。創(chuàng)建與使用存儲過程任務(wù)8-2任務(wù)1-1數(shù)據(jù)處理創(chuàng)建與使用存儲過程任務(wù)8-2任務(wù)1-1數(shù)據(jù)處理（2）間接方式。該方式是指在執(zhí)行EXEC語句之前，聲明參數(shù)并為這些參數(shù)賦值，然后在EXEC語句中引用這些已經(jīng)獲取數(shù)據(jù)值的參數(shù)。例如，在EXEC語句執(zhí)行存儲過程“pro_學(xué)生_性別_信息”之前，使用DECLARE語句聲明變量，然后使用SET語句為已聲明的變量賦值。最后，在EXEC語句中引用變量作為存儲過程的參數(shù)值，具體情況如圖8.7所示。創(chuàng)建與使用存儲過程任務(wù)8-2任務(wù)1-1數(shù)據(jù)處理（四）管理存儲過程1．修改存儲過程使用ALTERPROCEDURE語句修改現(xiàn)有的存儲過程，這與刪除和重建存儲過程不同，因?yàn)樗员３执鎯^程的權(quán)限不發(fā)生變化。在使用ALTERPROCEDURE語句修改存儲過程時(shí)，SQLServer會覆蓋以前定義的存儲過程。修改存儲過程的基本語法格式如下。ALTERPROCEDUREprocedure_name[;number][{@parameterdata_type}[=default][OUTPUT][,…n]ASsql_statement[…n]創(chuàng)建與使用存儲過程任務(wù)8-2任務(wù)1-1數(shù)據(jù)處理【例8.23】修改“pro_學(xué)生信息”存儲過程來返回所有“濰坊”的學(xué)生信息。USEgrademanagerGOALTERPROCEDUREpro_學(xué)生信息ASSELECTsno,sname,classno,saddressFROMstudentWHEREsaddressLIKE'%濰坊%'ORDERBYsno【例8.24】執(zhí)行“pro_學(xué)生信息”存儲過程。USEgrademanagerGOEXECpro_學(xué)生信息創(chuàng)建與使用存儲過程任務(wù)8-2任務(wù)1-1數(shù)據(jù)處理2．刪除存儲過程可使用DROPPROCEDURE語句從當(dāng)前數(shù)據(jù)庫中刪除用戶定義的存儲過程。刪除存儲過程的語法格式如下。DROPPROCEDURE{procedure}[,…n]【例8.25】刪除“pro_學(xué)生信息”存儲過程。DROPPROCpro_學(xué)生信息如果另一個(gè)存儲過程調(diào)用某個(gè)已被刪除的存儲過程，則SQLServer將在執(zhí)行調(diào)用進(jìn)程時(shí)顯示一條錯誤消息。創(chuàng)建與使用存儲過程任務(wù)8-2任務(wù)1-1數(shù)據(jù)處理3．查看存儲過程查看存儲過程的定義信息，可以通過SSMS管理工具，也可以使用系統(tǒng)存儲過程（sp_helptext）、系統(tǒng)函數(shù)（OBJECT_DEFINITION）和目錄視圖（sys.sql_modules）?！纠?.26】使用系統(tǒng)存儲過程sp_helptext查看“pro_學(xué)生_性別_信息”存儲過程的定義文本信息，結(jié)果如圖8.9所示。USEgrademanagerGOEXCUsp_helptextpro_學(xué)生_性別_信息任務(wù)8-3觸發(fā)器PART3（一）觸發(fā)器概述（二）創(chuàng)建觸發(fā)器（三）管理觸發(fā)器任務(wù)assignments觸發(fā)器任務(wù)8-3任務(wù)1-1數(shù)據(jù)處理【任務(wù)分析】為了確保數(shù)據(jù)的完整性，可以采觸發(fā)器實(shí)現(xiàn)復(fù)雜的業(yè)務(wù)規(guī)則?！菊n堂任務(wù)】掌握觸發(fā)器的概念及應(yīng)用。?觸發(fā)器的概念?觸發(fā)器的創(chuàng)建及管理觸發(fā)器任務(wù)8-3任務(wù)1-1數(shù)據(jù)處理（一）觸發(fā)器概述1．觸發(fā)器的定義觸發(fā)器（Trigger）是一種特殊的存儲過程，它與表緊密相連，可以是表定義的一部分。當(dāng)預(yù)定義的事件（如用戶修改指定表或者視圖中的數(shù)據(jù)）發(fā)生時(shí)，觸發(fā)器會自動執(zhí)行。觸發(fā)器基于一個(gè)表創(chuàng)建，但是可以對多個(gè)表進(jìn)行操作。因此觸發(fā)器可以用來對表實(shí)施復(fù)雜的完整性約束，當(dāng)觸發(fā)器保存的數(shù)據(jù)改變時(shí)，觸發(fā)器被自動激活，從而防止對數(shù)據(jù)的不正確修改。觸發(fā)器的優(yōu)點(diǎn)如下。觸發(fā)器任務(wù)8-3任務(wù)1-1數(shù)據(jù)處理（1）觸發(fā)器自動執(zhí)行，在對表的數(shù)據(jù)做了任何修改（如手工輸入或者使用程序采集的操作）之后立即激活。（2）觸發(fā)器可以通過數(shù)據(jù)庫中的相關(guān)表進(jìn)行層疊更改。這比直接把代碼寫在前臺的做法更安全合理。（3）觸發(fā)器可以強(qiáng)制限制，這些限制比用CHECK約束定義的更復(fù)雜。與CHECK約束不同的是，觸發(fā)器可以引用其他表中的列。觸發(fā)器任務(wù)8-3任務(wù)1-1數(shù)據(jù)處理2．觸發(fā)器的分類在SQLServer系統(tǒng)中，按照觸發(fā)事件的不同，可以把提供的觸發(fā)器分成兩大類型，即DDL觸發(fā)器和DML觸發(fā)器。（1）DML觸發(fā)器DML觸發(fā)器為特殊類型的存儲過程，可在發(fā)生數(shù)據(jù)操作語言(DML)事件時(shí)自動生效，以便影響觸發(fā)器中定義的表或視圖。DML事件包括INSERT、UPDATE或DELETE語句。DML觸發(fā)器可用于強(qiáng)制業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性、查詢其他表并包括復(fù)雜的Transact-SQL語句。將觸發(fā)器和觸發(fā)它的語句作為可在觸發(fā)器內(nèi)回滾的單個(gè)事務(wù)對待。如果檢測到錯誤（如磁盤空間不足），則整個(gè)事務(wù)自動回滾。（2）DDL觸發(fā)器DDL觸發(fā)器將激發(fā)響應(yīng)各種數(shù)據(jù)定義語言（DDL）事件。這些事件主要與以關(guān)鍵字CREATE、ALTER、DROP、GRANT、DENY、REVOKE或UPDATESTATISTICS開頭的Transact-SQL語句對應(yīng)。觸發(fā)器任務(wù)8-3任務(wù)1-1數(shù)據(jù)處理（二）創(chuàng)建觸發(fā)器1．DML觸發(fā)器因?yàn)镈ML觸發(fā)器是一種特殊的存儲過程，所以DML觸發(fā)器的創(chuàng)建和存儲過程的創(chuàng)建方式有很多相似之處，創(chuàng)建DML觸發(fā)器的基本語法如下。CREATETRIGGERtrigger_nameON{table|view}{{{FOR|AFTER|INSTEADOF}{[UPDATE[[,][INSERT][,][DELETE]]ASsql_statement}}觸發(fā)器任務(wù)8-3任務(wù)1-1數(shù)據(jù)處理CREATETRIGGER語句中，主要參數(shù)的含義如下。trigger_name：是要創(chuàng)建的觸發(fā)器的名稱。table|view：是在其上執(zhí)行觸發(fā)器的表或者視圖，有時(shí)稱為觸發(fā)器表或者觸發(fā)器視圖?？梢赃x擇是否指定表或者視圖的所有者。FOR、AFTER、INSTEADOF：指定觸發(fā)器觸發(fā)的時(shí)機(jī)。AFTER：指定觸發(fā)器只有在SQL語句中指定的所有操作都已成功執(zhí)行后才觸發(fā)，只有在所有的引用級聯(lián)操作和約束檢查成功完成后，才能執(zhí)行此觸發(fā)器。如果僅指定FOR關(guān)鍵字，則AFTER是默認(rèn)設(shè)置。觸發(fā)器任務(wù)8-3任務(wù)1-1數(shù)據(jù)處理INSTEADOF：指定執(zhí)行觸發(fā)器而不是執(zhí)行觸發(fā)的SQL語句，從而替代觸發(fā)語句的操作。在表或視圖上，每個(gè)INSERT、UPDATE或DELETE語句最多可以定義一個(gè)INSTEADOF觸發(fā)器。DELETE、INSERT、UPDATE：指定在表或視圖上執(zhí)行哪些語句時(shí)將觸發(fā)觸發(fā)器的關(guān)鍵字。必須至少指定一個(gè)選項(xiàng)。在觸發(fā)器定義中允許使用以任意順序組合的這些關(guān)鍵字。如果指定的選項(xiàng)多于一個(gè)，需用逗號分隔這些選項(xiàng)。sql_statement：指定觸發(fā)器執(zhí)行的Transact-SQL語句。觸發(fā)器任務(wù)8-3任務(wù)1-1數(shù)據(jù)處理【例8.27】在grademanager數(shù)據(jù)庫中，創(chuàng)建名稱為“trig_更新班級人數(shù)”的觸發(fā)器。當(dāng)向student表添加一條學(xué)生信息時(shí)，同時(shí)更新class表中的classnumber列。創(chuàng)建一個(gè)INSERT觸發(fā)器，在用戶每次向student表添加新的學(xué)生信息時(shí)，更新相應(yīng)班級的人數(shù)。USEgrademanagerGOCREATETRIGGERtrig_更新班級人數(shù)ONstudentAFTERINSERTASUPDATEclassSETclassnumber=classnumber+1WHEREclassnoIN(SELECTleft(sno,8)FROMinserted)GO觸發(fā)器任務(wù)8-3任務(wù)1-1數(shù)據(jù)處理執(zhí)行上面的語句，就創(chuàng)建了一個(gè)“trig_更新班級人數(shù)”觸發(fā)器。接下來，使用INSERT語句插入一條新的學(xué)生信息，以驗(yàn)證觸發(fā)器是否會自動執(zhí)行。這里由于觸發(fā)器基于student表，因此插入也針對此表，在INSERT語句之前之后各添加一條SELECT語句，比較插入記錄前后處理狀態(tài)的變化。測試語句如圖8.10所示。從圖8.10中可以看到，執(zhí)行INSERT語句后，班級人數(shù)已經(jīng)更改，比執(zhí)行INSERT語句前多1，說明INSERT觸發(fā)器已經(jīng)成功執(zhí)行。觸發(fā)器任務(wù)8-3任務(wù)1-1數(shù)據(jù)處理觸發(fā)器任務(wù)8-3任務(wù)1-1數(shù)據(jù)處理觸發(fā)器任務(wù)8-3任務(wù)1-1數(shù)據(jù)處理【例8.29】創(chuàng)建一個(gè)觸發(fā)器，當(dāng)class表中的班級編號變更時(shí)，同時(shí)更新student表中的相應(yīng)班級編號信息。USEgrademanagerGOCREATETRIGGERtrig_班級信息更新ONclassFORUPDATEASIFUPDATE(classno)BEGINUPDATEstudentSETclassno=(SELECTclassnoFROMinserted)WHEREclassnoIN(SELECTclassnoFROMdeleted)END觸發(fā)器任務(wù)8-3任務(wù)1-1數(shù)據(jù)處理【例8.30】創(chuàng)建一個(gè)觸發(fā)器，用于在grademanager數(shù)據(jù)庫中刪除student表的一個(gè)學(xué)生信息時(shí)，級聯(lián)刪除該學(xué)生對應(yīng)sc表中的成績信息。默認(rèn)時(shí)，由于student表和sc表在sno列上存在外鍵約束，因此不允許直接刪除student表中的內(nèi)容。創(chuàng)建一個(gè)INSTRADOFDELETE觸發(fā)器，在檢測到有DELETE語句執(zhí)行時(shí)，先刪除外鍵表sc中對應(yīng)的信息，再刪除student表中的內(nèi)容。具體代碼如下觸發(fā)器任務(wù)8-3任務(wù)1-1數(shù)據(jù)處理USEgrademanagerGOCREATETRIGGERtrig_DELETEONstudentINSTEADOFDELETEASBEGINDELETEscWHEREsnoIN(SELECTsnoFROMdeleted)DELETEstudentWHEREsnoIN(SELECTsnoFROMdeleted)END觸發(fā)器任務(wù)8-3任務(wù)1-1數(shù)據(jù)處理2．DDL觸發(fā)器DDL觸發(fā)器和DML觸發(fā)器一樣，為了響應(yīng)事件而激活。創(chuàng)建DDL觸發(fā)器的語法格式如下。CREATETRIGGERtrigger_nameON{ALLSERVER|DATABASE}WITHENCRYPTION{FOR|AFTER|{enent_type}ASsql_statement觸發(fā)器任務(wù)8-3任務(wù)1-1數(shù)據(jù)處理（1）ALLSERVER：表示DDL觸發(fā)器的作用域是整個(gè)服務(wù)器。（2）DATABASE：表示該DDL觸發(fā)器的作用域是整個(gè)數(shù)據(jù)庫。（3）event_type：指定觸發(fā)DDL觸發(fā)器的事件。如果想要控制哪位用戶可以修改數(shù)據(jù)庫結(jié)構(gòu)及如何修改，甚至想跟蹤數(shù)據(jù)庫結(jié)構(gòu)上發(fā)生的修改，那么使用DDL觸發(fā)器非常合適。例如，重要數(shù)據(jù)庫內(nèi)部的結(jié)構(gòu)及其數(shù)據(jù)都很重要，不能輕易刪除或者改變，即便能改動，也要在改動之前做好備份，以免丟失重要數(shù)據(jù)。為此，可以創(chuàng)建一個(gè)DDL觸發(fā)器來防止刪除或者改變數(shù)據(jù)庫這樣的操作發(fā)生。觸發(fā)器任務(wù)8-3任務(wù)1-1數(shù)據(jù)處理觸發(fā)器任務(wù)8-3任務(wù)1-1數(shù)據(jù)處理（三）管理觸發(fā)器1．修改觸發(fā)器修改觸發(fā)器的定義和屬性有兩種方法：一是先刪除原來的觸發(fā)器定義，再重新創(chuàng)建與之同名的觸發(fā)器；二是直接修改現(xiàn)有的觸發(fā)器定義?！纠?.32】將上述的DDL觸發(fā)器“trig_DDL_學(xué)生信息”修改成只保護(hù)ALTERTABLE語句。USEgrademanagerGOALTERTRIGGERtrig_DDL_學(xué)生信息ONDATABASEFORALTER_TABLEASBEGINPRINT'不能修改當(dāng)前數(shù)據(jù)庫的內(nèi)容！'ROLLBACKEND在【新建查詢】窗口中執(zhí)行上述語句，就修改了以前的觸發(fā)器定義觸發(fā)器任務(wù)8-3任務(wù)1-1數(shù)據(jù)處理2．禁用觸發(fā)器在修改表的ALTERTABLE語句中，使用DISABLETRIGGER子句可以使該表的某一觸發(fā)器無效。當(dāng)再次需要時(shí)，可以使用ALTERTABLE語句的ENABLETRIGGER子句使觸發(fā)器重新有效。例如，使student表的“trig_更新班級人數(shù)”觸發(fā)器無效，可以使用如下語句。USEgrademanagerGOALTERTABLEstudentDISABLETRIGGERtrig_更新班級人數(shù)觸發(fā)器任務(wù)8-3任務(wù)1-1數(shù)據(jù)處理使“trig_更新班級人數(shù)”觸發(fā)器再次有效可以使用下面的語句。ALTERTABLEstudentENABLETRIGGERtrig_更新班級人數(shù)3．刪除觸發(fā)器使用DROPTRIGGER語句可以刪除當(dāng)前數(shù)據(jù)庫的一個(gè)或者多個(gè)觸發(fā)器。【例8.33】刪除觸發(fā)器“trig_更新班級人數(shù)”和“trig_班級信息更新”。USEgrademanagerGODROPTRIGGERtrig_更新班級人數(shù),trig_班級信息更新PART4任務(wù)8-4事務(wù)、鎖的概念和應(yīng)用任務(wù)assignments（一）事務(wù)概述（二）事務(wù)的ACID特性（三）事務(wù)的定義（四）事務(wù)并發(fā)操作的問題（五）事務(wù)隔離級別（六）SQLServer的鎖定機(jī)制（七）活鎖和死鎖事務(wù)、鎖的概念和應(yīng)用任務(wù)8-4任務(wù)1-1數(shù)據(jù)處理【任務(wù)分析】為了確保數(shù)據(jù)的完整性和有效性，可以使用事務(wù)確保同時(shí)發(fā)生的行為與數(shù)據(jù)的有效性不發(fā)生沖突。同時(shí)，為了解決并發(fā)操作帶來的問題，可以使用鎖來實(shí)現(xiàn)并發(fā)控制，以確保多個(gè)用戶同時(shí)操作同一個(gè)數(shù)據(jù)庫中的數(shù)據(jù)時(shí)，不會發(fā)生數(shù)據(jù)不一致的問題。【課堂任務(wù)】掌握事務(wù)和鎖的概念及應(yīng)用。?事務(wù)的基本概念及分類?事務(wù)的4個(gè)屬性?并發(fā)操作引起的問題?鎖的類型?死鎖的處理事務(wù)、鎖的概念和應(yīng)用任務(wù)8-4任務(wù)1-1數(shù)據(jù)處理（一）事務(wù)概述事務(wù)就是用戶定義的一個(gè)數(shù)據(jù)庫操作序列，這些操作要么全做，要么全不做，是一個(gè)不可分割的工作單位。事務(wù)是單個(gè)的工作單元，是數(shù)據(jù)庫中不可再分的基本部分。1．為什么要引入事務(wù)事務(wù)處理機(jī)制在程序開發(fā)過程中起著非常重要的作用，它可以使整個(gè)系統(tǒng)更加安全。例如，在銀行處理轉(zhuǎn)賬業(yè)務(wù)時(shí)，如果A賬戶中的金額剛被轉(zhuǎn)出，而B賬戶還沒有接收就停電；或者A賬戶中的金額在轉(zhuǎn)出過程中因出現(xiàn)錯誤未轉(zhuǎn)出，但B賬戶已完成了轉(zhuǎn)入工作，這會給銀行和個(gè)人帶來很大的經(jīng)濟(jì)損失。采用事務(wù)處理機(jī)制后，一旦在轉(zhuǎn)賬過程中發(fā)生意外，則整個(gè)轉(zhuǎn)賬業(yè)務(wù)將全部撤銷，不做任何處理，從而確保數(shù)據(jù)的一致性和有效性。事務(wù)、鎖的概念和應(yīng)用任務(wù)8-4任務(wù)1-1數(shù)據(jù)處理2．SQLServer事務(wù)處理機(jī)制在數(shù)據(jù)庫中，事務(wù)管理不善常常導(dǎo)致用戶量很大的系統(tǒng)出現(xiàn)爭用和性能問題。隨著訪問數(shù)據(jù)的用戶數(shù)量增加，能夠高效地使用事務(wù)的應(yīng)用程序也變得更為重要。SQLServer數(shù)據(jù)庫引擎使用事務(wù)鎖定和行版本控制機(jī)制，確保每個(gè)事務(wù)的物理完整性并提供有關(guān)應(yīng)用程序如何高效控制事務(wù)的信息。這種控制機(jī)制適用從SQLServer2005(9.x)到SQLServer2017（除非特別指出）的版本。事務(wù)、鎖的概念和應(yīng)用任務(wù)8-4任務(wù)1-1數(shù)據(jù)處理（二）事務(wù)的ACID特性事務(wù)是作為單個(gè)邏輯工作單元執(zhí)行的一系列操作。一個(gè)邏輯工作單元必須有4個(gè)特性，即原子性（Atomic）、一致性（Consistency）、隔離性（Isolation）和持久性（Durability），這四個(gè)特性總稱為ACID特性，只有滿足ACID特性才能稱為事務(wù)。1．原子性一個(gè)事務(wù)是一個(gè)不可分割的工作單元。事務(wù)在執(zhí)行時(shí)，應(yīng)該遵守“要么不做，要么全做（NothingorAll）”的原則，即不允許事務(wù)部分地完成，即使因?yàn)楣收隙故聞?wù)未能完成，它執(zhí)行的部分結(jié)果就要被取消。保證原子性是數(shù)據(jù)系統(tǒng)本身的職責(zé)，由DBMS的事務(wù)管理子系統(tǒng)實(shí)現(xiàn)。事務(wù)、鎖的概念和應(yīng)用任務(wù)8-4任務(wù)1-1數(shù)據(jù)處理2．一致性事務(wù)對數(shù)據(jù)庫的作用是使數(shù)據(jù)庫從一個(gè)一致狀態(tài)轉(zhuǎn)變到另一個(gè)一致狀態(tài)。所謂數(shù)據(jù)庫的一致狀態(tài)，是指數(shù)據(jù)庫中的數(shù)據(jù)滿足完整性約束。例如，在銀行業(yè)務(wù)中，“從賬號A轉(zhuǎn)移資金額R到賬號B”是一個(gè)典型的事務(wù)，這個(gè)事務(wù)包括兩個(gè)操作，從賬號A中減去資金額R和在賬號B中增加資金額R，如果只執(zhí)行其中的一個(gè)操作，則數(shù)據(jù)庫處于不一致狀態(tài)，賬務(wù)會出現(xiàn)問題，也就是說，兩個(gè)操作要么全做，要么全不做，否則就不能成為事務(wù)?？梢娛聞?wù)的一致性與原子性是密切相關(guān)的。確保單個(gè)事務(wù)的一致性是編寫事務(wù)的應(yīng)用程序員的職責(zé)，在系統(tǒng)運(yùn)行中，是由DBMS的完整性子系統(tǒng)實(shí)現(xiàn)的。事務(wù)、鎖的概念和應(yīng)用任務(wù)8-4任務(wù)1-1數(shù)據(jù)處理3．隔離性如果多個(gè)事務(wù)并發(fā)執(zhí)行，則應(yīng)像各個(gè)事務(wù)獨(dú)立執(zhí)行一樣，一個(gè)事務(wù)的執(zhí)行不能被其他事務(wù)干擾，即一個(gè)事務(wù)內(nèi)部的操作及使用的數(shù)據(jù)對并發(fā)的其他事務(wù)是隔離的。并發(fā)控制就是為了保證事務(wù)間的隔離性。隔離性是由DBMS的并發(fā)控制子系統(tǒng)實(shí)現(xiàn)的。4．持久性最后，一個(gè)事務(wù)一旦提交，它對數(shù)據(jù)庫中數(shù)據(jù)的改變就應(yīng)該是持久的。如果提交一個(gè)事務(wù)以后計(jì)算機(jī)癱瘓，或數(shù)據(jù)庫因故障受到破壞，那么重新啟動計(jì)算機(jī)后，DBMS也應(yīng)該能夠恢復(fù)，該事務(wù)的結(jié)果將依然是存在的。事務(wù)的持久性是由DBMS的恢復(fù)管理子系統(tǒng)實(shí)現(xiàn)的。事務(wù)、鎖的概念和應(yīng)用任務(wù)8-4任務(wù)1-1數(shù)據(jù)處理三）事務(wù)的定義一個(gè)事務(wù)可以是一條SQL語句、一組SQL語句或整個(gè)程序，一個(gè)應(yīng)用程序可以包括多個(gè)事務(wù)。事務(wù)的開始與結(jié)束可以由用戶顯式控制。如果用戶沒有顯式地定義事務(wù)，則由DBMS按照默認(rèn)規(guī)則自動劃分事務(wù)。1．開始事務(wù)BAGINTRANSACTION語句標(biāo)識一個(gè)用戶自定義事務(wù)的開始。此語句可以簡化為BEGINTRAN。事務(wù)是可以嵌套的，發(fā)布一條BEGINTRANSACTION命令之后，發(fā)布另一個(gè)BEGINTRANSACTION命令，然后提交或回退等待處理的事務(wù)。原則上是必須先提交或回退內(nèi)層事務(wù)，然后提交或回退外層事務(wù)，即一條COMMITTRANSACTION或ROLLBACKTRANSACTION語句對應(yīng)最近的一條BEGINTRANSACTION語句。事務(wù)、鎖的概念和應(yīng)用任務(wù)8-4任務(wù)1-1數(shù)據(jù)處理2．結(jié)束事務(wù)COMMITTRANSACTION語句用于結(jié)束一個(gè)用戶定義的事務(wù)，保證對數(shù)據(jù)的修改已經(jīng)成功地寫入數(shù)據(jù)庫。此時(shí)事務(wù)正常結(jié)束。此語句可簡化為COMMITTRAN。3．回滾事務(wù)ROLLBACKTRANSACTION取消在當(dāng)前事務(wù)期間所做的任何更改并結(jié)束事務(wù)。即在事務(wù)運(yùn)行的過程中發(fā)生某種故障時(shí)，事務(wù)不能繼續(xù)執(zhí)行，SQLServer系統(tǒng)將拋棄自最近一條BEGINTRANSACTION語句以后的所有修改，回滾到事務(wù)開始時(shí)的狀態(tài)。4．設(shè)置保存點(diǎn)SAVETRANSACTION語句用于在事務(wù)內(nèi)設(shè)置保存點(diǎn)。事務(wù)、鎖的概念和應(yīng)用任務(wù)8-4任務(wù)1-1數(shù)據(jù)處理事務(wù)、鎖的概念和應(yīng)用任務(wù)8-4任務(wù)1-1數(shù)據(jù)處理（四）事務(wù)并發(fā)操作引起的問題當(dāng)同一數(shù)據(jù)庫系統(tǒng)中有多個(gè)事務(wù)并發(fā)運(yùn)行時(shí)，如果不加以適當(dāng)控制，就可能產(chǎn)生數(shù)據(jù)不一致性問題。例如，并發(fā)取款操作。假設(shè)存款余額R=1000元，甲事務(wù)T1取走存款100元，乙事務(wù)T2取走存款200元，如果正常操作，即甲事務(wù)T1執(zhí)行完畢再執(zhí)行乙事務(wù)T2，存款余額更新后應(yīng)該是700元，但是如果按照如下順序操作，則會有不同的結(jié)果。（1）甲事務(wù)T1讀取存款余額R=1000元。（2）乙事務(wù)T2讀取存款余額R=1000元。（3）甲事務(wù)T1取走存款100元，修改存款余額R=R-100=900，把R=900寫回到數(shù)據(jù)庫。（4）乙事務(wù)T2取走存款200元，修改存款余額R=R-200=800，把R=800寫回到數(shù)據(jù)庫。結(jié)果兩個(gè)事務(wù)共取走存款300元，數(shù)據(jù)庫中的存款只少了200元。事務(wù)、鎖的概念和應(yīng)用任務(wù)8-4任務(wù)1-1數(shù)據(jù)處理得到這種錯誤的結(jié)果是甲、乙兩個(gè)事務(wù)并發(fā)操作引起的，數(shù)據(jù)庫的并發(fā)操作導(dǎo)致的數(shù)據(jù)不一致性主要有3種：丟失更新(LostUpdate)、臟讀(DirtyRead)和不可重復(fù)讀(UnrepeatableRead)。1．丟失更新當(dāng)兩個(gè)事務(wù)T1和T2讀入同一數(shù)據(jù)做修改并發(fā)執(zhí)行時(shí)，T2把T1或T1把T2的修改結(jié)果覆蓋了，造成數(shù)據(jù)丟失更新問題，導(dǎo)致數(shù)據(jù)不一致。仍以上例中的操作為例進(jìn)行分析。事務(wù)、鎖的概念和應(yīng)用任務(wù)8-4任務(wù)1-1數(shù)據(jù)處理事務(wù)、鎖的概念和應(yīng)用任務(wù)8-4任務(wù)1-1數(shù)據(jù)處理2．臟讀臟讀也稱“污讀”，即事務(wù)T1更新了數(shù)據(jù)R，事務(wù)T2讀取了更新后的數(shù)據(jù)R，事務(wù)T1由于某種原因被撤銷，修改無效，數(shù)據(jù)R恢復(fù)原值。這樣事務(wù)T2得到的數(shù)據(jù)與數(shù)據(jù)庫的內(nèi)容不一致，這種情況稱為臟讀。在表8.4中，事務(wù)T1把R的值改為900，但此時(shí)尚未做COMMIT操作，事務(wù)T2將修改過的值900讀出來，之后事務(wù)T1執(zhí)行ROLLBACK操作，R的值恢復(fù)為1000，而事務(wù)T2將仍在使用已被撤銷了的R值900。原因在于t4時(shí)刻，事務(wù)T2讀取了T1未提交的更新操作結(jié)果，這種值是不穩(wěn)定的，在事務(wù)T1結(jié)束前，隨時(shí)可能執(zhí)行ROLLBACK操作。事務(wù)、鎖的概念和應(yīng)用任務(wù)8-4任務(wù)1-1數(shù)據(jù)處理事務(wù)、鎖的概念和應(yīng)用任務(wù)8-4任務(wù)1-1數(shù)據(jù)處理3．不可重復(fù)讀不可重復(fù)讀是指一個(gè)事務(wù)在不同時(shí)刻讀取同一行數(shù)據(jù)，但是得到了不同的結(jié)果。不可重復(fù)讀包括以下情況。（1）事務(wù)T1讀取了數(shù)據(jù)R，事務(wù)T2讀取并更新了數(shù)據(jù)R，當(dāng)事務(wù)T1再讀取數(shù)據(jù)R以進(jìn)行核對時(shí)，得到的兩次讀取值不一致。（2）事務(wù)在操作過程中查詢兩次，第2次查詢的結(jié)果包含了第1次查詢中未出現(xiàn)的數(shù)據(jù)或者缺少了第1次查詢中出現(xiàn)的數(shù)據(jù)（這里并不要求兩次查詢的SQL語句相同）。這種現(xiàn)象稱為“幻讀（PhantomReads）。這是因?yàn)閮纱尾樵冞^程中有另外一個(gè)事務(wù)插入或刪除了數(shù)據(jù)。事務(wù)、鎖的概念和應(yīng)用任務(wù)8-4任務(wù)1-1數(shù)據(jù)處理事務(wù)、鎖的概念和應(yīng)用任務(wù)8-4任務(wù)1-1數(shù)據(jù)處理（五）事務(wù)隔離級別在并發(fā)操作帶來的問題中，“丟失更新”是應(yīng)該完全避免的。但防止更新丟失，并不能單靠數(shù)據(jù)庫事務(wù)控制器來解決，需要應(yīng)用程序?qū)σ碌臄?shù)據(jù)加必要的鎖來解決，因此防止丟失更新應(yīng)該由應(yīng)用程序來解決。臟讀和“不可重復(fù)讀”其實(shí)都是數(shù)據(jù)庫的一致性問題，必須由數(shù)據(jù)庫提供一定的事務(wù)隔離機(jī)制來解決。數(shù)據(jù)庫實(shí)現(xiàn)事務(wù)隔離的方式基本上可以分為兩種。（1）在讀取數(shù)據(jù)前，對其加鎖，阻止其他事務(wù)修改數(shù)據(jù)。（2）不用加任何鎖，通過一定的機(jī)制生成一個(gè)數(shù)據(jù)請求時(shí)間點(diǎn)的一致性數(shù)據(jù)快照（Snapshot），并用這個(gè)快照來提供一定級別（語句級或事務(wù)級）的一致性讀取。事務(wù)、鎖的概念和