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