文档章节

MSSQL 自定义函数详解

深圳大道
 深圳大道
发布于 2016/12/29 15:39
字数 850
阅读 1
收藏 0

自定义函数分为:标量值函数或表值函数

如果 RETURNS 子句指定一种标量数据类型,则函数为标量值函数。可以使用多条 Transact-SQL 语句定义标量值函数。
如果 RETURNS 子句指定 TABLE,则函数为表值函数。
表值函数又可分为:内嵌表值函数(行内函数)或多语句函数

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

CREATE FUNCTION dbo.Foo
(
)
RETURNS INT
AS
BEGIN
	DECLARE @n INT 
	SELECT @n = 3 
	RETURN @n
END

内嵌表值函数示例

CREATE FUNCTION dbo.Foo
(
)
RETURNS TABLE
AS
	RETURN 
	SELECT id,
	       title
	FROM   msgs

多语句表值函数示例(部分)

CREATE FUNCTION dbo.fn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE 
(
    EmployeeID int primary key NOT NULL,
    Name nvarchar(255) NOT NULL,
    Title nvarchar(50) NOT NULL,
    EmployeeLevel int NOT NULL,
    Sort nvarchar (255) NOT NULL
)
--Returns a result set that lists all the employees who report to the 
--specific employee directly or indirectly.*/
AS
BEGIN
   WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS
    (SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)
     FROM HumanResources.Employee AS e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
     WHERE e.EmployeeID = @InEmpID
   UNION ALL
     SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + 
                 LastName)
     FROM HumanResources.Employee as e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID
          JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
-- copy the required columns to the result of the function 
   INSERT @retFindReports
   SELECT EmployeeID, Name, Title, EmployeeLevel, Sort
   FROM DirectReports 
   RETURN
END;
GO
在以下示例中,调用了此函数。
-- Example invocation
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM dbo.fn_FindReports(109)
ORDER BY Sort;

注意其 RETURNS 部分。

多语句函数的主体中允许使用以下语句。未在下面的列表中列出的语句不能用在函数主体中。
赋值语句。
控制流语句。
DECLARE 语句,该语句定义函数局部的数据变量和游标。
SELECT 语句,该语句包含带有表达式的选择列表,其中的表达式将值赋予函数的局部变量。
游标操作,该操作引用在函数中声明、打开、关闭和释放的局部游标。只允许使用以 INTO 子句向局部变量赋值的 FETCH 语句;不允许使用将数据返回到客户端的 FETCH 语句。
INSERT、UPDATE 和 DELETE 语句,这些语句修改函数的局部 table 变量。
EXECUTE 语句调用扩展存储过程。

ALTER FUNCTION [dbo].[AmountRole1]  
(  
    @Amount numeric(18,2)  
)  
RETURNS numeric(18,2)  
AS  
BEGIN  
    DECLARE @i FLOAT  
    IF @Amount >= 2000  
        SET @i = @Amount * 0.98 --满2000打9.8折    
    ELSE   
        SET @i = @Amount   
    RETURN (@i)  
END



自定义函数语法

Scalar Functions
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
                function_body 
        RETURN scalar_expression
    END
[ ; ]

Inline Table-valued Functions
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Multistatement Table-valued Functions
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
  ]
)
RETURNS @return_variable TABLE < table_type_definition >
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
                function_body 
        RETURN
    END
[ ; ]

CLR Functions
CREATE FUNCTION [ schema_name. ] function_name 
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type 
        [ = default ] } 
    [ ,...n ]
)
RETURNS { return_data_type | TABLE <clr_table_type_definition> }
    [ WITH <clr_function_option> [ ,...n ] ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

Method Specifier
<method_specifier>::=
    assembly_name.class_name.method_name

Function Options
<function_option>::= 
{
    [ ENCRYPTION ]
  | [ SCHEMABINDING ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

<clr_function_option>::=
}
    [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

Table Type Definitions
<table_type_definition>:: = 
( { <column_definition> <column_constraint> 
  | <computed_column_definition> } 
        [ <table_constraint> ] [ ,...n ]
) 

<clr_table_type_definition>::= 
( { column_name data_type } [ ,...n ] )

<column_definition>::=
{
    { column_name data_type }
    [ [ DEFAULT constant_expression ] 
      [ COLLATE collation_name ] | [ ROWGUIDCOL ]
    ]
    | [ IDENTITY [ (seed , increment ) ] ]
    [ <column_constraint> [ ...n ] ] 
}
<column_constraint>::= 
{
    [ NULL | NOT NULL ] 
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ] 
        [ WITH FILLFACTOR = fillfactor 
        | WITH ( < index_option > [ , ...n ] )
      [ ON { filegroup | "default" } ]
  | [ CHECK ( logical_expression ) ] [ ,...n ]
}

<computed_column_definition>::=
column_name AS computed_column_expression 

<table_constraint>::=
{ 
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ] 
            ( column_name [ ASC | DESC ] [ ,...n ] )
        [ WITH FILLFACTOR = fillfactor 
        | WITH ( <index_option> [ , ...n ] )
  | [ CHECK ( logical_expression ) ] [ ,...n ]
}

<index_option>::=
{ 
    PAD_INDEX = { ON | OFF }   | FILLFACTOR = fillfactor 
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS ={ ON | OFF } 
}

 

本文转载自:http://blog.csdn.net/smartsmile2012/article/details/9284669

共有 人打赏支持
深圳大道
粉丝 3
博文 877
码字总数 0
作品 0
深圳
架构师
私信 提问
sqlserver,你的delete语句表名为什么不能别名?

前几天写了几行sql,用于清除环境里面的脏数据,平时开发用的是oracle数据库,看了下里面的语句,处理concat字符串拼接函数看起来稍微特殊点,其它都没有啥问题。百度了下,cancat可以用在收...

heshifk
2018/04/23
0
0
在Linux上使用环境变量配置SQL Server设置

你可以使用不同的环境变量在Linux上配置SQL Server 2017。这些变量用于两个场景: 使用mssql-conf setup命令配置初始化设置。 配置一个新的Docker容器中的SQL Server。 环境变量 示例:初始设...

UltraSQL
2018/07/30
0
0
WAF Bypass数据库特性(MSsql探索篇)

0x01 背景 探索玩了Mysql特性,继续来探索一下MSsql特性。 0x02 测试 常见有5个位置即:select * from admin where id=1【位置一】union【位置二】select【位置三】1,2,db_name()【位置四】f...

bypass
2017/07/02
0
0
拦截SQLSERVER的SSL加密通道替换传输过程中的用户名密码实现运维审计(一)

工作准备 •一台SQLSERVER 2005/SQLSERVER 2008服务 •SQLSERVER jdbc驱动程序 •Java开发环境eclipse + jdk1.8 •java反编译工具JD-Core 反编译JDBC分析SQLSERVER客户端与服务器通信原理 SQ...

紅顏為君笑
2018/07/20
0
0
php连接mssql数据库的几种方式

数据库查询不外乎4个步骤,1、建立连接。2、输入查询代码。3、建立查询并取出数据。4、关闭连接。 php连接mssql数据库有几个注意事项,尤其mssql的多个版本、32位、64位都有区别。 首先,php...

blazeq
2013/02/21
0
0

没有更多内容

加载失败,请刷新页面

加载更多

带你看数据挖掘与机器学习-厦大EDP上课出勤预测

带你看数据挖掘与机器学习-厦大EDP上课出勤预测 标签: 数据挖掘 特征工程 机器学习 出勤预测 write by xmhexi 2019/3/22 内容提要 首先说明本文是一篇科普文章,通过一个实际案例,帮助理解什...

xmhexi
今天
43
0
IOS  学习记录

1.StackView=>IOS 9及以上支持 2.布局方式: AutoLayout / StackView 堆布局 (线性布局) 3.屏幕适配 (资源分辨率、设计分辨率、屏幕分辨率) Size Class技术 可以针对 屏幕的方向进行设置...

萨x姆
今天
4
0
第四次工业革命:自主经济的崛起

https://36kr.com/p/5170370.html

shengjuntu
昨天
3
0
Cloud Native 与12-Factor

12-Factor(twelve-factor),也称为“十二要素”,是一套流行的应用程序开发原则。Cloud Native架构中使用12-Factor作为设计准则。 12-Factor 的目标在于: 使用标准化流程自动配置,从而使...

waylau
昨天
10
0
java多线程2

“非线程安全”问题存在于“实例变量”中,如果是方法内部的私有变量,则不存在线程安全问题。这是因为方法内部的变量都是私有造成的。 synchronized 获取的都是对象锁。如果多个线程访问多个...

一滴水穿石
昨天
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部