sqlserver 函數、預存程序、遊標與事務模板

1.純量涵式：結果為一個單一的值，可包含邏輯處理過程。其中不能用getdate()之類的不確定性系統函數. 複製代碼 代碼如下:--純量值函式
-- ================================================
-- 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.內聯資料表值函式:傳回值為一張表,僅通過一條SQL語句實現,沒有邏輯處理能力.可執行大資料量的查詢.

複製代碼 代碼如下:--內聯資料表值函式

-- ================================================
-- 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.多語句資料表值函式:傳回值為一張表,有邏輯處理能力,但僅能對小資料量資料有效,資料量大時,速度很慢.

複製代碼 代碼如下:--多語句資料表值函式

-- ================================================
-- 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.遊標:對多條資料進行同樣的操作.如同程式的for迴圈一樣.有幾種迴圈方向控制,一般用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
--取資料到臨時變數
WHILE @@FETCH_STATUS = 0 --系統@@FETCH_STATUS = 0 時迴圈結束
--做迴圈處理
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.事務:當一次處理中存在多個操作,要麼全部操作,要麼全部不操作,操作失敗一個,其他的就全部要撤銷,不管其他的是否執行成功,這時就需要用到事務.

複製代碼 代碼如下: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