数据库实验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, https://www.360docs.net/doc/1a14142387.html,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中调用，而存储过程不行。