当前位置:   article > 正文

数据库原理与应用(SQL Server)笔记——第十三章 用户定义函数_sql标量函数

sql标量函数


前言

本章内容将介绍数据库用户自定义T-SQL函数,以及其定义和调用。

一、用户定义函数的定义

用户定义函数,即是用户根据自己需要所定义的函数,它有允许模块化程序设计、执行速度快、减少网络流量等特点。创建好的用户定义函数可在当前数据库——可编程性——函数中找到,如下图:
在这里插入图片描述

二、用户定义函数的分类

用户定义函数分为两类,为内联表值函数多语句表值函数

三、标量函数和内联表值函数

内联表值函数是在RETURN 子句中包含单个SELECT语句。

(一)标量函数的定义

标量函数返回在RETURNS 子句中定义的类型的单个数据值,即返回单个数据值
格式如下:

CREATE FUNCTION <函数名>(@参数的名称 类型)
RETURNS <返回参数的类型>
AS
	BEGIN
		<函数体(SQL语句)>
		RETURN <返回值>
	END
;
...

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

(二)标量函数的调用

1、SELECT语句调用

格式如下:
架构名.函数名(实参1,实参2,...,实参n)

2、EXEC语句调用

格式如下:
EXEC变量名=架构名.函数名 实参1,实参2,...,实参n
EXEC变量名=架构名.函数名 形参名1=实参1,...,形参名2=实参2,...,形参名n=实参n

例1、根据商品信息表,定义一个标量函数F_Sales,其功能是:输入商品的ID号,根据ID号返回该商品的价格。
在这里插入图片描述sql语句
创建函数:
CREATE FUNCTION F_Sales(@ProductID char(6)) RETURNS int AS BEGIN DECLARE @Price int SELECT @Price=Price FROM Product WHERE ProductID=@ProductID RETURN @Price END
用SELECT语句调用函数(查询ID为P01001的商品价格):
USE Sales DECLARE @ProductID char(6) DECLARE @Price int SELECT @ProductID='P01001' SELECT @Price=dbo.F_Sales(@ProductID) SELECT @Price AS '商品价格'

在这里插入图片描述
这里当然也可以使用EXEC语句来调用函数即改为,结果也是一样的(查询ID为P01001的商品价格):
USE Sales DECLARE @ProductID char(6) DECLARE @Price int EXEC @Price=dbo.F_Sales @ProductID='P01001' SELECT @Price AS '商品价格'
在这里插入图片描述

(三)内联表值函数的定义

标量函数只返回单个标量值,而对于内联表值函数返回表值(结果集)
格式如下:

CREATE FUNCTION <函数名>(@参数的名称 类型)
RETURNS TABLE
AS
RETURN 
(
	<SQL语句>
)
;
...

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

(四)内联表值函数的调用

这里要注意,内联表值函数的调用与标量函数的调用不一样,它只能通过SELECT语句来调用,而且在调用时可以只使用函数的名称

例2、根据商品信息表,定义一个内联表值函数F_Sales1,其功能是:输入商品的ID号,根据ID号查询该商品的商品名称、商品价格和商品的库存量。
在这里插入图片描述
sql语句
创建函数:
CREATE FUNCTION F_Sales1(@ProductID char(6)) RETURNS TABLE AS RETURN ( SELECT ProductName,Price,Stocks FROM Product WHERE @ProductID=ProductID
用SELECT语句调用函数(查询ID为P01001的商品名称、商品价格和商品的库存量):
USE Sales SELECT *FROM F_Sales1('P01001')
在这里插入图片描述

四、多语句表值函数

(一)多语句表值函数的定义

多语句表值函数和内联表值函数都返回表值。这里要说明一下它们的区别
对于内联表值函数,它不需要定义返回表的类型,其返回表是由单个T-SQL语句的结果集,不需要用BEGIN...END语句分隔。
对于多语句标量函数,它需要定义返回表的类型,其返回表是由多个T-SQL语句的结果集,其BEGIN...END语句中包含多个T-SQL语句。
格式如下:

CREATE FUNCTION <函数名>(@参数的名称 类型)
RETURNS <@返回表的名称> TABLE<列属性>AS
BEGIN
	<函数体(SQL语句)>
	RETURN
END
;
...

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13

(二)多语句表值函数的调用

多语句表值函数的调用与内联表值函数的调用一样,它也是只能通过SELECT语句来调用,而且在调用时可以只使用函数的名称

例3、根据商品信息表,定义一个多语句表值函数F_Sales2,其功能是:输入商品的ID号,根据ID号查询该商品的商品名称、商品分类、商品价格和商品的库存量。
在这里插入图片描述
sql语句

创建函数:
CREATE FUNCTION F_Sales2(@ProductID char(6)) RETURNS @ProductInfo TABLE ( PName varchar(30), CID int, Pr money, St smallint ) AS BEGIN INSERT @ProductInfo SELECT ProductName,CategoryID,Price,Stocks FROM Product WHERE @ProductID=ProductID RETURN END
用SELECT语句调用函数(查询ID为P03001的商品名称、商品分类、商品价格和商品的库存量):
USE Sales SELECT * FROM F_Sales2('P03001')
在这里插入图片描述

五、用户定义函数的删除

我们可以通过对象资源管理器删除所定义的函数,如下图:
在这里插入图片描述
也可以通过T-SQL语句进行删除,可一次删除一个或者多个函数,格式如下:

DROP FUNCTION <函数的名称>...

  • 1
  • 2

结语

以上就是本次数据库原理与应用的全部内容,篇幅较长,感谢您的阅读和支持,若有表述或代码中有不当之处,望指出!您的指出和建议能给作者带来很大的动力!!!

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/煮酒与君饮/article/detail/748638
推荐阅读
相关标签
  

闽ICP备14008679号