实验14存储过程与函数.docx

1、实验14存储过程与函数实验十四存储过程与函数姓名：廖冬凤学号：20070721140专业：网络工程班级：07网络（1）班同组人：无实验日期：2009-12-10【实验目的与要求】1 熟练掌握存储过程的创建、调用和删除。2 理解什么是标量函数、内嵌表值函数及多语句表值函数。3 熟练掌握标量函数、内嵌表值函数、多语句表值函数的定义和调用。【实验内容与步骤】14.1存储过程存储过程（StoredProcedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库。中用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程的优点 1.存储过程只在创造时进行编译，以后每

2、次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。ITPUB个人空间Z5:-C 2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。ITPUB个人空间b NjX;_3.存储过程可以重复使用,可减少数据库开发人员的工作量?7q.JSt6f H4o04.安全性高,可设定只有某此用户才具有对指定存储过程的使用权对于CPXS数据库，创建如下存储过程： 1、无参存储过程编写一无参存储过程用于查询每个客户购买产品的情况（包括客户编号

3、、产品编号、客户名称、产品名称、价格、购买日期、购买数量），然后调用该存储过程。CREATE PROCEDURE CPXS_CUSAS SELECT CPXSB.客户编号,CPXSB.产品编号,客户名称,产品名称,价格,销售日期,数量FROM CP,XSS,CPXSBWHERE XSS.客户编号=CPXSB.客户编号 and CP.产品编号=CPXSB.产品编号EXEC CPXS_CUS2、带有参数的存储过程编写一加密存储过程，查询指定客户购买产品的情况。并调用该存储过程查询客户编号为“000002”的客户购买情况。CREATE PROCEDURE CPXS_CGQCGQ VARCHAR(10

4、)AS SELECT CPXSB.客户编号,CPXSB.产品编号,客户名称,产品名称,价格,销售日期,数量FROM CP,XSS,CPXSBWHERE XSS.客户编号=CPXSB.客户编号 and CP.产品编号=CPXSB.产品编号 and CPXSB.客户编号=CGQEXEC CPXS_CGQ 0000023、带有通配符参数的存储过程编写一存储过程，查询指定产品的销售情况。如果没有提供参数，则查询产品名称中包含有“冰箱”的产品销售情况。CREATE PROCEDURE CPXS_BXBX VARCHAR(10)=%冰箱%AS SELECT CPXSB.客户编号,CPXSB.产品编号,客户

5、名称,产品名称,价格,销售日期,数量FROM CP,XSS,CPXSBWHERE XSS.客户编号=CPXSB.客户编号 and CP.产品编号=CPXSB.产品编号 and 产品名称 LIKE BXEXEC CPXS_BX %冰箱%4、带有OUTPUT参数的存储过程编写一存储过程，查询指定客户在指定时间段内购买指定产品的数量，存储过程中使用了输入和输出参数。并调用该存储过程查询名称为“家电市场”的客户在2004年购买“洗衣机”的数量。CREATE PROCEDURE CPXS_CJDCUS VARCHAR(30),TIME VARCHAR(30),XYJ VARCHAR(30),BUYNUM

6、 INT OUTPUTAS SET BUYNUM= (select cpxsb.数量 from cpxsb where cpxsb.客户编号= (select xss.客户编号 from xss where xss.客户名称=CUS) and cpxsb.销售日期 like TIME and cpxsb.产品编号= (select cp.产品编号 from cp where 产品名称 LIKE XYJ)DECLARE BUYNUM INT EXEC CPXS_CJD 家电市场,%2004%,%洗衣机%,BUYNUM OUTPUT SELECT BUYNUM AS 购买的数量5、带有OUTPUT

7、游标参数的存储过程编写一带有OUTPUT游标参数的存储过程，游标结果集为客户信息，并通过调用该存储过程，实现依次读取游标CUR2中各行数据。drop procedure pro_and_cursor-创建存储过程create procedure pro_and_cursoras-创建游标declare output_all_cs_inf scroll cursorfor select 客户编号,客户名称,地区,负责人,电话 from xss order by 客户编号 open output_all_cs_inf-定义变量存储游标检索信息列值declare csid char (6), csn

8、ame char(30), zone char(10), chargeman char(8), tel char(12)print 游标结果集的纪录总数为+cast(cursor_rows as varchar(2)-执行第一次取数操作，将游标定位到第一行纪录Fetch next from output_all_cs_inf into csid , csname , zone , chargeman , telwhile Fetch_status=0begin -输出纪录 print 客户编号:+cast(csid as char(8)+ 客户名称:+cast(csname as char(1

9、5)+ 地区:+cast(zone as char(6)+ 负责人:+cast(chargeman as char(8)+ 电话:+ cast(tel as char(12)Fetch next from output_all_cs_inf into csid , csname , zone , chargeman , telendexec pro_and_cursor14.2 函数1、函数的定义对于CPXS数据库，定义完成如下功能的函数：（1） 据产品名称，查询该产品的相关信息；（函数名为FU_CP）create Function FU_CP(cpname char(30)returns t

10、ableasreturn (select cp.产品编号,cp.产品名称,cp.价格,cp.库存量 from cp where cp.产品名称=cpname)select * fromFU_CP(洗衣机)（2）按某年某季度统计给定产品名称的销售数量及销售金额；分别用名为FU1_CPXS内嵌表值函数和名为FU2_CPXS的多语句表值函数。create function FU1_CPXS (pname char(30),year varchar(20),quarter varchar(20)returns tableasreturn (select pname as 产品名称, sum(cpxsb

11、.数量) as 销售数量,sum(cpxsb.销售额)as 销售总金额 from cpxsb where cpxsb.产品编号=(select cp.产品编号 from cpwhere cp.产品名称=pname) and datename(yy,cpxsb.销售日期)=year and datename(qq,cpxsb.销售日期)=quarter)create function FU2_CPXS(pname char(30),year varchar(20),quarter varchar(20)returns 销售情况 table(产品名称 char(30), 销售数量 int, 销售金

12、额 float(8)asbegin insert into 销售情况 select pname, sum(cpxsb.数量), sum(cpxsb.销售额) from cpxsb where cpxsb.产品编号=(select cp.产品编号 from cp where cp.产品名称=pname) and datename(yy,cpxsb.销售日期)=year and datename(qq,cpxsb.销售日期)=quarterreturnend（3）根据销售商名称，统计其在某年某季度内销售商品名称、数量及金额。（函数名为FU3_CPXS）create function FU3_CPX

13、S (cname char(30),year int,quarter int)returns tableasreturn (select xss.客户名称,cp.产品名称,cpxsb.数量,cpxsb.销售额 from cpxsb inner join xss on cpxsb.客户编号=xss.客户编号,cp where cp.产品编号=cpxsb.产品编号 and xss.客户名称=cname and datepart(yy,cpxsb.销售日期)=year and datepart(qq,cpxsb.销售日期)=quarter)2、函数的调用（1）对函数FU_CP，查询产品名称为“mp3

14、”的产品情况；select * from FU_CP (MP3)（2）对函数FU1_CPXS，查询2004年第3季度彩色电视机的销售数量和销售金额；-查询第三季度select *from FU1_CPXS(彩色电视机,2004,3)-查询第二季度select *from FU1_CPXS(彩色电视机,2004,2)（3）对函数FU2_CPXS，查询2004年第1季度洗衣机的销售数量和销售金额；select *from FU2_CPXS(洗衣机,2004,1)（4）对函数FU3_CPXS，查询广电公司2004年第1季度销售的产品名称、销售数量和销售金额。select *from FU3_CPXS(广电公司,2004,1)