实验14 存储过程与函数Word文档格式.docx
《实验14 存储过程与函数Word文档格式.docx》由会员分享，可在线阅读，更多相关《实验14 存储过程与函数Word文档格式.docx（19页珍藏版）》请在冰豆网上搜索。
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）请给出相应的代码
CREATEPROCgetName
@timedatetime
OUTPUT
select产品名称
fromCP,CPXSB
wherecp.产品编号=CPXSB.产品编号and销售日期=@time
执行存储：
execgetName'
2009-9-18'
（2）执行存储过程,并给出执行结果：
14.3.存储过程的参数
1.输入参数（值参）
实验14-4：
输入参数为某产品的名字。
CREATEPROCEDUREP_CPXS2
@Product_namechar（30）--形式参数
As
SELECT,产品名称,价格,库存量
WHERE产品名称=@Product_name
执行存储过程：
（1）直接传值：
EXECP_CPXS2'
冰箱'
--实参表
请给出执行结果：
（2）变量传值：
DECLARE@tempchar（30）
SET@temp='
洗衣机'
EXECP_CPXS2@temp--实参表
实验14-5：
使用默认参数
阅读以下程序段，理解参数传递过程
CREATEPROCEDUREP_CPXS3
@namevarchar（10）=NULL--默认参数
IF@nameISNULL
SELECT产品编号,产品名称,价格,库存量
FROMCP
ELSE
WHERE产品名称=@name
（1）不带参数时
EXECP_CPXS3
（2）带参数时
EXECP_CPXS3‘彩色电视机’
2.输出参数（变参）
实验14-6：
利用输出参数计算阶乘。
–判断系统中是否有名为factorial的存储过程，若有，则删除之
IFEXISTS（SELECTnameFROMsysobjects
WHEREname='
factorial'
ANDtype='
P'
）---
DROPPROCEDUREfactorial
GO--前面这段仅是准备工作，真正的工作在之后
CREATEPROCEDUREfactorial--创建存储过程
@infloat,--输入形式参数
@outfloatOUTPUT--输出形式参数
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.创建用户自定义函数
在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，再展开选择“可编程性”节点，右击“函数”，选择“新建”命令，在下一级菜单中选择适合的选项，如图所示：
在右侧查询编辑器中出现函数的模板，可以在此基础上编辑函数，单击“执行”按钮，即可创建该函数。
2.使用T-SQL语句创建函数
函数分为标量函数和表值函数。
前者返回一个标量值结果，在创建函数时，应在Returns语句后指明标量类型（如：
int）；
后者以表的形式返回结果，在创建函数时，应在Returns语句后用关键词Table指时其反回类型。
在查询分析器里可使用T-SQL可直接创建函数
CREATEFUNCTION函数名（
@变参名类型
）
[RETURNS类型]
3.使用标量函数
标量函数接受0个或多个输入参数，并返回一个标量值。
因为标量函数只返回一个值，所以通常在一个select语句的列列表中使用它们，也可以在where子句中使用它们。
实验14-7：
基于CP表编写函数getStock（），根据传进的参数“产品编号”，查询并返回相应产品的“库存量”。
请阅读以下程序，理解其基本结构和实现思想，给出运行结果。
调用函数：
函数一般在Select语句或Where子句中被调用，以下是一函数调用实例：
createfunctiongetStock（@ProductIDchar（6））
returnsint
as
begin
declare@stockint
select@stock=库存量
fromdbo.cp
where产品编号=@ProductID
return@stock
end
selectdbo.getStock（'
100002'
）as库存量
4.使用表值函数
表值函数遵守与标量函数相同的规则，区别在于表值函数返回一个表作为输出。
因此，一般在select语句的from子句中使用它们，并可能与其他表或视图进行联接。
实验14-8：
创建一个自定义函数fun_cpInfo（），根据产品编号返回该产品的名称、价格和库存量。
createfunctionfun_cpInfo（@product_Nochar（6））
returnstable--表值函数，返回查询结果集（即表）
return（select产品名称,价格,库存量
fromCP
where产品编号=@product_No）
函数创建后，可在SQL语句中调用。
调用函数fun_cpInfo（），可在查询分析器中执行如下Select语句：
思考：
请比较标量函数和表值函数，理解其在编写和使用上的差别。
14.5.实验练习
14.5.1存储过程
对于CPXS数据库，完成以下存储过程。
1.无参存储过程
编写一无参存储过程用于查询每个客户购买产品的情况（包括客户编号、产品编号、客户名称、产品名称、价格、购买日期、购买数量），然后调用该存储过程。
请给出程序源码：
CREATEPROCEDUREget_informations--此为无参存储过程
SELECTXSS.客户编号,CPXSB.产品编号,客户名称,产品名称,价格,销售日期,数量
FROMCP,CPXSB,XSS
WHERECP.产品编号=CPXSB.产品编号andXSS.客户编号=CPXSB.客户编号
EXECget_informations
请给出执行测试结果：
2.带有参数的存储过程
编写一加密存储过程，查询指定客户购买产品的情况。
并调用该存储过程查询客户编号为“000002”的客户购买情况。
CREATEPROCEDUREget_CI--此为无参存储过程
@C_Noint
SELECTXSS.客户编号,CPXSB.产品编号,客户名称,产品名称,价格,
销售日期,数量
WHERECP.产品编号=CPXSB.产品编号andXSS.客户编号=CPXSB.客户编号
andCPXSB.客户编号=@C_No
EXECget_CI'
000002'
3.带有通配符参数的存储过程
编写一存储过程，查询指定产品的销售情况。
如果没有提供参数，则查询产品名称中包含有“冰箱”的产品销售情况。
CREATEPROCEDUREget_SInformations
@P_Namechar（10）=null
if@P_Nameisnull
SELECTdistinct销售日期,CPXSB.产品编号,产品名称,价格,数量,销售额
FROMCP,CPXSB,XSS
WHERECP.产品编号=CPXSB.产品编号andCP.产品名称like'
%冰箱'
orderby销售日期asc
else
SELECTdistinct销售日期,CPXSB.产品编号,产品名称,价格,数量
WHERECP.产品编号=CPXSB.产品编号andCP.产品名称=@P_Name
EXECget_SInformations
4.带有OUTPUT参数的存储过程
编写一存储过程，查询指定客户在指定时间段内购买指定产品的数量，存储过程中使用了输入和输出参数。
并调用该存储过程查询名称为“家电市场”的客户在2004年购买“洗衣机”的数量。
useCPXS
go
CREATEPROCEDUREget_amounts--创建存储过程
（@in_cchar（20）,
@in_tchar（4）,
@in_pchar（20）,--输入形式参数
@outintOUTPUT）--输出形式参数
select@out=数量
fromCPXSB,CP,XSS
WHERECPXSB.客户编号=XSS.客户编号
andCPXSB.产品编号=CP.产品编号
andCPXSB.销售日期like'
%'
+@in_t+'
andXSS.客户名称=@in_c
andCP.产品名称=@in_p
--给输出参数赋值
DECLARE@ouint--–定义变量，用于存储结果值
EXECget_amounts'
家电公司'
'
2004'
@ouOUT--实参表
print'
数量='
+convert（char（5）,@ou）
5.带有OUTPUT游标参数的存储过程
编写一带有OUTPUT游标参数的存储过程，游标结果集为客户信息，并通过调用该存储过程，实现依次读取游标CUR2中各行数据。
6.创建一个多表查询的存储过程。
查询在2009年9月18日有销售的产品名称（若无此数据，请先添加之，以便于测试）。
CREATEPROCEDUREget_PNames1
@S_Timedatetime
SELECT产品名称
FROMCP,CPXSB
WHERECP.产品编号=CPXSB.产品编号and销售日期=@S_Time
EXECget_PNames1'
14.5.2函数
对于CPXS数据库，定义完成如下功能的函数。
1.据产品名称，查询该产品的相关信息。
（函数名为：
FU_CP）
createfunctionFU_CP（@pNamechar（10））
returnstable
return
（select产品编号,价格,库存量,产品名称
where产品名称=@pName）
select*
fromFU_CP（'
查询产品名称为“mp3”的产品情况
2.按某年某季度统计给定产品名称的销售数量及销售金额。
FU1_CPXS）
createfunctionFU_CPXSB（@pNamechar（10）,@sTimeYint,@sTimeMint）
select产品名称,数量,销售额
fromCPXSB,CP
where（CPXSB.产品编号=CP.产品编号and产品名称=@pName
anddatepart（YY,销售日期）=@sTimeY
anddatepart（QQ,销售日期）=@sTimeM）
select*
fromFU_CPXSB（'
彩色电视机'
03'
查询2004年第3季度彩色电视机的销售数量和销售金额
3.根据销售商名称，统计其在某年某季度内销售商品名称、数量及金额。
（函数名为FU2_CPXS）
createfunctionfu_cpxs1（@yearint,@quarterint,@客户名称char（10））
return
select产品名称,SUM（数量）数量,SUM（销售额）销售总额
fromCP,CPXSB,XSS
whereCPXSB.产品编号=CP.产品编号
andCPXSB.客户编号=XSS.客户编号
and客户名称=@客户名称
anddatepart（YY,销售日期）=@year
anddatepart（QQ,销售日期）=@quarter
groupby产品名称
Select*
Fromfu2_cpxs1（'
1'
广电公司'
查询广电公司2004年第1季度销售的产品名称、销售数量和销售金额。
升级会员 