用户自定义函数存储过程和触发器Word下载.docx
《用户自定义函数存储过程和触发器Word下载.docx》由会员分享，可在线阅读，更多相关《用户自定义函数存储过程和触发器Word下载.docx（18页珍藏版）》请在冰豆网上搜索。
END
FROMProducts
WHEREProductID=@ProdID）
END
2）此函数中输入变量是什么？
返回值类型？
如何定义的返回值？
输入变量：
@ProdIDINT返回值类型：
numeric（5,4）定义返回值：
利用一个case…end分支语句来定义返回值
3）输入并执行语句测试函数
SELECTProductName,UnitPrice,Northwind.dbo.fn_TaxRate（ProductID）
ASTaxRate,UnitPrice*Northwind.dbo.fn_TaxRate（ProductID）ASPriceWithTax
FROMProducts
查看结果
2.返回值为多值的自定义函数
创建函数返回多列多值。
CREATEFUNCTIONfn_LargeFreight
（@FreightAmtmoney）
RETURNSTABLE
AS
（SELECTS.ShipperID,S.CompanyName,
O.OrderID,O.ShippedDate,O.Freight
FROMShippersASSJOINOrdersASO
ONS.ShipperID=O.ShipVia
WHEREO.Freight>
@FreightAmt
）
2）函数中输入变量是什么？
输入变量：
@FreightAmtmoney返回值类型：
TABLE定义返回值：
将SELECT语句的查询结果作为函数的返回值返回。
SELECT*FROMfn_LargeFreight（600）
3.返回值为多值的自定义函数
本实验创建的函数也是返回多列多值，注意与上面实验的差别。
CREATEFUNCTIONfn_FindReports（@InEmployeeIDchar（5））
RETURNS@reportsTABLE
（EmployeeIDchar（5）PRIMARYKEY,
Namenvarchar（40）NOTNULL,
Titlenvarchar（30）,
MgrEmployeeIDint,
processedtinyintdefault0）
INSERT@reports
SELECTEmployeeID,Name=FirstName+'
'
+LastName,Title,ReportsTo,0
FROMEMPLOYEESWHEREReportsTo=@InEmployeeID
RETURN
返回值类型是什么？
@InEmployeeIDchar（5）返回值类型：
@reportsTABLE
定义返回值：
通过定义一个SELECT查询语句，然后在返回值之前加还有其他的T—SQL语句，最终把结果返回到@reports这个表里面。
SELECTEmployeeID,[Name],Title,MgrEmployeeIDFROMdbo.fn_FindReports（5）
查看结果。
4.设计一个函数，在OrderMag数据库中，输入零件类别，返回该类别零件的平均存量、最高存量和该类零件的总数量。
useOrderMag
go
createfunctionfn_cs
（@typechar（10））
returnstable
as
return
（selectptype,avg（pnum）平均存储量,max（pnum）最高存储量,sum（pnum）总存储量
fromstore
whereptype=@type
groupbyptype）
declare@typechar（10）
set@type='
传动'
select*fromfn_cs（@type）
5.设计一个函数，在OrderMag数据库中，输入订单号，返回该订单所涉及的零件名称和类别。
go
createfunctionfn_ono
（@nochar（4））
（selectono,pname,ptype
fromstorejoinordersonstore.pno=orders.pno
whereono=@no）
declare@nochar（4）
set@no='
O1'
select*fromfn_ono（@no）
6.设计一个函数，在OrderMag数据库中，根据零件库存量的大小，大于500的认为是充足，在100-500之间的是均衡，小于100的为面临缺货。
createfunctionfn_pj
（selectpno,pnum,
casewhenpnum>
=500then'
充足'
whenpnum>
=100then'
均衡'
whenpnum<
100then'
面临缺货'
endas状态
wherepno=@no）
declare@nochar（4）
p1'
select*fromfn_pj（@no）
7.设计一个函数，根据输入的数值，计算从1加到该数的和（如输入5，则计算1+2+3+4+5=15,输出为15）。
createfunctionsumhe
（@iint）
returnsint
as
begin
declare@aint,@sint
set@a=1
set@s=0
while（@a<
=@i）
set@s=@s+@a
set@a=@a+1
end
return@s
end
declare@iint
set@i=5
selectdbo.sumhe（@i）as总和
三、完成实验报告
1.回答实验指导书中提出的问题
2.用户自定义函数在定义与使用上有何需要注意的问题？
●定义标量值函数时：
1：
形式参数的数据类型为系统的基本标量类型，不能为Timestamp型用户定义数据类型和非标量类（如cursor和table）.2：
返回值类型为系统的标量类型，但textntextimagetimestamp除外。
3：
函数体由T—SQL语句序列组成。
●定义表值函数时，其函数体为一条SELECT语句，不使用begin和end.
●定义多语句表值函数时，returns语句要标明返回值类型为table型，指明返回表的名字和表结构定义。
●自定义函数的调用1:
必须在函数名前标注函数所有者：
dbo.2:
调用时形参和实参的名称可不同，但数据类型必须一致。
四、实验小结
从本次试验过中，我学到了如何标量值函数、表值函数、多语句表值函数（createfunctionreturnsas语句），以及这三种用户自定义函数的区别。
在实验过程中，我认为有以下几点应该注意：
自定义函数的调用必须在函数名前标注函数所有者：
dbo2：
在函数调用时形参和实参的名称可不同，但数据类型必须一致3：
表值函数中，函数体为一条select语句,不使用begin和end，RETURNS子句后面仅包含关键字table，指定返回的数据类型为table型4：
定义多语句表值函数时，RETURNS语句必须标明返回类型为table型和返回表的名字和表结构定义。
题目：
实验六．存储过程与触发器
2011年 11 月 7 日
1.了解、掌握SQL编程的特点、方法。
2.熟练掌握建立、调用存储过程的方法，学习存储的参数、返回值的使用方法。
3.了解游标的概念和使用。
4.练习创建触发器,并验证触发器的执行。
1.建立简单存储过程
创建一个简单的存储过程，了解实现存储过程的语法。
CREATEPROCEDUREFirstProc
SELECTTOP5ProductName,UnitPriceFROMProductsORDERBYUnitPricedesc
2）输入并执行如下语句：
Usenorthwind
execfirstproc
这个存储过程的含义是什么？
是否可以用视图实现同样的功能？
含义是：
在Northwind这个数据库，在Products表中，按照UnitPrice降序排列并输出前五个的产品名称和其单价。
可以用视图实现同样的功能。
createviewfirst5
2.进一步使用存储过程
当执行存储过程时，将执行时的信息返回给用户
createprocError_proc
declare@MaxPricemoney
declare@Charvarchar（20）
select@Maxprice=max（unitprice）fromproducts--找出价格最大值，并将值赋给变量
set@char=cast（@Maxpriceasvarchar（20））--转换数据类型为字符型
raiserror（'
Themaxpriceis%s'
10,1,@char）
go
2）输入并执行语句调用存储过程
execerror_proc
显示结果是什么？
变量值是否传递给显示信息？
显示的结果是：
Themaxpriceis263.50
变量值传递给了显示信息
注意事项：
必须将类型为money的变量@MaxPrice转换为字符型，才能在raiserror中引用。
3.使用输出参数返回变量值
通过使用Output选项返回存储过程中的数值
createprocReturn_proc
@ReturnMaxPricemoneyoutput
select@ReturnMaxPrice=max（unitprice）fromproducts
go
2）执行下面语句，调用存储过程
declare@returnmoney
execReturn_proc@returnoutput
select@return
是否显示结果？
显示的内容是什么？
显示结果，显示的内容是products表中最大的unitprice的值。
注意：
在存储过程中的返回参数定义Output选项，在调用存储过程时也要定义Output选项，来接收返回值。
4.按如下要求编写存储过程，并执行
1）在pubs数据库中创建一个存储过程，输入书的ID号（title_id），存储过程检索该书的书名、作者名。
usepubs
createprocedureid（@idchar（10））
selecttitles.title_id,title,au_lname,au_fname
fromdbo.titlesjoindbo.titleauthorontitles.title_id=titleauthor.title_idjoinauthorsonauthors.au_id=titleauthor.au_id
wheretitles.title_id=@id
execid'
BU1111'
2）创建一个存储过程，入口参数为一个时间类型的值，返回如下格式的时间字符串：
xxxx年xx月xx日。
（提示：
使用DATEPART函数，可在联机丛书中查询使用方法）
createprocedurechangeti（@datedatetime,@outvarchar（100）output）
begin
set@out=cast（DATEPART（YEAR,@date）asvarchar）+'
年'
+cast（DATEPART（MONTH,@date）asvarchar）+'
月'
+cast（DATEPART（DAY,@date）asvarchar）+'
日'
declare@loutvarchar（100）
execchangeti'
2011-11-11'
@loutoutput
print@lout
5.创建删除触发器
设有两张表NewCategories和NewProducts。
当删除NewCategories表中一条记录时，NewProducts表中的相关数据同时删除。
1）创建两张新表NewCategories和NewProducts。
SELECT*INTONewCategoriesFROMCategories
SELECT*INTONewProductsFROMProducts
2）输入并执行下面语句，用以在NewCategories表上创建删除触发器
CREATETRIGGERCategory_DeleteONNewCategories
FORDELETE
DELETENewProducts
FROMNewProductsASPINNERJOINDeletedASd
ONP.CategoryID=D.CategoryID
3）使用下面的语句测试触发器。
在NewCategories表中删除分类号为6的记录，并用两个Select语句查看NewProducts表结果。
（下面语句一起执行结果会很明显）
SELECTProductID,CategoryID,Discontinued
FROMNewProductsWHERECategoryID=6
DELETENewCategoriesWHERECategoryID=6
4）NewProducts表中分类号为6的记录是否自动删除?
是自动删除
6.使用触发器验证业务规则
newProducts表中存放每个产品的基本信息，[OrderDetails]表中存放的是订单信息。
如果一个产品存在着订单，那么这个产品不能从newProducts表中被删除。
1）在上面实验创建的newProducts表上创建触发器。
CREATETRIGGERProduct_Delete
ONNewProductsFORDELETE
IF（SelectCount（*）
FROM[OrderDetails]INNERJOINdeleted
ON[OrderDetails].ProductID=Deleted.ProductID
）>
0
RAISERROR（'
Transactioncannotbeprocessed.ThisProductstillhasahistoryoforders.'
16,1）
ROLLBACKTRANSACTION
2）使用下面语句测试触发器，将产品编号为1的产品信息从NewProducts中删除。
DELETENewProductsWHEREProductID=6
3）是否能删除？
为什么？
触发器与基表当作一个事务来执行。
不能被删除，因为当执行DELETENewProductsWHEREProductID=6时，系统会自动执行触发器Product_Delete，因为ProductID=6在OrderDetails表中，SelectCount（*）FROM[OrderDetails]INNERJOINdeletedON[OrderDetails].ProductID=Deleted.ProductID>
0，所以会发生回滚，并报出RAISERROR错误。
三、完成实验报告并回答问题
1.存储过程和自定义函数的区别在哪里
存储过程
自定义函数
用于在数据库中完成特定的操作或任务
用于特定的数据（如选择）
程序头部声明用Procedure
程序头部声明用Function
程序头部声明时不需描述返回类型
程序头部声明时要描述返回类型，而且PL/SQL块中至少要包括一个有效的return语句
可返回多个参数值
只有一个返回值或表
可作为一个独立的PL/SQL语句来执行
不能独立执行，必须作为表达式的一部分
可以通过in/out/inout返回零个或多个值
通过return语句返回一个值，且须与声明部分一致
SQL语句中不可使用存储过程
SQL语句中可以调用函数
2.带输出参数的存储过程调用时需要注意什么事项
调用时，必须要声明输出参数，指明输出参数的名称。
3.触发器的作用是什么？
Trigger作用：
实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性,当对表进行UPDATE、NSERT、DELETE操作时，SQLServer会自动执行触发器所定义的SQL语句
四：
实验小结
从本次试验中，我学到了如何创建（createprocedureas语句）、调用存储过程（exec语句），和存储的参数、返回值的使用方法，以及如何创建触发器（createtriggeronfor/afteras语句）和验证触发器的执行。
在创建调用存储过程中，应注意：
对于带有输入参数，输出参数的存储过程，在调用时，如果时有时候必须先声明，赋值，然后进行执行，例如declare@returnmoneyexecReturn_proc@returnoutputselect@return，有的直接进行执行，但这时候参数是具体的实参。
在触发器的创建中，应注意：
明确for.和after的区别2：
不能在临时表上建立Trigger3：
在某些触发器中，会定义，如果发生某件事时，显示RAISERROR语句，RAISERROR（错误提示,严重级别,状态），然后回滚该事务，rollback。
升级会员 