Sqlserver 自定义函数 Function使用介绍
作者:mdxy-dxy 发布时间:2024-01-16 09:11:36
一.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
猜你喜欢
- 处于兴趣,写了一个遍历指定城市五天内的天气预报,并转为华氏度显示。把城市名字写到一个列表里这样可以方便的添加城市。并附有详细注释import
- 最近在Ubuntu16.04上安装Python3.6之后,使用pip命令出现了问题,提示说找不到ssl模块,出现错误如下:pip is co
- Jquery中的一些东西学习一下子,补充完善一下,毕竟有些时候没有使用到这个方式很有用,在使用bootstrap table的时候,选择当前
- 代码片段是开发者每天都要面对的东西,甚至有时候查找代码片段的时间比编写新代码的时间还要多。因为如果找到能够在项目中直接使用的代码片段,这意味
- python time.sleep()-睡眠线程还是进程?它会阻止线程。如果查看Python源代码中的Modules / timemodul
- 本文实例对比分析了python中lambda与def的用法。分享给大家供大家参考。具体分析如下:1、lambda用来创建匿名函数,不同于de
- 当使用Python的flask框架来开发网站后台,解析前端Post来的数据,通常都会使用request.form来获取前端传过来的数据,但是
- 一、系统的默认用户1)sys用户是超级用户,具有最高权限,具有sysdba角色,有create database的权限,该用户默认的密码是s
- 正则表达式,又称正规表示法、常规表示法(英语:Regular Expression,在代码中常简写为regex、regexp或RE),计算机
- 对于数据库管理员来说,可以经常想了解一些之前未听说过的存储过程,特别是无文档类型的存储过程。或许是用这些存储过程,能够简化日常的数据管理。为
- 要做好一个HTML邮件,说简单,还真不怎么复杂,说它复杂,其实也不难。-_-!!以前写过类似的[ 关于HTML邮件的总结 ],最近又犯愁,就
- 使用Scrapy爬取豆瓣某影星的所有个人图片以莫妮卡·贝鲁奇为例1.首先我们在命令行进入到我们要创建的目录,输入 scrapy startp
- ASCII(str) 返回字符串str的第一个字符的ASCII值(str是空串时返回0)mysql> select ASCII(
- 用科讯CMS“分页显示(专题)文章列表标签”,可以在栏目文章列表下面产生一个页码行。从图可以看出,这段DIV,还需要CSS修饰,但是查看Ht
- 本文实例讲述了PHP单例模式用法。分享给大家供大家参考,具体如下:<?phpclass db { public $conn
- 前言本文主要给大家介绍的是关于Python中表达式x += y和x = x+y 区别的相关内容,分享出来供大家参考学习,下面来看看详细的介绍
- Python 语言的优势在于其功能强大,可以用于网络数据采集、数据分析等各种应用场景。本篇文章将介绍如何使用 Python 获取网络数据、使
- 一、数据描述数据集中9994条数据,横跨1237天,销售额为2,297,200.8603美元,利润为286,397.0217美元,他们的库存
- 此文刊登在《程序员》2009年5月期:SQL全名是结构化查询语言(Structured Query Language),一直是后台开发者用来
- 需求:获取oracle表增量信息,发送至udp514端口,支持ip配置步骤:(1)需要的jar oracle的 odbc5.ja