SQL->可编程性->函数

SQL函数说明大全

   函数的目标是返回一个值。大多数函数都返回一个标量值(scalar value)，标量值代表一个数据单元或一个简单值。实际上，函数可以返回任何数据类型，包括表、游标等可返回完整的多行结果集的类型。

CONVERT()函数可接受2个或3个参数。因此，下列两个例子都是正确的：

SELECT CONVERT(Varchar(20), GETDATE())

SELECT CONVERT(Varchar(20), GETDATE(), 101)

    这个函数的第一个参数是数据类型Varchar(20)，第2个参数是另一个函数GETDATE()。GETDATE()函数用datetime数据类型将返回当前的系统日期和时间。第2条语句中的第3个参数决定了日期的样式。这个例子中的101指以mm/dd/yyyy格式返回日期。本章后面将详细介绍GETDATE()函数。即使函数不带参数或者不需要参数，调用这个函数时也需要写上一对括号，例如GETDATE()函数。注意在书中使用函数名引用函数时，一定要包含括号，因为这是一种标准形式。

确定性函数

由于数据库引擎的内部工作机制，SQL Server必须根据所谓的确定性，将函数分成两个不同的组。这不是一种新时代的信仰，只和能否根据其输入参数或执行对函数输出结果进行预测有关。如果函数的输出只与输入参数的值相关，而与其他外部因素无关，这个函数就是确定性函数。如果函数的输出基于环境条件，或者产生随机或者依赖结果的算法，这个函数就是非确定性的。例如，GETDATE()函数是非确定性函数，因为它不会两次返回相同的值。为什么要把看起来简单的事弄得如此复杂呢？主要原因是非确定性函数与全局变量不能在一些数据库编程对象中使用(如用户自定义函数)。部分原因是SQL Server缓存与预编译可执行对象的方式。例如，即席查询可以使用任何函数，不过如果打算构建先进的、可重用的编程对象，理解这种区别很重要。

以下这些函数是确定性的：

l AVG()(所有的聚合函数都是确定性的)

l CAST()

l CONVERT()

l DATEADD()

l DATEDIFF()

l ASCII()         SELECT ASCII('A')       65

l CHAR()

l SUBSTRING()

以下这些函数与变量是非确定性的：

l GETDATE()

l @@ERROR

l @@SERVICENAME

l CURSORSTATUS()

l RAND()

    

在函数中使用用户变量

变量既可用于输入，也可用于输出。在T-SQL中，用户变量以@符号开头，用于声明为特定的数据类型。可以使用SET或者SELECT语句给变量赋值。以下的例子用于将一个int类型的变量@MyNumber传递给SQRT()函数：

DECLARE @MyNumber int

SET @MyNumber=144 

SELECT SQRT(@MyNumber)

结果是12，即144的平方根。

用SET给变量赋值

以下例子使用另一个int型的变量@MyResult，来捕获该函数的返回值。这个技术类似于过程式编程语言中的函数调用样式，即把SET语句和一个表达式结合起来，给参数赋值：

DECLARE @MyNumber int, @MyResult int

SET @MyNumber = 144 

-- Assign the function result to the variable: 

SET @MyResult = SQRT(@MyNumber) 

-- Return the variable value 

SELECT @MyResult

 用SELECT给变量赋值

使用SELECT的另一种形式也可以获得同样的结果。对变量要在赋值前要先声明。使用SELECT语句来替代SET命令的主要优点是，可以在一个操作内同时给多个变量赋值。执行下面的SELECT语句，通过SELECT语句赋值的变量就可以用于任何操作了。

DECLARE @MyNumber1 int, @MyNumber2 int,

@MyResult1 int, @MyResult2 int

SELECT @MyNumber1 = 144, @MyNumber2 = 121  

-- Assign the function result to the variable: 

SELECT @MyResult1 = SQRT(@MyNumber1),

@MyResult2 = SQRT(@MyNumber2)  

-- Return the variable value 

SELECT @MyResult1, @MyResult2

 上面的例子首先声明了4个变量，然后用两个SELECT语句给这些变量赋值，而不是用4个SELECT语句给变量赋值。虽然这些技术在功能上是相同的，但是在服务器的资源耗费上，用一个SELECT语句给多个变量赋值一般比用多个SET命令的效率要高。将一个甚至多个值选进参数的限制是，对变量的赋值不能和数据检索操作同时进行。这就是上面的例子使用SELECT语句来填充变量，而用另外一个SELECT语句来检索变量中数据的原因。例如，下面的脚本就不能工作:

DECLARE @RestockName varchar(50) 

SELECT ProductId 

      ,@RestockName = Name + ':' + ProductNumber 

FROM Production.Product

 这个脚本会产生如下错误:

消息141，级别15，状态1，第2 行

向变量赋值的SELECT 语句不能与数据检索操作结合使用。

在查询中使用函数

函数经常和查询表达式结合使用来修改列值。这只需将列名作为参数传递给函数即可，随后函数将引用插入到SELECT查询的列的列表中，如下所示：

SELECT Title, NationalIDNumber, YEAR(BirthDate) AS BirthYear 

FROM HumanResources.Employee

在这个例子中，BirthDate列的值被作为参数传递给YEAR()函数。函数的结果是别名为BirthYear的列。

嵌套函数

我们需要的功能常常不能仅由一个函数来实现。根据设计，函数应尽量简单，用于提供特定的功能。如果一个函数要执行许多不同的操作，就变得复杂和难以使用。因此，每个函数通常仅执行一个操作，要实现所有的功能，可以将一个函数的返回值传递给另一个函数，这称为嵌套函数调用。

以下是一个简单的例子：GETDATE()函数的作用是返回当前的日期与时间，但不能返回经过格式化的数据，因为这是CONVERT()函数的功能。要想同时使用这两个函数，可以把GETDATE()函数的输出作为CONVERT()函数的输入参数。

SELECT CONVERT(Varchar(20), GETDATE(), 101)