数据库原理与应用实验五报告

数据库系统概论实验报告（五）
姓名：田垒
班级学号： 2010080405226
学院：信息学院
专业：计算机科学与技术
2010年12月12日
实验五、用户自定义函数
一、实验目的
学习、掌握用户自定义函数的建立和使用
二、实验平台
操作系统：Windows 2000或者Windows XP。

数据库管理系统：Microsoft SQL Server 2000 或Microsoft SQL Server 2005。

三、实验内容
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
SELECT ProductName, UnitPrice,Northwind.dbo.fn_TaxRate(ProductID) AS TaxRate,UnitPrice * Northwind.dbo.fn_TaxRate(ProductID) AS PriceWithTax FROM Products
结果：
注意：函数可以在Select子句后面调用。

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

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
)
SELECT * FROM fn_LargeFreight(600)
结果：
3、返回值为多值的自定义函数
本实验创建的函数也是返回多列多值，注意与上面实验的差别。

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
SELECT EmployeeID, [Name], Title, MgrEmployeeID FROM dbo.fn_FindReports(5)
结果：
4．设计一个函数，在OrderMag数据库中，输入零件类别，返回该类别零件的平均存量、最高存量和该类零件的总数量。

USE OrderMag
GO
CREATE FUNCTION fpnum
(@fPtype varchar(60))
RETURNS TABLE
AS
RETURN
( SELECT avg(S.Pnum) avgnum,max(S.Pnum) maxnum,sum(S.Pnum) sumnum
FROM Store AS S WHERE S.Ptype= @fPtype
)
SELECT * FROM fpnum('传动')
结果：
5．设计一个函数，在OrderMag数据库中，输入订单号，返回该订单所涉及的零件名称和类别。

USE OrderMag
GO
CREATE FUNCTION fpname
(@fOno varchar(60))
RETURNS TABLE
AS
RETURN
( select O.Ono,S.Pname,S.Ptype from Store AS S join Orders As O On O.Pno=S.Pno where O.Ono= @fOno
)
select * from fpname('O1')
结果：
6．设计一个函数，在OrderMag数据库中，根据零件库存量的大小，大于500的认为是充足，在100-500之间的是均衡，小于100的为面临缺货。

USE OrderMag
GO
CREATE FUNCTION fpnum4()
RETURNS Table
AS
RETURN
(SELECT
'Pnum Range'=
CASE
WHEN Pnum>500 THEN '充足'
WHEN Pnum BETWEEN 100 and 500 THEN '均衡'
WHEN Pnum<100 THEN '面临缺货'
END
FROM Store
)
select * from fpnum4()
结果：
7．设计一个函数，根据输入的数值，计算从1加到该数的和（如输入5，则计算1+2+3+4+5=15,输出为15）。

create function sumn(@num int)
returns int
as
begin
declare @i int;
declare @s int;
set @i=1;
set @s=0;
while (@i<=@num)
begin
set @s=@s+@i;
set @i=@i+1;
end
return @s
end
print '从1加到该数的和是：'+cast(dbo.sumn(5) as varchar)
结果：
四、实验中遇到的问题
1．用户自定义函数在定义与使用上有何需要注意的问题？与存储过程有何不同？
答：在SQL SERVER中调用自定义函数时，必须在自定义函数前加上创建此函数的用户
存储过程:
存储过程可以使得对数据库的管理、以及显示关于数据库及其用户信息的工作容易得多。

存储过程是 SQL 语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。

存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其它强大的编程功能。

存储过程可包含程序流、逻辑以及对数据库的查询。

它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。

可以出于任何使用 SQL 语句的目的来使用存储过程，它具有以下优点：
1、可以在单个存储过程中执行一系列 SQL 语句。

2、可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。

3、存储过程在创建时即在服务器上进行编译，所以执行起来比单个 SQL 语句快。

用户定义函数：Microsoft SQL Server 2000 允许创建用户定义函数。

与任何函数一样，用户定义函数是可返回值的例程。

根据所返回值的类型，每个用户定义函数可分成以下三个类别：
1、返回可更新数据表的函数
如果用户定义函数包含单个 SELECT 语句且该语句可更新，则该函数返回的表格格式结果也可以更新。

2、返回不可更新数据表的函数
如果用户定义函数包含不止一个 SELECT 语句，或包含一个不可更新的 SELECT 语句，则该函数返回的表格格式结果也不可更新。

3、返回标量值的函数
用户定义函数可以返回标量值。

五、实验小结
通过本次实验学到的知识：
用户自定义函数的建立和使用。