实验14 存储过程与函数

PAGE第14页共17页厦门理工学院实验报告实验十四存储过程与函数【实验目的与要求】熟练掌握存储过程的编写。熟练掌握函数的编写与使用。【实验内容与步骤】基础知识存储过程（StoredProcedure）和函数是一组编译好存储在服务器上的完成特定功能T-SQL代码，是某数据库的对象。客户端应用程序可以通过指定存储过程或函数的名字并给出参数（如果该存储过程带有参数）来执行存储过程。创建用户存储过程1.使用存储过程模板创建存储过程在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，再展开选择“可编程性”节点，右击“存储过程”，选择“新建存储过程”命令，如图所示：在右侧查询编辑器中出现存储过程的模板，可以在此基础上编辑存储过程，单击“执行”按钮，即可创建该存储过程。实验14-1：创建一个简单的存储过程，实现从CP表中读取Mp3产品相关信息USECPXSGOCREATEPROCEDUREget_mp3--此为无参存储过程ASSELECT产品编号,产品名称,价格,库存量FROMCPWHERE产品名称='mp3'实验14-2：执行存储过程执行存储过程可用下列方法之一：（1）使用存储过程名字如：get_mp3（2)使用Exec命令：如：EXECget_mp3执行上面创建的存储过程，并给出执行结果：2.使用T-SQL语句创建存储过程在查询分析器里使用T-SQL可直接创建存储过程格式：CREATEPROC过程名@形参名类型@变参名类型OUTPUTASSQL语句实验14-3：创建一个多表查询的存储过程。问题：查询在2009年9月18日有销售的产品名称（1）请给出相应的代码createprocget_cpasselect产品名称fromcp,cpxsbwhere销售日期='2009-9-18'（2）执行存储过程,并给出执行结果：（cpxsb中无销售日期为20090918的数据）存储过程的参数1.输入参数（值参）实验14-4：输入参数为某产品的名字。USECPXSGOCREATEPROCEDUREP_CPXS2@Product_namechar(30)--形式参数AsSELECT产品编号,产品名称,价格,库存量FROMCPWHERE产品名称=@Product_nameGO执行存储过程：（1）直接传值：EXECP_CPXS2'冰箱'--实参表请给出执行结果：（2）变量传值：DECLARE@tempchar(30)SET@temp='洗衣机'EXECP_CPXS2@temp--实参表请给出执行结果：实验14-5：使用默认参数阅读以下程序段，理解参数传递过程USECPXSGOCREATEPROCEDUREP_CPXS3@namevarchar(10)=NULL--默认参数ASIF@nameISNULLSELECT产品编号,产品名称,价格,库存量FROMCPELSESELECT产品编号,产品名称,价格,库存量FROMCPWHERE产品名称=@nameGO执行存储过程：(1)不带参数时EXECP_CPXS3请给出执行结果：(2)不带参数时EXECP_CPXS3‘彩电’请给出执行结果：2.输出参数（变参）实验14-6：利用输出参数计算阶乘。USECPXS–判断系统中是否有名为factorial的存储过程，若有，则删除之IFEXISTS(SELECTnameFROMsysobjectsWHEREname='factorial'ANDtype='P')DROPPROCEDUREfactorialGO--前面这段仅是准备工作，真正的工作在之后CREATEPROCEDUREfactorial--创建存储过程@infloat,--输入形式参数@outfloatOUTPUT--输出形式参数ASDECLARE@iintDECLARE@sfloatSET@i=1SET@s=1WHILE@i<=@inBEGINSET@s=@s*@iSET@i=@i+1ENDSET@out=@s--给输出参数赋值调用存储过程：DECLARE@oufloat–定义变量，用于存储结果值EXECfactorial5,@ouOUT--实参表PRINT@ou请给出执行结果：创建用户自定义函数1.使用存储过程模板创建存储过程在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，再展开选择“可编程性”节点，右击“函数”，选择“新建”命令，在下一级菜单中选择适合的选项，如图所示：在右侧查询编辑器中出现函数的模板，可以在此基础上编辑函数，单击“执行”按钮，即可创建该函数。2.使用T-SQL语句创建函数函数分为标量函数和表值函数。前者返回一个标量值结果，在创建函数时，应在Returns语句后指明标量类型(如：int)；后者以表的形式返回结果，在创建函数时，应在Returns语句后用关键词Table指时其反回类型。在查询分析器里可使用T-SQL可直接创建函数格式：CREATEFUNCTION函数名(@形参名类型@变参名类型)[RETURNS类型]ASSQL语句3.使用标量函数标量函数接受0个或多个输入参数，并返回一个标量值。因为标量函数只返回一个值，所以通常在一个select语句的列列表中使用它们，也可以在where子句中使用它们。实验14-7：基于CP表编写函数getStock()，根据传进的参数“产品编号”，查询并返回相应产品的“库存量”。请阅读以下程序，理解其基本结构和实现思想，给出运行结果。调用函数：函数一般在Select语句或Where子句中被调用，以下是一函数调用实例：请给出执行结果：4.使用表值函数表值函数遵守与标量函数相同的规则，区别在于表值函数返回一个表作为输出。因此，一般在select语句的from子句中使用它们，并可能与其他表或视图进行联接。实验14-8：创建一个自定义函数fun_cpInfo()，根据产品编号返回该产品的名称、价格和库存量。createfunctionfun_cpInfo(@product_Nochar(6))returnstable--表值函数，返回查询结果集(即表)asreturn(select产品名称,价格,库存量 fromCP where产品编号=@product_No)调用函数：函数创建后，可在SQL语句中调用。调用函数fun_cpInfo()，可在查询分析器中执行如下Select语句：请给出执行结果：思考：请比较标量函数和表值函数，理解其在编写和使用上的差别。实验练习14.5.1存储过程对于CPXS数据库，完成以下存储过程。1.无参存储过程编写一无参存储过程用于查询每个客户购买产品的情况（包括客户编号、产品编号、客户名称、产品名称、价格、购买日期、购买数量），然后调用该存储过程。请给出程序源码：createprockh_cp_infoasselectcpxsb.客户编号,cpxsb.产品编号,客户名称,产品名称,价格,销售日期,数量fromcp,xss,cpxsbwherecp.产品编号=cpxsb.产品编号andxss.客户编号=cpxsb.客户编号请给出执行测试结果：2.带有参数的存储过程编写一加密存储过程，查询指定客户购买产品的情况。并调用该存储过程查询客户编号为“000002”的客户购买情况。请给出程序源码：createprockh_info@khbhchar(6)withencryptionasselectcpxsb.客户编号,cpxsb.产品编号,客户名称,产品名称,价格,销售日期,数量fromcp,xss,cpxsbwherexss.客户编号=@khbhandcp.产品编号=cpxsb.产品编号andxss.客户编号=cpxsb.客户编号请给出执行测试结果：3.带有通配符参数的存储过程编写一存储过程，查询指定产品的销售情况。如果没有提供参数，则查询产品名称中包含有“冰箱”的产品销售情况。请给出程序源码：createproccp_infor@mcvarchar(20)='%冰箱%'asselectcpxsb.客户编号,cpxsb.产品编号,客户名称,产品名称,价格,销售日期,数量fromcp,xss,cpxsbwherecpxsb.产品编号in(select产品编号fromcpwhere产品名称like@mc)andcp.产品编号=cpxsb.产品编号andxss.客户编号=cpxsb.客户编号请给出执行测试结果：4.带有OUTPUT参数的存储过程编写一存储过程，查询指定客户在指定时间段内购买指定产品的数量，存储过程中使用了输入和输出参数。并调用该存储过程查询名称为“家电市场”的客户在2004年购买“洗衣机”的数量。请给出程序源码：请给出执行测试结果：5.带有OUTPUT游标参数的存储过程编写一带有OUTPUT游标参数的存储过程，游标结果集为客户信息，并通过调用该存储过程，实现依次读取游标CUR2中各行数据。请给出程序源码：请给出执行测试结果：6.创建一个多表查询的存储过程。问题：查询在2009年9月18日有销售的产品名称(若无此数据，请先添加之，以便于测试)。（1）请给出相应的代码createfunctioncp_name(@cp_namechar(10))returnstableasreturn(select产品名称fromcp,cpxsbwhere销售日期=@timeandcp.产品编号=cpxsb.产品编号)（2）执行存储过程,并给出执行结果：14.5.2函数对于CPXS数据库，定义完成如下功能的函数。1.据产品名称，查询该产品的相关信息。（函数名为：FU_CP）请给出程序源码：createfunctionfu_cp(@cp_namechar(10))returnstableasreturn(select*fromCPwhere产品名称=@cp_name)请给出执行测试结果：查询产品名称为“mp3”的产品情况2.按某年某季度统计给定产品名称的销售数量及销售金额。(函数名为：FU1_CPXS)请给出程序源码：createfunctionFU1_CPXS(@cp_namechar(20),@yearint,@quarterint)returnstableasreturn(selectsum(数量)as销售数量,sum(销售额)as销售总额fromcp,xss,cpxsbwherecp.产品编号=cpxsb.产品编号andxss.客户编号=cpxsb.客户编号anddatepart(year,销售日期)=@yearanddatepart(quarter,销售日期)=@quarterand产品名称=@cp_namegroupby产品名称)请给出执行测试结果：查询2004年第3季度彩色电视机的销售数量和销售金额(数据库中无第三季度的数据，改为查询第一季度)3.根据销售商名称，统计其在某年某季度内销售商品名称、数量及金额。（函数名为FU2_CPXS）请给出程序源码：CreatefunctionFU2_CPXS(@kh_namechar(20),@yearint,@quarterint)returnst