实验14存储过程与函数.docx
《实验14存储过程与函数.docx》由会员分享，可在线阅读，更多相关《实验14存储过程与函数.docx（12页珍藏版）》请在冰豆网上搜索。
实验14存储过程与函数
实验十四 存储过程与函数
姓名：
廖冬凤
学号：
20070721140
专业：
网络工程
班级：
07网络
（1）班
同组人：
无
实验日期：
2009-12-10
【实验目的与要求】
1．熟练掌握存储过程的创建、调用和删除。
2．理解什么是标量函数、内嵌表值函数及多语句表值函数。
3．熟练掌握标量函数、内嵌表值函数、多语句表值函数的定义和调用。
【实验内容与步骤】
14.1存储过程
存储过程（Stored Procedure）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库。
中用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
存储过程的优点
1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
ITPUB个人空间Z}5`:
~-C]
2.当对数据库进行复杂操作时（如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
ITPUB个人空间b{NjX;_
3.存储过程可以重复使用,可减少数据库开发人员的工作量
?
7q.J"St6fH4o0 4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权
对于CPXS数据库，创建如下存储过程：
1、无参存储过程
编写一无参存储过程用于查询每个客户购买产品的情况（包括客户编号、产品编号、客户名称、产品名称、价格、购买日期、购买数量），然后调用该存储过程。
CREATEPROCEDURECPXS_CUS
AS
SELECTCPXSB.客户编号,CPXSB.产品编号,客户名称,产品名称,价格,销售日期,数量
FROMCP,XSS,CPXSB
WHEREXSS.客户编号=CPXSB.客户编号
andCP.产品编号=CPXSB.产品编号
EXECCPXS_CUS
2、带有参数的存储过程
编写一加密存储过程，查询指定客户购买产品的情况。
并调用该存储过程查询客户编号为“000002”的客户购买情况。
CREATEPROCEDURECPXS_CGQ
@CGQVARCHAR（10）
AS
SELECTCPXSB.客户编号,CPXSB.产品编号,客户名称,产品名称,价格,销售日期,数量
FROMCP,XSS,CPXSB
WHEREXSS.客户编号=CPXSB.客户编号
andCP.产品编号=CPXSB.产品编号
andCPXSB.客户编号=@CGQ
EXECCPXS_CGQ'000002'
3、带有通配符参数的存储过程
编写一存储过程，查询指定产品的销售情况。
如果没有提供参数，则查询产品名称中包含有“冰箱”的产品销售情况。
CREATEPROCEDURECPXS_BX
@BXVARCHAR（10）='%冰箱%'
AS
SELECTCPXSB.客户编号,CPXSB.产品编号,客户名称,产品名称,价格,销售日期,数量
FROMCP,XSS,CPXSB
WHEREXSS.客户编号=CPXSB.客户编号
andCP.产品编号=CPXSB.产品编号
and产品名称LIKE@BX
EXECCPXS_BX'%冰箱%'
4、带有OUTPUT参数的存储过程
编写一存储过程，查询指定客户在指定时间段内购买指定产品的数量，存储过程中使用了输入和输出参数。
并调用该存储过程查询名称为“家电市场”的客户在2004年购买“洗衣机”的数量。
CREATEPROCEDURECPXS_CJD
@CUSVARCHAR（30）,
@TIMEVARCHAR（30）,
@XYJVARCHAR（30）,
@BUYNUMINTOUTPUT
AS
SET@BUYNUM=
（selectcpxsb.数量
fromcpxsb
wherecpxsb.客户编号=
（selectxss.客户编号
fromxsswherexss.客户名称=@CUS）
andcpxsb.销售日期like@TIME
andcpxsb.产品编号=
（selectcp.产品编号fromcpwhere产品名称LIKE@XYJ））
DECLARE@BUYNUMINT
EXECCPXS_CJD'家电市场','%2004%','%洗衣机%',
@BUYNUMOUTPUT
SELECT@BUYNUMAS购买的数量
5、带有OUTPUT游标参数的存储过程
编写一带有OUTPUT游标参数的存储过程，游标结果集为客户信息，并通过调用该存储过程，实现依次读取游标CUR2中各行数据。
dropprocedurepro_and_cursor
--创建存储过程
createprocedurepro_and_cursor
as
--创建游标
declareoutput_all_cs_infscrollcursor
for
select客户编号,客户名称,地区,负责人,电话
fromxss
orderby客户编号
openoutput_all_cs_inf
--定义变量存储游标检索信息列值
declare@csidchar（6）,
@csnamechar（30）,
@zonechar（10）,
@chargemanchar（8）,
@telchar（12）
print'游标结果集的纪录总数为'+cast（@@cursor_rowsasvarchar
（2））
--执行第一次取数操作，将游标定位到第一行纪录
Fetchnextfromoutput_all_cs_inf
into@csid,
@csname,
@zone,
@chargeman,
@tel
while@@Fetch_status=0
begin
--输出纪录
print'客户编号:
'+cast（@csidaschar（8））+
'客户名称:
'+cast（@csnameaschar（15））+
'地区:
'+cast（@zoneaschar（6））+
'负责人:
'+cast（@chargemanaschar（8））+
'电话:
'+cast（@telaschar（12））
Fetchnextfromoutput_all_cs_inf
into@csid,
@csname,
@zone,
@chargeman,
@tel
end
execpro_and_cursor
14.2函数
1、函数的定义
对于CPXS数据库，定义完成如下功能的函数：
（1）据产品名称，查询该产品的相关信息；（函数名为FU_CP）
createFunctionFU_CP
（@cpnamechar（30））
returnstable
as
return
（selectcp.产品编号,cp.产品名称,cp.价格,cp.库存量
fromcp
wherecp.产品名称=@cpname）
select*
from
FU_CP（'洗衣机'）
（2）按某年某季度统计给定产品名称的销售数量及销售金额；分别用名为FU1_CPXS内嵌表值函数和名为FU2_CPXS的多语句表值函数。
createfunctionFU1_CPXS（@pnamechar（30）,@yearvarchar（20）,@quartervarchar（20））
returnstable
as
return（select@pnameas产品名称,
sum（cpxsb.数量）as销售数量,
sum（cpxsb.销售额）as销售总金额
fromcpxsb
where
cpxsb.产品编号=（selectcp.产品编号
fromcp
wherecp.产品名称=@pname）
anddatename（yy,cpxsb.销售日期）=@year
anddatename（qq,cpxsb.销售日期）=@quarter）
createfunctionFU2_CPXS（@pnamechar（30）,@yearvarchar（20）,@quartervarchar（20））
returns@销售情况table（产品名称char（30）,
销售数量int,
销售金额float（8））
as
begin
insertinto@销售情况
select@pname,
sum（cpxsb.数量）,
sum（cpxsb.销售额）
fromcpxsb
where
cpxsb.产品编号=（selectcp.产品编号fromcpwherecp.产品名称=@pname）
anddatename（yy,cpxsb.销售日期）=@year
anddatename（qq,cpxsb.销售日期）=@quarter
return
end
（3）根据销售商名称，统计其在某年某季度内销售商品名称、数量及金额。
（函数名为FU3_CPXS）
createfunctionFU3_CPXS（@cnamechar（30）,@yearint,@quarterint）
returnstable
as
return（selectxss.客户名称,cp.产品名称,cpxsb.数量,cpxsb.销售额
fromcpxsbinner
joinxssoncpxsb.客户编号=xss.客户编号,cp
wherecp.产品编号=cpxsb.产品编号
andxss.客户名称=@cname
anddatepart（yy,cpxsb.销售日期）=@year
anddatepart（qq,cpxsb.销售日期）=@quarter）
2、函数的调用
（1）对函数FU_CP，查询产品名称为“mp3”的产品情况；
select*
from
FU_CP（'MP3'）
（2）对函数FU1_CPXS，查询2004年第3季度彩色电视机的销售数量和销售金额；
--查询第三季度
select*
fromFU1_CPXS（'彩色电视机','2004','3'）
--查询第二季度
select*
fromFU1_CPXS（'彩色电视机','2004','2'）
（3）对函数FU2_CPXS，查询2004年第1季度洗衣机的销售数量和销售金额；
select*
fromFU2_CPXS（'洗衣机','2004','1'）
（4）对函数FU3_CPXS，查询广电公司2004年第1季度销售的产品名称、销售数量和销售金额。
select*
fromFU3_CPXS（'广电公司',2004,1）
升级会员 