子項(xiàng)目7存儲(chǔ)過程和函數(shù)的使用

7.1Transact-SQL編程知識(shí)

Transact-SQL直接來源于SQL，是SQLServer2005在SQL的基礎(chǔ)上添加了流程控制語句的擴(kuò)展。Transact-SQL是SQLServer2005功能的核心。不管應(yīng)用程序的用戶界面是什么形式，只要與數(shù)據(jù)庫(kù)服務(wù)器交互使用，最終都必然表現(xiàn)為Transact-SQL。7.1.1批處理 批處理是將一組Transact-SQL語句作為一個(gè)整體進(jìn)行編譯和執(zhí)行。一組語句可以是一條語句，也可以是多條語句，最終以GO作為結(jié)束標(biāo)志。當(dāng)編譯器讀到GO時(shí)，就會(huì)把它之前，直到上一個(gè)GO之間的語句當(dāng)做一組批處理語句，發(fā)給服務(wù)器執(zhí)行。GO是一個(gè)標(biāo)志性語句，本身本不被執(zhí)行。7.1.2程序注釋語句（1）單行注釋（--）。 語法格式為：

--注釋文本，例如上面的代碼“--查詢經(jīng)費(fèi)本信息”。 （2）塊注釋。注釋符號(hào)與C語言中的注釋相同，即“/*…*/”，其中，“/*”為注釋的開頭，“*/”為注釋的結(jié)尾。7.1.3局部變量與全局變量1.局部變量局部變量是用戶自定義的變量，它的作用范圍僅在程序內(nèi)部。局部變量的名稱是用戶自定義的，局部變量的命名必須符合SQLServer標(biāo)識(shí)符命名規(guī)則。 局部變量的聲明必須使用DECLARE語句，具體的語法格式如下：

DECLARE{@varaible_name,datatype[,…n]}2.全局變量全局變量是SQLServer2005系統(tǒng)內(nèi)部事先定義好的變量，不需要用戶參與定義，對(duì)用戶而言是只讀的。其作用的范圍并不局限于某一程序，而是程序隨時(shí)可以調(diào)用。全局變量通常用于存儲(chǔ)一些SQLServer2005的配置設(shè)定值和效能統(tǒng)計(jì)數(shù)據(jù)。

7.1.4流程控制語句

1.IF…ELSE語句2.WHILE語句7.2存儲(chǔ)過程

存儲(chǔ)過程是一組為了完成特定功能的Transact-SQL語句集，經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫(kù)中。存儲(chǔ)過程是數(shù)據(jù)庫(kù)的一個(gè)重要對(duì)象，任何一個(gè)設(shè)計(jì)良好的為數(shù)據(jù)庫(kù)應(yīng)用程序都應(yīng)該用到存儲(chǔ)過程?？梢允褂肨ransact-SQLEXECUTE語句來運(yùn)行存儲(chǔ)過程。用戶通過指定存儲(chǔ)過程的名字并給出參數(shù)（如果該存儲(chǔ)過程帶有參數(shù)）來執(zhí)行它。7.2.2創(chuàng)建和執(zhí)行簡(jiǎn)單的存儲(chǔ)過程

創(chuàng)建存儲(chǔ)過程的基本語法如下：CREATEPROCEDURE存儲(chǔ)過程名[WITHENCRYPTION][WITHRECOMPILE]ASSQL語句其中，WITHENCRYPTION為對(duì)存儲(chǔ)過程加密；WITHRECOMPILE為對(duì)存儲(chǔ)過程重新編譯。7.2.3創(chuàng)建復(fù)雜的存儲(chǔ)過程

聲明帶有參數(shù)的存儲(chǔ)過程的語法格式如下：

CREATEPROCEDURE存儲(chǔ)過程名@參數(shù)名數(shù)據(jù)類型[=默認(rèn)值][,...n][WITHENCRYPTION][WITHRECOMPILE]ASSQL語句 其中，“@參數(shù)名”是局部變量，前面一定要加“@”符號(hào)，在聲明局部變量一樣，后面一定要有數(shù)據(jù)類型。同時(shí)，多個(gè)參數(shù)之間用逗號(hào)隔開。7.2.4存儲(chǔ)過程的管理和維護(hù)（1）查看存儲(chǔ)過程 查看存儲(chǔ)過程的定義有兩種方法，一種是使用Transact-SQL查看存儲(chǔ)過程的定義，另一種是使用SQLServerManagementStudio查看存儲(chǔ)過程的定義。 （2）修改存儲(chǔ)過程修改已創(chuàng)建的存儲(chǔ)過程已有圖形界面方式和使用Transact_SQL語句，在7.2.2節(jié)中，創(chuàng)建和執(zhí)行簡(jiǎn)單的存儲(chǔ)過程里，已經(jīng)提到如何使用圖形界面工具修改存儲(chǔ)過程。在這里，主要說明如何使用Transact_SQL語句修改存儲(chǔ)過程。（3）刪除存儲(chǔ)過程 當(dāng)不再需要某個(gè)存儲(chǔ)過程時(shí)，可將其刪除。刪除存儲(chǔ)過程已有兩種方法：一種是使用Transact-SQL語句刪除存儲(chǔ)過程，另一種是使用SQLServerManagementStudio刪除存儲(chǔ)過程。7.3用戶自定義函數(shù)

函數(shù)是具有內(nèi)部運(yùn)算功能的語法元素，在數(shù)據(jù)查詢和程序設(shè)計(jì)過程中，常常需要調(diào)用函數(shù)實(shí)現(xiàn)一些復(fù)雜功能。在SQLServer2005里內(nèi)置了許多函數(shù)，用以簡(jiǎn)化大量的復(fù)雜的數(shù)據(jù)和修改操作，巧妙地利用系統(tǒng)函數(shù)或自定義函數(shù)，可以實(shí)現(xiàn)復(fù)雜的數(shù)據(jù)查詢功能，簡(jiǎn)化程序設(shè)計(jì)復(fù)雜性，減少代碼長(zhǎng)度，提高數(shù)據(jù)查詢的效率。7.3.1字符串函數(shù)

例7.1在用戶表“tbuserinfo”中查詢用戶編號(hào)為6的用戶名字的長(zhǎng)度。 在查詢分析器中運(yùn)行如下命令：

SELECTLEN(username) FROMtbuserinfo WHEREuserid=6 GO 例7.2當(dāng)用戶登錄財(cái)務(wù)管理系統(tǒng)時(shí)，如圖7-27所示，需要驗(yàn)證用戶的用戶名和密碼，驗(yàn)證成功后才允許登錄，編寫存儲(chǔ)過程實(shí)現(xiàn)該功能。USE[FM]GOCREATEPROCEDURE[dbo].[pc_Login1]( @usernameVARCHAR(1024), @userpwd VARCHAR(1024))AS SET@username=ltrim(@username)--清除左邊的空格

SET@username=rtrim(@username) --清除右邊的空格

SET@userpwd=ltrim(@userpwd) --清除左邊的空格

SET@userpwd=rtrim(@userpwd) --清除右邊的空格BEGINSELECT*FROMtbuserinfoWHEREusername=@usernameanduserpwd=@userpwdanduserflag='1'END7.3.2日期和時(shí)間函數(shù)日期和時(shí)間函數(shù)主要用來顯示有關(guān)日期和時(shí)間的信息。日期和時(shí)間函數(shù)執(zhí)行算術(shù)運(yùn)算與其它函數(shù)一樣，也可以在SQL語句的SELECT和WHERE子句以及表達(dá)式中使用。 例7.3實(shí)驗(yàn)室財(cái)務(wù)管理系統(tǒng)的新增財(cái)務(wù)處事件模塊如圖7-29所示。將新增的各項(xiàng)和表“tbfevent”中的部分字段相對(duì)應(yīng)，編寫存儲(chǔ)過程，實(shí)現(xiàn)新增的功能。CREATEPROCEDURE[dbo].[pc_fevent1]( @f_id VARCHAR(1024), @userid VARCHAR(1024), @fe_abstract VARCHAR(1024), @fe_income float, @fe_output float, @fe_memo VARCHAR(1024),

@fe_flag varchar(1) )AS declare@table_nameVARCHAR(20),@PKcolumn_nameVARCHAR(20),@idvarchar(20),@fe_datedatetime set@table_name='tbfevent' set@PKcolumn_name='fe_id' execpc_GetID@table_name,@PKcolumn_name,@max_id=@idoutput --pc_GetID的定義在7.2.3 set@fe_id=@id set@fe_date=getdate() begin insertintotbfevent(f_id,userid,fe_id,fe_abstract,fe_income,fe_output,fe_date,fe_memo,fe_flag)

values(@f_id,@userid,@fe_id,@fe_abstract,@fe_income,@fe_output,@fe_date,@fe_memo,@fe_flag) end7.3.3數(shù)據(jù)類型轉(zhuǎn)換函數(shù)

一般情況下，SQLServer2005會(huì)自動(dòng)完成數(shù)據(jù)類型轉(zhuǎn)換，這種轉(zhuǎn)換叫做隱式轉(zhuǎn)換。但是，有些類型就不能直接轉(zhuǎn)換，比如INT和CHAR類型，則需要顯式轉(zhuǎn)換。顯式轉(zhuǎn)換函數(shù)共有兩個(gè)，分別為CAST函數(shù)和CONVERT函數(shù)7.3.4用戶自定義函數(shù)

1.標(biāo)量函數(shù) 創(chuàng)建標(biāo)量函數(shù)的語法格式如下：CREATEFUNCTION[所有者名稱.]函數(shù)名稱

[（{@參數(shù)名稱[AS]標(biāo)量數(shù)據(jù)類型=[默認(rèn)值]}[…n]）]RETURNS標(biāo)量數(shù)據(jù)類型

[AS]BEGIN

函數(shù)體

RETURN標(biāo)量表達(dá)式

END2.內(nèi)嵌表值函數(shù)內(nèi)嵌表值函數(shù)沒有由BEGIN…END語句括起來的函數(shù)體，其返回的表從一個(gè)位于RETURN子句中的SELECT命令段從數(shù)據(jù)庫(kù)篩選出來。內(nèi)嵌表值函數(shù)功能相當(dāng)于一個(gè)參數(shù)化的視圖。 創(chuàng)建內(nèi)嵌表值函數(shù)的語法格式如下：CREATEFUNCTION[所有者名稱.]函數(shù)名稱

[（{@參數(shù)名稱[AS]標(biāo)量數(shù)據(jù)類型=[默認(rèn)值]}[…n]）]

RETURNSTABLE

[AS]

RETURN[(SELECT語句)]3.多語句表值函數(shù) 多語句表值函數(shù)可以看作標(biāo)量函數(shù)和內(nèi)嵌表值函數(shù)的結(jié)合體。它的返回值是一個(gè)表，但它與標(biāo)量函數(shù)一樣有一個(gè)用BEGIN…END語句括起來的函數(shù)體，返回值的表中的數(shù)據(jù)是由函數(shù)體中的語句插入的。由此可見，它可以進(jìn)行多次查詢，對(duì)數(shù)據(jù)進(jìn)行多次篩選、合并、彌補(bǔ)了內(nèi)嵌表值函數(shù)的不足。 創(chuàng)建多語句表值函數(shù)的語法格式如下：CREATEFUNCTION[所有者名稱.]函數(shù)名稱

[（{@參數(shù)名稱[AS]標(biāo)量數(shù)據(jù)類型=[默認(rèn)值]}[…n]）]RETURNS@表名變量TABLE表的定義

[AS]BEGIN

函數(shù)體