实验14 存储过程与函数Word文档格式.docx

1、FROM CPWHERE产品名称=mp3实验14-2：执行存储过程执行存储过程可用下列方法之一：（1）使用存储过程名字如：get_mp3 （2） 使用Exec命令：如：EXEC get_mp3执行上面创建的存储过程，并给出执行结果：2. 使用T-SQL语句创建存储过程在查询分析器里使用T-SQL可直接创建存储过程格式：CREATE PROC 过程名形参名 类型变参名 类型 OUTPUTAS SQL语句实验14-3：创建一个多表查询的存储过程。问题：查询在2009年9月18日有销售的产品名称（1）请给出相应的代码CREATE PROC getNametime datetimeOUTPUTsele

2、ct 产品名称from CP,CPXSBwhere cp.产品编号=CPXSB.产品编号 and 销售日期=time 执行存储：exec getName 2009-9-18（2）执行存储过程,并给出执行结果：14.3. 存储过程的参数1. 输入参数（值参）实验14-4：输入参数为某产品的名字。CREATE PROCEDURE P_CPXS2Product_name char（30） -形式参数AsSELECT ,产品名称,价格,库存量WHERE 产品名称= Product_name执行存储过程：（1）直接传值：EXEC P_CPXS2 冰箱 -实参表请给出执行结果：（2）变量传值：DECLAR

3、E temp char（30）SET temp=洗衣机EXEC P_CPXS2 temp -实参表实验14-5：使用默认参数阅读以下程序段，理解参数传递过程CREATE PROCEDURE P_CPXS3name varchar（10）=NULL -默认参数IF name IS NULL SELECT 产品编号,产品名称,价格,库存量 FROM CPELSE WHERE 产品名称=name（1） 不带参数时EXEC P_CPXS3（2） 带参数时EXEC P_CPXS3 彩色电视机2. 输出参数（变参）实验14-6：利用输出参数计算阶乘。判断系统中是否有名为factorial的存储过程，若有，

4、则删除之IF EXISTS（SELECT name FROM sysobjects WHERE name=factorial AND type=P）- DROP PROCEDURE factorialGO -前面这段仅是准备工作，真正的工作在之后CREATE PROCEDURE factorial -创建存储过程 in float, -输入形式参数 out float OUTPUT -输出形式参数DECLARE i intDECLARE s floatSET i=1SET s=1WHILE i=in BEGIN SET s=s*i SET i=i+1 ENDSET out=s -给输出参数赋值

5、调用存储过程：DECLARE ou float 定义变量，用于存储结果值EXEC factorial 5,ou OUT -实参表PRINT ou14.4. 创建用户自定义函数在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，再展开选择“可编程性”节点，右击“函数”，选择“新建”命令，在下一级菜单中选择适合的选项，如图所示：在右侧查询编辑器中出现函数的模板，可以在此基础上编辑函数，单击“执行”按钮，即可创建该函数。2. 使用T-SQL语句创建函数函数分为标量函数和表值函数。前者返回一个标量值结果，在创建函数时，应在Returns语句后指明标量类型（如：int ）；后者

6、以表的形式返回结果，在创建函数时，应在Returns语句后用关键词Table指时其反回类型。在查询分析器里可使用T-SQL可直接创建函数CREATE FUNCTION 函数名（变参名 类型）RETURNS 类型3. 使用标量函数标量函数接受0个或多个输入参数，并返回一个标量值。因为标量函数只返回一个值，所以通常在一个select语句的列列表中使用它们，也可以在where子句中使用它们。实验14-7：基于CP表编写函数getStock（ ），根据传进的参数“产品编号”，查询并返回相应产品的“库存量”。请阅读以下程序，理解其基本结构和实现思想，给出运行结果。调用函数：函数一般在Select语句或W

7、here子句中被调用，以下是一函数调用实例：create function getStock（ProductID char（6）returns intas begin declare stock int select stock=库存量 from dbo.cp where 产品编号=ProductID return stock endselect dbo.getStock（100002） as 库存量4. 使用表值函数表值函数遵守与标量函数相同的规则，区别在于表值函数返回一个表作为输出。因此，一般在select语句的from子句中使用它们，并可能与其他表或视图进行联接。实验14-8：创建一个自

8、定义函数fun_cpInfo（），根据产品编号返回该产品的名称、价格和库存量。create function fun_cpInfo（product_No char（6）returns table -表值函数，返回查询结果集（即表）return（ select 产品名称,价格,库存量 from CP where 产品编号=product_No）函数创建后，可在SQL语句中调用。调用函数fun_cpInfo（），可在查询分析器中执行如下Select语句：思考：请比较标量函数和表值函数，理解其在编写和使用上的差别。14.5. 实验练习14.5.1 存储过程对于CPXS数据库，完成以下存储过程。1.

9、无参存储过程编写一无参存储过程用于查询每个客户购买产品的情况（包括客户编号、产品编号、客户名称、产品名称、价格、购买日期、购买数量），然后调用该存储过程。请给出程序源码：CREATE PROCEDURE get_informations -此为无参存储过程SELECT XSS.客户编号,CPXSB.产品编号,客户名称,产品名称,价格,销售日期,数量FROM CP,CPXSB,XSSWHERE CP.产品编号=CPXSB.产品编号 and XSS.客户编号=CPXSB.客户编号EXEC get_informations请给出执行测试结果：2. 带有参数的存储过程编写一加密存储过程，查询指定客户购

10、买产品的情况。并调用该存储过程查询客户编号为“000002”的客户购买情况。CREATE PROCEDURE get_CI -此为无参存储过程 C_No intSELECT XSS.客户编号,CPXSB.产品编号,客户名称,产品名称,价格, 销售日期,数量WHERE CP.产品编号=CPXSB.产品编号and XSS.客户编号=CPXSB.客户编号 and CPXSB.客户编号=C_NoEXEC get_CI 0000023. 带有通配符参数的存储过程编写一存储过程，查询指定产品的销售情况。如果没有提供参数，则查询产品名称中包含有“冰箱”的产品销售情况。CREATE PROCEDURE get

11、_SInformations P_Name char（10）=nullif P_Name is null SELECT distinct 销售日期,CPXSB.产品编号,产品名称,价格,数量,销售额 FROM CP,CPXSB,XSS WHERE CP.产品编号=CPXSB.产品编号and CP.产品名称like %冰箱 order by 销售日期asc else SELECT distinct 销售日期,CPXSB.产品编号,产品名称,价格,数量 WHERE CP.产品编号=CPXSB.产品编号and CP.产品名称=P_NameEXEC get_SInformations4. 带有OUTP

12、UT参数的存储过程编写一存储过程，查询指定客户在指定时间段内购买指定产品的数量，存储过程中使用了输入和输出参数。并调用该存储过程查询名称为“家电市场”的客户在2004年购买“洗衣机”的数量。use CPXSgoCREATE PROCEDURE get_amounts -创建存储过程 （ in_c char（20）, in_t char （4）, in_p char （20）, -输入形式参数 out int OUTPUT） -输出形式参数select out=数量from CPXSB,CP,XSSWHERE CPXSB.客户编号=XSS.客户编号and CPXSB.产品编号=CP.产品编号an

13、d CPXSB.销售日期like %+in_t+and XSS.客户名称=in_cand CP.产品名称=in_p -给输出参数赋值DECLARE ou int -定义变量，用于存储结果值EXEC get_amounts 家电公司,2004,ou OUT -实参表print 数量= +convert（char（5）,ou）5. 带有OUTPUT游标参数的存储过程编写一带有OUTPUT游标参数的存储过程，游标结果集为客户信息，并通过调用该存储过程，实现依次读取游标CUR2中各行数据。6. 创建一个多表查询的存储过程。查询在2009年9月18日有销售的产品名称（若无此数据，请先添加之，以便于测试）

14、。CREATE PROCEDURE get_PNames1 S_Time datetime SELECT 产品名称 FROM CP,CPXSB WHERE CP.产品编号=CPXSB.产品编号and 销售日期=S_TimeEXEC get_PNames1 14.5.2 函数对于CPXS数据库，定义完成如下功能的函数。1.据产品名称，查询该产品的相关信息。（函数名为：FU_CP）create function FU_CP（pName char（10） returns table return （select 产品编号,价格,库存量,产品名称 where 产品名称=pName）select*fro

15、m FU_CP（查询产品名称为“mp3”的产品情况2.按某年某季度统计给定产品名称的销售数量及销售金额。FU1_CPXS） create function FU_CPXSB（pName char（10）,sTimeY int,sTimeM int） select 产品名称,数量,销售额 from CPXSB,CP where （CPXSB.产品编号=CP.产品编号and 产品名称=pName and datepart（YY,销售日期）=sTimeY and datepart（QQ,销售日期）=sTimeM）select *from FU_CPXSB（彩色电视机03查询2004年第3季度彩色电视

16、机的销售数量和销售金额3.根据销售商名称，统计其在某年某季度内销售商品名称、数量及金额。（函数名为FU2_CPXS）create function fu_cpxs1（year int,quarter int,客户名称char（10）return select 产品名称,SUM（数量） 数量,SUM（销售额） 销售总额 from CP,CPXSB, XSS where CPXSB.产品编号=CP.产品编号 and CPXSB.客户编号=XSS.客户编号 and 客户名称=客户名称 and datepart（YY,销售日期）=year and datepart（QQ,销售日期）=quarter group by 产品名称Select *From fu2_cpxs1（1广电公司查询广电公司2004年第1季度销售的产品名称、销售数量和销售金额。