大二上學(xué)期文件-數(shù)據(jù)庫(kù)王睿智chap05-1存儲(chǔ)過程

數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用第五章存儲(chǔ)過程5.1

Transact-SQL語(yǔ)言基礎(chǔ)5.2

存儲(chǔ)過程(重點(diǎn))5.3

事務(wù)5.4

觸發(fā)器(重點(diǎn))本節(jié)內(nèi)容提要

Transact-SQL(簡(jiǎn)為T-SQL)語(yǔ)言用于管理微軟SQLServer數(shù)據(jù)庫(kù)引擎實(shí)例，創(chuàng)建和管理數(shù)據(jù)庫(kù)對(duì)象，以及插入、檢索、修改和刪除數(shù)據(jù)等。Transact-SQL是對(duì)標(biāo)準(zhǔn)SQL語(yǔ)言的擴(kuò)展。它在標(biāo)準(zhǔn)SQL語(yǔ)言的基礎(chǔ)上添加了控制語(yǔ)句，是一種結(jié)構(gòu)化的程序設(shè)計(jì)語(yǔ)言；既可在查詢分析器中交互運(yùn)行，也可嵌入到宿主語(yǔ)言中去運(yùn)行。5.1

T-SQL參考文獻(xiàn)：(v=sql.90).aspx

SQLServer中變量分兩種：全局變量(@@)@@開始的標(biāo)識(shí)符，以保存SQL

Server系統(tǒng)的某些參數(shù)值等。由系統(tǒng)定義和維護(hù),用戶只能引用而不能修改或定義。查看全局變量值可用SELECT語(yǔ)句。局部變量(@)以@開始的字母、數(shù)字、

_等字符串，不區(qū)分大小寫字母。用于保存用臨時(shí)數(shù)據(jù)或由存儲(chǔ)過程返回的結(jié)果。由用戶定義(DECLARE語(yǔ)句)和賦值(SET語(yǔ)句或SELECT語(yǔ)句)，使用范圍只局限于某一個(gè)語(yǔ)句或過程體內(nèi)；T-SQL變量聲明語(yǔ)法：

DECLARE@variable1類型[,@variable2類型[,…]說明：聲明變量后，所有變量均初始化為NULL。局部變量聲明與賦值賦值語(yǔ)法1：SET@variable1=值|返回單值的子查詢|NULL說明：不能用SET同時(shí)對(duì)多個(gè)變量進(jìn)行賦值。賦值語(yǔ)法2：

SELECT@variable1=值1[,@variable2=值2,…][FROM…WHERE…]

說明：如果查詢返回的結(jié)果包含多個(gè)值，將最后一個(gè)值賦給局部變量。SELECT語(yǔ)句可以直接為一個(gè)或多個(gè)局部變量賦值。例5.1

聲明兩個(gè)局部變量并賦值。DECLARE@mName

varchar(30),@mLenint

SET@mName='ForrestGump'--賦值

SET@mLen=142

--賦值賦值實(shí)例1或

DECLARE@mNamevarchar(30),@mLenint

SELECT@mName='ForrestGump',@mLen=142--賦值例5.2使用查詢?yōu)榫植孔兞抠x值.DECLARE@rowsintSET@rows=(SELECT

COUNT(*)FROMmovies)賦值實(shí)例2或

DECLARE

@rowsint

SELECT@rows=COUNT(*)FROMmovies常用流程控制語(yǔ)句(一)

語(yǔ)句語(yǔ)法功能語(yǔ)句塊BEGINSQL語(yǔ)句1;SQL語(yǔ)句2;…….END定義語(yǔ)句塊?？汕短譏F語(yǔ)句IF

條件表達(dá)式

SQL語(yǔ)句1|語(yǔ)句塊1ELSE

SQL語(yǔ)句2|語(yǔ)句塊2定義條件以及當(dāng)指定條件為TRUE或FALSE時(shí)執(zhí)行的SQL語(yǔ)句。WHILE語(yǔ)句(可含BREAK、CONTINUE)WHILE

條件表達(dá)式

SQL語(yǔ)句1|

語(yǔ)句塊1

[BREAK]|[CONTINUE]

SQL語(yǔ)句2|

語(yǔ)句塊2

當(dāng)條件為TRUE時(shí)重復(fù)語(yǔ)句。BREAK:退出最內(nèi)層的WHILE循環(huán)。CONTINUE:重新開始WHILE循環(huán),忽略CONTINUE關(guān)鍵字后面的任何語(yǔ)句。常用流程控制語(yǔ)句(二)

語(yǔ)句語(yǔ)法功能簡(jiǎn)單CASECASE

輸入表達(dá)式

WHEN表達(dá)式1THEN結(jié)果表達(dá)式1

[…n]

[

ELSE結(jié)果表達(dá)式n+1]END將輸入表達(dá)式的值與when中表達(dá)式值比較，若相等則計(jì)算THEN后的表達(dá)式；若與所有WHEN子句中的表達(dá)式值都不等，則計(jì)算ELSE后的表達(dá)式。最后將值返回。搜索型CASECASE

WHEN

條件表達(dá)式1THEN

結(jié)果表達(dá)式1

[…n

]

[

ELSE

結(jié)果表達(dá)式n+1]ENDRETURN語(yǔ)句RETURN

[整型表達(dá)式]無(wú)條件退出。CASE表達(dá)式應(yīng)用例5.3實(shí)驗(yàn)四中的12小題,統(tǒng)計(jì)每門課程的選課人數(shù)及不及格人數(shù)。SELECTumAS課程號(hào),cnameAS課程名,count(snum)AS選課人數(shù),sum(CASEWHENscore<60THEN1ELSE0END)AS不及格人數(shù)FROMsc,sections,courseWHEREsc.secnum=sections.secnumANDum=umGROUPBYum,cname5.2存儲(chǔ)過程

存儲(chǔ)過程(StoredProcedure)是指封裝了可重用代碼的、存儲(chǔ)在服務(wù)器端的數(shù)據(jù)庫(kù)中的程序模塊(modules)。

它類似傳統(tǒng)語(yǔ)言(如C)中的過程。可接收0或多個(gè)輸入?yún)?shù)；具有返回狀態(tài)值(整型，默認(rèn)為0)，表明被調(diào)用成功與否；并能以多輸出參數(shù)的格式返回多個(gè)結(jié)果。一經(jīng)創(chuàng)建，就可在SQL查詢或宿主語(yǔ)言中被調(diào)用。優(yōu)點(diǎn)：存儲(chǔ)過程在第一次被執(zhí)行時(shí)，便在服務(wù)器上被編譯。以后的每次調(diào)用直接執(zhí)行已編譯好的二進(jìn)制代碼，不再重新編譯。執(zhí)行效率高、顯著降低了網(wǎng)絡(luò)通信量。它是數(shù)據(jù)庫(kù)編程的首選模式。

分類：系統(tǒng)存儲(chǔ)過程、用戶存儲(chǔ)過程、擴(kuò)展存儲(chǔ)過程。創(chuàng)建存儲(chǔ)過程CREATEPROC<存儲(chǔ)過程名>

[@parameter

類型[=默認(rèn)值]

[OUTPUT]][,…n]AS

<存儲(chǔ)過程體>說明：

存儲(chǔ)過程參數(shù)：參數(shù)名-類型-模式@parameter：參數(shù)名；類型：除table之外的其他所有數(shù)據(jù)類型均可用作存儲(chǔ)過程的參數(shù)類型。參數(shù)模式：OUTPUT(或OUT)表示該參數(shù)為輸出參數(shù)；默認(rèn)為輸入?yún)?shù)。

<存儲(chǔ)過程體>：表示包含在過程中的一個(gè)或多個(gè)T-SQL語(yǔ)句。存儲(chǔ)過程參數(shù)例5.4一個(gè)無(wú)參數(shù)存儲(chǔ)過程例5.4創(chuàng)建一個(gè)簡(jiǎn)單的存儲(chǔ)過程，完成顯示所有學(xué)生的平均分?jǐn)?shù)，數(shù)據(jù)源見關(guān)系SC。CREATEPROCsp_sc_avgASSELECTsnum,AVG(score)as_avg

FROMsc

GROUPBYsnum一個(gè)存儲(chǔ)過程可看作一個(gè)SQL語(yǔ)句，它可在如下3種環(huán)境中使用：在服務(wù)器端SSMS查詢編輯窗口以EXECUTE為關(guān)鍵字調(diào)用?？稍诹硪淮鎯?chǔ)過程或函數(shù)中被調(diào)用。在宿主語(yǔ)言(如)中被當(dāng)作嵌入式SQL被調(diào)用。(具體，見第8章課件：chap08數(shù)據(jù)庫(kù)訪問技術(shù)-2)存儲(chǔ)過程的調(diào)用在服務(wù)器端SSMS中調(diào)用存儲(chǔ)過程，其語(yǔ)法：說明：@return_status可選的整型變量，存儲(chǔ)過程的返回狀態(tài)。;number是可選整數(shù)，用于對(duì)同名的過程分組。注意：后續(xù)版本的MicrosoftSQLServer將刪除該功能。@parameter

參數(shù)名，首字符必須為@。如果任何參數(shù)使用了@parameter=參數(shù)值格式，則后續(xù)的所有參數(shù)均必須使用該格式。@variable是用來存儲(chǔ)參數(shù)或返回參數(shù)的變量。OUTPUT

指定存儲(chǔ)過程返回一個(gè)參數(shù)。在服務(wù)器端調(diào)用EXEC

[@return_status=]存儲(chǔ)過程名[;number][[@parameter=]參數(shù)值

|@variable[OUTPUT

]|[默認(rèn)值]][,...n]在服務(wù)器端調(diào)用并執(zhí)行例5.4中創(chuàng)建的存儲(chǔ)過程。在SSMS的新建查詢窗口中輸入如下語(yǔ)句后,點(diǎn)擊工具欄中的執(zhí)行按鈕“!”。DECLARE

@return_valueINTEXEC

@return_value=sp_sc_avgSELECT

'returnvalue'=@return_value服務(wù)器端執(zhí)行例5.4結(jié)果集返回值執(zhí)行結(jié)果例5.5

創(chuàng)建一個(gè)存儲(chǔ)過程，實(shí)現(xiàn)如下功能：從University中查詢選修指定課號(hào)的學(xué)生成績(jī)，如果該課成績(jī)中存在90分及其以上的學(xué)生記錄，則將其姓名輸出顯示，并返回‘×××成績(jī)?yōu)閮?yōu)的記錄數(shù)為:××'消息；否則返回'×××成績(jī)?yōu)閮?yōu)的記錄數(shù)為零！'消息。例5.5

一個(gè)帶輸入?yún)?shù)的存儲(chǔ)過程CREATEPROC

sp_topstudent@_cnumchar(4)AS

Declare@_iint

;

SET@_i=(Selectcount(*)FROMscjoinsectionsonsc.secnum=sections.secnumWHEREcnum=@_cnumand

score>=90);

IF@_i<>0ELSE

例5.5(續(xù))BEGIN

RAISERROR('%s成績(jī)?yōu)閮?yōu)的記錄數(shù)為:%d',1,1,@_cnum,@_i);

SELECTsname

FROMstudent,sc,sections

WHEREstudent.snum=sc.snumand

sections.secnum=sc.secnumandcnum=@_cnumandscore>=90

ENDPRINT@_cnum+'成績(jī)?yōu)閮?yōu)的記錄數(shù)為零!

';服務(wù)器端執(zhí)行例5.5在服務(wù)器端調(diào)用并執(zhí)行例5.7中創(chuàng)建的存儲(chǔ)過程。在SSMS的新建查詢窗口中輸入如下語(yǔ)句后,點(diǎn)擊執(zhí)行按鈕“!”

DECLARE@cchar(4)SET@c='c120'EXECsp_topstudent@cEXECsp_topstudent@_cnum='c120'或參數(shù)名必須與存儲(chǔ)過程定義中的參數(shù)名完全相同CREATEPROCsp_sc_5Levels@_secnumchar(5)ASSELECTsnumAS學(xué)號(hào),

'五級(jí)制'

=

CASEscore/10WHEN6THEN

'及格'

WHEN7THEN

'中'WHEN8THEN

'良'WHEN9THEN'優(yōu)'WHEN10THEN'優(yōu)'

ELSE'不及格'ENDFROMscWHEREsecnum=@_secnum例5.6帶輸入?yún)?shù)的存儲(chǔ)過程二例5.6

創(chuàng)建一個(gè)存儲(chǔ)過程，將指定班號(hào)的學(xué)生成績(jī)

(百分制)按優(yōu)、良、中、及格和不及格五級(jí)制的形式輸出，數(shù)據(jù)源為關(guān)系sc。服務(wù)器端執(zhí)行例5.6在服務(wù)器端調(diào)用并執(zhí)行例5.6中創(chuàng)建的存儲(chǔ)過程。在SSMS的新建查詢窗口中輸入如下語(yǔ)句后,點(diǎn)擊執(zhí)行按鈕“!”DECLARE@_secnumchar(5)SET@_secnum='11601'print

'班號(hào)為'+@_secnum+'的五分制成績(jī)?nèi)缦拢?EXECsp_sc_5Levels@_secnum'例5.7一個(gè)帶輸入輸出參數(shù)的存儲(chǔ)過程例5.7創(chuàng)建帶輸出參數(shù)的存儲(chǔ)過程，求指定課號(hào)的成績(jī)方差，并保存在變量@_variance中，數(shù)據(jù)源為關(guān)系sc,sections。CREATEPROCsp_cnum_variance@_cnumchar(4),@_varianceREALOUTASDECLARE@_scoreint,@_iint,@_meanreal;SET@_i=0;SET@_variance=0;DECLAREscore_cursorCURSORFORSELECTscoreFROMscJOINsectionsONsc.secnum=sections.secnumWHEREcnum=@_cnum;OPENscore_cursor;FETCHNEXTFROMscore_cursorINTO@_score;WHILE(@@FETCH_STATUS=0)BEGINSET@_variance=@_variance+@_score*@_score;SET@_i=@_i+1;FETCHNEXTFROMscore_cursorINTO@_score;ENDSELECT@_mean=AVG(score)FROMscJOINsectionsONsc.secnum=sections.secnumwhereum=@_cnum;SET@_variance=@_variance/@_i-@_mean*@_meanCLOSEscore_cursorDEALLOCATEscore_cursor服務(wù)器端執(zhí)行例5.7在服務(wù)器端調(diào)用并執(zhí)行例5.9中創(chuàng)建的存儲(chǔ)過程。在SSMS的新建查詢窗口中輸入如下語(yǔ)句后,點(diǎn)擊執(zhí)行按鈕“!”DECLARE@_varianceREALEXECdbo.sp_cnum_variance'c120',@_varianceOUT;

SELECT

'cnum'='c120','variance'=@_variance存儲(chǔ)過程的返回狀態(tài)值

每個(gè)存儲(chǔ)過程的執(zhí)行，都將自動(dòng)返回一個(gè)整型狀態(tài)值，表明被調(diào)用存儲(chǔ)過程的執(zhí)行狀態(tài)。返回狀態(tài)值若為0，表示存儲(chǔ)過程執(zhí)行成功；-1~-99之間的數(shù)表示存儲(chǔ)過程執(zhí)行失敗。

用戶(即編程者)可在被調(diào)存儲(chǔ)過程中，自定義返回狀態(tài)值(取>0或<-99的整數(shù))，以表示不同的執(zhí)行狀態(tài)。然后在調(diào)用程序中，根據(jù)返回狀態(tài)值作相應(yīng)的處理。自定義返回狀態(tài)值例5.8

創(chuàng)建具有返回執(zhí)行狀態(tài)的存儲(chǔ)過程。檢查給定學(xué)號(hào)的學(xué)生有無(wú)不及格的記錄，若無(wú)則返回0，并輸出該生的成績(jī)記錄；若有則返回5；若沒有提供學(xué)號(hào)，則返回15。CREATEPROCsp_sc_score_1@_snumCHAR(4)=NULLAS

IF@_snum

ISNULL

RETURN15

ELSE

IFEXISTS(SELECT

*FROM

scWHEREsnum=@_snumAND

score<60)

RETURN5

ELSE

BEGIN

SELECT

*

FROMsc

WHEREsnum=@_snum

RETURN0ENDCREATEPROCsp_sc_score_2@_snumchar(4)=nullASDECLARE@return_statusINTEXEC@return_status=sp_sc_score_1@_snum

IF@return_status=15

PRINT

'缺少輸入?yún)?shù)！'IF@return_status=5PRINT'該生有不及格的記錄！'新建一存儲(chǔ)過程調(diào)用sp_sc_score_1服務(wù)器端執(zhí)行sp_sc_score_2服務(wù)器端執(zhí)行sp_sc_score_2,指定學(xué)號(hào)為s001：EXEC

sp_sc_score2

@_snum='s001'服務(wù)器端執(zhí)行sp_sc_score_2,不指定學(xué)號(hào)EXECsp_sc_score服務(wù)器端執(zhí)行sp_sc_score_2,指定學(xué)號(hào)為s005EXEC

@return_status=sp_sc_score's005'可以通過系統(tǒng)存儲(chǔ)過程sp_helptext，來查看用戶自定義的存儲(chǔ)過程。

sp_helptext存儲(chǔ)過程名

可以在對(duì)象資源管理器中，展開指定的數(shù)據(jù)庫(kù)節(jié)點(diǎn)，選擇【可編程性】→【存儲(chǔ)過程】，選擇目標(biāo)存儲(chǔ)過程，點(diǎn)擊鼠標(biāo)右鍵，從快捷菜單中選擇【修改】，便可在查詢?cè)O(shè)計(jì)器中查看。查看存儲(chǔ)過程ALTERPROC存儲(chǔ)過程名[參數(shù)列表]

AS

<存儲(chǔ)過程體>修改存儲(chǔ)過程DROPPROC

存儲(chǔ)過程名刪除存儲(chǔ)過程T-SQL提供三種函數(shù)行集函數(shù)Opendatasource(providername,init_string)

例如：聚合函數(shù)標(biāo)量函數(shù)：表5.9中列出的函數(shù)、數(shù)據(jù)轉(zhuǎn)換函數(shù)函數(shù)SELECT*FROMOPENDATASOURCE('SQLNCLI','DataSource=wang\ruizhisqlexpress;IntegratedSecurity=SSPI')

.UNIVERSITY.dbo.student1.

字串函數(shù)：23個(gè)

常用標(biāo)量函數(shù)-字符串函數(shù)函數(shù)語(yǔ)法功能描述舉例SUBSTRING(str1,start,length)返回字符串從指定的start位置開始截取長(zhǎng)度為length的子串。SUBSTRING(‘a(chǎn)bced’,2,3)返回’bce’LOWER(str1)將字符串中的所有字母變成小寫字母。LOWER(‘AcedBF’)返回

’acedbf’UPPER(str1)將字符串中的所有字母變成大寫字母。UPPER(‘AcedBF’)返回

’ACEDBF’LEN(str1)返回字符串的字符個(gè)數(shù)，其中不包含尾隨空格。LEN(‘howareyou’)返回11函數(shù)語(yǔ)法功能描述舉例DATEPART(datepart,date)返回指定日期的指定部分的整數(shù)DATEPART(day,’05132010’)返回13DAY(date)返回指定日期中天的整數(shù)DAY(‘05/13/2010’)返回13GETDATE()返回當(dāng)期系統(tǒng)日期和時(shí)間GETDATE()返回’0513201021:30PM’MONTH(date)返回指定日期中的月份MONTH(‘05/13/2010’)返回5YEAR(date)返回指定日期中的年份YEAR(‘05/13/2010’)返回2010DATEADD(datepart,number,date)在指定的日期上加一段時(shí)間/日期間隔，返回新的日期值DATEADD(Year,1,‘2014-1-1’)返回2015-01-01DATEDIFF(datepart,startdate,enddate)計(jì)算兩個(gè)日期/時(shí)間之間的間隔，返回整數(shù)Select

DATEDIFF(YEAR,birthday,GETDATE())as年齡FromStudent時(shí)間日期函數(shù)：7個(gè)常用標(biāo)量函數(shù)-日期時(shí)間函數(shù)數(shù)據(jù)類型轉(zhuǎn)換函數(shù)CONVERT(data_type[(length)],expression[,style])其中參數(shù)說明：expression:待轉(zhuǎn)換數(shù)據(jù)表達(dá)式.data_type:目標(biāo)數(shù)據(jù)類型.Length：指定目標(biāo)數(shù)據(jù)類型長(zhǎng)度，默認(rèn)值為30.style：表示將日期時(shí)間型數(shù)據(jù)轉(zhuǎn)換為字符數(shù)據(jù)的樣式。如，樣式為101，則返回“mm/dd/yyyy”；樣式為102，返回“yyyy.mm.dd”；樣式11，返回“yy/mm/dd”常用標(biāo)量函數(shù)-數(shù)據(jù)類型轉(zhuǎn)換函數(shù)CAST(expression

ASdata_type[(length)])（續(xù)上）例用CAST()函數(shù)將表student中birthday由日期時(shí)間類型轉(zhuǎn)換為字符串，要求只顯示日期信息。SELECTsnum,sname,CAST(birthdayAS

CHAR(10))asbirthdayfromstudent標(biāo)量函數(shù)

用戶自定義函數(shù)—標(biāo)量函數(shù)CREATEFUNCTION函數(shù)名(形式參數(shù)定義表)

RETURNS

返回值數(shù)據(jù)類型

[AS]BEGIN

函數(shù)體

RETURN返回值表達(dá)式

END已知SC(snum,secnum,score),Sections(secnum,cnum,pnum)創(chuàng)建一個(gè)標(biāo)量函數(shù)，返回某課程的平均分。

例5.9自定義標(biāo)量函數(shù)CREATEFUNCTIONget_cnum_avg(@_cnumchar(4))RETURNS

intASBEGIN

DECLARE@tempint

SELECT@temp=avg(score)

FROMSC,Sections

WHERESC.secnum=Sections.secnuma