实验14 存储过程与函数.docx
- 文档编号:20124346
- 上传时间:2023-04-25
- 格式:DOCX
- 页数:20
- 大小:306.10KB
实验14 存储过程与函数.docx
《实验14 存储过程与函数.docx》由会员分享,可在线阅读,更多相关《实验14 存储过程与函数.docx(20页珍藏版)》请在冰豆网上搜索。
实验14存储过程与函数
实验十四 存储过程与函数
姓名:
方令
学号:
0807022205
专业:
计算机科学与技术
班级:
网2
同组人:
无
实验日期:
2010.12.3
【实验目的与要求】
1.熟练掌握存储过程的编写。
2.熟练掌握函数的编写与使用。
【实验内容与步骤】
14.1.基础知识
存储过程(StoredProcedure)和函数是一组编译好存储在服务器上的完成特定功能T-SQL代码,是某数据库的对象。
客户端应用程序可以通过指定存储过程或函数的名字并给出参数(如果该存储过程带有参数)来执行存储过程。
14.2.创建用户存储过程
1.使用存储过程模板创建存储过程
在【对象资源管理器】窗口中,展开“数据库”节点,再展开所选择的具体数据库节点,再展开选择“可编程性”节点,右击“存储过程”,选择“新建存储过程”命令,如图所示:
在右侧查询编辑器中出现存储过程的模板,可以在此基础上编辑存储过程,单击“执行”按钮,即可创建该存储过程。
实验14-1:
创建一个简单的存储过程,实现从CP表中读取Mp3产品相关信息
USECPXS
GO
CREATEPROCEDUREget_mp3--此为无参存储过程
AS
SELECT产品编号,产品名称,价格,库存量
FROMCP
WHERE产品名称='mp3'
实验14-2:
执行存储过程
执行存储过程可用下列方法之一:
(1)使用存储过程名字如:
get_mp3
(2)使用Exec命令:
如:
EXECget_mp3
执行上面创建的存储过程,执行结果:
2.使用T-SQL语句创建存储过程
在查询分析器里使用T-SQL可直接创建存储过程
格式:
CREATEPROC过程名
@形参名类型
@变参名类型OUTPUT
AS
SQL语句
实验14-3:
创建一个多表查询的存储过程。
问题:
查询在2009年9月18日有销售的产品名称
(1)请给出相应的代码
CREATEprocedureG_product
AS
SELECT产品名称
FROMCP,cpxsb
WHERECPXSB.产品编号=CP.产品编号and销售日期='2004-6-200:
00:
00';
(2)执行存储过程,执行结果:
14.3.存储过程的参数
1.输入参数(值参)
实验14-4:
输入参数为某产品的名字。
USECPXS
GO
CREATEPROCEDUREP_CPXS2
@Product_namechar(30)--形式参数
As
SELECT产品编号,产品名称,价格,库存量
FROMCP
WHERE产品名称=@Product_name
GO
执行存储过程:
(1)直接传值:
EXECP_CPXS2'冰箱'--实参表
执行结果:
(2)变量传值:
DECLARE@tempchar(30)
SET@temp='洗衣机'
EXECP_CPXS2@temp--实参表
执行结果:
实验14-5:
使用默认参数
阅读以下程序段,理解参数传递过程
USECPXS
GO
CREATEPROCEDUREP_CPXS3
@namevarchar(10)=NULL--默认参数
AS
IF@nameISNULL
SELECT产品编号,产品名称,价格,库存量
FROMCP
ELSE
SELECT产品编号,产品名称,价格,库存量
FROMCP
WHERE产品名称=@name
GO
执行存储过程:
(1)不带参数时
EXECP_CPXS3
执行结果:
(2)不带参数时
EXECP_CPXS3‘彩色电视机’
执行结果:
2.输出参数(变参)
实验14-6:
利用输出参数计算阶乘。
USECPXS
–判断系统中是否有名为factorial的存储过程,若有,则删除之
IFEXISTS(SELECTnameFROMsysobjects
WHEREname='factorial'ANDtype='P')
DROPPROCEDUREfactorial
GO--前面这段仅是准备工作,真正的工作在之后
CREATEPROCEDUREfactorial--创建存储过程
@infloat,--输入形式参数
@outfloatOUTPUT--输出形式参数
AS
DECLARE@iint
DECLARE@sfloat
SET@i=1
SET@s=1
WHILE@i<=@in
BEGIN
SET@s=@s*@i
SET@i=@i+1
END
SET@out=@s--给输出参数赋值
调用存储过程:
DECLARE@oufloat–定义变量,用于存储结果值
EXECfactorial5,@ouOUT--实参表
PRINT@ou
执行结果:
14.4.创建用户自定义函数
1.使用存储过程模板创建存储过程
在【对象资源管理器】窗口中,展开“数据库”节点,再展开所选择的具体数据库节点,再展开选择“可编程性”节点,右击“函数”,选择“新建”命令,在下一级菜单中选择适合的选项,如图所示:
在右侧查询编辑器中出现函数的模板,可以在此基础上编辑函数,单击“执行”按钮,即可创建该函数。
2.使用T-SQL语句创建函数
函数分为标量函数和表值函数。
前者返回一个标量值结果,在创建函数时,应在Returns语句后指明标量类型(如:
int);后者以表的形式返回结果,在创建函数时,应在Returns语句后用关键词Table指时其反回类型。
在查询分析器里可使用T-SQL直接创建函数
格式:
CREATEFUNCTION函数名(
@形参名类型
@变参名类型
)
[RETURNS类型]
AS
SQL语句
3.使用标量函数
标量函数接受0个或多个输入参数,并返回一个标量值。
因为标量函数只返回一个值,所以通常在一个select语句的列表中使用它们,也可以在where子句中使用它们。
实验14-7:
基于CP表编写函数getStock(),根据传进的参数“产品编号”,查询并返回相应产品的“库存量”。
请阅读以下程序,理解其基本结构和实现思想,给出运行结果。
createfunctiongetStock(@productidchar(6))
returnsint
as
begin
declare@stockint;
select@stock=库存量
fromdbo.cp
where产品编号=@productid
return@stock;
end;
调用函数:
函数一般在Select语句或Where子句中被调用,以下是一函数调用实例:
执行结果:
4.使用表值函数
表值函数遵守与标量函数相同的规则,区别在于表值函数返回一个表作为输出。
因此,一般在select语句的from子句中使用它们,并可能与其他表或视图进行联接。
实验14-8:
创建一个自定义函数fun_cpInfo(),根据产品编号返回该产品的名称、价格和库存量。
createfunctionfun_cpInfo(@product_Nochar(6))
returnstable--表值函数,返回查询结果集(即表)
as
return(select产品名称,价格,库存量
fromCP
where产品编号=@product_No)
调用函数:
函数创建后,可在SQL语句中调用。
调用函数fun_cpInfo(),可在查询分析器中执行如下Select语句:
执行结果:
思考:
请比较标量函数和表值函数,理解其在编写和使用上的差别。
14.5.实验练习
14.5.1存储过程
对于CPXS数据库,完成以下存储过程。
1.无参存储过程
编写一无参存储过程用于查询每个客户购买产品的情况(包括客户编号、产品编号、客户名称、产品名称、价格、购买日期、购买数量),然后调用该存储过程。
程序源码:
createprocedureau_info_all
as
select*
fromcpleftjoincpxsb
onCPXSB.产品编号=CP.产品编号leftjoinxss
oncpxsb.客户编号=xss.客户编号
执行测试结果:
2.带有参数的存储过程
编写一加密存储过程,查询指定客户购买产品的情况。
并调用该存储过程查询客户编号为“000002”的客户购买情况。
程序源码:
createprocedureaa_info_all@guestnochar(6)withencryption
as
select*
fromcpleftjoincpxsb
onCPXSB.产品编号=CP.产品编号leftjoinxss
oncpxsb.客户编号=xss.客户编号
wherexss.客户编号=@guestno;
测试结果:
3.带有通配符参数的存储过程
编写一存储过程,查询指定产品的销售情况。
如果没有提供参数,则查询产品名称中包含有“冰箱”的产品销售情况。
程序源码:
createprocedureaaa_info_all(@productnamechar(16))
as
select数量
fromcpleftjoincpxsb
onCPXSB.产品编号=CP.产品编号leftjoinxss
oncpxsb.客户编号=xss.客户编号
where产品名称=@productname
执行测试结果:
4.带有OUTPUT参数的存储过程
编写一存储过程,查询指定客户在指定时间段内购买指定产品的数量,存储过程中使用了输入和输出参数。
并调用该存储过程查询名称为“家电市场”的客户在2004年购买“洗衣机”的数量。
程序源码:
createprocedureau
@productnumintoutput,
@guestnamechar(30),
@datechar(4),
@productnamechar(30)
as
select@productnum=数量
fromcp,cpxsb,xss
whereCPXSB.产品编号=CP.产品编号andcpxsb.客户编号=xss.客户编号
andxss.客户名称=@guestnameandcpxsb.销售日期like'%'+@date+'%'
andcp.产品名称=@productname
测试结果:
5.带有OUTPUT游标参数的存储过程
编写一带有OUTPUT游标参数的存储过程,游标结果集为客户信息,并通过调用该存储过程,实现依次读取游标CUR2中各行数据。
程序源码:
createproceduretitl_cursor@titl_cursorcursorvaryingoutput
as
set@titl_cursor=cursor
forward_onlystaticfor
select*
fromxss
open@titl_cursor
declare@mycursorcursor
exectitl_cursor@titl_cursor=@mycursoroutput
fetchnextfrom@mycursor
while(@@fetch_status=0)
begin
fetchnextfrom@mycursor
end
测试结果:
6.创建一个多表查询的存储过程。
问题:
查询在2009年9月18日有销售的产品名称(若无此数据,请先添加之,以便于测试)。
(1)请给出相应的代码
createprocedureau_info_all
as
selectcp.产品名称
fromcpleftjoincpxsb
onCPXSB.产品编号=CP.产品编号leftjoinxss
oncpxsb.客户编号=xss.客户编号
wherecpxsb.销售日期=’2009-09-1800:
00’
(2)执行存储过程,并给出执行结果:
14.5.2函数
对于CPXS数据库,定义完成如下功能的函数。
1.据产品名称,查询该产品的相关信息。
(函数名为:
FU_CP)
程序源码:
createfunctionfu_cp(@productnamechar(12))
returnstable
as
return
(
select*
fromcp
where产品名称=@productname
)
执行测试结果:
查询产品名称为“mp3”的产品情况
2.按某年某季度统计给定产品名称的销售数量及销售金额。
(函数名为:
FU1_CPXS)
出程序源码:
CREATEFUNCTIONFU1_CPXS(@YEARINT,@QUARTERINT,@产品名称char(10))
RETURNSTABLE
AS
RETURN
SELECT产品名称,SUM(数量)AS销售数量,SUM(销售额)AS销售总额
FROMCP,CPXSB
WHERECPXSB.产品编号=CP.产品编号AND产品名称=@产品名称
ANDDATEPART(YY,销售日期)=@YEARANDDATEPART(QQ,销售日期)=@QUARTERGROUPBY产品名称
执行测试结果:
查询2004年第3季度彩色电视机的销售数量和销售金额
3.根据销售商名称,统计其在某年某季度内销售商品名称、数量及金额。
(函数名为FU2_CPXS)
程序源码:
CREATEFUNCTIONFU3_CPXS(@客户名称char(10),@YEARINT,@QUARTERINT)
RETURNSTABLE
AS
RETURN
SELECT产品名称,SUM(数量)AS销售数量,SUM(销售额)AS销售总额
FROMXSS,CPXSB,CP
WHERECPXSB.产品编号=CP.产品编号ANDCPXSB.客户编号=XSS.客户编号
AND客户名称=@客户名称ANDDATEPART(YY,销售日期)=@YEAR
ANDDATEPART(QQ,销售日期)=@QUARTERGROUPBY产品名称
测试结果:
查询广电公司2004年第1季度销售的产品名称、销售数量和销售金额。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 实验14 存储过程与函数 实验 14 存储 过程 函数
![提示](https://static.bdocx.com/images/bang_tan.gif)