用户自定义函数存储过程和触发器.docx
- 文档编号:3928392
- 上传时间:2022-11-26
- 格式:DOCX
- 页数:18
- 大小:396.16KB
用户自定义函数存储过程和触发器.docx
《用户自定义函数存储过程和触发器.docx》由会员分享,可在线阅读,更多相关《用户自定义函数存储过程和触发器.docx(18页珍藏版)》请在冰豆网上搜索。
用户自定义函数存储过程和触发器
北京联合大学信息学院
数据库管理与实现
实验报告
题目:
实验五.用户自定义函数
一、实验目的:
学习、掌握用户自定义函数的建立和使用
二、实验内容:
1.创建自定义函数
创建一个用户自定义函数,并测试、查看函数返回值。
1)输入并执行下面语句
USENorthwind
GO
CREATEFUNCTIONfn_TaxRate
(@ProdIDINT)
RETURNSnumeric(5,4)
AS
BEGIN
RETURN
(SELECT
CASECategoryID
WHEN1THEN1.10
WHEN2THEN1
WHEN3THEN1.10
WHEN4THEN1.05
WHEN5THEN1
WHEN6THEN1.05
WHEN7THEN1
WHEN8THEN1.05
END
FROMProducts
WHEREProductID=@ProdID)
END
GO
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.返回值为多值的自定义函数
创建函数返回多列多值。
1)输入并执行下面语句
USENorthwind
GO
CREATEFUNCTIONfn_LargeFreight
(@FreightAmtmoney)
RETURNSTABLE
AS
RETURN
(SELECTS.ShipperID,S.CompanyName,
O.OrderID,O.ShippedDate,O.Freight
FROMShippersASSJOINOrdersASO
ONS.ShipperID=O.ShipVia
WHEREO.Freight>@FreightAmt
)
2)函数中输入变量是什么?
返回值类型?
如何定义的返回值?
输入变量:
@FreightAmtmoney返回值类型:
TABLE定义返回值:
将SELECT语句的查询结果作为函数的返回值返回。
3)输入并执行语句测试函数
SELECT*FROMfn_LargeFreight(600)
查看结果
3.返回值为多值的自定义函数
本实验创建的函数也是返回多列多值,注意与上面实验的差别。
1)输入并执行下面语句
USENorthwind
GO
CREATEFUNCTIONfn_FindReports(@InEmployeeIDchar(5))
RETURNS@reportsTABLE
(EmployeeIDchar(5)PRIMARYKEY,
Namenvarchar(40)NOTNULL,
Titlenvarchar(30),
MgrEmployeeIDint,
processedtinyintdefault0)
AS
BEGIN
INSERT@reports
SELECTEmployeeID,Name=FirstName+''+LastName,Title,ReportsTo,0
FROMEMPLOYEESWHEREReportsTo=@InEmployeeID
RETURN
END
GO
2)此函数中输入变量是什么?
返回值类型是什么?
如何定义的返回值?
输入变量:
@InEmployeeIDchar(5)返回值类型:
@reportsTABLE
(EmployeeIDchar(5)PRIMARYKEY,
Namenvarchar(40)NOTNULL,
Titlenvarchar(30),
MgrEmployeeIDint,
processedtinyintdefault0)
定义返回值:
通过定义一个SELECT查询语句,然后在返回值之前加还有其他的T—SQL语句,最终把结果返回到@reports这个表里面。
3)输入并执行语句测试函数
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数据库中,输入订单号,返回该订单所涉及的零件名称和类别。
useOrderMag
go
createfunctionfn_ono
(@nochar(4))
returnstable
as
return
(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的为面临缺货。
useOrderMag
go
createfunctionfn_pj
(@nochar(4))
returnstable
as
return
(selectpno,pnum,
casewhenpnum>=500then'充足'
whenpnum>=100then'均衡'
whenpnum<100then'面临缺货'
endas状态
fromstore
wherepno=@no)
declare@nochar(4)
set@no='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)
begin
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语句),以及这三种用户自定义函数的区别。
在实验过程中,我认为有以下几点应该注意:
1:
自定义函数的调用必须在函数名前标注函数所有者:
dbo2:
在函数调用时形参和实参的名称可不同,但数据类型必须一致3:
表值函数中,函数体为一条select语句,不使用begin和end,RETURNS子句后面仅包含关键字table,指定返回的数据类型为table型4:
定义多语句表值函数时,RETURNS语句必须标明返回类型为table型和返回表的名字和表结构定义。
北京联合大学信息学院
数据库管理与实现
实验报告
题目:
实验六.存储过程与触发器
2011年 11 月 7 日
一、实验目的:
1.了解、掌握SQL编程的特点、方法。
2.熟练掌握建立、调用存储过程的方法,学习存储的参数、返回值的使用方法。
3.了解游标的概念和使用。
4.练习创建触发器,并验证触发器的执行。
二、实验内容:
1.建立简单存储过程
创建一个简单的存储过程,了解实现存储过程的语法。
1)输入并执行下面语句
USENorthwind
GO
CREATEPROCEDUREFirstProc
AS
SELECTTOP5ProductName,UnitPriceFROMProductsORDERBYUnitPricedesc
GO
2)输入并执行如下语句:
Usenorthwind
execfirstproc
这个存储过程的含义是什么?
是否可以用视图实现同样的功能?
含义是:
在Northwind这个数据库,在Products表中,按照UnitPrice降序排列并输出前五个的产品名称和其单价。
可以用视图实现同样的功能。
USENorthwind
GO
createviewfirst5
as
SELECTTOP5ProductName,UnitPriceFROMProductsORDERBYUnitPricedesc
2.进一步使用存储过程
当执行存储过程时,将执行时的信息返回给用户
1)输入并执行下面语句
createprocError_proc
as
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选项返回存储过程中的数值
1)输入并执行下面语句
createprocReturn_proc
@ReturnMaxPricemoneyoutput
as
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
go
createprocedureid(@idchar(10))
as
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)
as
begin
set@out=cast(DATEPART(YEAR,@date)asvarchar)+'年'+cast(DATEPART(MONTH,@date)asvarchar)+'月'+cast(DATEPART(DAY,@date)asvarchar)+'日'
end
declare@loutvarchar(100)
execchangeti'2011-11-11',@loutoutput
print@lout
5.创建删除触发器
设有两张表NewCategories和NewProducts。
当删除NewCategories表中一条记录时,NewProducts表中的相关数据同时删除。
1)创建两张新表NewCategories和NewProducts。
USENorthwind
GO
SELECT*INTONewCategoriesFROMCategories
SELECT*INTONewProductsFROMProducts
GO
2)输入并执行下面语句,用以在NewCategories表上创建删除触发器
CREATETRIGGERCategory_DeleteONNewCategories
FORDELETE
AS
DELETENewProducts
FROMNewProductsASPINNERJOINDeletedASd
ONP.CategoryID=D.CategoryID
3)使用下面的语句测试触发器。
在NewCategories表中删除分类号为6的记录,并用两个Select语句查看NewProducts表结果。
(下面语句一起执行结果会很明显)
SELECTProductID,CategoryID,Discontinued
FROMNewProductsWHERECategoryID=6
DELETENewCategoriesWHERECategoryID=6
SELECTProductID,CategoryID,Discontinued
FROMNewProductsWHERECategoryID=6
4)NewProducts表中分类号为6的记录是否自动删除?
是自动删除
6.使用触发器验证业务规则
newProducts表中存放每个产品的基本信息,[OrderDetails]表中存放的是订单信息。
如果一个产品存在着订单,那么这个产品不能从newProducts表中被删除。
1)在上面实验创建的newProducts表上创建触发器。
USENorthwind
GO
CREATETRIGGERProduct_Delete
ONNewProductsFORDELETE
AS
IF(SelectCount(*)
FROM[OrderDetails]INNERJOINdeleted
ON[OrderDetails].ProductID=Deleted.ProductID
)>0
BEGIN
RAISERROR('Transactioncannotbeprocessed.ThisProductstillhasahistoryoforders.',16,1)
ROLLBACKTRANSACTION
END
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,有的直接进行执行,但这时候参数是具体的实参。
在触发器的创建中,应注意:
1:
明确for.和after的区别2:
不能在临时表上建立Trigger3:
在某些触发器中,会定义,如果发生某件事时,显示RAISERROR语句,RAISERROR(错误提示,严重级别,状态),然后回滚该事务,rollback。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 用户自定义函数 存储过程和触发器 用户 自定义 函数 存储 过程 触发器