sqlserver 函數(shù)、存儲過程、游標與事務模板

更新時間：2010年08月07日 21:37:29 作者：

SQL 函數(shù)、存儲過程、游標與事務模板，學習sqlserver的朋友很多情況下都需要用得到。

1.標量函數(shù)：結果為一個單一的值，可包含邏輯處理過程。其中不能用getdate()之類的不確定性系統(tǒng)函數(shù).

 
--標量值函數(shù) 
-- ================================================ 
-- Template generated from Template Explorer using: 
-- Create Scalar Function (New Menu).SQL 
-- 
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below. 
-- 
-- This block of comments will not be included in 
-- the definition of the function. 
-- ================================================ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
-- ============================================= 
-- Author: <Author,,Name> 
-- Create date: <Create Date, ,> 
-- Description: <Description, ,> 
-- ============================================= 
CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName> 
( 
-- Add the parameters for the function here 
<@Param1, sysname, @p1> <Data_Type_For_Param1, , int> 
) 
RETURNS <Function_Data_Type, ,int> 
AS 
BEGIN 
-- Declare the return variable here 
DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int> 

-- Add the T-SQL statements to compute the return value here 
SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1> 

-- Return the result of the function 
RETURN <@ResultVar, sysname, @Result> 

END 

2.內(nèi)聯(lián)表值函數(shù):返回值為一張表,僅通過一條SQL語句實現(xiàn),沒有邏輯處理能力.可執(zhí)行大數(shù)據(jù)量的查詢.

復制代碼代碼如下:

 
--內(nèi)聯(lián)表值函數(shù) 

-- ================================================ 
-- Template generated from Template Explorer using: 
-- Create Inline Function (New Menu).SQL 
-- 
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below. 
-- 
-- This block of comments will not be included in 
-- the definition of the function. 
-- ================================================ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
-- ============================================= 
-- Author: <Author,,Name> 
-- Create date: <Create Date,,> 
-- Description: <Description,,> 
-- ============================================= 
CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName> 
( 
-- Add the parameters for the function here 
<@param1, sysname, @p1> <Data_Type_For_Param1, , int>, 
<@param2, sysname, @p2> <Data_Type_For_Param2, , char> 
) 
RETURNS TABLE 
AS 
RETURN 
( 
-- Add the SELECT statement with parameter references here 
SELECT 0 
) 
GO 

3.多語句表值函數(shù):返回值為一張表,有邏輯處理能力,但僅能對小數(shù)據(jù)量數(shù)據(jù)有效,數(shù)據(jù)量大時,速度很慢.

復制代碼代碼如下:

 
--多語句表值函數(shù) 

-- ================================================ 
-- Template generated from Template Explorer using: 
-- Create Multi-Statement Function (New Menu).SQL 
-- 
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below. 
-- 
-- This block of comments will not be included in 
-- the definition of the function. 
-- ================================================ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
-- ============================================= 
-- Author: <Author,,Name> 
-- Create date: <Create Date,,> 
-- Description: <Description,,> 
-- ============================================= 
CREATE FUNCTION <Table_Function_Name, sysname, FunctionName> 
( 
-- Add the parameters for the function here 
<@param1, sysname, @p1> <data_type_for_param1, , int>, 
<@param2, sysname, @p2> <data_type_for_param2, , char> 
) 
RETURNS 
<@Table_Variable_Name, sysname, @Table_Var> TABLE 
( 
-- Add the column definitions for the TABLE variable here 
<Column_1, sysname, c1> <Data_Type_For_Column1, , int>, 
<Column_2, sysname, c2> <Data_Type_For_Column2, , int> 
) 
AS 
BEGIN 
-- Fill the table variable with the rows for your result set 

RETURN 
END 
GO 

4.游標:對多條數(shù)據(jù)進行同樣的操作.如同程序的for循環(huán)一樣.有幾種循環(huán)方向控制,一般用FETCH Next.

復制代碼代碼如下:

 
--示意性SQL腳本 

DECLARE @MergeDate Datetime 
DECLARE @MasterId Int 
DECLARE @DuplicateId Int 

SELECT @MergeDate = GetDate() 


DECLARE merge_cursor CURSOR FAST_FORWARD FOR SELECT MasterCustomerId, DuplicateCustomerId FROM DuplicateCustomers WHERE IsMerged = 0 
--定義一個游標對象[merge_cursor] 
--該游標中包含的為:[SELECT MasterCustomerId, DuplicateCustomerId FROM DuplicateCustomers WHERE IsMerged = 0 ]查詢的結果. 

OPEN merge_cursor 
--打開游標 
FETCH NEXT FROM merge_cursor INTO @MasterId, @DuplicateId 
--取數(shù)據(jù)到臨時變量 
WHILE @@FETCH_STATUS = 0 --系統(tǒng)@@FETCH_STATUS = 0 時循環(huán)結束 
--做循環(huán)處理 
BEGIN 
EXEC MergeDuplicateCustomers @MasterId, @DuplicateId 

UPDATE DuplicateCustomers 
SET 
IsMerged = 1, 
MergeDate = @MergeDate 
WHERE 
MasterCustomerId = @MasterId AND 
DuplicateCustomerId = @DuplicateId 

FETCH NEXT FROM merge_cursor INTO @MasterId, @DuplicateId 
--再次取值 
END 

CLOSE merge_cursor 
--關閉游標 
DEALLOCATE merge_cursor 
--刪除游標 

[說明:游標使用必須要配對,Open--Close,最后一定要記得刪除游標.]

5.事務:當一次處理中存在多個操作,要么全部操作,要么全部不操作,操作失敗一個,其他的就全部要撤銷,不管其他的是否執(zhí)行成功,這時就需要用到事務.

復制代碼代碼如下:

 
begin tran 
update tableA 
set columnsA=1,columnsB=2 
where RecIs=1 
if(@@ERROR <> 0 OR @@ROWCOUNT <> 1) 
begin 
rollback tran 
raiserror( '此次update表tableA出錯!!' , 16 , 1 ) 
return 
end 

insert into tableB (columnsA,columnsB) values (1,2) 
if(@@ERROR <> 0 OR @@ROWCOUNT <> 1) 
begin 
rollback tran 
raiserror( '此次update表tableA出錯!!' , 16 , 1 ) 
return 
end 

end 
commit 