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

1 第六章使用存储过程提高进销存系统的执行效率 终极目标 能根据需要灵活 快速地创建存储过程计算机应用教研室 2 本次课的学习目标 学习目标1 了解存储过程2 掌握存储过程的定义与使用学习重点 掌握带输入参数和输出参数的存储过程的使用 3 6 1工作任务 任务 待审核入出库单选择功能的选择描述 此功能从没有审核的入库单的汇总数据中选择需要审核的入出库单 重点是此功能提供了通用查询的实现方式 4 6 1工作任务 5 6 2存储过程的优点 可以提供对数据的轻松访问 客户端不需要知道复杂的数据结构或业务逻辑过程 只要把查询指令通过存储过程发给服务器 服务就把需要的数据返回给用户 利用服务器强大的计算能力和速度 避免了把大量的数据从服务器下载到客户端 减少了网络上传输量服务器只用把计算结果给客户端 可提高客户端工作的效率 6 6 3存储过程的分类 大体分为以下两类 1 系统存储过程 sqlserver有大量的系统存储过程 用于管理sqlserver并显示有关数据库和用户的信息 以sp 开关 用户一般不用sp 开头的存储过程 扩展存储过程 xp 如xp cmdshell 2 用户自定义存储过程 用于解决用于的需要而建立的存储过程 按返回数据类型分为两类 1 类似于select查询 用于检索数据 检索到的数据能够以结果集的形式返回给客户端 2 通过输出参数返回信息或不返回信息 或只执行一个动作 7 6 4存储过程的定义 语法格式Create Procedure Proc 存储过程名称参数名称参数类型 output 参数名称1参数类型 output AsT SQL语句实现的业务逻辑 8 6 5无参数存储过程的创建 语法CreateProc存储过程名称asT SQL语句 9 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 edasEdDate 10 6 5无参数存储过程的调用 1 Execute存储过程名称2 Exec存储过程名称3 存储过程名称Executeup getdayExecup getdayUp getday 11 6 6有参数存储过程的定义 语法格式Create Procedure Proc 存储过程名称参数名称参数类型 参数名称1参数类型 AsT SQL语句实现的业务逻辑 12 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 edased 13 6 6有参数存储过程的定义 例 定义存储过程up StudentQuery 输入参数 cno Sno Sname 检索学生课程成绩信息 输出学号 姓名 课程编号 课程名称 成绩 14 6 6有参数存储过程的定义 15 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 16 6 6有参数存储过程的调用 Exec存储过程名参数值 参数值1 必须按参数声明顺序输入参数值Exec存储过程名参数名 参数值 参数名 参数值 可不按参数声明顺序输入参数值Up getEndDay200910ExecUp getEndDay200910ExecUp getEndDay ym 200910Execup studentquery Sno cno 0 sname 17 6 7带参数的存储过程的定义 语法格式Create Procedure Proc 存储过程名称参数名称参数类型 output 参数名称1参数类型 output AsT SQL语句实现的业务逻辑 18 6 7带参数的存储过程的定义 例 生成入出库编号功能描述 入出库单据号获取设计思路 入出库编号规则 编号总体长度为12位 前八位日期YYYYMMDD 后四位为流水号 每天从1开始 位数不够用0补位 如201002220001代表2010年2月22日第1笔1 从号码表中读取对应类型编号的 参数 格式 号码长度 等规则信息2 通过参数 Num获取指定数量的号码3 生成后的号码通过参数 ReturnNum进行输出 如一次获得多个 参数 num 1 则 ReturnNum为最大的4 更新号码表中对应参数值 如果号码 当前日期和系统日期不同 更新号码表的当前日期 同时参数值从1开始 19 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类型编码 TypeCodeend 20 6 7带参数的存储过程的定义 什么时候用output参数 当select语句返回一个值 而不是一个值集 时 可以用output参数来返回值 21 6 7带参数的存储过程的调用 Declare Numdecimal 18 0 execUp GetNum RCKBH 1 Numoutputselect num 22 5 加密的存储过程ifexists selectnamefromsysobjectswherename encrypt this andtype p dropprocedureencrypt thisgocreateprocedureencrypt thiswithencryptionasselect fromxsgo 23 6 注意 一般的存储过程在创建后 可利用sp helptext进行查看创建的命令 做为一个加密存储过程 利用该命令的结果为 也可以直接在查询分析器中对存储过程进行编辑 24 修改存储过程1 界面2 命令alter 25 不返回值的存储过程 公司进货时 让库存表中的数量自动进行增加 库存表 货物编号 货物名称 货物数量 creaetprocc 1char 20 1floatasupdate库存表set货物数量 货物数量 1where货物编号 1 26 删除存储过程基本语句格式删除 存储过程 DROPPROCEDURE存储过程名 例 删除上面所建存储过程encrypt this Dropprocedureencrypt this 27 常用系统存储过程sp helpsp ren