【基础6】存储过程的 创建与调用

目录

什么是存储过程

用户自定义存储过程

练习

---

什么是存储过程

1. 什么是存储过程

   • 类似于C语言中的函数。
   • 用来执行管理任务或应用复杂的业务规则
   • 存储过程可以带参数，也可以返回结果
   • 存储过程可以包含数据操纵等语句、变量、逻辑控制语句等。（单个select语句、select语句块、select语句与逻辑控制语句）

2. 存储过程的优点

   • 执行速度更快
   • 允许模块化程序设计
   • 提高系统安全性
   • 减少网络流量

3. 存储过程的分类

   1. 系统存储过程
      • 由系统定义，存放在master数据库中。
      • 类似c语言中的系统函数
      • 系统存储过程的名称都以“SP_ ”开头或“XP_ ”开头
   2. 用户自定义存储过程
      • 由用户在自己的数据库中创建的存储过程
      • 类似C语言中的用户自定义函数

4. 常用的系统存储过程：XP_cmdshell

   --系统存储过程
   exec xp_cmdshell 'mkdir D:\bank' ,no_output    --加上no_output  只提示命令已完成，不显示 几行受影响，也不显示执行结果
   exec xp_cmdshell 'dir D:\bank'					--查看文件信息exec sp_helpdb Sales							--查看数据库信息 
   exec sp_databases								--查看当前服务器的所有数据库use Sales
   go
   exec sp_help Employees						--查看Employees表的所有  信息
   exec sp_helpconstraint Employees		--查看Employees表的所有  约束
   exec sp_helpindex Employees				--查看Employees表的所有  索引exec sp_helptext V_number		 			--显示未加密的存储过程exec sp_stored_procedures					--列出当前库中的所有存储过程

   

    

   • 可以执行dos命令下的一些操作

   • 以文本方式返回任何输出

   • 调用语法：

     exec xp _ cmdshell DOS命令[no_output]
     

   • 在sql 2005中启用扩展存储过程：xp_cmdshell

     要在外围配置管理器中启用

   • 在sql 2008及以上中启用扩展存储过程：xp_cmdshell

     --启用xp_cmdshell
     USE master 
     EXEC sp_configure 'show advanced options', 1 
     RECONFIGURE WITH OVERRIDE 
     EXEC sp_configure 'xp_cmdshell', 1 
     RECONFIGURE WITH OVERRIDE 
     EXEC sp_configure   'show advanced options', 0
     RECONFIGURE WITH OVERRIDE --关闭xp_cmdshell
     USE master 
     EXEC sp_configure 'show advanced options', 1 
     RECONFIGURE WITH OVERRIDE 
     EXEC sp_configure 'xp_cmdshell', 0 
     RECONFIGURE WITH OVERRIDE 
     EXEC sp_configure   'show advanced options', 0
     RECONFIGURE WITH OVERRIDE 

用户自定义存储过程

1. 用户自定义存储过程

   定义存储过程的语法

   create proc[edure] 存储过程名@参数1 数据类型@参数2 数据类型=默认值				 --有默认值的话才有（=默认值 ），这个是带默认值的输入参数。@参数3 数据类型=默认值 output 	 --默认值后面有output的，这个是带默认值的输出参数········@参数n 数据类型=默认值 output as  SQL语句					--可以是学过的任何语句
   go

   无参存储过程的创建，调用存储过程 对存储过程的加密，显示创建存储过程的文本

      --创建存储过程：proc_销售额    ，查看每个人的销售情况--这个存储过程是显示所有人的销售额，不需要传参数，所以创建的是无参的存储过程use Salesgoselect * from Employeesselect * from Goodsselect * from Sellif exists (select * from sysobjects where name='proc_销售额')							--视图、表、存储过程 都是存储过程，全部的数据库对象：sysobjectsdrop procedure proc_销售额gocreate procedure proc_销售额as									--因为是没有参数，所以as前面不用写参数print '各员工的销售情况如下：'select 编号,姓名,销售额=casewhen sum(G.零售价*S.数量) is null then '无销售信息'else  cast(sum(G.零售价*S.数量) as char(20))end from Employees E left join Sell S on E.编号=S.售货员工编号 left join Goods G on G.商品编号=S.商品编号 group by E.编号,E.姓名--调用存储过程 execute 简写：exec 
   --在创建存储过程的过程中，已经编译过一次，以后再也不需要进行编译，直接调用就可以
   --语法：exec 过程名 [参数]		--有参数的话加参数，没有就不用。  
   exec proc_销售额--查看存储过程创建的文本
   exec sp_helptext proc_销售额--修改存储过程文本 为加密
   --1、if exists (select * from sysobjects where name='proc_销售额')							--视图、表、存储过程 都是存储过程，全部的数据库对象：sysobjectsdrop procedure proc_销售额gocreate procedure proc_销售额--在这里加一个   with encryptionwith encryption --加密存储过程创建的文本as									--因为是没有参数，所以as前面不用写参数print '各员工的销售情况如下：'select 编号,姓名,销售额=casewhen sum(G.零售价*S.数量) is null then '无销售信息'else  cast(sum(G.零售价*S.数量) as char(20))end from Employees E left join Sell S on E.编号=S.售货员工编号 left join Goods G on G.商品编号=S.商品编号 group by E.编号,E.姓名--2、取消对文本的加密，创建存储过程的文本中，create  改成  alter  然后把加密的那行文本注释/删除
   alter procedure proc_销售额
   --with encryption --加密存储过程创建的文本
   as									--因为是没有参数，所以as前面不用写参数
   print '各员工的销售情况如下：'
   select 编号,姓名,销售额=casewhen sum(G.零售价*S.数量) is null then '无销售信息'else  cast(sum(G.零售价*S.数量) as char(20))end 
   from Employees E left join Sell S on E.编号=S.售货员工编号 left join Goods G on G.商品编号=S.商品编号 group by E.编号,E.姓名

2. 输入过程的参数分为两种：

   • 输入参数：往里面传值，
   • 输出参数 ：调用完成后，传出一个结果值，这个值可以进一步使用

   输入参数存储过程的创建 /调用/多个参数有默认值的调用

   --输入参数的存储过程
   if exists (select * from sysobjects where name='proc_销售额')					
   drop procedure proc_销售额
   go
   create procedure proc_销售额
   @EmployeesID int
   as						
   set nocount on		
   print '编号为'+cast(@EmployeesID as char(4))+'的员工销售情况如下：'
   select 编号,姓名,销售额=casewhen sum(G.零售价*S.数量) is null then '无销售信息'else  cast(sum(G.零售价*S.数量) as char(20))end 
   from Employees E left join Sell S on E.编号=S.售货员工编号 left join Goods G on G.商品编号=S.商品编号 where 编号=@EmployeesID
   group by E.编号,E.姓名
   go--调用输入参数的存储过程    如果有多个输入参数，那么调用时，要与创建时的参数一一对应
   exec proc_销售额 '1301'
   --另一种调用：按名称调用，与参数位置无关
   exec proc_销售额 @EmployeesID='1301'--带输入参数的默认值的存储过程
   if exists (select * from sysobjects where name='proc_销售额')	
   drop procedure proc_销售额
   go
   create procedure proc_销售额
   @EmployeesID int='1301'
   as									
   set nocount on		
   print '编号为'+cast(@EmployeesID as char(4))+'的员工销售情况如下：'
   select 编号,姓名,销售额=casewhen sum(G.零售价*S.数量) is null then '无销售信息'else  cast(sum(G.零售价*S.数量) as char(20))end 
   from Employees E left join Sell S on E.编号=S.售货员工编号 left join Goods G on G.商品编号=S.商品编号 where 编号=@EmployeesID
   group by E.编号,E.姓名
   go--调用有输入参数的存储过程 
   exec proc_销售额 				--使用默认值
   exec proc_销售额 '1302'		--不使用默认值--多个参数只有一个默认值调用时
   --按位置 这个默认值一般放在最后，调用时只用写不是默认值的，最后的默认值省略不写，就是调用的默认值。
   --按名称   如要使用默认值，则默认值不用写，其他按名称调用即可
   

3. 带输出参数的存储过程的创建

   ​ 如果希望调用存储过程后，返回了一个或多个值，这时就需要使用输出函数了。

   --输出参数的存储过程
   --根据销售额判断是什么员工
   if exists (select * from sysobjects where name='proc_销售额')						
   drop procedure proc_销售额
   go
   create proc proc_销售额
   @EmployeesID int='1301', @sellsum money output
   as								
   set nocount on			
   print '编号为'+cast(@EmployeesID as char(4))+'的员工销售情况如下：'
   select 编号,姓名,销售额=casewhen sum(G.零售价*S.数量) is null then '无销售信息'else  cast(sum(G.零售价*S.数量) as char(20))end 
   from Employees E left join Sell S on E.编号=S.售货员工编号 left join Goods G on G.商品编号=S.商品编号 where 编号=@EmployeesID
   group by E.编号,E.姓名
   if @EmployeesID in (select 售货员工编号 from Sell)select @sellsum=sum(零售价*S.数量) from Goods G , Sell S where G.商品编号=S.商品编号 and  售货员工编号=@EmployeesID
   elseset @sellsum=0
   go--调用有输出参数的存储过程
   --调用时，要再定义一个变量接收输出值(类型要与输出值对应)，且调用时变量后要+output
   declare @a money
   exec proc_销售额 '1302' ,@a output
   if @a>=30000print '优秀员工'
   else if @a>=10000print '合格员工'
   else print '无销售员工'--按名称调用
   declare @a money
   exec proc_销售额 @EmployeesID='1302' ,@sellsum=@a output
   if @a>=30000print '优秀员工'
   else if @a>=10000print '合格员工'
   else print '无销售员工'--使用默认值
   declare @a money
   exec proc_销售额 @sellsum=@a output
   if @a>=30000print '优秀员工'
   else if @a>=10000print '合格员工'
   else print '无销售员工'
   

4. 处理存储工程的中的错误

   • 可以使用print语句显示错误信息，但是这些信息是临时的，只能显示给用户
   • raiserror 显示用户定义的错误信息时：可指定严重级别，设置系统变量@@error ，记录发生的错误等

5. 返回值

   调用时要专门定义一个变量返回这个值，然后用输出语句输出
   

总结：

• 存储过程是一组预编译的SQL语句，它可以包含数据操纵语句、变量、逻辑控制语句等。
• 存储过程允许带参数，参数分为：输入参数 / 输出参数
• 其中，输入参数可以有默认值
  • 输入参数：可以在调用时向存储过程传递参数，此类参数可用来向存储过程中传入值
  • 输出参数从存储过程中返回(输出)值，后面跟output关键字
  • raiserror语句用来向用户报告错误

练习

use XK
goselect * from Student
select * from StuCou
select * from Course
select * from Class--1、在Xk数据库中，创建存储过程proc_学生选课情况1,完成如下功能。
--查询学生的选课信息（要求显示学号、姓名，课程名，志愿号）（1）对存储过程进行加密（2）调用存储过程if exists (select * from sysobjects where name='proc_学生选课情况1')					
drop procedure proc_学生选课情况1
go
create procedure proc_学生选课情况1with encryptionasset nocount on	print '学生选课信息如下：'select S.StuNo 学号,StuName 姓名,CouName 课程名,WillOrder 志愿号 from Student S ,StuCou SC ,Course C where S.StuNo=SC.StuNo and SC.CouNo=C.CouNo
go--调用
exec proc_学生选课情况1--2、在Xk数据库中，创建存储过程proc_学生选课情况2,完成如下功能。
--（1）查询给定学号的学生选课情况（要求显示学号、姓名，课程名，志愿号）；
--（2）存储过程带1个输入参数，表示学号；
--（3）执行存储过程完成指定学号'00000001' , '00000025', '000000001'的选课信息查询if exists (select * from sysobjects where name='proc_学生选课情况2')					
drop procedure proc_学生选课情况2
go
create procedure proc_学生选课情况2
@StuID  nvarchar(8)				--这里的类型一定要与StuNo相匹配
as
set nocount on	
if @StuId in (select StuNo from StuCou)beginprint '该学生选课信息如下：'select S.StuNo 学号,StuName 姓名,CouName 课程名,WillOrder 志愿号 from Student S ,StuCou SC ,Course C where S.StuNo=SC.StuNo and SC.CouNo=C.CouNo and S.StuNo=@StuIDend
else if @StuId in (select StuNo from Student)print '该学生无选课信息！'
elseprint '无此人，请重新输入'
go--调用
exec proc_学生选课情况2 '00000001'	
exec proc_学生选课情况2 '00000025'	
exec proc_学生选课情况2 '000000001'	--3、在Xk数据库中，创建存储过程proc_学生选课情况3,完成如下功能。
--（1）查询指定学号的学生的选课情况（要求显示学号、姓名、课程名称，志愿号）。
--（2）存储过程带2个参数，输入参数表示学号，输出参数用于返回每个学生的选课门数。
--（3）完成指定学号的选课情况查询，选过课程的，存储过程返回1； 若没有选过课程的则给出提示“该学生没有选过程课程！”，存储过程返回0。
--（4）选课门数等于5门，显示“已经达到选课要求！”；选课门数大于等于4门，显示“请继续选课，还差1门达到选课要求！”；选课门数等于3门，显示“请继续选课，还差2门达到选课要求！”；选课门数等于2门，显示“请继续选课，还差3门达到选课要求！”；选课门数等于1门，显示“请继续选课，还差4门达到选课要求！”
--（5）对创建存储过程的文本进行加密。
--（6）分别以'00000001' , '00000005', '00000025', '000000001'学号进行存储过程调用的测试。（执行结果如下图所示）if exists (select * from sysobjects where name='proc_学生选课情况3')					
drop procedure proc_学生选课情况3
go
create procedure proc_学生选课情况3
@StuID  nvarchar(8) ,
@Choosesum int output
with encryption
as
set nocount on	
if @StuId in (select StuNo from StuCou)beginprint '该学生选课信息如下：'select S.StuNo,StuName,CouName,WillOrder from Student S ,StuCou SC ,Course C where S.StuNo=SC.StuNo and SC.CouNo=C.CouNo and S.StuNo=@StuIDselect @Choosesum=count(StuName) from (	select S.StuNo,StuName,CouName,WillOrder from Student S ,StuCou SC ,Course C where S.StuNo=SC.StuNo and SC.CouNo=C.CouNo and S.StuNo=@StuID) xuanke print '该学生选课门数：'+cast(@Choosesum as char(4))if @Choosesum=5print '已达到选课计划要求'else if @Choosesum=4print '请继续选课，还差1门达到选课要求！'else if @Choosesum=3print '请继续选课，还差2门达到选课要求！'else if @Choosesum=2print '请继续选课，还差3门达到选课要求！'elseprint '请继续选课，还差4达到选课要求！'return 1end
else if @StuId in (select StuNo from Student)beginprint '该学生没有选过课程！'return 0end
elseraiserror('非本校学生，请重新输入！',16,1)
go--调用
declare @sum int ,@a int
exec @a=proc_学生选课情况3 '00000001',@sum output
print '返回值：'+cast(@a as char(1))--调用
declare @sum int,@a int
exec @a=proc_学生选课情况3 '00000005',@sum output
print '返回值：'+cast(@a as char(1))--调用
declare @sum int,@a int
exec @a=proc_学生选课情况3 '00000025',@sum output
print '返回值：'+cast(@a as char(1))--调用
declare @sum int,@a int
exec @a=proc_学生选课情况3 '000000001',@sum output
print '返回值：'+cast(@a as char(100))