數(shù)據(jù)庫原理與應(yīng)用教程―SQL Server 2019 課件 尹志宇 第9-14章 T-SQL編程-數(shù)據(jù)庫備份和還原

數(shù)據(jù)庫原理與應(yīng)用教程

―SQLServer2019第9章T-SQL編程9.1.1標(biāo)識(shí)符標(biāo)識(shí)符是用來標(biāo)識(shí)事物的符號(hào)，其作用類似于給事物起的名稱。標(biāo)識(shí)符分為兩類：常規(guī)標(biāo)識(shí)符和分隔標(biāo)識(shí)符。1、常規(guī)標(biāo)識(shí)符常規(guī)標(biāo)識(shí)符格式的規(guī)則如下：①常規(guī)標(biāo)識(shí)符必須以漢字、字母（包括從a到z和從A到Z的拉丁字符以及其他語言的字母字符）、下劃線(_)、@或#開頭

。9.1T-SQL編程基礎(chǔ)后續(xù)字符可以是：漢字、字母；十進(jìn)制數(shù)字；下劃線(_)、@或#

。②常規(guī)標(biāo)識(shí)符不能是SQLServer保留字，SQLServer保留字不區(qū)分大小寫。③常規(guī)標(biāo)識(shí)符最長不能超過128個(gè)字符。9.1.1標(biāo)識(shí)符2、分隔標(biāo)識(shí)符分隔標(biāo)識(shí)符括在方括號(hào)[]或雙引號(hào)“”中。在下列情況下，需要使用分隔標(biāo)識(shí)符：(1)使用保留關(guān)鍵字作為對(duì)象名或?qū)ο竺囊徊糠帧?2)標(biāo)識(shí)符的命名不符合常規(guī)標(biāo)識(shí)符格式的規(guī)則。9.1.1標(biāo)識(shí)符1、變量的分類①全局變量全局變量由系統(tǒng)提供且預(yù)先聲明，通過在名稱前加兩個(gè)“@”符號(hào)區(qū)別于局部變量。用戶只能使用全局變量，不能對(duì)它們進(jìn)行修改。全局變量的作用范圍是整個(gè)SQLServer系統(tǒng)，任何程序都可以隨時(shí)調(diào)用它們。9.1.2變量②局部變量變量是一種程序設(shè)計(jì)語言中必不可少的組成部分，可以用它保存程序運(yùn)行過程中的中間值，也可以在語句之間傳遞數(shù)據(jù)。T-SQL語言中的變量是可以保存單個(gè)特定類型的數(shù)據(jù)值的對(duì)象，也稱為局部變量，只在定義它們的批處理或過程中可見。9.1.2變量2、局部變量定義T-SQL語言中的變量在定義和引用時(shí)要在其名稱前加上“＠”，而且必須先用DECLARE命令定義后才可以使用。一般格式如下：DECLARE{@local_variable

data_type}[,…n]例：創(chuàng)建了一個(gè)變量@CurrentDateTimeDECLARE@CurrentDateTimechar(30)9.1.2變量3、局部變量的賦值方法使用DECLARE命令聲明并創(chuàng)建變量之后，系統(tǒng)會(huì)將其初始值設(shè)為NULL，如果想要設(shè)定變量的值，必須使用SET命令或者SELECT命令。例：SELECT@CurrentDateTime=GETDATE()或SET@CurrentDateTime=GETDATE()9.1.2變量4、局部變量的作用域**一個(gè)變量的作用域就是可以引用該變量的T-SQL語句范圍。局部變量只能在聲明它們的批處理或存儲(chǔ)過程中使用，一旦這些批處理或存儲(chǔ)過程結(jié)束，局部變量將自行清除。9.1.2變量5、變量使用舉例[例9-1]：創(chuàng)建了一個(gè)變量@CurrentDateTime，然后將GETDATE（）函數(shù)的值放在變量中，最后輸出@CurrentDateTime變量的值。9.1.2變量DECLARE@CurrentDateTimechar(30)SET@CurrentDateTime=GETDATE()SELECT@CurrentDateTimeAS'當(dāng)前的日期和時(shí)間'GO注意：因?yàn)樽兞恐辉诙x它的批處理中有效，因此，在上例中的程序中間不能寫入GO語句。9.1.2變量在SQLServer中，可以使用的運(yùn)算符可以分為算術(shù)運(yùn)算符、比較運(yùn)算符、邏輯運(yùn)算符、賦值運(yùn)算符、字符串串聯(lián)運(yùn)算符、按位運(yùn)算符、一元運(yùn)算符等。9.1.3運(yùn)算符批處理是包含一個(gè)或多個(gè)T-SQL語句的組，結(jié)束符為“GO”。從應(yīng)用程序一次性地發(fā)送到SQLServer進(jìn)行執(zhí)行，因此可以節(jié)省系統(tǒng)開銷。SQLServer將批處理的語句編譯為一個(gè)可執(zhí)行單元，稱為執(zhí)行計(jì)劃。編譯錯(cuò)誤（如語法錯(cuò)誤）可使執(zhí)行計(jì)劃無法編譯，因此未執(zhí)行批處理中的任何語句。9.1.4批處理運(yùn)行時(shí)錯(cuò)誤（如算術(shù)溢出或違反約束）會(huì)產(chǎn)生以下兩種影響之一：(1)大多數(shù)運(yùn)行時(shí)錯(cuò)誤將停止執(zhí)行批處理中當(dāng)前語句和它之后的語句；(2)某些運(yùn)行時(shí)錯(cuò)誤（如違反約束）僅停止執(zhí)行當(dāng)前語句。而繼續(xù)執(zhí)行批處理中其它所有語句。9.1.4批處理單行注釋：使用兩個(gè)連在一起的減號(hào)“--”作為注釋符。多行注釋：使用“/**/”作為注釋符。9.1.5注釋控制語句說明SET賦值語句BEGIN…END定義語句塊IF...ELSE、CASE條件語句、多分支語句WHILE循環(huán)語句CONTINUE重新開始下一次循環(huán)BREAK退出循環(huán)GOTO無條件轉(zhuǎn)移語句RETURN無條件返回

T-SQL提供一些特殊關(guān)鍵字用于控制T-SQL語句、語句塊和存儲(chǔ)過程的執(zhí)行。與所有的計(jì)算機(jī)編程語言一樣，T-SQL過程性語法結(jié)構(gòu)，包括:順序、分支、循環(huán)、存儲(chǔ)過程等。

9.2流程控制語句

聲明一個(gè)局部變量后，該變量被初始化為NULL。使用SET語句可以給一個(gè)變量賦值。在初始化多個(gè)變量時(shí)，要為每個(gè)局部變量使用單獨(dú)的SET語句。其語法格式為：

SET@local_variable=expression[例9-7]：聲明變量，并用SET給變量賦值。DECLARE@myvarchar(20)SET@myvar='Thisisatest'SELECT@myvar

或PRINT'string='+@myvarGO9.2.1SET語句

BEGIN…END語句能夠?qū)⒍鄠€(gè)T-SQL語句組合成一個(gè)語句塊，并將它們視為一個(gè)單元處理。其語法格式如下：BEGIN

{sql_statement

|statement_block

}END其中，{sql_statement|statement_block}為任何有效的T-SQL語句或語句塊。9.2.2BEGIN…END語句

語法格式：

IFBoolean_expression

/*條件表達(dá)式，可含有SELECT語句*/{sql_statement|statement_block}/*條件表達(dá)式為真時(shí)執(zhí)行，語句塊用BEGIN…END*/[ELSE{sql_statement|statement_block}]/*條件表達(dá)式為假時(shí)執(zhí)行，語句塊用BEGIN…END*/注：如果條件表達(dá)式中含有SELECT語句，必須用圓括號(hào)將SELECT語句括起來。9.2.3IF…ELSE語句

【例9-8】如果“C001”號(hào)課的平均成績高于80分，則顯示“平均成績還不錯(cuò)”，否則顯示“平均成績一般”。USEteachingGOIF(SELECTAVG(score)FROMscWHEREcno='C001')>80PRINT'C001號(hào)課的平均成績還不錯(cuò)'ELSEPRINT'C001號(hào)課的平均成績一般'9.2.3IF…ELSE語句

【例9-9】輸出202202001號(hào)學(xué)生的平均成績，如果沒有這個(gè)學(xué)生或該學(xué)生沒有選課，則顯示相應(yīng)的提示信息。USEteachingGOIFEXISTS(SELECT*FROMscWHEREsno='202202001')SELECTAVG(score)as'202202001號(hào)學(xué)生的平均分'FROMscWHEREsno='202202001'ELSEPRINT'沒有202202001號(hào)學(xué)生或202202001號(hào)學(xué)生沒選課'9.2.3IF…ELSE語句

使用CASE語句可以進(jìn)行多個(gè)分支的選擇。（1）簡(jiǎn)單CASE格式：將某個(gè)表達(dá)式與一組簡(jiǎn)單表達(dá)式進(jìn)行比較（=），以確定結(jié)果。語法格式：CASEinput_expressionWHENwhen_expressionTHENresult_expression[...n][

ELSEelse_result_expression]

END9.2.4CASE語句

【例9-10】以簡(jiǎn)單CASE格式查詢所有學(xué)生的專業(yè)情況，包括學(xué)號(hào)，姓名和專業(yè)的英文名。USEteachingSELECTsno,sname,CASEspecialtyWHEN'計(jì)算機(jī)'THEN'Computer'WHEN'電子信息'THEN'ElectronicInformation'WHEN'通信工程'THEN'CommunicationEngineering'ELSE'NetworkEngineering'ENDASspecialtyFROMstudent9.2.4CASE語句

(2)搜索CASE格式：計(jì)算一組布爾表達(dá)式（范圍”>”、“<“等），以確定結(jié)果。語法格式：CASE

WHENBoolean_expressionTHENresult_expression

[...n]

[ELSEelse_result_expression]

END9.2.4CASE語句

【例9-11】查詢所有學(xué)生的考試等級(jí)，包括學(xué)號(hào)，課程號(hào)和成績級(jí)別（a、b、c、d、e）。USEteachingSELECTsno,cno,score_level=

CASEWHENscore>=90then'a'WHENscore>=80then'b'WHENscore>=70then'c'WHENscore>=60then'd'WHENscore<60then'e'ENDFROMsc9.2.4CASE語句

如果需要重復(fù)執(zhí)行程序中的一部分語句，可使用WHILE循環(huán)語句實(shí)現(xiàn)?？梢允褂肂REAK和CONTINUE關(guān)鍵字在循環(huán)內(nèi)部控制WHILE循環(huán)中語句的執(zhí)行?！纠?-12】創(chuàng)建一個(gè)usern表，包含userid和username列，接著利用while循環(huán)向其中插入前20行數(shù)據(jù)。declare

@i

int

set

@i=1

while

@i<=20

begin

insert

into

usern

(userid,username)

values(@i,'user'+ltrim(str(@i)))

set

@i=@i+1

end

9.2.5WHILE語句

GOTO語句可以實(shí)現(xiàn)無條件的跳轉(zhuǎn)。語法格式為：GOTOlable/*lable為要跳轉(zhuǎn)到的語句標(biāo)號(hào)*/9.2.6GOTO語句

使用RETURN語句，可以從查詢或過程中無條件退出?？稍谌魏螘r(shí)候用于從過程、批處理或語句塊中退出，而不執(zhí)行位于RETURN之后的語句。語法格式為：

RETURN[integer_expression]/*整型表達(dá)式*/其中，整型表達(dá)式為一個(gè)整數(shù)值，是RETURN語句要返回的值。9.2.7RETURN語句

【例9-16】利用存儲(chǔ)過程求某個(gè)學(xué)號(hào)學(xué)生的平均成績。USEteachingGOCREATEPROCEDUREmypro@nochar(7)ASRETURN(SELECTAVG(score)FROMscWHEREsno=@no)創(chuàng)建查詢：DECLARE@avgfloat,@nochar(7)SET@no='202202001'EXEC@avg=mypro@noSELECTsname,@avgas'平均分'FROMstudentWHEREsno=@no9.2.7RETURN語句

函數(shù)是由一個(gè)或多個(gè)T-SQL語句組成的子程序，可用于封裝代碼以便重復(fù)使用。9.3.1系統(tǒng)內(nèi)置函數(shù)T-SQL系統(tǒng)內(nèi)置函數(shù)按函數(shù)的功能可分為配置函數(shù)、系統(tǒng)函數(shù)、系統(tǒng)統(tǒng)計(jì)函數(shù)、聚合函數(shù)、數(shù)學(xué)函數(shù)、字符串函數(shù)、日期和時(shí)間函數(shù)、游標(biāo)函數(shù)、文本和圖像函數(shù)、元數(shù)據(jù)函數(shù)、安全函數(shù)、行集函數(shù)等類型?！纠?-18】計(jì)算現(xiàn)在是幾月。SELECTMONTH(GETDATE())【例9-19】將字符串“Iamastudent”以大寫字母顯示。SELECTUPPER(‘Iamastudent’)9.3函數(shù)

SQLServer用戶定義函數(shù)可以針對(duì)特定應(yīng)用程序問題提供解決方案，目的主要是為了求值。用戶定義函數(shù)都是經(jīng)過封裝的T-SQL子程序，可以通過其他T-SQL代碼調(diào)用這些子程序來返回單一的值或者數(shù)據(jù)表值。9.3.2用戶定義函數(shù)根據(jù)用戶定義函數(shù)返回值的類型，可將用戶定義函數(shù)分為如下三個(gè)類別：(1)標(biāo)量函數(shù):返回標(biāo)量值（單個(gè)值）的函數(shù)。(2)內(nèi)嵌表值函數(shù):函數(shù)中只包含一個(gè)T-SQL語句，而返回值為多個(gè)值，就好像是形成了一個(gè)新的表。(3)多語句表值函數(shù):函數(shù)中可以包含多個(gè)T-SQL語句，返回值就是形成一個(gè)數(shù)據(jù)表。

9.3.2用戶定義函數(shù)**定義語法：CREATEFUNCTION[owner_name.]function_name/*函數(shù)名部分*/

([{@parameter_name[AS]parameter_data_type[=default]}[,...n]])/*形參定義部分*/RETURNSreturn_data_type

/*返回值的類型*/[AS]BEGINfunction_body/*函數(shù)體部分*/

RETURNexpression/*返回語句*/END（1）標(biāo)量函數(shù)9.3.2用戶定義函數(shù)【例9-20】求某門課的平均成績CREATEFUNCTIONaverage(@cnchar(4))RETURNSfloatASBEGINDECLARE@averfloatSELECT@aver=(SELECTavg(score)FROMscWHEREcno=@cn

)RETURN@averENDGO9.3.2用戶定義函數(shù)當(dāng)調(diào)用用戶定義的標(biāo)量函數(shù)時(shí)，必須提供至少由兩部分組成的名稱（所有者名.函數(shù)名）?？梢栽赟ELECT語句或用EXEC命令調(diào)用，調(diào)用形式：所有者名.函數(shù)名(實(shí)參1,…,實(shí)參n)實(shí)參可為已賦值的局部變量或表達(dá)式。**標(biāo)量函數(shù)的調(diào)用【例9-21】求C001號(hào)課的平均成績USEteachingDECLARE@course1char(4)SET@course1='C001'SELECTdbo.average(@course1)AS'C001號(hào)課程的平均成績'9.3.2用戶定義函數(shù)也可以在建表時(shí)使用函數(shù)求某列。

**標(biāo)量函數(shù)的調(diào)用【例】新建course1表

createtablecourse1(cnochar(4)primarykey,

cname

nvarchar(20),creditint,averas(dbo.average(cno)))

9.3.2用戶定義函數(shù)**定義語法：CREATEFUNCTION[owner_name.]function_name/*定義函數(shù)名部分*/([{@parameter_name[AS]parameter_data_type[=default]}[,...n]])/*定義參數(shù)部分*/RETURNSTABLE

/*返回值為表類型*/[AS]RETURN[(select-stmt[])]

/*通過SELECT語句返回內(nèi)嵌表*/（2）內(nèi)嵌表值函數(shù)9.3.2用戶定義函數(shù)【例9-22】查詢某個(gè)專業(yè)所有學(xué)生的學(xué)號(hào)、姓名、所選的課程號(hào)和成績。USEteachingGOCREATEFUNCTIONst_func(@majornchar(20))RETURNStableASreturnSELECTa.sno,sname,cno,score

FROMstudenta,sc

WHEREspecialty=@majoranda.sno=sc.sno9.3.2用戶定義函數(shù)【例9-23】查詢計(jì)算機(jī)專業(yè)所有學(xué)生的學(xué)號(hào)、姓名、所選的課程號(hào)和成績。**內(nèi)嵌表值函數(shù)的調(diào)用內(nèi)嵌表值函數(shù)只能通過SELECT語句調(diào)用，就像查詢一個(gè)表一樣，內(nèi)嵌表值函數(shù)調(diào)用時(shí)，可以僅使用函數(shù)名，省略所有者名。UseteachinggoSELECT

*FROMst_func('計(jì)算機(jī)')

9.3.2用戶定義函數(shù)

內(nèi)嵌表值函數(shù)和多語句表值函數(shù)都返回表，二者不同之處在于：內(nèi)嵌表值函數(shù)沒有函數(shù)主體，返回的表是單個(gè)SELECT語句的結(jié)果集；而多語句表值函數(shù)在BEGIN...END塊中定義的函數(shù)主體可包含多條insert語句，每個(gè)insert語句都嵌套一個(gè)select語句，它們將每個(gè)select語句查詢到的數(shù)據(jù)行插入至表中，最后返回表。(3)多語句表值函數(shù)9.3.2用戶定義函數(shù)CREATEFUNCTION[owner_name.]function_name/*定義函數(shù)名部分*/

([{@parameter_name[AS]parameter_data_type[=default]}[,...n]])/*定義函數(shù)參數(shù)部分*/RETURNS@return_variableTABLE<table_definition>

/*定義作為返回值的表*/[AS]BEGIN

function_body/*定義函數(shù)體*/

RETURNEND**多語句表值函數(shù)定義9.3.2用戶定義函數(shù)【例9-24】創(chuàng)建多語句表值函數(shù)，通過學(xué)號(hào)作為實(shí)參調(diào)用該函數(shù)，可顯示該學(xué)生的姓名以及各門功課的成績和學(xué)分。CREATEFUNCTIONst_score(@nochar(10))

RETURNS@scoretable(s_nochar(10),s_namenvarchar(8),c_namechar(10),c_scoretinyint,c_credittinyint)ASBEGININSERTinto@scoreSELECTs.sno,sname,cname, credit,scoreFROMstudents,coursec,scWHEREs.sno=sc.snoANDo=oANDs.sno=@noRETURNEND9.3.2用戶定義函數(shù)【例9-25】查詢2022020001號(hào)學(xué)生的姓名以及各門功課的成績和學(xué)分。UseteachingGoselect*fromst_score('2022020001')**

多語句表值函數(shù)的調(diào)用多語句表值函數(shù)的調(diào)用與內(nèi)嵌表值函數(shù)的調(diào)用方法相同，只能通過SELECT語句調(diào)用。9.3.2用戶定義函數(shù)用戶函數(shù)的修改和刪除利用ALTERFUNCTION對(duì)用戶定義函數(shù)修改，語法結(jié)構(gòu)與定義時(shí)完全相同。利用DROPFUNCTION刪除用戶定義函數(shù)，語法：DROPFUNCTION函數(shù)名。9.3.2用戶定義函數(shù)

游標(biāo)是處理數(shù)據(jù)的一種方法，它允許應(yīng)用程序?qū)Σ樵冋Z句SELECT返回的結(jié)果集中每一行進(jìn)行相同或不同的操作，而不是一次對(duì)整個(gè)結(jié)果集進(jìn)行同一種操作。我們可以把游標(biāo)當(dāng)作一個(gè)指針，它可以指定結(jié)果中的任何位置，然后允許用戶對(duì)指定位置的數(shù)據(jù)進(jìn)行處理。游標(biāo)把作為面向集合的數(shù)據(jù)庫管理系統(tǒng)和面向行的程序設(shè)計(jì)兩者聯(lián)系起來。

9.4游標(biāo)

游標(biāo)通過以下方式擴(kuò)展結(jié)果處理：

(1)允許定位在結(jié)果集的特定行。

(2)從結(jié)果集的當(dāng)前位置檢索一行或多行。

(3)支持對(duì)結(jié)果集中當(dāng)前位置的行進(jìn)行數(shù)據(jù)修改。

(4)為由其他用戶對(duì)顯示在結(jié)果集中的數(shù)據(jù)庫數(shù)據(jù)所做的更改提供不同級(jí)別的可見性支持。

(5)提供腳本、存儲(chǔ)過程和觸發(fā)器中使用的訪問結(jié)果集中的數(shù)據(jù)的

T-SQL

語句。

9.4.1游標(biāo)概述

1.T-SQL游標(biāo)

T-SQL游標(biāo)是由DECLARECURSOR語法定義、主要用在服務(wù)器

T-SQL腳本、存儲(chǔ)過程和觸發(fā)器中。

2.API游標(biāo)

API游標(biāo)支持在OLEDB、ODBC以及DB_library中使用游標(biāo)函數(shù)，主要用在服務(wù)器上。

3.客戶游標(biāo)客戶游標(biāo)主要是當(dāng)在客戶機(jī)上緩存結(jié)果集時(shí)才使用。

9.4.2游標(biāo)的類型服務(wù)器游標(biāo)包含四種：(1)靜態(tài)游標(biāo)靜態(tài)游標(biāo)的完整結(jié)果集將打開游標(biāo)時(shí)建立的結(jié)果集存儲(chǔ)在臨時(shí)表中。靜態(tài)游標(biāo)始終是只讀的，總是按照打開游標(biāo)時(shí)的原樣顯示結(jié)果集；不反映數(shù)據(jù)庫中作的任何更改。(2)動(dòng)態(tài)游標(biāo)當(dāng)滾動(dòng)游標(biāo)時(shí)動(dòng)態(tài)游標(biāo)反映結(jié)果集中的所有更改。9.4.2游標(biāo)的類型(3)只進(jìn)游標(biāo)只進(jìn)游標(biāo)不支持滾動(dòng)，它只支持游標(biāo)從頭到尾順序提取數(shù)據(jù)行。該游標(biāo)反映對(duì)結(jié)果集所做的所有更改。(4)鍵集驅(qū)動(dòng)游標(biāo)鍵集驅(qū)動(dòng)游標(biāo)同時(shí)具有靜態(tài)游標(biāo)和動(dòng)態(tài)游標(biāo)的特點(diǎn)。對(duì)非鍵集列的數(shù)據(jù)值的更改在用戶游標(biāo)滾動(dòng)的時(shí)候可以看見，在游標(biāo)打開以后對(duì)數(shù)據(jù)庫中插入的行是不可見的，除非關(guān)閉重新打開游標(biāo)。

9.4.2游標(biāo)的類型1.聲明游標(biāo)游標(biāo)的聲明包括兩個(gè)部分：游標(biāo)的名稱和這個(gè)游標(biāo)所用到的SQL語句。DECLAREcursor_name[INSENSITIVE][SCROLL]CURSORFORselect_statement[FOR{READONLY|UPDATE[OFcolumn_name[,...n]]}]9.4.3游標(biāo)的操作

2.打開游標(biāo)聲明了游標(biāo)后在做其它操作之前必須打開它。其語法為：OPEN{{[GLOBAL]cursor_name}|cursor_variable_name}

3.讀取游標(biāo)可以從游標(biāo)中逐行地讀取數(shù)據(jù)以進(jìn)行相關(guān)處理。語法格式：FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]FROM]{{[GLOBAL]cursor_name}|cursor_variable_name}[INTO@variable_name[,…n]]9.4.3游標(biāo)的操作

4.關(guān)閉游標(biāo)在處理完游標(biāo)中數(shù)據(jù)之后必須關(guān)閉游標(biāo)，以便釋放數(shù)據(jù)結(jié)果集和定位于數(shù)據(jù)記錄上的鎖。語法格式如下：CLOSE{{[GLOBAL]cursor_name}|cursor_variable_name}

5.釋放游標(biāo)DEALLOCATE語句釋放數(shù)據(jù)結(jié)構(gòu)和游標(biāo)所加的鎖。語法格式如下：DEALLOCATE{{[GLOBAL]cursor_name}|cursor_variable_name}9.4.3游標(biāo)的操作

【例9-26】聲明一個(gè)名為S_Cursor的游標(biāo)，用以查詢計(jì)算機(jī)專業(yè)的所有學(xué)生的信息：DECLARES_CursorCURSORFORSELECT*FROMstudentWHEREspecialty='計(jì)算機(jī)'9.4.3游標(biāo)的操作

【例9-27】聲明一個(gè)名為Sh_Cursor的游標(biāo)，用以查詢2023級(jí)學(xué)生的信息。要求該游標(biāo)是動(dòng)態(tài)的、可前后滾動(dòng)，其中的專業(yè)列數(shù)據(jù)可以修改。DECLARESh_CursorCURSORDYNAMICFORSELECT*FROMWHEREgrade='2023級(jí)'FORUPDATEOFspecialty9.4.3游標(biāo)的操作

【例9-28】打開例9-26聲明的游標(biāo)。OPENS_Cursor【例9-29】從例9-26聲明的游標(biāo)中讀取數(shù)據(jù)。FETCHNEXTFROMS_Cursor【例9-30】關(guān)閉S_Cursor游標(biāo)。CLOSES_Cursor【例9-31】釋放S_Cursor游標(biāo)。DEALLOCATES_Cursor9.4.3游標(biāo)的操作

6.游標(biāo)的完整實(shí)例以下實(shí)例用于對(duì)bankcard數(shù)據(jù)庫中數(shù)據(jù)的操作。【例9-32】聲明一個(gè)Sh1_Cursor，只顯示儲(chǔ)戶表中第三行和第五行數(shù)據(jù)。DECLARESh1_CursorCURSORSTATICFORSELECT*FROMdepositorOPENSh1_CursorFETCHABSOLUTE3FROMSh1_CursorFETCHABSOLUTE5FROMSh1_CursorCLOSESh1_CursorDEALLOCATESh1_Cursor9.4.3游標(biāo)的操作

【例9-33】首先顯示身份證號(hào)為130***197412120221的儲(chǔ)戶的全部賬號(hào)信息；聲明Sh2_Cursor游標(biāo)，將此儲(chǔ)戶的第一個(gè)賬戶的余額加500，第二個(gè)賬戶的余額減500；再次顯示身份證號(hào)為130***197412120221的儲(chǔ)戶的全部賬號(hào)信息。SELECT*FROMaccountWHEREIDNO='130***197412120221'DECLARESh2_CursorCURSORDYNAMICFORSELECT*FROMaccountWHEREIDNO='130***197412120221'FORUPDATEOFBalanceOPENSh2_CursorFETCHNEXTFROMSh2_CursorUPDATEaccountSETBalance=Balance+500WHERECURRENTOFSh2_CursorFETCHNEXTFROMSh2_CursorUPDATEaccountSETBalance=Balance-500WHERECURRENTOFSh2_CursorCLOSESh2_CursorDEALLOCATESh2_CursorSELECT*FROMaccountWHEREIDNO='130***197412120221'9.4.3游標(biāo)的操作

數(shù)據(jù)庫原理與應(yīng)用教程

―SQLServer2019第10章視圖和索引第10章視圖和索引

數(shù)據(jù)庫的基本表是按照數(shù)據(jù)庫設(shè)計(jì)人員的觀點(diǎn)設(shè)計(jì)的，并不一定符合所有用戶的需求。SQLServer可以根據(jù)用戶需求重新定義表的數(shù)據(jù)結(jié)構(gòu)，這種數(shù)據(jù)結(jié)構(gòu)就是視圖（用戶外模式）。數(shù)據(jù)庫中的索引與書籍中的目錄類似，在一本書中，目錄可以方便用戶不必閱讀整本書就能找到需要的內(nèi)容。同樣，在數(shù)據(jù)庫中，索引可以使數(shù)據(jù)庫程序在最短的時(shí)間內(nèi)找到所需要的數(shù)據(jù)，而不必查找整個(gè)數(shù)據(jù)庫，從而提高查找效率。第10章視圖和索引在數(shù)據(jù)庫的三級(jí)模式結(jié)構(gòu)當(dāng)中，索引對(duì)應(yīng)的是內(nèi)模式部分，基本表對(duì)應(yīng)的是模式部分，而視圖對(duì)應(yīng)的是外模式部分。本章主要內(nèi)容：視圖的基本概念、視圖的操作索引的基本概念、索引的操作三級(jí)模式結(jié)構(gòu)的一個(gè)具體實(shí)例視圖1圖書信息出版社名稱字符型20書名字符型30作者姓名字符型20出版日期日期型

視圖2作者著書信息作者姓名字符型20書名字符型30視圖3出版社出書類型出版社名稱字符型20書名字符型30類型字符型1

作者

作品

出版社

數(shù)據(jù)文件數(shù)據(jù)文件數(shù)據(jù)文件

索引文件索引文件索引文件

作者

作品

出版社

作者編號(hào)字符型5書號(hào)字符型6出版社編號(hào)字符型5

作者姓名字符型20書名字符型30出版社名稱字符型20

通信地址字符型50類型字符型1城市字符型10

郵編字符型6價(jià)格貨幣

電話字符型11出版日期日期型

外模式模式內(nèi)模式第10章視圖和索引10.1視圖視圖(View，外模式)是從一個(gè)或幾個(gè)基本表（模式）導(dǎo)出來的表，是一個(gè)虛表，并不表示任何物理數(shù)據(jù)。數(shù)據(jù)庫中只存儲(chǔ)視圖的定義，而不存儲(chǔ)視圖對(duì)應(yīng)的數(shù)據(jù)，這些數(shù)據(jù)仍存儲(chǔ)在導(dǎo)出視圖的基本表中*。10.1視圖10.1.1視圖概述

數(shù)據(jù)庫的基本表是按照數(shù)據(jù)庫設(shè)計(jì)人員的觀點(diǎn)設(shè)計(jì)的，并不一定符合所有用戶的需求。SQLServer可以根據(jù)用戶需求重新定義表的數(shù)據(jù)結(jié)構(gòu)，這種數(shù)據(jù)結(jié)構(gòu)就是視圖（用戶外模式）。10.1視圖10.1.1視圖概述視圖的優(yōu)點(diǎn)**：(1)為用戶集中數(shù)據(jù)，簡(jiǎn)化用戶的數(shù)據(jù)查詢和處理。(2)保證數(shù)據(jù)的邏輯獨(dú)立性。(3)重新定制數(shù)據(jù)，使得數(shù)據(jù)便于共享。(4)提高了數(shù)據(jù)的安全性。

10.1.2創(chuàng)建視圖視圖是數(shù)據(jù)庫中一個(gè)獨(dú)立的對(duì)象，創(chuàng)建時(shí)應(yīng)該遵循以下原則。①只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建視圖。②視圖名稱必須遵循標(biāo)識(shí)符的規(guī)則，且對(duì)每個(gè)用戶必須唯一。③用戶可以在其他視圖之上建立視圖。④如果視圖中的某一列是一個(gè)算術(shù)表達(dá)式、內(nèi)置函數(shù)或常量派生而來，那么，用戶需要為其指定特定的名稱*。10.1視圖1．在SQLServerManagementStudio使用向?qū)?chuàng)建視圖(例：創(chuàng)建男生視圖male_view)2．使用T-SQL語句創(chuàng)建視圖SQLServer提供了CREATEVIEW語句創(chuàng)建視圖，語法格式如下：CREATEVIEW[schema_name.]view_name[(column_name[,...n])][with<view_attribute>[…n]]ASselect_statement

[WITHCHECKOPTION]10.1.2創(chuàng)建視圖[例10-1]創(chuàng)建視圖s_c_sc

，包括電子信息專業(yè)的學(xué)生的學(xué)號(hào)、姓名，和他們選修的課程號(hào)、課程名和成績。USEteaching

GOCREATEVIEWs_c_scASSELECTstudent.sno,sname,o,cname,scoreFROMstudent,sc,courseWHEREstudent.sno=sc.snoANDo=oANDspecialty='電子信息'GO10.1.2創(chuàng)建視圖【例10-2】針對(duì)教材附錄的實(shí)驗(yàn)中bankcard數(shù)據(jù)庫,創(chuàng)建名為“acc_count”的賬戶統(tǒng)計(jì)視圖，求每個(gè)儲(chǔ)戶的賬戶個(gè)數(shù)，要求包括身份證號(hào)和姓名。USEbankcardGOCREATEVIEWacc_countASSELECTdepositor.IDNO,Dname,COUNT(*)ASNumberFROMdepositor,accountWHEREdepositor.IDNO=account.IDNOGROUPBYdepositor.IDNO,DnameGO10.1.2創(chuàng)建視圖10.1.3修改視圖1．在SQLServerManagementStudio中修改2．T-SQL提供了ALTERVIEW語句修改視圖，語法格式如下：ALTERVIEW[schema_name.]view_name[(column_name[,...n])][with<view_attribute>[…n]]ASselect_statement[WITHCHECKOPTION]注：除命令詞與CREATEVIEW不同之外，其他子命令和參數(shù)都相同。10.1視圖【例10-3】修改“acc_count”視圖，求每個(gè)儲(chǔ)戶的賬戶個(gè)數(shù)和總存款余額，要求包括身份證號(hào)和姓名。USEbankcardGOALTERVIEWacc_countASSELECTdepositor.IDNO,Dname,COUNT(*)ASNumber,SUM(Balance)SumBalanceFROMdepositor,accountWHEREdepositor.IDNO=account.IDNOGROUPBYdepositor.IDNO,DnameGO10.1.3修改視圖【例10-4】在視圖上創(chuàng)建視圖：創(chuàng)建“Few_Balance”余額統(tǒng)計(jì)視圖，求總存款余額少于5000的儲(chǔ)戶信息，包括身份證號(hào)和姓名和手機(jī)號(hào)。USEbankcardGOCREATEVIEWFew_BalanceASSELECTdepositor.IDNO,depositor.Dname,TelephoneFROMacc_count,depositorWHEREacc_count.IDNO=depositor.IDNOandSumBalance<5000GO10.1.3修改視圖10.1.4使用視圖視圖創(chuàng)建完畢，可以同查詢基本表一樣通過視圖查詢所需要的數(shù)據(jù)，也可以通過視圖更新基表中數(shù)據(jù)。1．使用視圖進(jìn)行數(shù)據(jù)查詢可以在SQLServerManagementStudio中選中要查詢的視圖并打開，瀏覽該視圖查詢的所有數(shù)據(jù)；也可以在查詢窗口中執(zhí)行T-SQL語句查詢視圖。例如，要查詢各儲(chǔ)戶的賬戶統(tǒng)計(jì)信息，可以右擊“acc_count

”，選擇”選擇前1000行”或“編輯前200行”選項(xiàng)；也可以執(zhí)行T-SQL語句：SELECT*FROMacc_count

10.1視圖【例10-5】在查詢窗口中查詢s_c_sc視圖，統(tǒng)計(jì)“C++語言”課程的總分和平均分。USEteachingSELECTsumscore=SUM(score),avgscore=AVG(score)FROMs_c_scWHEREcname='C++語言'10.1.4使用視圖【例10-6】查詢“acc_count”視圖中儲(chǔ)戶“張青”的統(tǒng)計(jì)信息。USEbankcardSELECT*FROMacc_countWHEREDname='張青'10.1.4使用視圖2．使用視圖更新基本表中數(shù)據(jù)更新視圖的數(shù)據(jù)，其實(shí)就是對(duì)基本表進(jìn)行更新，因?yàn)檎嬲娣艛?shù)據(jù)的地方是基本表而不是視圖。同樣使用INSERT、UPDATE、DELETE語句來完成數(shù)據(jù)的插入、修改和刪除。注意：并不是所有的視圖都可以更新數(shù)據(jù)，只有對(duì)滿足可更新條件的視圖才能更新數(shù)據(jù)。10.1.4使用視圖可更新條件*：(1)任何通過視圖的數(shù)據(jù)更新（包括UPDATE、INSERT和DELETE語句）都只能引用一個(gè)基本表的列。①如果視圖數(shù)據(jù)為一個(gè)表的行、列子集，則此視圖可更新（包括UPDATE、INSERT和DELETE語句）；但如果視圖中沒有包含表中某個(gè)不允許取空值又沒有默認(rèn)值約束的列，則不能利用視圖插入數(shù)據(jù)。②如果視圖所依賴的基本表有多個(gè)時(shí)，完全不能向該視圖添加（INSERT）數(shù)據(jù)。10.1.4使用視圖③若視圖依賴于多個(gè)基本表，那么一次修改只能修改（UPDATE）一個(gè)基本表中的數(shù)據(jù)。④若視圖依賴于多個(gè)基本表，那么不能通過視圖刪除（DELETE）數(shù)據(jù)。(2)視圖中被修改的列必須直接引用表列中的基礎(chǔ)數(shù)據(jù)。不能是通過任何其他方式對(duì)這些列進(jìn)行派生而來的數(shù)據(jù)，比如通過聚合函數(shù)、計(jì)算（如表達(dá)式計(jì)算）、集合運(yùn)算等。(3)被修改的列不應(yīng)是在創(chuàng)建視圖時(shí)受GROUPBY、HAVING、DISTINCT或TOP子句影響的。10.1.4使用視圖注意：有可能插入并不滿足視圖查詢的WHERE子句條件中的一行。為了進(jìn)行限制此操作，可以在創(chuàng)建視圖時(shí)使用WITHCHECKOPTION**選項(xiàng)。10.1.4使用視圖【例10-7】通過“male_view”視圖向“student”表中插入一個(gè)“男”生。INSERTINTOmale_viewVALUES('2023010005','張三','男','2005-6-1','電子信息','2023級(jí)')如果通過“male_view”視圖向“student”表中插入一個(gè)“女”生，也可以完成。10.1.4使用視圖如果不希望用戶通過“male_view”視圖插入“女”生，在創(chuàng)建“male_view”視圖時(shí)應(yīng)該使用WITHCHECKOPTION選項(xiàng)。命令如下：CREATEVIEWmale_viewASSELECTsno,sname,ssex,sbirthday,specialty,gradeFROMstudentWHEREssex='男'WITHCHECKOPTION10.1視圖10.1.5刪除視圖在不需要該視圖的時(shí)候，可以刪除該視圖。1．在SQLServerManagementStudio刪除視圖選中要?jiǎng)h除的視圖，右擊選擇“刪除”命令。2．T-SQL提供了視圖刪除語句DROPVIEW。語法格式：DROPVIEWview_name【例10-8】刪除例10-1創(chuàng)建的s_c_sc視圖。USEteachingGODROPVIEWs_c_scGO

10.1視圖第10章視圖和索引10.2索引索引(Index)是對(duì)數(shù)據(jù)庫表中一個(gè)或多個(gè)列的值進(jìn)行排序的結(jié)構(gòu)，其主要目的是提高SQLServer系統(tǒng)的性能，加快查詢數(shù)據(jù)的速度。10.2.1索引簡(jiǎn)介數(shù)據(jù)庫的索引就類似于書籍的目錄，如果想快速查找而不是逐頁查找指定的內(nèi)容，可以通過目錄中章節(jié)的頁號(hào)找到其對(duì)應(yīng)的內(nèi)容。類似地，索引通過記錄表中的關(guān)鍵值指向表中的記錄，不用掃描整個(gè)表而定位到相關(guān)的記錄。10.2索引索引的優(yōu)點(diǎn)**：（1）大大加快數(shù)據(jù)的檢索速度，這是創(chuàng)建索引的最主要的原因。（2）創(chuàng)建唯一性索引，保證表中每一行數(shù)據(jù)的唯一性。（3）加速表和表之間的連接。（4）在使用分組和排序子句進(jìn)行數(shù)據(jù)檢索時(shí)，同樣可以顯著減少查詢中分組和排序的時(shí)間。（5）查詢優(yōu)化器可以提高系統(tǒng)的性能，但它是依靠索引起作用的。10.2索引10.2.2索引類型SQLServer支持在表中任何列上定義索引。索引可以是唯一的，即索引列不會(huì)有兩行記錄相同，這樣的索引稱為唯一索引。例如，如果在表中的“姓名”列上創(chuàng)建了唯一索引，則以后輸入的姓名將不能同名。索引也可以是不唯一的，即索引列上可以有多行記錄相同。如果索引是根據(jù)單列創(chuàng)建的，稱為單列索引，根據(jù)多列組合創(chuàng)建的索引則稱為復(fù)合索引。根據(jù)索引的組織方式的不同，又可以將索引分為聚集索引和非聚集索引。10.2.2索引類型

1.聚集索引聚集索引會(huì)對(duì)表中數(shù)據(jù)進(jìn)行物理排序，所以這種索引對(duì)查詢非常有效，在表和視圖中只能有一個(gè)聚集索引。當(dāng)建立主鍵約束時(shí)，如果表中沒有聚集索引，SQLServer會(huì)用主鍵列作為聚集索引鍵?？梢栽诒淼娜魏瘟谢蛄械慕M合上建立索引，但實(shí)際應(yīng)用中一般為定義成主鍵約束的列建立聚集索引。例如，漢語字典的正文就是一個(gè)聚集索引的順序結(jié)構(gòu)（拼音順序）。比如，要查“安”字，就可以翻開字典的前幾頁，因?yàn)椤鞍病钡钠匆羰恰癮n”。如果翻完了所有“an”讀音的部分仍然找不到這個(gè)字，那么就說明字典中沒有這個(gè)字。同樣，如果查“張”字，可以將字典翻到最后部分，因?yàn)椤皬垺钡钠匆羰恰皕hang”。因?yàn)樽值涞恼膬?nèi)容本身就是按照音序排列的，而“漢語拼音音節(jié)索引”就可以稱為“聚集索引”。10.2.2索引類型2.非聚集索引非聚集索引不會(huì)對(duì)表中數(shù)據(jù)進(jìn)行物理排序。如果表中不存在聚集索引，則表是未排序的。例如，查字典時(shí)，不認(rèn)識(shí)的字按照上面的方法很難查找?？梢愿鶕?jù)“偏旁部首”來查。比如查“張”字，在查部首之后的檢字表中“張”字對(duì)應(yīng)的頁碼是622頁，檢字表中“張”的上面是“弛”，對(duì)應(yīng)的頁碼卻是60頁，“張”的下面是“弟”，對(duì)應(yīng)的頁碼是95頁，正文中這些字并不相鄰。所以“偏旁部首”索引中的排序與字典正文不一致。10.2.2索引類型那么，按部首查字就要兩個(gè)步驟：先找到部首查字法即“偏旁部首”索引目錄中的結(jié)果，然后再翻到所需要的頁碼。這種目錄純粹是目錄，正文純粹是正文的排序方式就稱為“非聚集索引”。在表或視圖中，最多可以建立250個(gè)非聚集索引，或者249個(gè)非聚集索引和1個(gè)聚集索引。注*：SQLServer中不管聚集還是非聚集索引都采用B+樹的存儲(chǔ)結(jié)構(gòu)。10.2.2索引類型B-樹實(shí)例圖10.2.2索引類型B+樹實(shí)例圖馬龍

鄭曉娟康劉馬龍齊菲鄭曉娟蔡紅范小麗馬龍宋張鄭曉娟寧潘齊菲戴鄧范小麗安白蔡紅

假定此B+樹為按學(xué)生姓名創(chuàng)建的非聚集索引，下面的指針指向?qū)W生表中相應(yīng)的學(xué)生信息。10.2.2索引類型10.2索引10.2.3創(chuàng)建索引注意：創(chuàng)建時(shí)，哪些列適合創(chuàng)建索引，哪些列不適合創(chuàng)建索引，需要進(jìn)行詳細(xì)的考察。

1．創(chuàng)建索引時(shí)應(yīng)考慮的問題*（1）對(duì)一個(gè)表中建大量的索引，應(yīng)進(jìn)行權(quán)衡**。對(duì)于SELECT查詢，大量索引可以提高性能，可以從中選擇最快的查詢方法；但是，會(huì)影響INSERT、UPDATE和DELETE語句的性能，因?yàn)閷?duì)表中的數(shù)據(jù)進(jìn)行修改時(shí)，索引也要?jiǎng)討B(tài)的維護(hù)，所以應(yīng)避免對(duì)經(jīng)常更新的表建立過多的索引，而對(duì)更新少而且數(shù)據(jù)量大的表創(chuàng)建多個(gè)索引，可以大大提高查詢性能。10.2.3創(chuàng)建索引（2）對(duì)于小型表（行數(shù)較少）進(jìn)行索引可能不會(huì)產(chǎn)生優(yōu)化效果。（3）對(duì)于主鍵和外鍵列應(yīng)考慮建索引，因?yàn)榻?jīng)常通過主鍵查詢數(shù)據(jù)，而外鍵用于表間的連接。（4）很少在查詢中使用的列以及值很少的列不應(yīng)考慮建索引。（5）視圖中如果包含聚合函數(shù)或連接時(shí)，創(chuàng)建視圖的索引可以顯著提升查詢性能。2．通過SQLServerManagementStudio,使用向?qū)?chuàng)建索引是一種圖形界面環(huán)境下最快捷的創(chuàng)建方式3．利用T-SQL語句創(chuàng)建索引基本語法格式：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED（默認(rèn)）]INDEXindex_name

ON{table_name|view_name}(column[ASC|DESC][,...n])10.2.3創(chuàng)建索引[例10-9]根據(jù)教學(xué)庫中學(xué)生表的姓名列的升序創(chuàng)建一個(gè)名為index_sname的普通索引。CREATEINDEXindex_snameONstudent(sname)GO分析受益的語句：如：Select*fromstudentwheresname=‘張強(qiáng)’再如：updatestudentsetspecialty=‘網(wǎng)絡(luò)工程’

wheresname=‘張強(qiáng)’10.2.3創(chuàng)建索引【例10-10】根據(jù)teaching庫中student表的專業(yè)、年級(jí)創(chuàng)建一個(gè)名為specialty_grade的復(fù)合索引，其中專業(yè)稱為升序，年級(jí)為降序。USEteachingGOCREATEINDEXspecialty_gradeONstudent(specialtyASC,gradeDESC)10.2.3創(chuàng)建索引4．間接創(chuàng)建索引在定義表結(jié)構(gòu)或修改表結(jié)構(gòu)時(shí)，如果定義了主鍵約束（PRAMARYKEY）或者唯一性約束（UNIQUE），可以間接創(chuàng)建索引。[例10-11]創(chuàng)建一個(gè)s3表，并定義了主鍵約束。USEteachingGOCREATETABLEs3(snochar(6)PRIMARYKEY,snamechar(8))此例中，就按sno升序創(chuàng)建了一個(gè)聚集索引。10.2.3創(chuàng)建索引[例10-12]創(chuàng)建一個(gè)教師表，并定義了主鍵約束和唯一性約束。USEteachingGOCREATETABLEteacher(tnochar(6)PRIMARYKEY,tnamechar(8)UNIQUE)此例中，創(chuàng)建了兩個(gè)索引，按tno升序創(chuàng)建了一個(gè)聚集索引，按tname升序創(chuàng)建了一個(gè)非聚集唯一索引。10.2.3創(chuàng)建索引5．創(chuàng)建視圖的索引【例10-13】創(chuàng)建一個(gè)female_view女生視圖，并為該視圖按sno升序創(chuàng)建一個(gè)具有唯一性的聚集索引。創(chuàng)建視圖：USEteachingGOCREATEVIEWfemale_viewWITHSCHEMABINDINGASSELECTsno,sname,ssex,specialtyFROMdbo.studentWHEREssex='女'10.2.3創(chuàng)建索引T-SQL語句創(chuàng)建索引：CREATEUNIQUECLUSTEREDINDEXindex_femaleONfemale_view(sno)創(chuàng)建聚集索引后，對(duì)于任何試圖為視圖修改基本數(shù)據(jù)而進(jìn)行的連接，其選項(xiàng)設(shè)置必須與創(chuàng)建索引所需的選項(xiàng)設(shè)置相同（如本例為sno）。若刪除視圖，視圖上的所有索引也將被刪除。若刪除聚集索引，視圖上的所有非聚集索引也將被刪除。

10.2.3創(chuàng)建索引10.2索引10.2.5刪除索引當(dāng)一個(gè)索引不再需要時(shí)，可以將其從數(shù)據(jù)庫中刪除，以釋放占用的存儲(chǔ)空間。注意**：（1）必須刪除約束，才能刪除PRIMARYKEY或UNIQUE約束使用的索引。（2）刪除某個(gè)表時(shí)，會(huì)自動(dòng)刪除在此表上創(chuàng)建的索引。10.2.5刪除索引1.在SQLServerManagementStudio中刪除索引右擊要?jiǎng)h除的索引，選擇“刪除”按鈕2.使用T-SQL語句刪除索引語法格式：DROPINDEXtable_name.index_name[例10-13]刪除學(xué)生表中的“Index_sname”索引。USEteachingGODROPINDEXstudent.Index_snameGO數(shù)據(jù)庫原理與應(yīng)用教程

―SQLServer2019第11章存儲(chǔ)過程、觸發(fā)器第11章存儲(chǔ)過程和觸發(fā)器在SQLServer應(yīng)用操作中，存儲(chǔ)過程扮演著相當(dāng)重要的角色。存儲(chǔ)過程可以使用戶對(duì)數(shù)據(jù)庫的管理工作變得更容易。**存儲(chǔ)過程是SQL語句和可選流程控制語句的預(yù)編譯集合，它以一個(gè)名稱存儲(chǔ)并作為一個(gè)單元處理，能夠提高系統(tǒng)的應(yīng)用效率和執(zhí)行速度(預(yù)先編譯好的一段程序)。第11章存儲(chǔ)過程和觸發(fā)器11.1存儲(chǔ)過程開發(fā)應(yīng)用程序時(shí)，為了易于修改和擴(kuò)充，經(jīng)常會(huì)將負(fù)責(zé)不同功能的語句集中起來而且按照用途分別獨(dú)立放置，以便能夠反復(fù)調(diào)用，而這些獨(dú)立放置且擁有不同功能的語句，即是“過程”(Procedure)。*用戶可以通過指定存儲(chǔ)過程的名字并給出參數(shù)（如果該存儲(chǔ)過程帶有參數(shù)）來執(zhí)行(調(diào)用)存儲(chǔ)過程。存儲(chǔ)過程類似于函數(shù)，是獨(dú)立存儲(chǔ)的數(shù)據(jù)庫對(duì)象。11.1存儲(chǔ)過程11.1.1存儲(chǔ)過程概述利用SQLServer創(chuàng)建一個(gè)應(yīng)用程序時(shí)，SQL是主要的編程語言。使用SQL進(jìn)行編程，有兩種方法。一是，在本地存儲(chǔ)SQL程序，并創(chuàng)建應(yīng)用程序向SQLServer服務(wù)器發(fā)送命令來對(duì)結(jié)果進(jìn)行處理。二是，可以把部分用SQL語句編寫的程序作為存儲(chǔ)過程存儲(chǔ)在SQLServer服務(wù)器中，然后創(chuàng)建應(yīng)用程序來調(diào)用存儲(chǔ)過程，對(duì)數(shù)據(jù)結(jié)果進(jìn)行處理。一般來講，使用服務(wù)器中的存儲(chǔ)過程而不使用客戶機(jī)本地T-SQL程序的優(yōu)勢(shì)主要表現(xiàn)在**：(1)允許模塊化程序設(shè)計(jì)：一次編寫，多次調(diào)用；易于修改。(2)允許更快速地執(zhí)行：存儲(chǔ)過程是預(yù)先編譯和優(yōu)化好的。(3)減少網(wǎng)絡(luò)流量：程序代碼（調(diào)用存儲(chǔ)過程）和數(shù)據(jù)（一次性傳輸）的傳輸量少。(4)可作為安全機(jī)制使用：可以只給用戶執(zhí)行存儲(chǔ)過程的權(quán)限，不允許他的程序直接訪問數(shù)據(jù)。

11.1.1存儲(chǔ)過程概述11.1.2存儲(chǔ)過程的類型1.系統(tǒng)存儲(chǔ)過程SQLServer2019中的許多管理活動(dòng)或信息性的活動(dòng)（如獲取數(shù)據(jù)庫和數(shù)據(jù)庫對(duì)象的信息）都是通過一種特殊的存儲(chǔ)過程執(zhí)行的，這種存儲(chǔ)過程被稱為系統(tǒng)存儲(chǔ)過程。系統(tǒng)存儲(chǔ)過程主要存儲(chǔ)在master數(shù)據(jù)庫中并以sp_開頭（如,SP_HELPDB:報(bào)告有關(guān)指定數(shù)據(jù)庫或所有數(shù)據(jù)庫的信息。11.1存儲(chǔ)過程2．本地存儲(chǔ)過程即本地服務(wù)器上的存儲(chǔ)過程，也就是一般所稱的用戶自定義存儲(chǔ)過程*。本地存儲(chǔ)過程也就是用戶自行創(chuàng)建、能完成某一特定功能(如查詢用戶所需的數(shù)據(jù)信息)、并存儲(chǔ)在用戶數(shù)據(jù)庫中的存儲(chǔ)過程，一般所說的存儲(chǔ)過程指的就是本地存儲(chǔ)過程。(有權(quán)限的用戶程序都可以調(diào)用)11.1.2存儲(chǔ)過程的類型3.臨時(shí)存儲(chǔ)過程（有一定的使用期限）(1)本地臨時(shí)存儲(chǔ)過程(2)全局臨時(shí)存儲(chǔ)過程4.遠(yuǎn)程存儲(chǔ)過程（即遠(yuǎn)程服務(wù)器上的存儲(chǔ)過程）5.擴(kuò)展存儲(chǔ)過程（高級(jí)語言編寫，功能更強(qiáng)）11.1.2存儲(chǔ)過程的類型11.1存儲(chǔ)過程11.1.3創(chuàng)建存儲(chǔ)過程1．在SQLServerManagementStudio中創(chuàng)建存儲(chǔ)過程展開“可編程性”選項(xiàng)，可以看到存儲(chǔ)過程列表中系統(tǒng)自動(dòng)為數(shù)據(jù)庫創(chuàng)建的系統(tǒng)存儲(chǔ)過程。右擊“存儲(chǔ)過程”選項(xiàng)，選“新建存儲(chǔ)過程”命令。2．利用T-SQL語句創(chuàng)建存儲(chǔ)過程CREATEPROCEDURE創(chuàng)建存儲(chǔ)過程，語法格式如下：CREATE{PROC|PROCEDURE}procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUT[PUT]]][,...n][WITH{RECOMPILE|ENCRYPTION

|RECOMPILE,ENCRYPTION

}]ASsql_statement[...n]11.1.3創(chuàng)建存儲(chǔ)過程在創(chuàng)建存儲(chǔ)過程時(shí)，需注意以下幾點(diǎn)。(1)存儲(chǔ)過程最大不能超過128MB。(2)用戶定義的存儲(chǔ)過程只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建。(3)CREATEPROCEDURE必須是一個(gè)批處理的第一條語句。(4)SQLServer允許在存儲(chǔ)過程創(chuàng)建時(shí)引用一個(gè)不存在的對(duì)象，在創(chuàng)建的時(shí)候，系統(tǒng)只檢查創(chuàng)建存儲(chǔ)過程的語法。

11.1.3創(chuàng)建存儲(chǔ)過程[例11-1]在教學(xué)庫創(chuàng)建無參存儲(chǔ)過程，查詢每個(gè)學(xué)生的平均成績。USEteachingGOCREATEPROCEDUREstudent_avgASSELECTsno,avg(score)as'avgscore'FROMscGROUPBYsnoGO11.1.3創(chuàng)建存儲(chǔ)過程[例11-2]在教學(xué)庫創(chuàng)建帶參數(shù)的存儲(chǔ)過程，查詢某個(gè)同學(xué)的基本信息。USEteachingGOCREATEPROCEDUREGetStudent@numberchar(10)ASSELECT*FROMstudentWHEREsno=@numberGO11.1.3創(chuàng)建存儲(chǔ)過程【例11-3】創(chuàng)建存儲(chǔ)過程，修改某個(gè)學(xué)生某門課的成績。USEteachingGOCREATEPROCEDUREUpdate_score@numberchar(10),@cnochar(4),@scoreintASUPDATEscSETscore=@scoreWHEREsno=@numberandcno=@cnoSELECT*FROMscWHEREsno=@number11.1.3創(chuàng)建存儲(chǔ)過程【例11-4】在bankcard數(shù)據(jù)庫中使用流程控制語句創(chuàng)建存儲(chǔ)過程。假設(shè)今天銀行有活動(dòng)，如果今天某賬號(hào)交易支出總金額超過3000元，則獎(jiǎng)勵(lì)其10元。USEbankcardGOCREATEPROCEDUREadd_10@AccNOchar(20)WITHENCRYPTIONASIF(SELECTSUM(Expense)FROMTrecordWHERETdate=CONVERT(varchar(10),GETDATE(),120)ANDAccNO=@AccNO)>=3000BEGINUPDATEAccountSETBalance=Balance+10WHEREAccNO=@AccNOINSERTTrecord(TDate,AccNO,Income,Abstract)VALUES(GETDATE(),@AccNO,10,'銀行活動(dòng)獎(jiǎng)勵(lì)')END11.1.3創(chuàng)建存儲(chǔ)過程【例11-5】在bankcard數(shù)據(jù)庫創(chuàng)建帶OUTPUT參數(shù)的存儲(chǔ)過程，用于計(jì)算指定的儲(chǔ)戶的總余額，存儲(chǔ)過程中使用一個(gè)輸入?yún)?shù)（身份證號(hào)）和兩個(gè)輸出參數(shù)（儲(chǔ)戶姓名和總余額）。USEbankcardGOCREATEPROCEDUREs_balance@IDNOchar(18),@dname

nvarchar(10)OUTPUT,@sbalancemoneyOUTPUTASSELECT@dname=DnameFROMdepositorWHEREIDNO=@IDNOSELECT@sbalance=SUM(Balance)FROMaccount WHEREIDNO=@IDNOGO11.1.3創(chuàng)建存儲(chǔ)過程11.1存儲(chǔ)過程11.1.4執(zhí)行（調(diào)用）存儲(chǔ)過程執(zhí)行存儲(chǔ)過程使用T-SQL語中的EXECUTE命令。如果執(zhí)行存儲(chǔ)過程是批處理中的第一條語句，那么不使用EXECUTE關(guān)鍵字也可以。注意：1、對(duì)于存儲(chǔ)過程的所有者或任何一名對(duì)此過程擁有EXECUTE權(quán)限的用戶，都可以執(zhí)行此存儲(chǔ)過程。2、輸入?yún)?shù)在存儲(chǔ)過程名后逐一給定，用逗號(hào)隔開，不必使用括號(hào)。3、如果沒有使用@參數(shù)名=value這種方式傳入值，則參數(shù)的排列必須和建立存儲(chǔ)過程所定義的次序?qū)?yīng)。4、用來接受輸出值的參數(shù)必須加上OUTPUT。EXECUTE語句的語法格式：[[EXECUTE[UTE]][@return_status=]procedure_name[;number]{[[@parameter=]value|[@parameter=]@variable[OUTPUT]]}[WITHRECOMPILE]【例11-6】執(zhí)行存儲(chǔ)過程student_avg。EXECUTEstudent_avg11.1.4執(zhí)行（調(diào)用）存儲(chǔ)過程【例11-7】執(zhí)行帶參數(shù)的存儲(chǔ)過程GetStudent，查詢

2021010001號(hào)學(xué)生的基本信息。EXECUTEGetStudent'2021010001'【例11-8】執(zhí)行修改成績的存儲(chǔ)過程Update_score，將2022020001號(hào)學(xué)生選修的C001號(hào)課程的成績改為100。EXECUTEUpdate_score'2022020001','C001',100【例11-9】用賬號(hào)412542800335120***01執(zhí)行存儲(chǔ)過程add_10。EXECUTEadd_10'412542800335120***01'11.1.4執(zhí)行（調(diào)用）存儲(chǔ)過程【例11-10】執(zhí)行帶有輸入和輸出參數(shù)的存儲(chǔ)過程s_balance。Declare@IDNOchar(18),@dnamenvarchar(10),@sbalancemoneyEXECUTEs_balance'133***198812110101',@dnameOUTPUT,@sbalanceOUTPUTPrint'儲(chǔ)戶'+@dname+'目前總余額'+str(@sbalance)11.1.4執(zhí)行（調(diào)用）存儲(chǔ)過程11.1.5查看、修改和刪除存儲(chǔ)過程1．查看存儲(chǔ)過程可以執(zhí)行系統(tǒng)存儲(chǔ)過程sp_helptext，用于查看創(chuàng)建存儲(chǔ)過程的命令語句；也可以執(zhí)行系統(tǒng)存儲(chǔ)過程sp_help，用于查看存儲(chǔ)過程的名稱、擁有者、類型、創(chuàng)建時(shí)間等基本信息?！纠?1-11】查看存儲(chǔ)過程s_balance的相關(guān)信息。(1)sp_helptexts_balance

(2)sp_helps_balance

11.1存儲(chǔ)過程11.1.5查看、修改和刪除存儲(chǔ)過程2．修改存儲(chǔ)過程可以在SQLServerManagementStudio中“修改”；也可以通過T-SQL語句完成，語法與創(chuàng)建時(shí)完全相同：ALTER{PROC|PROCEDURE}procedure_name[;number][{@parameterdata_type}[VARYING][=default][[OUT[PUT]][,...n][WITH{RECOMPILE|ENCRYPTION

|RECOMPILE,ENCRYPTION

}[,...n]][FORREPLICATION]ASsql_statement[...n]【例11-12】修改存儲(chǔ)過程add_10，將3000元和10元設(shè)置為兩個(gè)參數(shù)的默認(rèn)值，使存儲(chǔ)過程應(yīng)用更靈活。USEbankcardGOALTERPROCEDUREadd_10@AccNOchar(20),@expmoney=3000,@addint=10WITHENCRYPTIONASIF(SELECTSUM(Expense)FROMTrecordWHERETDate=CONVERT(varchar(10),GETDATE(),120)ANDAccNO=@AccNO)>=@expBEGINUPDATEAccountSETBalance=Balance+@addWHEREAccNO=@AccNOINSERTTrecord(TDate,AccNO,Income,Abstract)VALUES(CONVERT(varchar(10),GETDATE(),120),@AccNO,@add,'銀行活動(dòng)獎(jiǎng)勵(lì)')END11.1.5查看、修改和刪除存儲(chǔ)過程【例11-13】執(zhí)行帶有參數(shù)和默認(rèn)值的存儲(chǔ)過程add_10。(1)EXECUTEadd_10'412542800335120***01'(2)EXECUTEadd_10'412542800335120***01',2000(3)EXECUTEadd_10'412542800335120***01',5000,2011.1.5查看、修改和刪除存儲(chǔ)過程3．刪除存儲(chǔ)過程對(duì)于不需要的存儲(chǔ)過程可以在SQLServerManagementStudio中刪除；也可以使用T-SQL語句中的DROPPROCEDURE命令刪除。T-SQL語句的語法格式為：DROPPROCEDURE{procedure_name}[,…n]procedure_name指要?jiǎng)h除的存儲(chǔ)過程或存儲(chǔ)過程組的名稱?！纠?1-14】刪除存儲(chǔ)過程s_balanceDROPPROCEDUREs_balance11.1.5查看、修改和刪除存儲(chǔ)過程11.2觸發(fā)器存儲(chǔ)過程是SQL語句和可選流程控制語句的預(yù)編譯集合，它以一個(gè)名稱存儲(chǔ)并作為一個(gè)單元處理，用戶可以通過指定存儲(chǔ)過程的名稱并給出參數(shù)（如果該存儲(chǔ)過程帶有參數(shù)）來執(zhí)行(調(diào)用)存儲(chǔ)過程。*就本質(zhì)而言，觸