Sqlserver 自定义函数 Function使用介绍

一.FUNCTION:
在sqlserver2008中有3中自定义函数：标量函数/内联表值函数/多语句表值函数，首先总结下他们语法的异同点： 

同点：
1.创建定义是一样的： a, CREATE FUNCTION F_NAME(传入的参数名称 传入参数的类型) b,RETURNS 返回值类型 c,AS 异点：1.标量函数返回的是一个数据类型值，内联表值函数返回的是一个table，而多语句返回的是一个table的变量（类似前面两个的结合）；
2.语法的结构：标量函数和多语句函数都是要有begin，，，end，内联表值函数就没有；
3.调用：标量函数要写成在dbo,function_name；

标量函数，利用上篇文章写的数据表，在[T_员工信息]表中查姓名为李异峰员工的工号：

USE SQL_SYSTEM 
GO
CREATE FUNCTION F_GonGHAO(@XINGMING NVARCHAr(5))
RETURNS INT 
AS
BEGIN
DECLARE @GonGHAO INT
SET @GonGHAO =(SELECt Y.工号 FROM[T_员工信息] AS Y WHERe Y.姓名 =@XINGMING )
RETURN @GonGHAO
END
GO

SELECt [姓名] FROM[T_员工信息] WHERe [姓名]='李异峰' 

SELECt [姓名],DBO.F_GonGHAO ('李异峰')AS 工号 FROM[T_员工信息] WHERe [姓名]='李异峰'
GO

F5下：

内联表值函数：他返回的可是一个table哦，比如：筛选出2014.2.28号所有员工的打卡记录：

USE SQL_SYSTEM 
GO
CREATE FUNCTION F_DAKAJILU(@RIQI DATE)
RETURNS TABLE
AS
RETURN(
SELECt* FROM[T_考勤]AS K WHERe K.日期 =@RIQI 
)
GO


SELECt*
FROM [F_DAKAJILU]('2014/02/28')
GO

F5：

多语句表值函数就是，表值函数+内联表值函数综合版：就写个返回当天打卡记录和涉及员工的个人信息：

USE SQL_SYSTEM 
GO
CREATE FUNCTION F_D_DAKAJILU(@RIQI DATE)
RETURNS @TEMP_TABLE TABLE( 
[姓名]NVARCHAr(5) NOT NULL,
[工号]INT NOT NULL,
[职位]NVARCHAr(10) NOT NULL,
[部门]NVARCHAr(5) NOT NULL,
[是否夜班]NCHAr(1) NOT NULL,
[日期]DATE NOT NULL,
[当天上班时间]FLOAT(1)NOT NULL
) 

AS
BEGIN
INSERT INTO @TEMP_TABLE 
SELECt Y.姓名 ,Y.工号 ,Y.职位 ,Y.部门 ,K.夜班 ,K.日期 ,K.当天上班时间 
FROM[T_员工信息] AS Y CROSS JOIN [T_考勤] AS K  
WHERe Y.工号 =K.工号 AND K.日期=@RIQI 
RETURN 
END
GO
SELECt*
FROM[F_D_DAKAJILU]('2014/02/28')
GO

F5：

二：CURSOR，当要检索复杂的数据的每条数据的时候就可以用到它，类似于c语言的指针一样，他可以在你的数据表中阅历每条数据和更新。 1.新写个cursor阅历数据表(T_员工信息)的数据过程： 1.1.声明一个只读cursor：

USE SQL_SYSTEM 
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION CURSOR_READ_T_员工信息
DECLARE CUR_T_员工信息 SCROLL CURSOR FOR
SELECt Y.姓名 ,Y.工号 ,Y.职位 ,Y.部门 FROM[T_员工信息]AS Y ORDER BY Y.工号 ASC 
COMMIT TRANSACTION CURSOR_READ_T_员工信息
GO

1.2.打开：

OPEN GLOBAL CUR_T_员工信息
GO

1.3.阅历每条数据：

DECLARE @XINGMING NVARCHAr(3),@GonGHAO INT,@ZHIWEI NVARCHAr(10),@BUMEN NVARCHAr(8)

PRINT'当前数据表中有'+CAST(@@CURSOR_ROWS AS NVARCHAr(6))+'行数据。'

FETCH FIRST FROM[CUR_T_员工信息]INTO @XINGMING,@GONGHAO,@ZHIWEI,@BUMEN

WHILE(@@FETCH_STATUS =0)
BEGIN
PRINT'姓名：'+@XINGMING+' 工号：'+ConVERT(VARCHAr(3),@GONGHAO)+' 职位：'+@ZHIWEI+' 部门：'+@BUMEN
FETCH NEXT FROM[CUR_T_员工信息] INTO @XINGMING,@GONGHAO,@ZHIWEI,@BUMEN
END
GO

F5:

1.4.用完后就关闭和释放：

CLOSE GLOBAL CUR_T_员工信息

DEALLOCATE GLOBAL CUR_T_员工信息
GO

这样的话就实现了读取每条记录的功能，然后就可以用它来更新相关的数据条（所有的工号值+100）：

USE SQL_SYSTEM 

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION TR_UPDATe_YG 
DECLARE CUR_T_YG CURSOR FOR
SELECT YG.姓名 ,YG.工号 ,YG.职位 ,YG.部门 FROM[T_yuangongxinxi]AS YG FOR 
UPDATe 
OPEN GLOBAL CUR_T_YG
COMMIT TRANSACTION TR_UPDATE_YG

UPDATE:

PRINT'当前有'+ConVERT(VARCHAr(3),@@CURSOR_ROWS)+'条数据行。'
FETCH NEXT FROM[CUR_T_YG]
WHILE(@@FETCH_STATUS=0)
BEGIN
UPDATe[T_yuangongxinxi] 
SET[工号] =[工号]+100
WHERe CURRENT OF CUR_T_YG
FETCH NEXT FROM[CUR_T_YG] 
END

SELECt:

关闭释放：

CLOSE GLOBAL CUR_T_YG
DEALLOCATE GLOBAL CUR_T_YG

三：PROCEDURE,存储过程是利用SQL Server所提供的Transact-SQL语言所编写的程序,同时也能在高级语言上调用其存储过程。 3.1.无参数：说一个调用查询数据表的pro：

USE SQL_SYSTEM 
GO
CREATE PROCEDURE PRO_SELECT_T
WITH RECOMPILE 
AS
SELECT* FROM[T_yuangongxinxi] 
GO
EXECUTE:
EXECUTE PRO_SELECt_T

F5：

3.1.2.EXECUTE PRO_SELECT_T在这里其实就是一个view，还可以把execute调用结果插入到新表：

USE SQL_SYSTEM 
SELECT*
INTO AA 
FROM[T_yuangongxinxi] 
GO
TRUNCATE TABLE AA --TRUNCATE与DELETe的区别就是TR效率高于DE，而且DE清空时会在日志里面留下恢复记录
GO
INSERT INTO AA
EXECUTE PRO_SELECT_T
GO

结果就不截图了， 3.2.带参pro，写个修改后输出修改的信息： 例如公司的员工经常有职位变动的情况，就写个这方面的，给出变动员工的工号就可以修改该员工的职位，然后将修改的操作反馈出来：

USE SQL_SYSTEM 
GO
CREATE PROCEDURE PRO_DAUP_ZHIWEI
@GonGHAO INT,@ZHIWEI NVARCHAr(10),@RETURNS NVARCHAr(50) OUTPUT
AS
BEGIN

DECLARE @QIAN_RETURN_ZHIWEI NVARCHAr(10),@XINGMING NVARCHAr(3)
SELECT @QIAN_RETURN_ZHIWEI =AA.职位 ,@XINGMING =AA.姓名 
FROM[AA]
WHERe AA.工号 =@GonGHAO 

UPDATe[AA] 
SET[职位] =@ZHIWEI WHERe AA.工号 =@GonGHAO 
SET @RETURNS = '已经成功将工号为：【'+ConVERT(VARCHAr(3),@GONGHAO)+'】,姓名为：【'+@XINGMING+'】,职位【'+
@QIAN_RETURN_ZHIWEI+'】更新为：【'+@ZHIWEI
END

EXECUTE:

DECLARE @PRINTF NVARCHAr(50);
EXECUTE PRO_DAUP_ZHIWEI 101,'SQL工程师',@PRINTF OUTPUT 
SELECT @PRINTF AS '更新消息'
GO

F5:

PRO就说到这里了；
四：TRIGGER，触发器，类似于一个地雷一样，只要你触犯了他的要求的话，他就开始工作了，也可以利用这个功能去维持或阻挡某些不想发生的错误操作， DDL：如：不许删除某个【AA】表：

USE SQL_SYSTEM 
GO
CREATE TRIGGER DROP_T
ON DATAbase FOR DROP_TABLE 
AS
BEGIN
ROLLBACK TRANSACTION 
PRINT'不能删除的,因为我已经添加了触发保护！'
END

drop：

DROP TABLE AA 

F5：

DML，是解决对最低层数据的问题： 在这里就存在临时的2个表：deleted 和 inserted 逻辑（概念）表，要搞明白就要知道trigger的工作原理： insert 操作时：

delete 操作时：

update 操作时：

简单举个例子来说下update的操作：也就是重点看inserted和deleted表上：

USE [SQL_SYSTEM]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[UPDATE_T]
ON [dbo].[AA] INSTEAD OF UPDATE
AS
BEGIN
INSERT INTO T_UPDATE_HOU
SELECT* 
FROM[inserted] 

INSERT INTO T_UPDATe_QIAN
SELECT*
FROM[deleted] 

PRINT'更新完毕，更新前的数据已经写入到【T_UPDATE_QIAN】，更新后的数据插入到【T_UPDATE_HOU】。'
END

update：

USE SQL_SYSTEM 
GO
UPDATE[AA] 
SET AA.职位 ='SQL高级工程师'
WHERe AA.工号=101

SELECT* FROM[T_UPDATe_HOU] --修改成的数据
SELECT* FROM[T_UPDATE_QIAN] --待修改的数据
GO

F5：

在去年的的时候我写了一个利用trigger对多表连接的view进行更新的操作：

https://www.jb51.net/article/95473.htm