1、第六章第六章 使用存储过程使用存储过程提高提高进销存系统的进销存系统的执行效率执行效率 终极目标：能根据需要灵活、快速地创建存储过程 计算机应用教研室本次课的学习目标本次课的学习目标学习目标u1.1.了解存储过程了解存储过程u2.2.掌握存储过程的定义与使用掌握存储过程的定义与使用学习重点:u掌握带输入参数和输出参数的存储过程掌握带输入参数和输出参数的存储过程的使用的使用6.1 6.1 工作任务工作任务任务：待审核入出库单选择功能的选择描述：此功能从没有审核的入库单的汇总数据中选择需要审核的入出库单，重点是此功能提供了通用查询的实现方式6.1 6.1 工作任务工作任务6.2 6.2 存储过程的
2、优点存储过程的优点可以提供对数据的轻松访问,客户端不需要知道复杂的数据结构或业务逻辑过程,只要把查询指令通过存储过程发给服务器,服务就把需要的数据返回给用户.利用服务器强大的计算能力和速度,避免了把大量的数据从服务器下载到客户端,减少了网络上传输量服务器只用把计算结果给客户端,可提高客户端工作的效率.6.3 6.3 存储过程的分类存储过程的分类大体分为以下两类:u1.1.系统存储过程系统存储过程.sqlsql server server有大量的系统存储过程有大量的系统存储过程,用于管理用于管理sqlsql server server并显示有关数据库和用户的信息并显示有关数据库和用户的信息.以以
3、sp_sp_开关开关,用户一般不用户一般不用用sp_sp_开头的存储过程开头的存储过程.(.(扩展存储过程扩展存储过程:xpxp_,_,如如xp_cmdshellxp_cmdshell)u2.2.用户自定义存储过程用户自定义存储过程.用于解决用于的需要而建立的存储过程用于解决用于的需要而建立的存储过程.按返回数据类型分为两类:u1.1.类似于类似于selectselect查询查询,用于检索数据用于检索数据,检索到的数据能够以结检索到的数据能够以结果集的形式返回给客户端果集的形式返回给客户端.u2.2.通过输出参数返回信息或不返回信息通过输出参数返回信息或不返回信息,或只执行一个动作或只执行一个
4、动作.6.4 6.4 存储过程的定义存储过程的定义语法格式Create Procedure|Proc 存储过程名称参数名称 参数类型 output,参数名称1 参数类型 output.As T-SQL语句实现的业务逻辑6.5 6.5 无参数存储过程的创建无参数存储过程的创建语法Create Proc 存储过程名称 asT-SQL语句6.5 6.5 无参数存储过程的创建无参数存储过程的创建【例】定义存储过程up_GetDay输出当前日期所在月份的第一天和最后一天create proc up_getdayasdeclare days int,st datetime,ed datetime sele
5、ct days=day(getdate()-1select st=dateadd(day,days*(-1),getdate()select ed=dateadd(day,-1,dateadd(month,1,st)select st as StDate,ed as EdDate6.5 6.5 无参数存储过程的调用无参数存储过程的调用1、Execute存储过程名称2、Exec 存储过程名称3、存储过程名称Execute up_getdayExec up_getdayUp_getday6.6 6.6 有参数存储过程的定义有参数存储过程的定义语法格式Create Procedure|Proc 存储
6、过程名称参数名称 参数类型,参数名称1 参数类型.As T-SQL语句实现的业务逻辑6.6 6.6 有参数存储过程的定义有参数存储过程的定义【例】定义存储过程up_GetEndDay，输入参数年月(YYYYMM),输入参数名称ym int,输出参数所在月份的最后一天Create proc up_getEndDayym intasdeclare days int,st datetime,ed datetime select st=convert(varchar(8),ym)+01select days=day(st)-1select ed=dateadd(day,-1,dateadd(month
7、,1,st)select ed as ed6.6 6.6 有参数存储过程的定义有参数存储过程的定义【例】定义存储过程up_StudentQuery，输入参数cno,Sno,Sname，检索学生课程成绩信息，输出学号，姓名，课程编号，课程名称，成绩6.6 6.6 有参数存储过程的定义有参数存储过程的定义6.6 6.6 有参数存储过程的定义有参数存储过程的定义Create proc up_studentquery cno int,sno varchar(10),sname varchar(10)Asselect c.学号,c.姓名,a.课程编号,a.课程名称,b.成绩from 课程 a inner
8、 join 学生成绩 b on a.课程编号=b.课程编号join 学生 c on b.学号=c.学号where(a.课程编号=cno or cno=0)and(c.学号=sno or sno=)and(c.姓名=sname or sname=)6.6 6.6 有参数存储过程的调用有参数存储过程的调用Exec 存储过程名 参数值，参数值1。必须按参数声明顺序输入参数值Exec 存储过程名 参数名=参数值，参数名=参数值。可不按参数声明顺序输入参数值Up_getEndDay 200910Exec Up_getEndDay 200910Exec Up_getEndDay ym=200910Exec
9、 up_studentquery Sno=,cno=0,sname=6.7 6.7 带参数的存储过程的定义带参数的存储过程的定义语法格式Create Procedure|Proc 存储过程名称参数名称 参数类型 output,参数名称1 参数类型 output.As T-SQL语句实现的业务逻辑6.7 6.7 带参数的存储过程的定义带参数的存储过程的定义 什么时候用output 参数?u当当select select 语句返回一个值语句返回一个值(而不是一个值集而不是一个值集)时时,可以用可以用output output 参数来返回值参数来返回值.【例】使用output创建一个存储过程CREA
10、TE PROCEDURE do_insert ASINSERT INTO 学生 VALUES(091201,陶伟,男,1359444,);再创建一个存储过程CREATE PROCEDURE do_action X char(2),STR CHAR(8)OUTPUT ASBEGIN EXEC do_insertIF X=男BEGINUPDATE 学生 SET 姓名=刘英,性别=女 WHERE 学号=091201SET STR=修改成功ENDELSEIF X=女BEGINDELETE FROM 学生 WHERE 学号=091201SET STR=删除成功ENDEND调用和输出DECLARE str
11、 char(8)EXEC do_action 男,str OUTPUTSELECT str;5.加密的存储过程if exists(select name from sysobjectswhere name=encrypt_this and type=p)drop procedure encrypt_thisgocreate procedure encrypt_this with encryptionasselect*from xsgo6.注意:u一般的存储过程在创建后一般的存储过程在创建后,可利用可利用sp_helptext进行查看创建的命令进行查看创建的命令.u做为一个加密存储过程做为一个加
12、密存储过程,利用该命令的结果为利用该命令的结果为:u也可以直接在查询分析器中对存储过程进行编也可以直接在查询分析器中对存储过程进行编辑辑:修改存储过程1.界面2.命令 alter不返回值的存储过程不返回值的存储过程公司进货时,让库存表中的数量自动进行增加.库存表库存表(货物编号货物编号,货物名称货物名称,货物数量货物数量)creaet proc c1 char(20),1 floatasupdate 库存表库存表set 货物数量货物数量=货物数量货物数量+1where 货物编号货物编号=1 删除存储过程u基本语句格式删除删除存储过程DROP PROCEDURE 存储过程名存储过程名例例:删除上
13、面所建存储过程删除上面所建存储过程encrypt_this Drop procedure encrypt_this 常用系统存储过程u sp_helpu sp_rename存储过程sp_helpu 显示存储过程的参数及其数据类型 基本语句格式基本语句格式返回sp_help objname=name存储过程的名称存储过程的名称 sp_renameu 重命名存储过程 返回sp_rename 原存储过程名称，新存储过程名称原存储过程名称，新存储过程名称作业作业根据需要创建存储过程u 创建一个存储过程求两个数之和创建一个存储过程求两个数之和(两种方两种方法，不带或带返回参数法，不带或带返回参数)。u 当输入一个课程名时，查看选修了该门当输入一个课程名时，查看选修了该门课的学生信息。课的学生信息。
