数据库原理及应用实验12_.doc
38页
- -实验十二 存储过程与函数XX：计算机科学与技术学号：专业：班级：同组人：无实验日期：【实验目的与要求】1．2． 熟练掌握存储过程的编写 3．4． 熟练掌握函数的编写与使用实验内容与步骤】14.1.14.2. 基础知识存储过程（Stored Procedure）和函数是一组编译好存储在服务器上的完成特定功能T-SQL代码，是某数据库的对象客户端应用程序可以通过指定存储过程或函数的名字并给出参数（如果该存储过程带有参数）来执行存储过程14.3.14.4. 创建用户存储过程1. 使用存储过程模板创建存储过程在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，再展开选择“可编程性”节点，右击“存储过程”，选择“新建存储过程”命令，如图所示：在右侧查询编辑器中出现存储过程的模板，可以在此基础上编辑存储过程，单击“执行”按钮，即可创建该存储过程实验14-1：创建一个简单的存储过程，实现从CP表中读取Mp3产品相关信息USE CPXSGOCREATEPROCEDUREget_mp3 --此为无参存储过程ASSELECT产品编号,产品名称,价格,库存量FROMCPWHERE产品名称=mp3实验14-2：执行存储过程执行存储过程可用下列方法之一：（1）使用存储过程名字如：get_mp3（2) 使用Exec命令：如：EXECget_mp3执行上面创建的存储过程，并给出执行结果：2. 使用T-SQL语句创建存储过程在查询分析器里使用T-SQL可直接创建存储过程格式：CREATE PROC 过程名形参名 类型变参名 类型 OUTPUTAS SQL语句实验14-3：创建一个多表查询的存储过程。
问题：查询在2009年9月18日有销售的产品名称（1）请给出相应的代码CREATEprocedure G_product ASSELECT 产品名称FROM CP,cpxsbWHERE CPXSB.产品编号=CP.产品编号and 销售日期=2009-9-18 0:00:00;（2）执行存储过程,并给出执行结果：14.5.14.6. 存储过程的参数1. 输入参数（值参）实验14-4：输入参数为某产品的名字USE CPXSGOCREATEPROCEDURE P_CPXS2Product_name char(30)--形式参数AsSELECT产品编号,产品名称,价格,库存量FROMCPWHERE产品名称= Product_nameGO执行存储过程：（1）直接传值：EXEC P_CPXS2 冰箱--实参表请给出执行结果：（2）变量传值：DECLARE temp char(30)SET temp=洗衣机EXEC P_CPXS2 temp --实参表请给出执行结果：实验14-5：使用默认参数阅读以下程序段，理解参数传递过程 USE CPXSGOCREATEPROCEDURE P_CPXS3name varchar(10)=NULL--默认参数ASIF name ISNULLSELECT产品编号,产品名称,价格,库存量FROMCPELSESELECT产品编号,产品名称,价格,库存量FROMCPWHERE产品名称=nameGO执行存储过程：(1) 不带参数时EXEC P_CPXS3请给出执行结果：(2)带参数时EXEC P_CPXS3 ‘彩色电视机’请给出执行结果：2. 输出参数（变参）实验14-6：利用输出参数计算阶乘。
USE CPXS–判断系统中是否有名为factorial的存储过程，若有，则删除之IFEXISTS(SELECTnameFROM sysobjectsWHEREname=factorialANDtype=P)DROPPROCEDURE factorialGO--前面这段仅是准备工作，真正的工作在之后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–定义变量，用于存储结果值EXEC factorial 5,ou OUT --实参表PRINT ou请给出执行结果：14.7.14.8. 创建用户自定义函数1. 使用存储过程模板创建存储过程在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，再展开选择“可编程性”节点，右击“函数”，选择“新建”命令，在下一级菜单中选择适合的选项，如图所示：在右侧查询编辑器中出现函数的模板，可以在此基础上编辑函数，单击“执行”按钮，即可创建该函数。
2. 使用T-SQL语句创建函数函数分为标量函数和表值函数前者返回一个标量值结果，在创建函数时，应在Returns语句后指明标量类型(如：int )；后者以表的形式返回结果，在创建函数时，应在Returns语句后用关键词Table指时其反回类型在查询分析器里可使用T-SQL可直接创建函数格式：CREATE FUNCTION函数名(形参名 类型变参名 类型)[RETURNS 类型]AS SQL语句3. 使用标量函数标量函数接受0个或多个输入参数，并返回一个标量值因为标量函数只返回一个值，所以通常在一个select语句的列列表中使用它们，也可以在where子句中使用它们实验14-7：基于CP表编写函数getStock( )，根据传进的参数“产品编号”，查询并返回相应产品的“库存量”请阅读以下程序，理解其基本结构和实现思想，给出运行结果createfunction getStock(productid char(6))returnsintasbegindeclare stock int;select stock=库存量from dbo.cpwhere 产品编号=productidreturn stock;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--表值函数，返回查询结果集(即表)asreturn( select 产品名称,价格,库存量from CP where 产品编号=product_No)调用函数：函数创建后，可在SQL语句中调用调用函数fun_cpInfo()，可在查询分析器中执行如下Select语句：请给出执行结果：思考：请比较标量函数和表值函数，理解其在编写和使用上的差别没什么差别14.9.14.10. 实验练习14.5.1 存储过程对于CPXS数据库，完成以下存储过程 1. 无参存储过程编写一无参存储过程用于查询每个客户购买产品的情况（包括客户编号、产品编号、客户名称、产品名称、价格、购买日期、购买数量），然后调用该存储过程请给出程序源码：createprocedure au_info_allasselect*from cp leftjoin cpxsbon CPXSB.产品编号=CP.产品编号leftjoin xsson cpxsb.客户编号=xss.客户编请给出执行测试结果：2. 带有参数的存储过程编写一加密存储过程，查询指定客户购买产品的情况。
并调用该存储过程查询客户编号为“000002”的客户购买情况请给出程序源码：createprocedure aa_info_all guestno char(6)with encryptionasselect*from cp leftjoin cpxsbon CPXSB.产品编号=CP.产品编号leftjoin xsson cpxsb.客户编号=xss.客户编号where xss.客户编号=guestno;请给出执行测试结果：3. 带有通配符参数的存储过程编写一存储过程，查询指定产品的销售情况如果没有提供参数，则查询产品名称中包含有“冰箱”的产品销售情况请给出程序源码：createprocedure aaa_info_all(productname char(16))asselect 数量from cp leftjoin cpxsbon CPXSB.产品编号=CP.产品编号leftjoin xsson cpxsb.客户编号=xss.客户编号where 产品名称=productname请给出执行测试结果：4. 带有OUTPUT参数的存储过程编写一存储过程，查询指定客户在指定时间段内购买指定产品的数量，存储过程中使用了输入和输出参数。
并调用该存储过程查询名称为“家电市场”的客户在2004年购买“洗衣机”的数量。
