Sql Server函數和預存程序

標籤：資料庫   儲存   伺服器   結構   設計   

SQL Server函數是一種封裝一條或多條SQL語句的結構。
SQL Server函數分為系統函數和使用者自訂函數兩種。

標量值函數：標量值函數的返回值是基礎資料型別 (Elementary Data Type)的單個值或單個值得運算式。
函數體既可以是一條語句，也可以是多條語句。

建立標量值函數
文法：
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 [email protected]_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
多語句表值函數的定義文法與標量值函數的定義文法相比有兩點區別：一是函式宣告中的RETURNS後面
是TABLE類型且必須指定結構;二是函數體中的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[email protected]
return
END
GO
調用多語句表值函數：
select * from getDeposit(‘魯迅’)

內聯表值函數：
內聯表值函數是多語句表值函數的一種特殊形式，與多語句表值函數的建立方式基本相同。二者
的區別在於內聯表值函數只能有一條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語句查詢資料行，並返回結果。
建立一個內聯表值函數getAccount,並接受一個參數，依據傳入的參數返回帳號資訊，樣本如下：
USE Bank
GO
create function getAccount(@account_id int)
returns table
AS
return
(
select * from Account where [email protected]_id
)
調用內聯表值函數：
select * from getAccount(5)

預存程序的優點：
1、允許模組化程式設計
2、執行速度更快
3、減少網路流量
4、可以作為安全機制使用
                                          常用的系統預存程序
           
   系統預存程序名稱                             說明
   sp_databases                                 列出伺服器上所有的資料庫
   sp_hepdb                                     報告有關指定資料庫或所有資料庫的資訊
   sp_renamedb                                  重新命名資料庫
   sp_tables                                    返回當前環境下任何能夠在FROM子句中出現的對象
   sp_columns                                   查看某個表的列表資訊
   sp_help                                      查看某個表的所有資訊
   sp_helpcoonstraint                           查看某個表的約束
   sp_helpindex                                 查看某個表的索引
   sp_stored_procedure                          列出當前環境中所有的預存程序
   sp_password                                  添加或修改登入賬戶的密碼     
   sp_helptext                                  顯示預設值、未加密的預存程序、使用者定義的預存程序、觸發器或者視圖的實際文本
   sp_addrole                                   向當前資料庫中建立新的資料角色
   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‘
   
   
帶輸出參數的預存程序
如果需要預存程序返回一個值或多個值，可以使用輸出參數。輸出參數必須在預存程序定義
時使用OUTPUT關鍵字進行聲明
提示：預存程序也可以通過RETURN返回值，但通常只會返回一些執行狀態值。
樣本如下：
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關鍵字。執行預存程序成功後，就可以通過變數得到儲存
過程傳出的參數值。
--先定義變數，與傳出參數類型保持一致
declare @day int
--執行預存程序時，將變數作為實際參數，並使用OUTPUT關鍵字進行說明
exec proc_getDay @day output, ‘2012-01-03‘
--執行完畢後，通過變數得到預存程序傳出的值
print ‘距當前天數：‘+cast（@day as varchar(4)）
GO

Sql Server函數和預存程序