当前位置: 首页 > 编程笔记 >

SQL Function 自定义函数详解

李永寿
2023-03-14
本文向大家介绍SQL Function 自定义函数详解,包括了SQL Function 自定义函数详解的使用技巧和注意事项,需要的朋友参考一下

目录

产生背景(已经有了存储过程,为什么还要使用自定义函数)
发展历史
构成
使用方法
适用范围
注意事项
疑问
内容

产生背景(已经有了存储过程,为什么还要使用自定义函数)

与存储过程的区别(存在的意义):

1.     能够在select等SQL语句中直接使用自定义函数,存储过程不行。
2.     自定义函数可以调用其他函数,也可以调用自己(递归)
3.     可以在表列和 CHECK 约束中使用自定义函数来实现特殊列或约束
4.       自定义函数不能有任何副作用。函数副作用是指对具有函数外作用域(例如数据库表的修改)的资源状态的任何永久性更改。函数中的语句唯一能做的更改是对函数上的局部对象(如局部游标或局部变量)的更改。不能在函数中执行的操作包括:对数据库表的修改,对不在函数上的局部游标进行操作,发送电子邮件,尝试修改 目录,以及生成返回至用户的结果集。存储过程没有此限制
5.       函数只能返回一个变量。而存储过程可以返回多个

发展历史

SqlServer 2000之后都支持用户自定义函数

构成

在SQL Server 2000 中根据函数返回值形式的不同将用户自定义函数分为三种类型:标量函数(Scalar Function)、内嵌表值函数(Inline Function)、多声明表值函数(Multi-Statement Function)
标量函数:标量函数是对单一值操作,返回单一值。能够使用表达式的地方,就可以使用标量函数。像我们经常使用的left、getdate等,都属于标量函数。系统函数中的标量函数包括:数学函数、日期和时间函数、字符串函数、数据类型转换函数等
内嵌表值函数:内嵌表值函数的功能相当于一个参数化的视图。它返回的是一个表,内联表值型函数没有由BEGIN-END 语句括起来的函数体。其返回的表由一个位于RETURN 子句中的SELECT 命令段从数据库中筛选出来。

作用

多声明表值函数:可以看作标量型和内嵌表值型函数的结合体。它的返回值是一个表,但它和标量型函数一样有一个用BEGIN-END 语句括起来的函数体,返回值的表中的数据是由函数体中的语句插入的。由此可见,它可以进行多次查询,对数据进行多次筛选与合并,弥补了内联表值型函数的不足。

使用方法

SQL Server 为三种类型的用户自定义函数 提供了不同的命令创建格式。

  (1) 创建标量型用户自定义函数(Scalar functions) 其语法如下:

各参数说明如下:
  owner_name :指定用户自定义函数的所有者。
  function_name:指定用户自定义函数的名称。database_name.owner_name.function_name 应是惟一的。
  @parameter_name:定义一个或多个参数的名称。一个函数最多可以定义1024 个参数每个参数前用“@”符号标明。参数的作用范围是整个函数。参数只能替代常量,不能替代表 名、列名或其它数据库对象的名称。用户自定义函数不支持输出参数。 
  scalar_parameter_data_type:指定标量型参数的数据类型,可以为除TEXT、 NTEXT、 IMAGE、 CURSOR、TIMESTAMP 和TABLE 类型外的其它数据类型。 
  scalar_return_data_type:指定标量型返回值的数据类型,可以为除TEXT、 NTEXT、 IMAGE、 CURSOR、TIMESTAMP 和TABLE 类型外的其它数据类型。 
  scalar_expression:指定标量型用户自定义函数返回的标量值表达式
  function_body:指定一系列的Transact-SQL 语句,它们决定了函数的返回值。 
  ENCRYPTION:加密选项。让SQL Server 对系统表中有关CREATE FUNCTION 的声明加密,以防止用户自定义函数作为SQL Server 复制的一部分被发布(Publish) 。
   SCHEMABINDING:计划绑定选项将用户自定义函数绑定到它所引用的数据库对象如果指定 了此选项,则函数所涉及的数据库对象从此将不能被删除或修改,除非函数被删除或去掉此选项。应注意的是,要绑定的数据库对象必须与函数在同一数据库中。

(2)创建内联表值型用户自定义函 数(Inline Table-valued Functions)

其语法如下:

各参数说明如下:

  TABLE:指定返回值为一个表。

  select-stmt:单个SELECT 语句,确定返回的表的数据。

  其余参数与标量型用户自定义函数相同。

(3) 创建多声明表值型用户自定义函数

  其语法如下:

各参数说明如下:

  @return_variable :一个TABLE 类型的变量,用于存储和累积返回的表中的数据行。 其余参数与标量型用户自定义函数相同。

  在多声明表值型用户自定义函数的函数体中允许使用下列Transact-SQL 语句。 赋值语句(Assignment statements); 流程控制语句(Control-of-Flow statements); 定义作用范围在函数内的变量和 游标的DECLARE 语句; SELECT 语句; 编辑函数中定义的表变量的INSERT、 UPDATE 和DELETE 语句; 在函数中允许涉及诸如声明游 标、打开游标、关闭游标、释放游标这样的游标操作,对于读取游标而言,除非在FETCH 语句中使用INTO 从句来对某一变量赋值,否则不允许在函数中使用FETCH 语句来向客户端返回数据。此 外不确定性函数(Non-deterministic functions) 不能在用户自定义函数中使 用。所谓不确定性函数是指那些使用相同的调用参数在不同时刻调用得到的返回值不同的函数。这些函数如表13-3 所示(全局变量也可以视为一种函数)。

适用范围

1. 只查询,不修改数据库的状态(修改、删除表中记录等)

2. 结果集需要通过递归等方法得到时,可以使用函数,函数比较灵活

3. 结果集需要直接被引用时,可以使用函数。需要对结果集进行再加工(指放在select语句中等),可以使用函数,函数可以嵌在select等sql语句中。

注意事项:

用户自定义函数不能用于执行一系列改变数据库状态的操作

在编写自定义函数时需要注意的:

对于标量函数:

1. 所有的入参前都必须加@

2. create后的返回,单词是returns,而不是return

3. returns后面的跟的不是变量,而是返回值的类型,如:int,char等。

4. 在begin/end语句块中,是return。

内嵌表值函数:

1. 只能返回table,所以returns后面一定是TABLE

2. AS后没有begin/end,只有一个return语句来返回特定的记录。

多语句表值函数:

1. returns后面直接定义返回的表类型,首先是定义表名,表明前面要加@,然后是关键字TABLE,最后是表的结构。

2. 在begin/end语句块中,直接将需要返回的结果insert到returns定义的表中就可以了,在最后return时,会将结果返回。

3. 最后只需要return,return后面不跟任何变量。

疑问:自定义函数不能修改数据库,但它可以调用存储过程,那么在自定义函数中调用一个有修改数据库的操作的存储过程,这个自定义函数能不能执行?

答:自定义函数只能调用扩展存储过程,但是SQL Server 2008的后续版本将删除该功能,不再支持扩展存储过程,所以应避免在开发中使用扩展存储过程。因此,可以得出结论是:实际开发中,函数不会去调用存储过程,也就无法对数据库进行修改操作了。

参考:
http://technet.microsoft.com/zh-tw/library/ms186755.aspx
http://msdn.microsoft.com/zh-cn/library/ms175200.aspx
http://www.cnblogs.com/Athrun/archive/2007/07/27/833416.html

 类似资料:
  • 本文向大家介绍Android自定义View构造函数详解,包括了Android自定义View构造函数详解的使用技巧和注意事项,需要的朋友参考一下 初始Custom View的构造函数 之前写过一篇实现圆形进度条的博客(自定义圆形进度条),通常我们在实现Custom View的时候,都会先继承View并实现View的三个构造函数,例如: 网上有很多关于三个构造函数使用时机的说法,但是说法正确的却没有几

  • 本文向大家介绍Oracle 自定义split 函数实例详解,包括了Oracle 自定义split 函数实例详解的使用技巧和注意事项,需要的朋友参考一下 Oracle 自定义split 函数 Oracle没有提供split函数,但可以自己建立一个函数实现此功能。比如“abc defg  hijkl   nmopqr     stuvw  xyz”,分隔符是空格,但空格个数不定。 源代码: 测试: 结

  • 本文向大家介绍vue自定义全局共用函数详解,包括了vue自定义全局共用函数详解的使用技巧和注意事项,需要的朋友参考一下 如果你需要让一个工具函数在每个组件可用,可以把方法挂载到 Vue.prototype上。 在main.js中: 组件中调用: 以上这篇vue自定义全局共用函数详解就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持呐喊教程。

  • 我做了这个自定义函数,并把它放在全局之外,这通常会工作。我也试着在主异步木偶函数中移动它,但也不起作用。这是一个简单的函数。在每个page evaluate函数中,我调用它并传递选择器。但是,它的说法没有定义和promise拒绝,这是奇怪的,因为函数不是promise....请帮助 我尝试将函数转换为异步函数,添加了一个新的参数页。然后,我将async添加到我的评估函数中,然后将puppeteer

  • XQuery提供编写自定义函数的功能。 下面列出了创建自定义函数的准则。 使用关键字来定义函数。 使用当前XML架构中定义的数据类型 将函数体包含在花括号内。 使用XML命名空间前缀函数的名称。 创建自定义函数时使用以下语法。 语法 示例 以下示例显示如何在XQuery中创建用户定义的函数。 XQuery表达式 输出结果 - 验证结果 要测试上述函数,用上面的XQuery表达式替换books.xq

  • 我在理解group_by如何在tidyverse中工作时遗漏了一些东西。示例将阐明: 我已经创建了下面的函数,它采用很少的参数,并计算tibble内部的最佳权重(可能不是最漂亮的,但似乎工作): 当我在tibble中只有一个组时,这个函数可以很好地工作。我创建函数的方法是尝试通过在一个函数上进行测试来实现它,希望在我稍后对数据进行切片时它会起作用。 然后,我希望可以使用mutate为我的多个组创建