实验十四存储过程与函数【实验目的与要求】1．熟练掌握存储过程的编写。
2．熟练掌握函数的编写与使用。
【实验内容与步骤】14.1.基础知识存储过程（Stored Procedure）和函数是一组编译好存储在服务器上的完成特定功能T-SQL代码，是某数据库的对象。
客户端应用程序可以通过指定存储过程或函数的名字并给出参数（如果该存储过程带有参数）来执行存储过程。
14.2.创建用户存储过程1. 使用存储过程模板创建存储过程在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，再展开选择“可编程性”节点，右击“存储过程”，选择“新建存储过程”命令，如图所示：在右侧查询编辑器中出现存储过程的模板，可以在此基础上编辑存储过程，单击“执行”按钮，即可创建该存储过程。
实验14-1：创建一个简单的存储过程，实现从CP表中读取Mp3产品相关信息USE CPXSGOCREATE PROCEDURE get_mp3 --此为无参存储过程ASSELECT产品编号,产品名称,价格,库存量FROM CPWHERE产品名称='mp3'实验14-2：执行存储过程执行存储过程可用下列方法之一：（1）使用存储过程名字如：get_mp3（2) 使用Exec命令：如：EXEC get_mp3执行上面创建的存储过程，并给出执行结果：2. 使用T-SQL语句创建存储过程在查询分析器里使用T-SQL可直接创建存储过程格式：CREATE PROC 过程名@形参名类型@变参名类型OUTPUTASSQL语句实验14-3：创建一个多表查询的存储过程。
问题：查询在2009年9月18日有销售的产品名称（1）请给出相应的代码create proc get_cpasselect产品名称from cp,cpxsbwhere销售日期='2009-9-18'（2）执行存储过程,并给出执行结果：（cpxsb中无销售日期为20090918的数据）14.3.存储过程的参数1. 输入参数（值参）实验14-4：输入参数为某产品的名字。
USE CPXSGOCREATE PROCEDURE P_CPXS2@Product_name char(30)--形式参数AsSELECT产品编号,产品名称,价格,库存量FROM CPWHERE 产品名称= @ Product_nameGO执行存储过程：（1）直接传值：EXEC P_CPXS2 '冰箱'--实参表请给出执行结果：（2）变量传值：DECLARE @temp char(30)SET @temp='洗衣机'EXEC P_CPXS2 @temp --实参表请给出执行结果：实验14-5：使用默认参数阅读以下程序段，理解参数传递过程USE CPXSGOCREATE PROCEDURE P_CPXS3@name varchar(10)=NULL--默认参数ASIF @name IS NULLSELECT 产品编号,产品名称,价格,库存量FROM CPELSESELECT 产品编号,产品名称,价格,库存量FROM CPWHERE 产品名称=@nameGO执行存储过程：(1) 不带参数时EXEC P_CPXS3请给出执行结果：(2) 不带参数时EXEC P_CPXS3 ‘彩电’请给出执行结果：2. 输出参数（变参）实验14-6：利用输出参数计算阶乘。
USE CPXS–判断系统中是否有名为factorial的存储过程，若有，则删除之IF EXISTS(SELECT name FROM sysobjectsWHERE name='factorial'AND type='P')DROP PROCEDURE factorialGO --前面这段仅是准备工作，真正的工作在之后CREATE PROCEDURE factorial --创建存储过程@in float,--输入形式参数@out float OUTPUT--输出形式参数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.4.创建用户自定义函数1. 使用存储过程模板创建存储过程在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，再展开选择“可编程性”节点，右击“函数”，选择“新建”命令，在下一级菜单中选择适合的选项，如图所示：在右侧查询编辑器中出现函数的模板，可以在此基础上编辑函数，单击“执行”按钮，即可创建该函数。
2. 使用T-SQL语句创建函数函数分为标量函数和表值函数。
前者返回一个标量值结果，在创建函数时，应在Returns 语句后指明标量类型(如：int )；后者以表的形式返回结果，在创建函数时，应在Returns 语句后用关键词Table指时其反回类型。
在查询分析器里可使用T-SQL可直接创建函数格式：CREATE FUNCTION 函数名(@形参名类型@变参名类型)[RETURNS 类型]ASSQL语句3. 使用标量函数标量函数接受0个或多个输入参数，并返回一个标量值。
因为标量函数只返回一个值，所以通常在一个select语句的列列表中使用它们，也可以在where子句中使用它们。
实验14-7：基于CP表编写函数getStock( )，根据传进的参数“产品编号”，查询并返回相应产品的“库存量”。
请阅读以下程序，理解其基本结构和实现思想，给出运行结果。
调用函数：函数一般在Select语句或Where子句中被调用，以下是一函数调用实例：请给出执行结果：4. 使用表值函数表值函数遵守与标量函数相同的规则，区别在于表值函数返回一个表作为输出。
因此，一般在select语句的from子句中使用它们，并可能与其他表或视图进行联接。
实验14-8：创建一个自定义函数fun_cpInfo()，根据产品编号返回该产品的名称、价格和库存量。
create function fun_cpInfo(@product_No char(6))returns table --表值函数，返回查询结果集(即表)asreturn( select 产品名称,价格,库存量from CPwhere 产品编号=@product_No)调用函数：函数创建后，可在SQL语句中调用。
调用函数fun_cpInfo()，可在查询分析器中执行如下Select语句：请给出执行结果：思考：请比较标量函数和表值函数，理解其在编写和使用上的差别。
14.5.实验练习14.5.1 存储过程对于CPXS数据库，完成以下存储过程。
1. 无参存储过程编写一无参存储过程用于查询每个客户购买产品的情况（包括客户编号、产品编号、客户名称、产品名称、价格、购买日期、购买数量），然后调用该存储过程。
请给出程序源码：create proc kh_cp_infoasselect cpxsb.客户编号,cpxsb.产品编号,客户名称,产品名称,价格,销售日期,数量from cp,xss,cpxsbwhere cp.产品编号=cpxsb.产品编号and xss.客户编号=cpxsb.客户编号请给出执行测试结果：2. 带有参数的存储过程编写一加密存储过程，查询指定客户购买产品的情况。
并调用该存储过程查询客户编号为“000002”的客户购买情况。
请给出程序源码：create proc kh_info@khbh char(6)with encryptionasselect cpxsb.客户编号,cpxsb.产品编号,客户名称,产品名称,价格,销售日期,数量from cp,xss,cpxsbwhere xss.客户编号=@khbhand cp.产品编号=cpxsb.产品编号and xss.客户编号=cpxsb.客户编号请给出执行测试结果：3. 带有通配符参数的存储过程编写一存储过程，查询指定产品的销售情况。
如果没有提供参数，则查询产品名称中包含有“冰箱”的产品销售情况。
请给出程序源码：create proc cp_infor@mc varchar(20)='%冰箱%'asselect cpxsb.客户编号,cpxsb.产品编号,客户名称,产品名称,价格,销售日期,数量from cp,xss,cpxsbwherecpxsb.产品编号in(select产品编号from cp where产品名称like @mc)and cp.产品编号=cpxsb.产品编号and xss.客户编号=cpxsb.客户编号请给出执行测试结果：4. 带有OUTPUT参数的存储过程编写一存储过程，查询指定客户在指定时间段内购买指定产品的数量，存储过程中使用了输入和输出参数。
并调用该存储过程查询名称为“家电市场”的客户在2004年购买“洗衣机”的数量。
请给出程序源码：请给出执行测试结果：5. 带有OUTPUT游标参数的存储过程编写一带有OUTPUT游标参数的存储过程，游标结果集为客户信息，并通过调用该存储过程，实现依次读取游标CUR2中各行数据。
请给出程序源码：请给出执行测试结果：6. 创建一个多表查询的存储过程。
问题：查询在2009年9月18日有销售的产品名称(若无此数据，请先添加之，以便于测试)。
（1）请给出相应的代码create function cp_name(@cp_name char(10))returns tableasreturn(select产品名称from cp,cpxsbwhere销售日期=@time and cp.产品编号=cpxsb.产品编号)（2）执行存储过程,并给出执行结果：14.5.2 函数对于CPXS数据库，定义完成如下功能的函数。
1.据产品名称，查询该产品的相关信息。
（函数名为：FU_CP）请给出程序源码：create function fu_cp(@cp_name char(10))returns tableasreturn(select*from CPwhere产品名称=@cp_name)请给出执行测试结果：查询产品名称为“mp3”的产品情况2.按某年某季度统计给定产品名称的销售数量及销售金额。
(函数名为：FU1_CPXS)请给出程序源码：create function FU1_CPXS (@cp_name char(20),@year int,@quarter int) returns tableasreturn(select sum(数量)as销售数量,sum(销售额)as销售总额from cp,xss,cpxsbwhere cp.产品编号=cpxsb.产品编号and xss.客户编号=cpxsb.客户编号and datepart(year,销售日期)=@yearand datepart(quarter,销售日期)=@quarterand产品名称=@cp_namegroup by产品名称)请给出执行测试结果：查询2004年第3季度彩色电视机的销售数量和销售金额(数据库中无第三季度的数据，改为查询第一季度)3.根据销售商名称，统计其在某年某季度内销售商品名称、数量及金额。