1200字范文 > 数据库原理与应用（SQL Server）笔记第十章用户定义函数

数据库原理与应用（SQL Server）笔记第十章用户定义函数

时间：2023-06-06 18:28:55

前言一、用户定义函数的定义二、用户定义函数的分类三、标量函数和内联表值函数（一）标量函数的定义（二）标量函数的调用1、SELECT语句调用2、EXEC语句调用（三）内联表值函数的定义（四）内联表值函数的调用四、多语句表值函数（一）多语句表值函数的定义（二）多语句表值函数的调用五、用户定义函数的删除结语

前言

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

一、用户定义函数的定义

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

二、用户定义函数的分类

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

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

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

（一）标量函数的定义

标量函数返回在RETURNS 子句中定义的类型的单个数据值，即返回单个数据值。

格式如下：

CREATE FUNCTION <函数名>（@参数的名称类型）RETURNS <返回参数的类型>ASBEGIN<函数体（SQL语句）>RETURN <返回值>END;...

（二）标量函数的调用

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 TABLEASRETURN (<SQL语句>);...

（四）内联表值函数的调用

这里要注意，内联表值函数的调用与标量函数的调用不一样，它只能通过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（<列属性>）ASBEGIN<函数体（SQL语句）>RETURNEND;...

（二）多语句表值函数的调用

多语句表值函数的调用与内联表值函数的调用一样，它也是只能通过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')