SQL Server自定义函数

自定义函数分为：标量值函数或表值函数两种。

• 标量值函数：如果 RETURNS 子句指定一种标量数据类型，则函数为标量值函数。（返回字段）
• 表值函数：如果 RETURNS 子句指定 TABLE，则函数为表值函数。（返回table）

　  表值函数又分为两种：内嵌表值函数（行内函数）或多语句函数

• 　　　　如果 RETURNS 子句指定的 TABLE 不附带列的列表，则该函数为内嵌表值函数。
• 　　　　如果 RETURNS 子句指定的 TABLE 类型带有列及其数据类型，则该函数是多语句表值函数

创建标量值函数的语法

Create function 函数名（参数）
Returns 返回值数据类型
[with {Encryption | Schemabinding }]
[as]
begin
SQL语句(必须有return 变量或值)
End

这里的with为附加选项：

• 如果需要对函数体进行加密，可使用WITH ENCRYPTION；
• 如果需要将创建的函数与引用的数据库绑定，可以使用WITH SCHEMABINDING（注：函数一旦绑定，则不能删除、修改，除非删除绑定）

举例：

CREATE FUNCTION AddNum(@age INT) 
	returns INT 
	AS 
	BEGIN
	  DECLARE @num INT 
	  SET @num = 2 
		RETURN @age +@num
	END 

 调用：（必须加上dbo.否则会报如图错误）

 

 

 创建内嵌表值函数的语法

 

create function 函数名（参数）
returns table
[with {Encryption | Schemabinding }]
as
return(一条SQL语句)

 

 举例：

CREATE FUNCTION Test12 ( @name VARCHAR ( 20 ) ) 
returns TABLE 
AS
RETURN (SELECT age, goals, name FROM test1 WHERE name =@name) 

 调用：

 

  创建多语句表值函数的语法

 

   create function 函数名（参数）
   returns 表格变量名table (表格变量定义)
   [with {Encryption | Schemabinding }]
as
   begin
    SQL语句
   end
--多句表格值函数包含多条SQL语句，至少有一条在表格变量中填上数据值

 

 举例：

CREATE FUNCTION getInfos ( @name VARCHAR ( 20 ) ) 
returns @copy table(username VARCHAR(20),goals DECIMAL(19,4))
AS
BEGIN
INSERT INTO @copy
SELECT name,goals FROM test1 WHERE name =@name
RETURN 
end
--字段类型要一一对应
--insert into select 将数据复制到现有的表中

 调用：