sqlserver 函數(shù)、存儲(chǔ)過(guò)程、游標(biāo)與事務(wù)模板

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

SQL 函數(shù)、存儲(chǔ)過(guò)程、游標(biāo)與事務(wù)模板，學(xué)習(xí)sqlserver的朋友很多情況下都需要用得到。

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

 
--標(biāo)量值函數(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ù):返回值為一張表,僅通過(guò)一條SQL語(yǔ)句實(shí)現(xiàn),沒(méi)有邏輯處理能力.可執(zhí)行大數(shù)據(jù)量的查詢.

復(fù)制代碼代碼如下:

 
--內(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.多語(yǔ)句表值函數(shù):返回值為一張表,有邏輯處理能力,但僅能對(duì)小數(shù)據(jù)量數(shù)據(jù)有效,數(shù)據(jù)量大時(shí),速度很慢.

復(fù)制代碼代碼如下:

 
--多語(yǔ)句表值函數(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.游標(biāo):對(duì)多條數(shù)據(jù)進(jìn)行同樣的操作.如同程序的for循環(huán)一樣.有幾種循環(huán)方向控制,一般用FETCH Next.

復(fù)制代碼代碼如下:

 
--示意性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 
--定義一個(gè)游標(biāo)對(duì)象[merge_cursor] 
--該游標(biāo)中包含的為:[SELECT MasterCustomerId, DuplicateCustomerId FROM DuplicateCustomers WHERE IsMerged = 0 ]查詢的結(jié)果. 

OPEN merge_cursor 
--打開(kāi)游標(biāo) 
FETCH NEXT FROM merge_cursor INTO @MasterId, @DuplicateId 
--取數(shù)據(jù)到臨時(shí)變量 
WHILE @@FETCH_STATUS = 0 --系統(tǒng)@@FETCH_STATUS = 0 時(shí)循環(huán)結(jié)束 
--做循環(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 
--關(guān)閉游標(biāo) 
DEALLOCATE merge_cursor 
--刪除游標(biāo) 

[說(shuō)明:游標(biāo)使用必須要配對(duì),Open--Close,最后一定要記得刪除游標(biāo).]

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

復(fù)制代碼代碼如下:

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

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

end 
commit 