Sql Server函數和存儲過程

SQL Server函數是1種封裝1條或多條SQL語句的結構。
SQL Server函數分為系統函數和用戶自定義函數兩種。

標量值函數：標量值函數的返回值是基本數據類型的單個值或單個值得表達式。
函數體既可以是1條語句，也能夠是多條語句。

創建標量值函數
語法：
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
語法說明：
1、[]中的內容都是可選的。
2、schema_name指定函數的架構名。
3、@parameter_name指定函數的參數名。
4、parameter_data_type指定參數的數據類型。
5、default_value指定參數的默許值。
6、RETURNS關鍵字指定函數的返回類型。
7、function_body指定函數體。
8、RETURN語句指定函數返回值或表達式。
示例以下：
USE Bank
GO
CREATE FUNCTION getAccountName
(
@account_id int ---參數

)
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
使用標量值函數
在使用標量值函數時，先傳入函數要求的參數，然后通過SELECT語句將標量值函數的返回
值賦給變量，也能夠直接輸出
--直接輸出標量
select dbo.getAccountName(1) as 賬戶名稱
* 注意：在調用函數的進程中，必須在函數名前添加“dbo.”。否則，沒法辨認該函數是內部函數，還是自定義函數。
--將標量值函數的返回值存入變量
DECLARE @accountName varchar(20)
select @accountName=getAccountName(1)
print '賬戶名稱為：'+@accountName
說明：變量值函數可以被另外的標量值函數或表值函數調用。

表值函數：表值函數的返回結果為數據表。表值函數功能強大，乃至在大多數情況下可以替換視圖。視圖沒法定義參數，而表值函數卻
可以作為帶參數的視圖使用。表值函數可以分為多語句表值函數和內聯表值函數。
1、多語句表值函數：
多語句表值函數要求返回類型為TABLE類型，其與標量值函數的區分是需要在函數定義的時候，在RETURNS關鍵字后面指定返回的表的結構。
語法：
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
多語句表值函數的定義語法與標量值函數的定義語法相比有兩點區分：1是函數聲明中的RETURNS后面
是TABLE類型且必須指定結構;2是函數體中的RETURN后面無需在寫返回的值或表達式。
示例以下：
USE Bank
GO
--函數履行終了后返回TABLE類型的變量@deposeitTable
create function getDeposit()
returns @depositTable table
(
accountName varchar(20),
balance float
)
AS
BEGIN
--為TABLE類型的變量賦值
insert into @depositTable
select account_name,balance from account,all_purpose_card where account.ACCOUNT_ID=all_purpost_card.ACCOUNT_ID
return --無需在寫值或表達式，直接返回變量@depositTable
END
GO
調用表值函數時，可以將其作為普通表使用
示例以下：
--使用表值函數getDeposit
select * from getDeposit()

使用帶參數的表值函數：
USE Bank
GO
--參數要求傳入賬戶名稱
create function getDeposit(@accountName varchar(20))
returns @depositTable table
(
accountName varchar(20),
balance float
)
AS
BEGIN
--在子查詢中使用參數@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
調用多語句表值函數：
select * from getDeposit(‘魯迅’)

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

存儲進程的優點：
1、允許模塊化程序設計
2、履行速度更快
3、減少網絡流量
4、可以作為安全機制使用
                                          經常使用的系統存儲進程
           
   系統存儲進程名稱                             說明
   sp_databases                                 列出http://www.jyygyx.com/server/上所有的http://www.jyygyx.com/db/
   sp_hepdb                                     報告有關指定http://www.jyygyx.com/db/或所有http://www.jyygyx.com/db/的信息
   sp_renamedb                                  重命名http://www.jyygyx.com/db/
   sp_tables                                    返回當前環境下任何能夠在FROM子句中出現的對象
   sp_columns                                   查看某個表的列表信息
   sp_help                                      查看某個表的所有信息
   sp_helpcoonstraint                           查看某個表的束縛
   sp_helpindex                                 查看某個表的索引
   sp_stored_procedure                          列出當前環境中所有的存儲進程
   sp_password                                  添加或修改登錄賬戶的密碼     
   sp_helptext                                  顯示默許值、未加密的存儲進程、用戶定義的存儲進程、觸發器或視圖的實際文本
   sp_addrole                                   向當前http://www.jyygyx.com/db/中創建新的數據角色
   sp_adduser                                   向當前數據添加新的用戶
   sp_cmdshell                                  使用DOS命令操作文件和目錄
   sp_logevent                                  將用戶定義的消息記入SQL Server日志文件盒Windows事件查看器

用戶自定義的存儲進程
語法：
CREATE PROC[EDURE] proc_name
[{@parameter_name data_type}=[默許值]] [OUTPUT,..,n]
AS
procedure_body
語法說明：PROCEDURE可以省略為PROC,proc_name指存儲進程名稱，存儲進程名稱后面是
參數列表，為可選項。如果有參數，則需要指定參數的數據類型；如果有默許值，則需要
指定參數的默許值。也能夠為參數指定OUTPUT關鍵字，表示參數為傳出參數。AS關鍵字
后面的procedure_body表示存儲進程的主體是存儲進程的核心。
1、創建不帶參數的存儲進程
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
調用存儲進程通過EXEC或EXECUTE命令履行
USE Bank
GO
exec proc_min_balance
2、帶輸入參數的存儲進程
在其他語言中，如果方法有參數，則在調用時需要傳遞實際參數值。
--創建帶參數的存儲進程，根據傳入的數據添加賬戶信息
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
履行帶參數的存儲進程，需要將實際參數值傳入存儲進程中
exec proc_Account_Insert '諸葛亮','546646265656651','2011-01-02'
   
   
帶輸出參數的存儲進程
如果需要存儲進程返回1個值或多個值，可使用輸出參數。輸出參數必須在存儲進程定義
時使用OUTPUT關鍵字進行聲明
提示：存儲進程也能夠通過RETURN返回值，但通常只會返回1些履行狀態值。
示例以下：
USE Bank
GO
if exists(select * from sysobjects where name='proc_getDay')
drop procedure proc_getDay
GO
create proc proc_getDay
@day int output,--輸出參數，返回天數
@date datetime
AS
select @day=datediff(day,@date,CETDATE())
GO
在調用帶傳出函數的存儲進程是，需要首先定義對應的變量作為實際參數，并且必須
在實際參數后使用OUTPUT關鍵字。履行存儲進程成功后，就能夠通過變量得到存儲
進程傳出的參數值。
--先定義變量，與傳出參數類型保持1致
declare @day int
--履行存儲進程時，將變量作為實際參數，并使用OUTPUT關鍵字進行說明
exec proc_getDay @day output, '2012-01-03'
--履行終了后，通過變量得到存儲進程傳出的值
print '距當前天數：'+cast（@day as varchar(4)）
GO

生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈