Sql Server函數(shù)和存儲(chǔ)過(guò)程

SQL Server函數(shù)是1種封裝1條或多條SQL語(yǔ)句的結(jié)構(gòu)。
SQL Server函數(shù)分為系統(tǒng)函數(shù)和用戶(hù)自定義函數(shù)兩種。

標(biāo)量值函數(shù)：標(biāo)量值函數(shù)的返回值是基本數(shù)據(jù)類(lèi)型的單個(gè)值或單個(gè)值得表達(dá)式。
函數(shù)體既可以是1條語(yǔ)句，也能夠是多條語(yǔ)句。

創(chuàng)建標(biāo)量值函數(shù)
語(yǔ)法：
CREATE FUNCTION [schema_name.]function_name([{@parameter_name parameter_data_type[=default_value]}[...n]])RETURNS return_data_type AS
BEGIN
Function_body
RETURN scalar_expression
END
語(yǔ)法說(shuō)明：
1、[]中的內(nèi)容都是可選的。
2、schema_name指定函數(shù)的架構(gòu)名。
3、@parameter_name指定函數(shù)的參數(shù)名。
4、parameter_data_type指定參數(shù)的數(shù)據(jù)類(lèi)型。
5、default_value指定參數(shù)的默許值。
6、RETURNS關(guān)鍵字指定函數(shù)的返回類(lèi)型。
7、function_body指定函數(shù)體。
8、RETURN語(yǔ)句指定函數(shù)返回值或表達(dá)式。
示例以下：
USE Bank
GO
CREATE FUNCTION getAccountName
(
@account_id int ---參數(shù)

)
RETURNS varchar(20)--返回varchar(20)
AS
BEGIN
DECLARE @accountName varchar(20)
select @accountName=account_name from Account where account_id=@account_id
RETURN @accountName--返回值
END
GO
使用標(biāo)量值函數(shù)
在使用標(biāo)量值函數(shù)時(shí)，先傳入函數(shù)要求的參數(shù)，然后通過(guò)SELECT語(yǔ)句將標(biāo)量值函數(shù)的返回
值賦給變量，也能夠直接輸出
--直接輸出標(biāo)量
select dbo.getAccountName(1) as 賬戶(hù)名稱(chēng)
* 注意：在調(diào)用函數(shù)的進(jìn)程中，必須在函數(shù)名前添加“dbo.”。否則，沒(méi)法辨認(rèn)該函數(shù)是內(nèi)部函數(shù)，還是自定義函數(shù)。
--將標(biāo)量值函數(shù)的返回值存入變量
DECLARE @accountName varchar(20)
select @accountName=getAccountName(1)
print '賬戶(hù)名稱(chēng)為：'+@accountName
說(shuō)明：變量值函數(shù)可以被另外的標(biāo)量值函數(shù)或表值函數(shù)調(diào)用。

表值函數(shù)：表值函數(shù)的返回結(jié)果為數(shù)據(jù)表。表值函數(shù)功能強(qiáng)大，乃至在大多數(shù)情況下可以替換視圖。視圖沒(méi)法定義參數(shù)，而表值函數(shù)卻
可以作為帶參數(shù)的視圖使用。表值函數(shù)可以分為多語(yǔ)句表值函數(shù)和內(nèi)聯(lián)表值函數(shù)。
1、多語(yǔ)句表值函數(shù)：
多語(yǔ)句表值函數(shù)要求返回類(lèi)型為T(mén)ABLE類(lèi)型，其與標(biāo)量值函數(shù)的區(qū)分是需要在函數(shù)定義的時(shí)候，在RETURNS關(guān)鍵字后面指定返回的表的結(jié)構(gòu)。
語(yǔ)法：
CREATE FUNCTION [schema_name.]function_name ([{@parameter_name parameter_data_type [=default_value]}[,...n]])
RETURNS @table_var_name TABLE(table_definition) AS
BEGIN
Function_body
RETURN
END
多語(yǔ)句表值函數(shù)的定義語(yǔ)法與標(biāo)量值函數(shù)的定義語(yǔ)法相比有兩點(diǎn)區(qū)分：1是函數(shù)聲明中的RETURNS后面
是TABLE類(lèi)型且必須指定結(jié)構(gòu);2是函數(shù)體中的RETURN后面無(wú)需在寫(xiě)返回的值或表達(dá)式。
示例以下：
USE Bank
GO
--函數(shù)履行終了后返回TABLE類(lèi)型的變量@deposeitTable
create function getDeposit()
returns @depositTable table
(
accountName varchar(20),
balance float
)
AS
BEGIN
--為T(mén)ABLE類(lèi)型的變量賦值
insert into @depositTable
select account_name,balance from account,all_purpose_card where account.ACCOUNT_ID=all_purpost_card.ACCOUNT_ID
return --無(wú)需在寫(xiě)值或表達(dá)式，直接返回變量@depositTable
END
GO
調(diào)用表值函數(shù)時(shí)，可以將其作為普通表使用
示例以下：
--使用表值函數(shù)getDeposit
select * from getDeposit()

使用帶參數(shù)的表值函數(shù)：
USE Bank
GO
--參數(shù)要求傳入賬戶(hù)名稱(chēng)
create function getDeposit(@accountName varchar(20))
returns @depositTable table
(
accountName varchar(20),
balance float
)
AS
BEGIN
--在子查詢(xún)中使用參數(shù)@accountName
insert into @depositTable
select account_name,balance from account,all_purpose_card where account.ACCOUNT_ID=all_purpost_card.ACCOUNT_ID and account_name=@accountName
return
END
GO
調(diào)用多語(yǔ)句表值函數(shù)：
select * from getDeposit(‘魯迅’)

內(nèi)聯(lián)表值函數(shù)：
內(nèi)聯(lián)表值函數(shù)是多語(yǔ)句表值函數(shù)的1種特殊情勢(shì)，與多語(yǔ)句表值函數(shù)的創(chuàng)建方式基本相同。2者
的區(qū)分在于內(nèi)聯(lián)表值函數(shù)只能有1條SELECT語(yǔ)句，且不必定義返回TABLE類(lèi)型的變量結(jié)構(gòu)，可以
在RETURN關(guān)鍵字后面直接返回SELECT語(yǔ)句的結(jié)果。
語(yǔ)法：
CREATE FUNCTION [schema_name.]function_name ([{@paramet_name parameter_data_type [=default_value]}[,...n]])
RETURNS TABLE AS
Function_body
RETURN [select_stmt]
RETURNS關(guān)鍵字后面不需要定義TABLE 類(lèi)型的變量，也無(wú)需表結(jié)構(gòu)的定義。在RETURN語(yǔ)句后面
直接使用SELECT語(yǔ)句查詢(xún)數(shù)據(jù)行，并返回結(jié)果。
創(chuàng)建1個(gè)內(nèi)聯(lián)表值函數(shù)getAccount,并接受1個(gè)參數(shù)，根據(jù)傳入的參數(shù)返回賬號(hào)信息，示例以下：
USE Bank
GO
create function getAccount(@account_id int)
returns table
AS
return
(
select * from Account where account_id=@account_id
)
調(diào)用內(nèi)聯(lián)表值函數(shù)：
select * from getAccount(5)

存儲(chǔ)進(jìn)程的優(yōu)點(diǎn)：
1、允許模塊化程序設(shè)計(jì)
2、履行速度更快
3、減少網(wǎng)絡(luò)流量
4、可以作為安全機(jī)制使用
                                          經(jīng)常使用的系統(tǒng)存儲(chǔ)進(jìn)程
           
   系統(tǒng)存儲(chǔ)進(jìn)程名稱(chēng)                             說(shuō)明
   sp_databases                                 列出http://www.vxbq.cn/server/上所有的http://www.vxbq.cn/db/
   sp_hepdb                                     報(bào)告有關(guān)指定http://www.vxbq.cn/db/或所有http://www.vxbq.cn/db/的信息
   sp_renamedb                                  重命名http://www.vxbq.cn/db/
   sp_tables                                    返回當(dāng)前環(huán)境下任何能夠在FROM子句中出現(xiàn)的對(duì)象
   sp_columns                                   查看某個(gè)表的列表信息
   sp_help                                      查看某個(gè)表的所有信息
   sp_helpcoonstraint                           查看某個(gè)表的束縛
   sp_helpindex                                 查看某個(gè)表的索引
   sp_stored_procedure                          列出當(dāng)前環(huán)境中所有的存儲(chǔ)進(jìn)程
   sp_password                                  添加或修改登錄賬戶(hù)的密碼     
   sp_helptext                                  顯示默許值、未加密的存儲(chǔ)進(jìn)程、用戶(hù)定義的存儲(chǔ)進(jìn)程、觸發(fā)器或視圖的實(shí)際文本
   sp_addrole                                   向當(dāng)前http://www.vxbq.cn/db/中創(chuàng)建新的數(shù)據(jù)角色
   sp_adduser                                   向當(dāng)前數(shù)據(jù)添加新的用戶(hù)
   sp_cmdshell                                  使用DOS命令操作文件和目錄
   sp_logevent                                  將用戶(hù)定義的消息記入SQL Server日志文件盒Windows事件查看器

用戶(hù)自定義的存儲(chǔ)進(jìn)程
語(yǔ)法：
CREATE PROC[EDURE] proc_name
[{@parameter_name data_type}=[默許值]] [OUTPUT,..,n]
AS
procedure_body
語(yǔ)法說(shuō)明：PROCEDURE可以省略為PROC,proc_name指存儲(chǔ)進(jìn)程名稱(chēng)，存儲(chǔ)進(jìn)程名稱(chēng)后面是
參數(shù)列表，為可選項(xiàng)。如果有參數(shù)，則需要指定參數(shù)的數(shù)據(jù)類(lèi)型；如果有默許值，則需要
指定參數(shù)的默許值。也能夠?yàn)閰?shù)指定OUTPUT關(guān)鍵字，表示參數(shù)為傳出參數(shù)。AS關(guān)鍵字
后面的procedure_body表示存儲(chǔ)進(jìn)程的主體是存儲(chǔ)進(jìn)程的核心。
1、創(chuàng)建不帶參數(shù)的存儲(chǔ)進(jìn)程
USE Bank
GO
if exists(select * from sysobjects where name='proc_min_balance')
drop procedure proc_min_balance
GO
create proc_min_balance
AS
select ACCOUNT_NAME from ACCOUNT where ACCOUNT_ID=
(
select top 1 ACCOUNT_ID from ALL_PURPOSE_CARD order by BALANCE

)
GO
調(diào)用存儲(chǔ)進(jìn)程通過(guò)EXEC或EXECUTE命令履行
USE Bank
GO
exec proc_min_balance
2、帶輸入?yún)?shù)的存儲(chǔ)進(jìn)程
在其他語(yǔ)言中，如果方法有參數(shù)，則在調(diào)用時(shí)需要傳遞實(shí)際參數(shù)值。
--創(chuàng)建帶參數(shù)的存儲(chǔ)進(jìn)程，根據(jù)傳入的數(shù)據(jù)添加賬戶(hù)信息
USE Bank
GO
if exists(select * from sysobjects where name='proc_Account_Insert')
drop procedure proc_Account_Insert
GO
create proc proc_Account_Insert
@Account_Name varchar(20),
@code varchar(18),
@open_time datetime
AS
insert into ACCOUNT values(@Account_Name,@code,@open_time)
if(@@ERROR=0)
print 'ok'
else
print 'error' 
GO
履行帶參數(shù)的存儲(chǔ)進(jìn)程，需要將實(shí)際參數(shù)值傳入存儲(chǔ)進(jìn)程中
exec proc_Account_Insert '諸葛亮','546646265656651','2011-01-02'
   
   
帶輸出參數(shù)的存儲(chǔ)進(jìn)程
如果需要存儲(chǔ)進(jìn)程返回1個(gè)值或多個(gè)值，可使用輸出參數(shù)。輸出參數(shù)必須在存儲(chǔ)進(jìn)程定義
時(shí)使用OUTPUT關(guān)鍵字進(jìn)行聲明
提示：存儲(chǔ)進(jìn)程也能夠通過(guò)RETURN返回值，但通常只會(huì)返回1些履行狀態(tài)值。
示例以下：
USE Bank
GO
if exists(select * from sysobjects where name='proc_getDay')
drop procedure proc_getDay
GO
create proc proc_getDay
@day int output,--輸出參數(shù)，返回天數(shù)
@date datetime
AS
select @day=datediff(day,@date,CETDATE())
GO
在調(diào)用帶傳出函數(shù)的存儲(chǔ)進(jìn)程是，需要首先定義對(duì)應(yīng)的變量作為實(shí)際參數(shù)，并且必須
在實(shí)際參數(shù)后使用OUTPUT關(guān)鍵字。履行存儲(chǔ)進(jìn)程成功后，就能夠通過(guò)變量得到存儲(chǔ)
進(jìn)程傳出的參數(shù)值。
--先定義變量，與傳出參數(shù)類(lèi)型保持1致
declare @day int
--履行存儲(chǔ)進(jìn)程時(shí)，將變量作為實(shí)際參數(shù)，并使用OUTPUT關(guān)鍵字進(jìn)行說(shuō)明
exec proc_getDay @day output, '2012-01-03'
--履行終了后，通過(guò)變量得到存儲(chǔ)進(jìn)程傳出的值
print '距當(dāng)前天數(shù)：'+cast（@day as varchar(4)）
GO

生活不易，碼農(nóng)辛苦
如果您覺(jué)得本網(wǎng)站對(duì)您的學(xué)習(xí)有所幫助,可以手機(jī)掃描二維碼進(jìn)行捐贈(zèng)