1.标量函数：结果为一个单一的值，可包含逻辑处理过程。其中不能用getdate()之类的不确定性系统函数. 复制代码 代码如下: –标量值函数 — ================================================ — Temp
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
