sqlserver 函数、存储过程、游标与事务模板
程序员文章站
2023-12-11 17:31:16
1.标量函数：结果为一个单一的值，可包含逻辑处理过程。其中不能用getdate()之类的不确定性系统函数. 复制代码 代码如下: --标量值函数 -- ==========...
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
复制代码 代码如下:
--标量值函数
-- ================================================
-- 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