Sqlserver 自定义函数 Function使用介绍

数据库 2024/12/28 佚名

3 2 1

帝王谷资源网 Design By www.wdxyy.com

一.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
/*上面是标量函数的一个简单的例举，下面就开始调用，注意是在dbo.下*/
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
/*需要注意的就是他的写法上没有begin+end*/
/*下面就是将date带入函数中*/
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 /*这句的意思是将下面的查询结果插入到@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.阅历每条数据：

/*声明4个变量用来接收暂存游标中的数据*/
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
/*利用@@FETCH_STATUS返回的数值来确定cursor在数据表中读取的情况=0为fetch执行成功*/
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 
/*下面声明和打开update——cursor*/
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
/****** Object: Trigger [dbo].[UPDATE_T] Script Date: 03/04/2014 16:04:21 ******/
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] 
/*将更新成的数据插入到【T_UPDATE_HOU】中*/
INSERT INTO T_UPDATE_QIAN
SELECT*
FROM[deleted] 
/*将更新前的数据插入到【T_UPDATE_QIAN】中*/
PRINT'更新完毕，更新前的数据已经写入到【T_UPDATE_QIAN】，更新后的数据插入到【T_UPDATE_HOU】。'
END

update：

USE SQL_SYSTEM 
GO
UPDATE[AA] 
SET AA.职位 ='SQL高级工程师'
WHERE AA.工号=101
/*以上是个简单的更新操作，用于update触发*/
SELECT* FROM[T_UPDATE_HOU] --修改成的数据
SELECT* FROM[T_UPDATE_QIAN] --待修改的数据
GO

F5：

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

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

Sqlserver,Function

标签：

Sqlserver,Function

帝王谷资源网 Design By www.wdxyy.com

广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com

帝王谷资源网 Design By www.wdxyy.com

评论“Sqlserver 自定义函数 Function使用介绍”

暂无评论...

www.wdxyy.com 帝王谷资源网

139,976互联网资源

144,792高清电影

21,817无损音乐

631,128技术资源

Sqlserver 自定义函数 Function使用介绍

Sqlserver,Function

利用SQL Server数据库邮件服务实现监控和预警

SQL Server 2012安装后服务器名称找不到的解决办法

评论“Sqlserver 自定义函数 Function使用介绍”

RTX 5090要首发性能要翻倍！三星展示GDDR7显存

更新日志

友情链接

Sqlserver 自定义函数 Function使用介绍

Sqlserver,Function

利用SQL Server数据库邮件服务实现监控和预警

SQL Server 2012安装后服务器名称找不到的解决办法

评论“Sqlserver 自定义函数 Function使用介绍”

RTX 5090要首发 性能要翻倍！三星展示GDDR7显存

更新日志

友情链接

RTX 5090要首发性能要翻倍！三星展示GDDR7显存