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
/*上面是标量函数的一个简单的例举,下面就开始调用,注意是在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进行更新的操作:

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

(0)

相关推荐

  • SQL Server 常用函数使用方法小结

    之前就想要把一些 SQL 的常用函数记录下来,不过一直没有实行...嘿嘿... 直到今天用到substring()这个函数,C# 里面这个方法起始值是 0,而 SQL 里面起始值是 1.傻傻分不清楚... 这篇博客作为记录 SQL 的函数的使用方法,想到哪里用到哪里就写到哪里... SubString():用于截取指定字符串的方法.该方法有三个参数: 参数1:用于指定要操作的字符串. 参数2:用于指定要截取的字符串的起始位置,起始值为 1 . 参数3:用于指定要截取的长度. select sub

  • sql server如何利用开窗函数over()进行分组统计

    这是一道常见的面试题,在实际项目中经常会用到. 需求:求出以产品类别为分组,各个分组里价格最高的产品信息. 实现过程如下: declare @t table( ProductID int, ProductName varchar(20), ProductType varchar(20), Price int) --测试数据 insert @t select 1,'name1','P1',3 union all select 2,'name2','P1',5 union all select 3,

  • SQL server中字符串逗号分隔函数分享

    继SQl -Function创建函数数据库输出的结果用逗号隔开,在开发中也有许多以参数的形式传入带逗号字条串参数(数据大时不建议这样做) 例:查找姓名为"张三,李二" 的数据此时在数据库里就要对此参数做处理如图: 函数代码如下 CREATE FUNCTION [dbo].[fnSplitStr] ( @sText NVARCHAR(Max), @sDelim CHAR(1) ) RETURNS @retArray TABLE ( value VARCHAR(100) ) AS BEGI

  • SQL Server实现split函数分割字符串功能及用法示例

    本文实例讲述了SQL Server实现split函数分割字符串功能及用法.分享给大家供大家参考,具体如下: /* 函数名称:f_SplitToNvarchar 作用:实现split功能的函数 更新记录: 设计思路:将nvarchar类型字符结合的一个串,分隔到一张只有一列nvarchar类型的表里 */ CREATE FUNCTION [dbo].[f_SplitToNvarchar] ( @SourceSql NVARCHAR(MAX),--源分隔字符串 @StrSeprate VARCHAR

  • Sql Server中REPLACE函数的使用

    REPLACE 用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式. 语法 REPLACE ( ''string_replace1'' , ''string_replace2'' , ''string_replace3'' ) 参数 ''string_replace1'' 待搜索的字符串表达式.string_replace1 可以是字符数据或二进制数据. ''string_replace2'' 待查找的字符串表达式.string_replace2 可以是字符数据或二进制数据.

  • Sqlserver 自定义函数 Function使用介绍

    一.FUNCTION: 在sqlserver2008中有3中自定义函数:标量函数/内联表值函数/多语句表值函数,首先总结下他们语法的异同点: 同点: 1.创建定义是一样的: a, CREATE FUNCTION F_NAME(传入的参数名称 传入参数的类型) b,RETURNS 返回值类型 c,AS 异点:1.标量函数返回的是一个数据类型值,内联表值函数返回的是一个table,而多语句返回的是一个table的变量(类似前面两个的结合): 2.语法的结构:标量函数和多语句函数都是要有begin,,

  • 谈谈sqlserver自定义函数与存储过程的区别

    一.自定义函数: 1. 可以返回表变量 2. 限制颇多,包括 不能使用output参数: 不能用临时表: 函数内部的操作不能影响到外部环境: 不能通过select返回结果集: 不能update,delete,数据库表: 3. 必须return 一个标量值或表变量 自定义函数一般用在复用度高,功能简单单一,争对性强的地方. 二.存储过程 1. 不能返回表变量 2. 限制少,可以执行对数据库表的操作,可以返回数据集 3. 可以return一个标量值,也可以省略return 存储过程一般用在实现复杂的

  • MySQL数据库之内置函数和自定义函数 function

    目录 1.内置函数 1.1.字符串函数 1.2.时间函数 1.3.数学函数 1.4.其他函数 2.自定义函数 2.1.创建函数 2.2.查看函数 2.3.调用函数 2.4.删除函数 2.5.注意事项 3.函数流程结构案例 前言: 函数分为两类:系统函数和自定义函数 使用函数: select 函数名(参数列表); 1.内置函数 1.1.字符串函数 函数名 说明 char_length 判断字符串的字符数 length 判断字符串的字节数,与字符集有关 concat 连接字符串 insrt 检查字符

  • SQL Function 自定义函数详解

    目录 产生背景(已经有了存储过程,为什么还要使用自定义函数) 发展历史 构成 使用方法 适用范围 注意事项 疑问 内容 产生背景(已经有了存储过程,为什么还要使用自定义函数) 与存储过程的区别(存在的意义): 1.     能够在select等SQL语句中直接使用自定义函数,存储过程不行. 2.     自定义函数可以调用其他函数,也可以调用自己(递归) 3.     可以在表列和 CHECK 约束中使用自定义函数来实现特殊列或约束 4.       自定义函数不能有任何副作用.函数副作用是指对

  • js document.getElementsByClassName的使用介绍与自定义函数

    getElementsByClassName()是HTML5 新增的DOM API.IE8以下不支持搜索. getElementByClassName()函数的使用方法: 使用JavaScript访问DOM的一个重大问题是,此过程需要一种通过元素类名称来选择类的类函数,对DOMContentReady,这种类函数的缺失导致开发人员需要自己编写自定义脚本业执行上述任务,许多这个类脚本都是围绕着getElementByClassName()建立的,这种解决方法在HTML5中被标准化,另外,这种方法还

  • sqlserver中的自定义函数的方法小结

    "自定义函数"是我们平常的说法,而"用户定义的函数"是 SQL Server 中书面的说法. SQL Server 2000 允许用户创建自定义函数,自定义函数可以有返回值. 自定义函数分为:标量值函数或表值函数 如果 RETURNS 子句指定一种标量数据类型,则函数为标量值函数.可以使用多条 Transact-SQL 语句定义标量值函数. 如果 RETURNS 子句指定 TABLE,则函数为表值函数. 表值函数又可分为:内嵌表值函数(行内函数)或多语句函数 如果

  • PHP统计目录大小的自定义函数分享

    计算文件.磁盘分区和目录的大小在各种应用程序中都是常见的任务.计算文件的大小可以通过前面介绍过的filesize()函数完成,统计磁盘大小也可以使用disk_free_space()和disk_total_space()两个函数实现.但PHP目前并没有提供目录总大小的标准函数,因此我们要自定义一个函数来完成这个任务.首先要考虑计算的目录中有没有包含其他子目录的情况,如果没有子目录,目录下所有文件的大小相加后的总和就是这个目录的大小.如果包含子目录,就按照这个方法再计算一下子目录的大小,使用递归函

  • 浅析PHP中call user func()函数及如何使用call user func调用自定义函数

    UCenter源代码里有一个函数call_user_func,开始以为是自己定义的函数,结果到处都找不到.后来才知道call_user_func是PHP的内置函数,该函数允许用户调用直接写的函数并传入一定的参数,下面总结下这个函数的使用方法. call_user_func函数类似于一种特别的调用函数的方法,使用方法如下: <?php function nowamagic($a,$b) { echo $a; echo $b; } call_user_func('nowamagic', "&q

  • PowerShell中简单的自定义函数和调用函数例子

    在PowerShell中是否有函数?PowerShell是否可以自定义函数?PowerShell中如何自定义函数? 在PowerShell中,我们可以使用函数(function)来简化编程开发.在PowerShell中使用function这个关键词来标识一个函数.一个自定义的函数,由function关键词开始,然后用一对大括号来包括起整个函数体的内容. 我们先来看一下简单的PowerShell函数: 复制代码 代码如下: function Test-Function {     Write-Ho

  • JS自定义函数对web前端上传的文件进行类型大小判断

    废话不多说了直接给大家贴js代码了.具体代码如下所示: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <me

随机推荐