联大数据库-实验五实验报告

1、实验五用户自定义函数（2学时）一、实验目的：学习、掌握用户自定义函数的建立和使用二、实验内容：1、创建自定义函数创建一个用户自定义函数，并测试、查看函数返回值。1）输入并执行下面语句USE NorthwindGOCREATE FUNCTION fn_TaxRate (ProdID INT)RETURNS numeric(5,4)ASBEGINRETURN(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 WH

2、EN 7 THEN 1 WHEN 8 THEN 1.05 ENDFROM Products WHERE ProductID = ProdID)ENDGO2） 此函数中输入变量是什么？返回值类型？如何定义的返回值？答：此函数输入变量是ProdID，返回值类型是numeric(5,4) 定义返回值：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 EN

3、D FROM Products WHERE ProductID = ProdID 3） 输入并执行语句测试函数SELECTProductName, UnitPrice,Northwind.dbo.fn_TaxRate(ProductID) AS TaxRate,UnitPrice * Northwind.dbo.fn_TaxRate(ProductID) AS PriceWithTaxFROM Products查看结果。注意：函数可以在Select子句后面调用。2、返回值为多值的自定义函数创建函数返回多列多值。1）输入并执行下面语句USE NorthwindGOCREATE FUNCTION

4、fn_LargeFreight (FreightAmt money)RETURNS TABLEAS RETURN( SELECTS.ShipperID, S.CompanyName,O.OrderID, O.ShippedDate, O.Freight FROM Shippers AS S JOIN Orders AS O ON S.ShipperID = O.ShipVia WHERE O.Freight FreightAmt)函数中输入变量是什么？返回值类型？如何定义的返回值？答：答：函数中输入变量是FreightAmt，返回值类型是money 定义返回值：SELECT S.Shipper

5、ID, S.CompanyName, O.OrderID, O.ShippedDate, O.Freight FROM Shippers AS S JOIN Orders AS O ON S.ShipperID = O.ShipVia WHERE O.Freight FreightAmt2）输入并执行语句测试函数SELECT * FROM fn_LargeFreight(600)查看结果3、返回值为多值的自定义函数本实验创建的函数也是返回多列多值，注意与上面实验的差别。1）输入并执行下面语句USE NorthwindGOCREATE FUNCTION fn_FindReports (InEmp

6、loyeeIDchar(5)RETURNS reports TABLE(EmployeeIDchar(5) PRIMARY KEY,Name nvarchar(40) NOT NULL,Title nvarchar(30),MgrEmployeeIDint,processedtinyint default 0)ASBEGININSERT reportsSELECT EmployeeID, Name = FirstName + + LastName, Title, ReportsTo, 0FROM EMPLOYEESWHERE ReportsTo = InEmployeeIDRETURN END

7、GO此函数中输入变量是什么？返回值类型是什么？如何定义的返回值？答：此函数中输入变量是InEmployeeID，返回值类型是TABLE，定义返回值：INSERT reports SELECT EmployeeID, Name = FirstName + + LastName, Title, ReportsTo, 0 FROM EMPLOYEES WHERE ReportsTo = InEmployeeID 3）输入并执行语句测试函数SELECT EmployeeID,Name,Title,MgrEmployeeID FROM dbo.fn_FindReports(5)查看结果。4 设计一个函数

8、，在OrderMag数据库中，输入零件类别，返回该类别零件的平均存量、最高存量和该类零件的总数量。CREATE FUNCTION fund_Pno (Pptype char(10) RETURNS TABLE AS RETURN ( SELECT avg(Pnum) Avgp,max(Pnum) Maxp,sum(Pnum) Sump FROM Store WHERE Ptype= Pptype ) select * from fund_Pno(传动)5 设计一个函数，在OrderMag数据库中，输入订单号，返回该订单所涉及的零件名称和类别。USE OrderMag GO CREATE FUN

9、CTION 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. where O.Ono= fOno ) select * from fpname(O1) 6 设计一个函数，在OrderMag数据库中，根据零件库存量的大小，大于500的认为是充足，在100-500之间的是均衡，小于100的为面临缺货。 USE OrderMag GO CREATE FUNCTION fpnum4() RETURNS T

10、able AS RETURN ( SELECT Pnum Range= CASE WHEN Pnum500 THEN 充足 WHEN Pnum BETWEEN 100 and 500 THEN 均衡 WHEN Pnum100 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;

11、 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）回答实验指导书中提出的问题（2）用户自定义函数在定义与使用上有何需要注意的问题？与存储过程有何不同？答：在SQL SERVER中调用自定义函数时，必须在自定义函数前加上创建此函数的用户存储过程: 存储过程可以使得对数据库的管理、以及显示关于数据库及其用户信息的工作容易得多。存储过程是SQL 语句和可选控制流语句的预编译集合，以一

12、个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其它强大的编程功能。 存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。 可以出于任何使用SQL 语句的目的来使用存储过程，它具有以下优点：1、 可以在单个存储过程中执行一系列SQL 语句。2、可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。3、存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL 语句快。 用户定义函数：Microsoft SQL Server 2000 允许创建用户定义函数。与任何函数一样，用户定义函数是可返回值的例程。根据所返回值的类型