数据库原理及应用实验12_

1、-. z.实验十二存储过程与函数*：计算机科学与技术*：专业：班级：同组人：无实验日期：【实验目的与要求】熟练掌握存储过程的编写。 熟练掌握函数的编写与使用。【实验内容与步骤】根底知识存储过程Stored Procedure和函数是一组编译好存储在效劳器上的完成特定功能T-SQL代码，是*数据库的对象。客户端应用程序可以通过指定存储过程或函数的名字并给出参数如果该存储过程带有参数来执行存储过程。创立用户存储过程1. 使用存储过程模板创立存储过程在【对象资源管理器】窗口中，展开数据库节点，再展开所选择的具体数据库节点，再展开选择可编程性节点，右击存储过程，选择新建存储过程命令，如下图：在右侧查询

2、编辑器中出现存储过程的模板，可以在此根底上编辑存储过程，单击执行按钮，即可创立该存储过程。实验14-1：创立一个简单的存储过程，实现从CP表中读取Mp3产品相关信息USE CP*SGOCREATEPROCEDUREget_mp3 -此为无参存储过程ASSELECT产品编号,产品名称,价格,库存量FROMCPWHERE产品名称=mp3实验14-2：执行存储过程执行存储过程可用以下方法之一：1使用存储过程名字如：get_mp32) 使用E*ec命令：如：E*ECget_mp3执行上面创立的存储过程，并给出执行结果：2. 使用T-SQL语句创立存储过程在查询分析器里使用T-SQL可直接创立存储过程格

3、式：CREATE PROC 过程名形参名 类型变参名 类型 OUTPUTAS SQL语句实验14-3：创立一个多表查询的存储过程。问题：查询在2009年9月18日有销售的产品名称1请给出相应的代码CREATEprocedure G_product ASSELECT 产品名称FROM CP,cp*sbWHERE CP*SB.产品编号=CP.产品编号and 销售日期=2009-9-18 0:00:00;2执行存储过程,并给出执行结果：存储过程的参数1. 输入参数值参实验14-4：输入参数为*产品的名字。USE CP*SGOCREATEPROCEDURE P_CP*S2Product_name ch

4、ar(30)-形式参数AsSELECT产品编号,产品名称,价格,库存量FROMCPWHERE产品名称= Product_nameGO执行存储过程：1直接传值：E*EC P_CP*S2 冰箱-实参表请给出执行结果：2变量传值：DECLARE temp char(30)SET temp=洗衣机E*EC P_CP*S2 temp -实参表请给出执行结果：实验14-5：使用默认参数阅读以下程序段，理解参数传递过程USE CP*SGOCREATEPROCEDURE P_CP*S3name varchar(10)=NULL-默认参数ASIF name ISNULLSELECT产品编号,产品名称,价格,库存

5、量FROMCPELSESELECT产品编号,产品名称,价格,库存量FROMCPWHERE产品名称=nameGO执行存储过程：(1) 不带参数时E*EC P_CP*S3请给出执行结果：(2)带参数时E*EC P_CP*S3 彩色电视机请给出执行结果：2. 输出参数变参实验14-6：利用输出参数计算阶乘。USE CP*S判断系统中是否有名为factorial的存储过程，假设有，则删除之IFE*ISTS(SELECTnameFROM sysobjectsWHEREname=factorialANDtype=P)DROPPROCEDURE factorialGO-前面这段仅是准备工作，真正的工作在之后

6、CREATEPROCEDURE factorial-创立存储过程 in float,-输入形式参数 out floatOUTPUT-输出形式参数ASDECLARE i intDECLARE s floatSET i=1SET s=1WHILE i=inBEGINSET s=s*iSET i=i+1ENDSET out=s -给输出参数赋值调用存储过程：DECLARE ou float定义变量，用于存储结果值E*EC factorial 5,ou OUT -实参表PRINT ou请给出执行结果：创立用户自定义函数1. 使用存储过程模板创立存储过程在【对象资源管理器】窗口中，展开数据库节点，再展开

7、所选择的具体数据库节点，再展开选择可编程性节点，右击函数，选择新建命令，在下一级菜单中选择适合的选项，如下图：在右侧查询编辑器中出现函数的模板，可以在此根底上编辑函数，单击执行按钮，即可创立该函数。2. 使用T-SQL语句创立函数函数分为标量函数和表值函数。前者返回一个标量值结果，在创立函数时，应在Returns语句后指明标量类型(如：int )；后者以表的形式返回结果，在创立函数时，应在Returns语句后用关键词Table指时其反回类型。在查询分析器里可使用T-SQL可直接创立函数格式：CREATE FUNCTION函数名(形参名 类型变参名 类型)RETURNS 类型AS SQL语句3.

8、 使用标量函数标量函数承受0个或多个输入参数，并返回一个标量值。因为标量函数只返回一个值，所以通常在一个select语句的列列表中使用它们，也可以在where子句中使用它们。实验14-7：基于CP表编写函数getStock( )，根据传进的参数产品编号，查询并返回相应产品的库存量。请阅读以下程序，理解其根本构造和实现思想，给出运行结果。createfunction getStock(productid char(6)returnsintasbegindeclare stock int;select stock=库存量from dbo.cpwhere 产品编号=productidreturn s

9、tock;end;调用函数：函数一般在Select语句或Where子句中被调用，以下是一函数调用实例：select dbo.getStock(100001)as 库存量请给出执行结果：4. 使用表值函数表值函数遵守与标量函数一样的规则，区别在于表值函数返回一个表作为输出。因此，一般在select语句的from子句中使用它们，并可能与其他表或视图进展联接。实验14-8：创立一个自定义函数fun_cpInfo()，根据产品编号返回该产品的名称、价格和库存量。create function fun_cpInfo(product_No char(6)returns table-表值函数，返回查询结果集

10、(即表)asreturn( select 产品名称,价格,库存量from CP where 产品编号=product_No)调用函数：函数创立后，可在SQL语句中调用。调用函数fun_cpInfo()，可在查询分析器中执行如下Select语句：请给出执行结果：思考：请比拟标量函数和表值函数，理解其在编写和使用上的差异。没什么差异。实验练习14.5.1 存储过程对于CP*S数据库，完成以下存储过程。 1. 无参存储过程编写一无参存储过程用于查询每个客户购置产品的情况包括客户编号、产品编号、客户名称、产品名称、价格、购置日期、购置数量，然后调用该存储过程。请给出程序源码：createprocedu

11、re au_info_allasselect*from cp leftjoin cp*sbon CP*SB.产品编号=CP.产品编号leftjoin *sson cp*sb.客户编号=*ss.客户编请给出执行测试结果：2. 带有参数的存储过程编写一加密存储过程，查询指定客户购置产品的情况。并调用该存储过程查询客户编号为000002的客户购置情况。请给出程序源码：createprocedure aa_info_all guestno char(6)with encryptionasselect*from cp leftjoin cp*sbon CP*SB.产品编号=CP.产品编号leftjoin

12、 *sson cp*sb.客户编号=*ss.客户编号where *ss.客户编号=guestno;请给出执行测试结果：3. 带有通配符参数的存储过程编写一存储过程，查询指定产品的销售情况。如果没有提供参数，则查询产品名称中包含有冰箱的产品销售情况。请给出程序源码：createprocedure aaa_info_all(productname char(16)asselect 数量from cp leftjoin cp*sbon CP*SB.产品编号=CP.产品编号leftjoin *sson cp*sb.客户编号=*ss.客户编号where 产品名称=productname请给出执行测试结果

13、：4. 带有OUTPUT参数的存储过程编写一存储过程，查询指定客户在指定时间段内购置指定产品的数量，存储过程中使用了输入和输出参数。并调用该存储过程查询名称为家电市场的客户在2004年购置洗衣机的数量。请给出程序源码：createprocedure auproductnum intoutput,guestname char(30),date char(4),productname char(30)asselect productnum=数量from cp,cp*sb,*sswhere CP*SB.产品编号=CP.产品编号and cp*sb.客户编号=*ss.客户编号and *ss.客户名称=g

14、uestname and cp*sb.销售日期like%+date+%and cp.产品名称=productname请给出执行测试结果：5. 带有OUTPUT游标参数的存储过程编写一带有OUTPUT游标参数的存储过程，游标结果集为客户信息，并通过调用该存储过程，实现依次读取游标CUR2中各行数据。请给出程序源码：createprocedure titl_cursor titl_cursor cursorvaryingoutputasset titl_cursor=cursorforward_only static forselect*from *ssopen titl_cursordeclar

15、e mycursor cursore*ec titl_cursor titl_cursor=mycursor outputfetch ne*t from mycursorwhile(fetch_status=0)beginfetch ne*t from mycursorend请给出执行测试结果：6. 创立一个多表查询的存储过程。问题：查询在2009年9月18日有销售的产品名称(假设无此数据，请先添加之，以便于测试)。1请给出相应的代码createprocedure au_info_alllasselect cp.产品名称from cp leftjoin cp*sbon CP*SB.产品编号=C

16、P.产品编号leftjoin *sson cp*sb.客户编号=*ss.客户编号where cp*sb.销售日期=2009-09-182执行存储过程,并给出执行结果：14.5.2 函数对于CP*S数据库，定义完成如下功能的函数。1.据产品名称，查询该产品的相关信息。函数名为：FU_CP请给出程序源码：createfunction fu_cp(productname char(12)returnstableasreturn(select*from cpwhere 产品名称=productname)请给出执行测试结果：查询产品名称为mp3的产品情况select*from fu_cp(mp3)2.按

17、*年*季度统计给定产品名称的销售数量及销售金额。(函数名为：FU1_CP*S) 请给出程序源码：CREATEFUNCTION FU1_CP*S (YEAR INT,QUARTER INT,产品名称char(10)RETURNSTABLEASRETURNSELECT 产品名称,SUM(数量)AS 销售数量,SUM(销售额)AS 销售总额FROM CP,CP*SB WHERE CP*SB.产品编号=CP.产品编号AND 产品名称= 产品名称ANDDATEPART(YY,销售日期)=YEAR AN请给出执行测试结果：查询2004年第3季度彩色电视机的销售数量和销售金额select*from FU1_

18、CP*S(2004,03,彩色电视机)3.根据销售商名称，统计其在*年*季度内销售商品名称、数量及金额。函数名为FU2_CP*S请给出程序源码：CREATEFUNCTION FU3_CP*S (客户名称char(10),YEAR INT,QUARTER INT)RETURNSTABLEASRETURNSELECT 产品名称,SUM(数量)AS 销售数量,SUM(销售额)AS 销售总额FROM *SS,CP*SB,CP WHERE CP*SB.产品编号=CP.产品编号AND CP*SB.客户编号=*SS.客户编号AND 客户名称= 客户名称ANDDATEPART(YY,销售日期)=YEAR ANDDATEPART(QQ,销售日期)=QUARTER GROUPBY 产品名称请给出执行测试结果：查询广电公司2004年第1季度销售的产品名称、销售数量和销售金额。select*from FU3_CP*