第六章使用存储过程实现进销存系统的业务逻辑课件

第六章

使用存储过程提高进销存系统的执行效率终极目标：能根据需要灵活、快速地创建存储过程计算机应用教研室本次课的学习目标学习目标1.了解存储过程2.掌握存储过程的定义与使用学习重点:掌握带输入参数和输出参数的存储过程的使用6.1工作任务任务：待审核入出库单选择功能的选择描述：此功能从没有审核的入库单的汇总数据中选择需要审核的入出库单，重点是此功能提供了通用查询的实现方式6.1工作任务6.2存储过程的优点可以提供对数据的轻松访问,客户端不需要知道复杂的数据结构或业务逻辑过程,只要把查询指令通过存储过程发给服务器,服务就把需要的数据返回给用户.利用服务器强大的计算能力和速度,避免了把大量的数据从服务器下载到客户端,减少了网络上传输量服务器只用把计算结果给客户端,可提高客户端工作的效率.6.3存储过程的分类大体分为以下两类:1.系统存储过程.sqlserver有大量的系统存储过程,用于管理sqlserver并显示有关数据库和用户的信息.以sp_开关,用户一般不用sp_开头的存储过程.(扩展存储过程:xp_,如xp_cmdshell)2.用户自定义存储过程.用于解决用于的需要而建立的存储过程.按返回数据类型分为两类:1.类似于select查询,用于检索数据,检索到的数据能够以结果集的形式返回给客户端.2.通过输出参数返回信息或不返回信息,或只执行一个动作.6.4存储过程的定义语法格式Create[Procedure|Proc]存储过程名称参数名称参数类型[output],参数名称1参数类型[output]….AsT-SQL语句实现的业务逻辑6.5无参数存储过程的创建语法CreateProc存储过程名称asT-SQL语句6.5无参数存储过程的创建【例】定义存储过程up_GetDay输出当前日期所在月份的第一天和最后一天createprocup_getdayasdeclare@daysint,@stdatetime,@eddatetimeselect@days=day(getdate())-1select@st=dateadd(day,@days*(-1),getdate())select@ed=dateadd(day,-1,dateadd(month,1,@st))select@stasStDate,@edasEdDate6.5无参数存储过程的调用1、Execute存储过程名称2、Exec存储过程名称3、存储过程名称Executeup_getdayExecup_getdayUp_getday6.6有参数存储过程的定义语法格式Create[Procedure|Proc]存储过程名称参数名称参数类型,参数名称1参数类型….AsT-SQL语句实现的业务逻辑6.6有参数存储过程的定义【例】定义存储过程up_GetEndDay，输入参数年月(YYYYMM),输入参数名称@ymint,输出参数所在月份的最后一天Createprocup_getEndDay@ymintasdeclare@daysint,@stdatetime,@eddatetimeselect@st=convert(varchar(8),@ym)+'01'select@days=day(@st)-1select@ed=dateadd(day,-1,dateadd(month,1,@st))select@edased6.6有参数存储过程的定义【例】定义存储过程up_StudentQuery，输入参数@cno,@Sno,@Sname，检索学生课程成绩信息，输出学号，姓名，课程编号，课程名称，成绩6.6有参数存储过程的定义6.6有参数存储过程的定义Createprocup_studentquery@cnoint,@snovarchar(10),@snamevarchar(10)Asselectc.学号,c.姓名,a.课程编号,a.课程名称,b.成绩from课程ainnerjoin学生成绩bona.课程编号=b.课程编号join学生conb.学号=c.学号where(a.课程编号=@cnoor@cno=0)and(c.学号=@snoor@sno='')and(c.姓名=@snameor@sname='')6.6有参数存储过程的调用Exec存储过程名参数值，参数值1。。。必须按参数声明顺序输入参数值Exec存储过程名参数名=参数值，参数名=参数值。。。可不按参数声明顺序输入参数值Up_getEndDay200910ExecUp_getEndDay200910ExecUp_getEndDay@ym=200910Execup_studentquery@Sno=‘’,@cno=0,@sname=‘’6.7带参数的存储过程的定义语法格式Create[Procedure|Proc]存储过程名称参数名称参数类型[output],参数名称1参数类型[output]….AsT-SQL语句实现的业务逻辑6.7带参数的存储过程的定义【例】生成入出库编号功能描述:入出库单据号获取设计思路：入出库编号规则：编号总体长度为12位，前八位日期YYYYMMDD,后四位为流水号,每天从1开始，位数不够用0补位，如2代表2010年2月22日第1笔1、从号码表中读取对应类型编号的“参数、格式、号码长度”等规则信息2、通过参数@Num获取指定数量的号码3、生成后的号码通过参数@ReturnNum进行输出，如一次获得多个（参数@num>1）,则@ReturnNum为最大的4、更新号码表中对应参数值，如果号码.当前日期和系统日期不同，更新号码表的当前日期，同时参数值从1开始6.7带参数的存储过程的定义createPROCEDUREUP_GetNum@TypeCodevarchar(10),--参数类型@Numint,--数量@ReturnNumdecimal(18,0)outputASDeclare@CurrentDatedatetime,@Lengthint,@Formatvarchar(20),@ParmValuedecimal(12,0)select@CurrentDate=当前日期,@Length=号码长度,@Format=格式,@ParmValue=参数值from号码表where类型编码=@TypeCode--如日期小于当前日期，更新当前日期和参数值，每天流水号从1开始if(@CurrentDate<convert(varchar(10),Getdate(),121))beginupdate号码表set当前日期=getdate(),参数值=0where类型编码=@TypeCodeendif(@Format='YYYYMMDD')beginselect@ReturnNum=Replace(convert(varchar(10),getdate(),121),'-','')+right('000000000'+convert(varchar(10),@ParmValue+@Num),@Length-len(@Format))update号码表Set参数值=@ParmValue+@Numwhere类型编码=@TypeCodeend6.7带参数的存储过程的定义什么时候用output参数?当select语句返回一个值(而不是一个值集)时,可以用output参数来返回值.6.7带参数的存储过程的调用Declare@Numdecimal(18,0)execUp_GetNum'RCKBH',1,@Numoutputselect@num5.加密的存储过程ifexists(selectnamefromsysobjectswherename='encrypt_this'andtype='p')dropprocedureencrypt_thisgocreateprocedureencrypt_thiswithencryptionasselect*fromxsgo6.注意:一般的存储过程在创建后,可利用sp_helptext进行查看创建的命令.做为一个加密存储过程,利用该命令的结果为:也可以直接在查询分析器中对存储过程进行编辑:修改存储过程1.界面2.命令alter不返回值的存储过程公司进货时,让库存表中的数量自动进行增加.库存表(货物编号,货物名称,货物数量)creaetprocc@1char(20),@1floatasupdate库存表set货物数量=货物数量+@1where货物编号=@1

删除存储过程基本语句格式删除存储过程DROPPROCEDURE存储过程名例:删除上面所建存储过程encrypt_thisDropprocedureencrypt_this常用系统存储过程sp_helpsp_rename存储过程sp_help显示存储过程的参数及其数据类型基本语句格式返回sp_help[[@objname=]name]存储过程的名称sp_rename重命名存储过程返回sp_rename原存储过程名称，新存储