数据库实验5报告

北京联合大学信息学院实验报告
题目：实验五用户自定义函数
系别：软件系
专业：软件工程
班级：软件1203B
学号：20120803600
姓名：
同组人：无
指导教师：果晓来
2014年5月21日
5．用户自定义函数（2学时）
实验目的：
学习、掌握用户自定义函数的建立和使用
实验内容：
1、创建自定义函数
创建一个用户自定义函数，并测试、查看函数返回值。

1）输入并执行下面语句
USE Northwind
GO
CREATE FUNCTION fn_TaxRate
(@ProdID INT)
RETURNS numeric(5,4)
AS
BEGIN
RETURN
(SELECT
CASE CategoryID
WHEN 1 THEN 1.10
WHEN 2 THEN 1
WHEN 3 THEN 1.10
WHEN 4 THEN 1.05
WHEN 5 THEN 1
WHEN 6 THEN 1.05
WHEN 7 THEN 1
WHEN 8 THEN 1.05
END
FROM Products
WHERE ProductID = @ProdID)
END
GO
2）此函数中输入变量是什么？返回值类型？如何定义的返回值？
输入变量：@ProdID 返回类型：numeric 定义的返回值：RETURNS numeric(5,4)
3）输入并执行语句测试函数
SELECT ProductName, UnitPrice,Northwind.dbo.fn_TaxRate(ProductID) AS TaxRate, UnitPrice * Northwind.dbo.fn_TaxRate(ProductID) AS PriceWithTax
FROM Products
注意：函数可以在Select子句后面调用。

2、返回值为多值的自定义函数
创建函数返回多列多值。

1）输入并执行下面语句
USE Northwind
GO
CREATE FUNCTION fn_LargeFreight
(@FreightAmt money)
RETURNS TABLE
AS
RETURN
( SELECT S.ShipperID, panyName,
O.OrderID, O.ShippedDate, O.Freight
FROM Shippers AS S JOIN Orders AS O
ON S.ShipperID = O.ShipVia
WHERE O.Freight > @FreightAmt
)
函数中输入变量是什么？返回值类型？如何定义的返回值？
输入变量：@FreightAmt 返回值类型：table 定义的返回值：returns table 3）输入并执行语句测试函数
SELECT * FROM fn_LargeFreight(600)
3、返回值为多值的自定义函数
本实验创建的函数也是返回多列多值，注意与上面实验的差别。

1）输入并执行下面语句
USE Northwind
GO
CREATE FUNCTION fn_FindReports (@InEmployeeID char(5))
RETURNS @reports TABLE
(EmployeeID char(5) PRIMARY KEY,
Name nvarchar(40) NOT NULL,
Title nvarchar(30),
MgrEmployeeID int,
processed tinyint default 0)
AS
BEGIN
INSERT @reports
SELECT EmployeeID, Name = FirstName + ' ' + LastName, Title, ReportsTo, 0
FROM EMPLOYEES WHERE ReportsTo = @InEmployeeID
RETURN
END
GO
此函数中输入变量是什么？返回值类型是什么？如何定义的返回值？
输入变量：@InEmployeeID 返回类型：table 定义的返回值：通过子查询
RETURNS @reports TABLE
(EmployeeID char(5)PRIMARY KEY,
Name nvarchar(40)NOT NULL,
Title nvarchar(30),
MgrEmployeeID int,
processed tinyint default 0)
3）输入并执行语句测试函数
SELECT EmployeeID, [Name], Title, MgrEmployeeID FROM dbo.fn_FindReports(5)
查看结果。

最高存量和该类零件的总数量。

5．设计一个函数，在OrderMag数据库中，输入订单号，返回该订单所涉及的零件名称和类别。

6．设计一个函数，在OrderMag数据库中，根据零件库存量的大小，大于500的认为是充足，在100-500之间的是均衡，小于100的为面临缺货。

7．设计一个函数，根据输入的数值，计算从1加到该数的和（如输入5，则计算1+2+3+4+5=15,输出为15）。

8．完成实验报告
（1）回答实验指导书中提出的问题
（2）用户自定义函数在定义与使用上有何需要注意的问题？与存储过程有何不同？
答：用户自定义函数不能用于执行一系列改变数据库状态的操作，但它可以像系统函数一样在查询或存储过程等的程序段中使用，也可以像存储过程一样通过EXECUTE命令来执行。

与存储过程的区别：函数只能返回一个变量的限制。

而存储过程可以返回多个。

而函数是可以嵌入在sql中使用的，可以在select中调用，而存储过程不行。