实验五 创建函数、存储过程、触发器.doc

实验五 创建函数、存储过程、触发器一、目标 完成这个实验后，你将能够：1、 创建函数2、 创建存储过程二、实验内容安装Northwind数据库。安装library数据库。练习1：创建函数1、 创建标量函数，返回指定产品（给定ProductID）的销售总量。CREATE FUNCTION ufnGetSell(ProductID int)RETURNS intBEGIN DECLARE ret int; SELECT ret=SUM(OD.Quantity) FROM Order Details OD WHERE ProductID=ProductID IF(ret IS NULL) SET ret=0 RETURN retENDSELECT ProductID,dbo.ufnGetSell(ProductID)FROM dbo.Order Details2、 在前面设计的标量函数中，增加ENCRYPTION及SCHEMABINDING选项，验证该选项含义。加密：ALTER FUNCTION ufnGetSell(ProductID int)RETURNS intWITH ENCRYPTIONBEGIN DECLARE ret int; SELECT ret=SUM(OD.Quantity) FROM Order Details OD WHERE ProductID=ProductID IF(ret IS NULL) SET ret=0 RETURN retEND架构绑定：ALTER FUNCTION ufnGetSell(ProductID int)RETURNS intWITH SCHEMABINDINGBEGIN DECLARE ret int; SELECT ret=SUM(OD.Quantity) FROM dbo.Order Details OD WHERE ProductID=ProductID IF(ret IS NULL) SET ret=0 RETURN retEND3、 调用该函数，输出每种产品的编号、名称及销售总量（TotalSale）。SELECT OD.ProductID,ProductName,dbo.ufnGetSell(OD.ProductID) TotalSaleFROM dbo.Order Details OD JOIN dbo.Products P ON OD.ProductID=P.ProductIDGROUP BY OD.ProductID,ProductName4、 创建内联表值函数，返回指定供应商（给定SupplierID）供应产品的编号、名称及销售总量。CREATE FUNCTION dbo.fnSales(SupplierID int)RETURNS TABLERETURN ( SELECT S.SupplierID,P.ProductID,ProductName,SUM(OD.Quantity) TotalSale FROM Suppliers S JOIN Products P ON S.SupplierID=P.SupplierID JOIN Order Details OD ON OD.ProductID=P.ProductID WHERE S.SupplierID=SupplierID GROUP BY S.SupplierID,P.ProductID,ProductName)5、 调用该函数，输出11号供应商供应产品的编号、名称及销售总量。SELECT *FROM dbo.fnSales(11)6、 在AdventureWorks数据库中，创建多语句表值函数ufnGetContactInformation（文件ufnGetContactInformation.txt），读懂该脚本。CREATE FUNCTION dbo.ufnGetContactInformation(ContactID int)RETURNS retContactInformation TABLE ( - Columns returned by the function ContactID int PRIMARY KEY NOT NULL, FirstName nvarchar(50) NULL, LastName nvarchar(50) NULL, JobTitle nvarchar(50) NULL, ContactType nvarchar(50) NULL)AS - Returns the first name, last name, job title and contact type for the specified contact.BEGIN DECLARE FirstName nvarchar(50), LastName nvarchar(50), JobTitle nvarchar(50), ContactType nvarchar(50); - Get common contact information SELECT ContactID = ContactID, FirstName = FirstName, LastName = LastName FROM Person.Contact WHERE ContactID = ContactID; SET JobTitle = CASE - Check for employee WHEN EXISTS(SELECT * FROM HumanResources.Employee e WHERE e.ContactID = ContactID) THEN (SELECT Title FROM HumanResources.Employee WHERE ContactID = ContactID) - Check for vendor WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc INNER JOIN Person.ContactType ct ON vc.ContactTypeID = ct.ContactTypeID WHERE vc.ContactID = ContactID) THEN (SELECT ct.Name FROM Purchasing.VendorContact vc INNER JOIN Person.ContactType ct ON vc.ContactTypeID = ct.ContactTypeID WHERE vc.ContactID = ContactID) - Check for store WHEN EXISTS(SELECT * FROM Sales.StoreContact sc INNER JOIN Person.ContactType ct ON sc.ContactTypeID = ct.ContactTypeID WHERE sc.ContactID = ContactID) THEN (SELECT ct.Name FROM Sales.StoreContact sc INNER JOIN Person.ContactType ct ON sc.ContactTypeID = ct.ContactTypeID WHERE ContactID = ContactID) ELSE NULL END; SET ContactType = CASE - Check for employee WHEN EXISTS(SELECT * FROM HumanResources.Employee e WHERE e.ContactID = ContactID) THEN Employee - Check for vendor WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc INNER JOIN Person.ContactType ct ON vc.ContactTypeID = ct.ContactTypeID WHERE vc.ContactID = ContactID) THEN Vendor Contact - Check for store WHEN EXISTS(SELECT * FROM Sales.StoreContact sc INNER JOIN Person.ContactType ct ON sc.ContactTypeID = ct.ContactTypeID WHERE sc.ContactID = ContactID) THEN Store Contact - Check for individual consumer WHEN EXISTS(SELECT * FROM Sales.Individual i WHERE i.ContactID = ContactID) THEN Consumer END; - Return the information to the caller IF ContactID IS NOT NULL BEGIN INSERT retContactInformation SELECT ContactID, FirstName, LastName, JobTitle, ContactType; END; RETURN;END;7、 调用多语句表值函数SELECT * FROM dbo.ufnGetContactInformation(100)8、 修改ufnGetContactInformation，返回所有联系人的相关信息。调用该函数。ALTER FUNCTION dbo.ufnGetContactInformation(ContactID int)RETURNS retContactInformation TABLE ( - Columns returned by the function ContactID int PRIMARY KEY NOT NULL, FirstName nvarchar(50) NULL, LastName nvarchar(50) NULL, JobTitle nvarchar(50) NULL, ContactType nvarchar(50) NULL)AS - Returns the first name, last name, job title and contact type for the specified contact.BEGIN DECLARE FirstName nvarchar(50), LastName nvarchar(50), JobTitle nvarchar(50), ContactType nvarchar(50); - Get common contact information DECLARE contact_cursor CURSOR FOR SELECT ContactID,FirstName,LastName FROM Person.Contact WHERE ContactID = ContactID OPEN contact_cursor FETCH NEXT FROM contact_cursor INTO LastName, FirstName WHILE FETCH_STATUS = 0 BEGIN FETCH NEXT FROM contact_cursor INTO LastName, FirstName END CLOSE contact_cursor DEALLOCATE contact_cursor; SET JobTitle = CASE - Check for employee WHEN EXISTS(SELECT * FROM HumanResources.Employee e WHERE e.ContactID = ContactID) THEN (SELECT Title FROM HumanResources.Employee WHERE ContactID = ContactID) - Check for vendor WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc INNER JOIN Person.ContactType ct ON vc.ContactTypeID = ct.ContactTypeID WHERE vc.ContactID = ContactID) THEN (SELECT ct.Name FROM Purchasing.VendorContact vc INNER JOIN Person.ContactType ct ON vc.ContactTypeID = ct.ContactTypeID WHERE vc.ContactID = ContactID) - Check for store WHEN EXISTS(SELECT * FROM Sales.StoreContact sc INNER JOIN Person.ContactType ct ON sc.ContactTypeID = ct.ContactTypeID WHERE sc.ContactID = ContactID) THEN (SELECT ct.Name FROM Sales.StoreContact sc INNER JOIN Person.ContactType ct ON sc.ContactTypeID = ct.ContactTypeID WHERE ContactID = ContactID) ELSE NULL END; SET ContactType = CASE - Check for employee WHEN EXISTS(SELECT * FROM HumanResources.Employee e WHERE e.ContactID = ContactID) THEN Employee - Check for vendor WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc INNER JOIN Person.ContactType ct ON vc.ContactTypeID = ct.ContactTypeID WHERE vc.ContactID = ContactID) THEN Vendor Contact - Check for store WHEN EXISTS(SELECT * FROM Sales.StoreContact sc INNER JOIN Person.ContactType ct ON sc.ContactTypeID = ct.ContactTypeID WHERE sc.ContactID = ContactID) THEN Store Contact - Check for individual consumer WHEN EXISTS(SELECT * FROM Sales.Individual i WHERE i.ContactID = ContactID) THEN Consumer END; - Return the information to the caller IF ContactID IS NOT NULL BEGIN I