还剩27页未读,继续阅读
本资源只提供10页预览,全部文档请下载后查看!喜欢就下载吧,查找使用更方便
文本内容:
实验十二 存储过程与函数姓名计算机科学与技术学号专业班级同组人无实验日期【实验目的与要求】1.熟练掌握存储过程的编写1.熟练掌握函数的编写与使用【实验内容与步骤】
14.
1.基础知识存储过程(StoredProcedure)和函数是一组编译好存储在服务器上的完成特定功能T-SQL代码,是某数据库的对象客户端应用程序可以通过指定存储过程或函数的名字并给出参数(如果该存储过程带有参数)来执行存储过程
14.
2.创建用户存储过程
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)请给出相应的代码CREATEprocedureG_productASSELECT产品名称FROMCPcpxsbWHERECPXSB.产品编号=CP.产品编号and销售日期=2009-9-180:00:00;
(2)执行存储过程并给出执行结果
14.
3.存储过程的参数
1.输入参数(值参)实验14-4输入参数为某产品的名字USECPXSGOCREATEPROCEDUREP_CPXS2@Product_namechar30--形式参数AsSELECT产品编号产品名称价格库存量FROMCPWHERE产品名称=@Product_nameGO执行存储过程
(1)直接传值EXECP_CPXS2冰箱--实参表请给出执行结果
(2)变量传值DECLARE@tempchar30SET@temp=洗衣机EXECP_CPXS2@temp--实参表请给出执行结果实验14-5使用默认参数阅读以下程序段,理解参数传递过程 USECPXSGOCREATEPROCEDUREP_CPXS3@namevarchar10=NULL--默认参数ASIF@nameISNULLSELECT产品编号产品名称价格库存量FROMCPELSESELECT产品编号产品名称价格库存量FROMCPWHERE产品名称=@nameGO执行存储过程1不带参数时EXECP_CPXS3请给出执行结果2带参数时EXECP_CPXS3‘彩色电视机’请给出执行结果
2.输出参数(变参)实验14-6利用输出参数计算阶乘USECPXS–判断系统中是否有名为factorial的存储过程,若有,则删除之IFEXISTSSELECTnameFROMsysobjectsWHEREname=factorialANDtype=PDROPPROCEDUREfactorialGO--前面这段仅是准备工作,真正的工作在之后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请给出执行结果
14.
4.创建用户自定义函数
1.使用存储过程模板创建存储过程在【对象资源管理器】窗口中,展开“数据库”节点,再展开所选择的具体数据库节点,再展开选择“可编程性”节点,右击“函数”,选择“新建”命令,在下一级菜单中选择适合的选项,如图所示在右侧查询编辑器中出现函数的模板,可以在此基础上编辑函数,单击“执行”按钮,即可创建该函数
2.使用T-SQL语句创建函数函数分为标量函数和表值函数前者返回一个标量值结果,在创建函数时,应在Returns语句后指明标量类型如int;后者以表的形式返回结果,在创建函数时,应在Returns语句后用关键词Table指时其反回类型在查询分析器里可使用T-SQL可直接创建函数格式CREATEFUNCTION函数名@形参名类型@变参名类型[RETURNS类型]ASSQL语句
3.使用标量函数标量函数接受0个或多个输入参数,并返回一个标量值因为标量函数只返回一个值,所以通常在一个select语句的列列表中使用它们,也可以在where子句中使用它们实验14-7基于CP表编写函数getStock,根据传进的参数“产品编号”,查询并返回相应产品的“库存量”请阅读以下程序,理解其基本结构和实现思想,给出运行结果createfunctiongetStock@productidchar6returnsintasbegindeclare@stockint;select@stock=库存量fromdbo.cpwhere产品编号=@productidreturn@stock;end;调用函数函数一般在Select语句或Where子句中被调用,以下是一函数调用实例selectdbo.getStock100001as库存量请给出执行结果
4.使用表值函数表值函数遵守与标量函数相同的规则,区别在于表值函数返回一个表作为输出因此,一般在select语句的from子句中使用它们,并可能与其他表或视图进行联接实验14-8创建一个自定义函数fun_cpInfo,根据产品编号返回该产品的名称、价格和库存量createfunctionfun_cpInfo@product_Nochar6returnstable--表值函数,返回查询结果集即表asreturnselect产品名称价格库存量fromCPwhere产品编号=@product_No调用函数函数创建后,可在SQL语句中调用调用函数fun_cpInfo,可在查询分析器中执行如下Select语句请给出执行结果思考请比较标量函数和表值函数,理解其在编写和使用上的差别没什么差别
14.
5.实验练习
14.
5.1存储过程对于CPXS数据库,完成以下存储过程
1.无参存储过程编写一无参存储过程用于查询每个客户购买产品的情况(包括客户编号、产品编号、客户名称、产品名称、价格、购买日期、购买数量),然后调用该存储过程请给出程序源码createprocedureau_info_allasselect*fromcpleftjoincpxsbonCPXSB.产品编号=CP.产品编号leftjoinxssoncpxsb.客户编号=xss.客户编请给出执行测试结果
2.带有参数的存储过程编写一加密存储过程,查询指定客户购买产品的情况并调用该存储过程查询客户编号为“000002”的客户购买情况请给出程序源码createprocedureaa_info_all@guestnochar6withencryptionasselect*fromcpleftjoincpxsbonCPXSB.产品编号=CP.产品编号leftjoinxssoncpxsb.客户编号=xss.客户编号wherexss.客户编号=@guestno;请给出执行测试结果
3.带有通配符参数的存储过程编写一存储过程,查询指定产品的销售情况如果没有提供参数,则查询产品名称中包含有“冰箱”的产品销售情况请给出程序源码createprocedureaaa_info_all@productnamechar16asselect数量fromcpleftjoincpxsbonCPXSB.产品编号=CP.产品编号leftjoinxssoncpxsb.客户编号=xss.客户编号where产品名称=@productname请给出执行测试结果
4.带有OUTPUT参数的存储过程编写一存储过程,查询指定客户在指定时间段内购买指定产品的数量,存储过程中使用了输入和输出参数并调用该存储过程查询名称为“家电市场”的客户在2004年购买“洗衣机”的数量请给出程序源码createprocedureau@productnumintoutput@guestnamechar30@datechar4@productnamechar30asselect@productnum=数量fromcpcpxsbxsswhereCPXSB.产品编号=CP.产品编号andcpxsb.客户编号=xss.客户编号andxss.客户名称=@guestnameandcpxsb.销售日期like%+@date+%andcp.产品名称=@productname请给出执行测试结果
5.带有OUTPUT游标参数的存储过程编写一带有OUTPUT游标参数的存储过程,游标结果集为客户信息,并通过调用该存储过程,实现依次读取游标CUR2中各行数据请给出程序源码createproceduretitl_cursor@titl_cursorcursorvaryingoutputasset@titl_cursor=cursorforward_onlystaticforselect*fromxssopen@titl_cursordeclare@mycursorcursorexectitl_cursor@titl_cursor=@mycursoroutputfetchnextfrom@mycursorwhile@@fetch_status=0beginfetchnextfrom@mycursorend请给出执行测试结果
6.创建一个多表查询的存储过程问题查询在2009年9月18日有销售的产品名称若无此数据,请先添加之,以便于测试
(1)请给出相应的代码createprocedureau_info_alllasselectcp.产品名称fromcpleftjoincpxsbonCPXSB.产品编号=CP.产品编号leftjoinxssoncpxsb.客户编号=xss.客户编号wherecpxsb.销售日期=2009-09-18
(2)执行存储过程并给出执行结果
14.
5.2函数对于CPXS数据库,定义完成如下功能的函数
1.据产品名称,查询该产品的相关信息(函数名为FU_CP)请给出程序源码createfunctionfu_cp@productnamechar12returnstableasreturnselect*fromcpwhere产品名称=@productname请给出执行测试结果查询产品名称为“mp3”的产品情况select*fromfu_cpmp
32.按某年某季度统计给定产品名称的销售数量及销售金额函数名为FU1_CPXS请给出程序源码CREATEFUNCTIONFU1_CPXS@YEARINT@QUARTERINT@产品名称char10RETURNSTABLEASRETURNSELECT产品名称SUM数量AS销售数量SUM销售额AS销售总额FROMCPCPXSBWHERECPXSB.产品编号=CP.产品编号AND产品名称=@产品名称ANDDATEPARTYY销售日期=@YEARAN请给出执行测试结果查询2004年第3季度彩色电视机的销售数量和销售金额select*fromFU1_CPXS200403彩色电视机
3.根据销售商名称,统计其在某年某季度内销售商品名称、数量及金额(函数名为FU2_CPXS)请给出程序源码CREATEFUNCTIONFU3_CPXS@客户名称char10@YEARINT@QUARTERINTRETURNSTABLEASRETURNSELECT产品名称SUM数量AS销售数量SUM销售额AS销售总额FROMXSSCPXSBCPWHERECPXSB.产品编号=CP.产品编号ANDCPXSB.客户编号=XSS.客户编号AND客户名称=@客户名称ANDDATEPARTYY销售日期=@YEARANDDATEPARTQQ销售日期=@QUARTERGROUPBY产品名称请给出执行测试结果查询广电公司2004年第1季度销售的产品名称、销售数量和销售金额select*fromFU3_CPXS广电公司20041。