1.標(biāo)量函數(shù)：結(jié)果為一個單一的值，可包含邏輯處理過程。其中不能用getdate()之類的不確定性系統(tǒng)函數(shù).
復(fù)制代碼 代碼如下:
--標(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ù):返回值為一張表,僅通過一條SQL語句實現(xiàn),沒有邏輯處理能力.可執(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.多語句表值函數(shù):返回值為一張表,有邏輯處理能力,但僅能對小數(shù)據(jù)量數(shù)據(jù)有效,數(shù)據(jù)量大時,速度很慢.
復(fù)制代碼 代碼如下:
--多語句表值函數(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):對多條數(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
--定義一個游標(biāo)對象[merge_cursor]
--該游標(biāo)中包含的為:[SELECT MasterCustomerId, DuplicateCustomerId FROM DuplicateCustomers WHERE IsMerged = 0 ]查詢的結(jié)果.
OPEN merge_cursor
--打開游標(biāo)
FETCH NEXT FROM merge_cursor INTO @MasterId, @DuplicateId
--取數(shù)據(jù)到臨時變量
WHILE @@FETCH_STATUS = 0 --系統(tǒng)@@FETCH_STATUS = 0 時循環(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)
[說明:游標(biāo)使用必須要配對,Open--Close,最后一定要記得刪除游標(biāo).]
5.事務(wù):當(dāng)一次處理中存在多個操作,要么全部操作,要么全部不操作,操作失敗一個,其他的就全部要撤銷,不管其他的是否執(zhí)行成功,這時就需要用到事務(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出錯!!' , 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
您可能感興趣的文章:- c#實現(xiàn)sqlserver事務(wù)處理示例
- SQL Server觸發(fā)器及觸發(fā)器中的事務(wù)學(xué)習(xí)
- sqlserver中的事務(wù)和鎖詳細(xì)解析
- Sqlserver 存儲過程中結(jié)合事務(wù)的代碼
- SQLSERVER分布式事務(wù)使用實例
- 淺析SQL Server中包含事務(wù)的存儲過程
- SQLServer存儲過程中事務(wù)的使用方法
- SQL Server存儲過程中編寫事務(wù)處理的方法小結(jié)
- Sql Server中的事務(wù)介紹
- Sql Server事務(wù)語法及使用方法實例分析
