SQL 函数、存储过程、游标与事务模板，学习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:
— Create date:
— Description:
— =============================================
CREATE FUNCTION
(
— Add the parameters for the function here
)
RETURNS
AS
BEGIN
— Declare the return variable here
DECLARE
— Add the T-SQL statements to compute the return value here
SELECT =
— Return the result of the function
RETURN
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:
— Create date:
— Description:
— =============================================
CREATE FUNCTION
(
— Add the parameters for the function here
)
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:
— Create date:
— Description:
— =============================================
CREATE FUNCTION
— Add the parameters for the function here
)
RETURNS
TABLE
(
— Add the column definitions for the TABLE variable here
)
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
