聯(lián)大數(shù)據(jù)庫-實驗五實驗報告

1、實驗五用戶自定義函數(shù)（2學時）一、實驗目的：學習、掌握用戶自定義函數(shù)的建立和使用二、實驗內容：1、創(chuàng)建自定義函數(shù)創(chuàng)建一個用戶自定義函數(shù)，并測試、查看函數(shù)返回值。1）輸入并執(zhí)行下面語句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） 此函數(shù)中輸入變量是什么？返回值類型？如何定義的返回值？答：此函數(shù)輸入變量是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） 輸入并執(zhí)行語句測試函數(shù)SELECTProductName, UnitPrice,Northwind.dbo.fn_TaxRate(ProductID) AS TaxRate,UnitPrice * Northwind.dbo.fn_TaxRate(ProductID) AS PriceWithTaxFROM Products查看結果。注意：函數(shù)可以在Select子句后面調用。2、返回值為多值的自定義函數(shù)創(chuàng)建函數(shù)返回多列多值。1）輸入并執(zhí)行下面語句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)函數(shù)中輸入變量是什么？返回值類型？如何定義的返回值？答：答：函數(shù)中輸入變量是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）輸入并執(zhí)行語句測試函數(shù)SELECT * FROM fn_LargeFreight(600)查看結果3、返回值為多值的自定義函數(shù)本實驗創(chuàng)建的函數(shù)也是返回多列多值，注意與上面實驗的差別。1）輸入并執(zhí)行下面語句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此函數(shù)中輸入變量是什么？返回值類型是什么？如何定義的返回值？答：此函數(shù)中輸入變量是InEmployeeID，返回值類型是TABLE，定義返回值：INSERT reports SELECT EmployeeID, Name = FirstName + + LastName, Title, ReportsTo, 0 FROM EMPLOYEES WHERE ReportsTo = InEmployeeID 3）輸入并執(zhí)行語句測試函數(shù)SELECT EmployeeID,Name,Title,MgrEmployeeID FROM dbo.fn_FindReports(5)查看結果。4 設計一個函數(shù)

8、，在OrderMag數(shù)據(jù)庫中，輸入零件類別，返回該類別零件的平均存量、最高存量和該類零件的總數(shù)量。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 設計一個函數(shù)，在OrderMag數(shù)據(jù)庫中，輸入訂單號，返回該訂單所涉及的零件名稱和類別。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 設計一個函數(shù)，在OrderMag數(shù)據(jù)庫中，根據(jù)零件庫存量的大小，大于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 設計一個函數(shù)，根據(jù)輸入的數(shù)值，計算從1加到該數(shù)的和（如輸入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加到該數(shù)的和是：+cast(dbo.sumn(5) as varchar) 三、完成實驗報告（1）回答實驗指導書中提出的問題（2）用戶自定義函數(shù)在定義與使用上有何需要注意的問題？與存儲過程有何不同？答：在SQL SERVER中調用自定義函數(shù)時，必須在自定義函數(shù)前加上創(chuàng)建此函數(shù)的用戶存儲過程: 存儲過程可以使得對數(shù)據(jù)庫的管理、以及顯示關于數(shù)據(jù)庫及其用戶信息的工作容易得多。存儲過程是SQL 語句和可選控制流語句的預編譯集合，以一

12、個名稱存儲并作為一個單元處理。存儲過程存儲在數(shù)據(jù)庫內，可由應用程序通過一個調用執(zhí)行，而且允許用戶聲明變量、有條件執(zhí)行以及其它強大的編程功能。 存儲過程可包含程序流、邏輯以及對數(shù)據(jù)庫的查詢。它們可以接受參數(shù)、輸出參數(shù)、返回單個或多個結果集以及返回值。 可以出于任何使用SQL 語句的目的來使用存儲過程，它具有以下優(yōu)點：1、 可以在單個存儲過程中執(zhí)行一系列SQL 語句。2、可以從自己的存儲過程內引用其它存儲過程，這可以簡化一系列復雜語句。3、存儲過程在創(chuàng)建時即在服務器上進行編譯，所以執(zhí)行起來比單個SQL 語句快。 用戶定義函數(shù)：Microsoft SQL Server 2000 允許創(chuàng)建用戶定義函數(shù)。與任何函數(shù)一樣，用戶定義函數(shù)是可返回值的例程。根據(jù)所返回值的類型