目录
什么是存储过程
-
什么是存储过程
- 类似于C语言中的函数。
- 用来执行管理任务或应用复杂的业务规则
- 存储过程可以带参数，也可以返回结果
- 存储过程可以包含数据操纵等语句、变量、逻辑控制语句等。（单个select语句、select语句块、select语句与逻辑控制语句）
-
存储过程的优点
- 执行速度更快
- 允许模块化程序设计
- 提高系统安全性
- 减少网络流量
-
存储过程的分类
- 系统存储过程
- 由系统定义，存放在master数据库中。
- 类似c语言中的系统函数
- 系统存储过程的名称都以“SP_ ”开头或“XP_ ”开头
- 用户自定义存储过程
- 由用户在自己的数据库中创建的存储过程
- 类似C语言中的用户自定义函数
- 系统存储过程
-
常用的系统存储过程：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
-
用户自定义存储过程
-
用户自定义存储过程
定义存储过程的语法
create proc[edure] 存储过程名 @参数1 数据类型 @参数2 数据类型=默认值 --有默认值的话才有（=默认值 ），这个是带默认值的输入参数。 @参数3 数据类型=默认值 output --默认值后面有output的，这个是带默认值的输出参数 ········ @参数n 数据类型=默认值 output as SQL语句 --可以是学过的任何语句 go无参存储过程的创建，调用存储过程 对存储过程的加密，显示创建存储过程的文本
--创建存储过程：proc_销售额 ，查看每个人的销售情况 --这个存储过程是显示所有人的销售额，不需要传参数，所以创建的是无参的存储过程 use Sales go select * from Employees select * from Goods select * from Sell if exists (select * from sysobjects where name='proc_销售额') --视图、表、存储过程 都是存储过程，全部的数据库对象：sysobjects drop procedure proc_销售额 go create procedure proc_销售额 as --因为是没有参数，所以as前面不用写参数 print '各员工的销售情况如下：' select 编号,姓名,销售额=case when 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_销售额') --视图、表、存储过程 都是存储过程，全部的数据库对象：sysobjects drop procedure proc_销售额 go create procedure proc_销售额 --在这里加一个 with encryption with encryption --加密存储过程创建的文本 as --因为是没有参数，所以as前面不用写参数 print '各员工的销售情况如下：' select 编号,姓名,销售额=case when 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 编号,姓名,销售额=case when 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.姓名 -
输入过程的参数分为两种：
- 输入参数：往里面传值，
- 输出参数 ：调用完成后，传出一个结果值，这个值可以进一步使用
--输入参数的存储过程 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 编号,姓名,销售额=case when 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 编号,姓名,销售额=case when 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' --不使用默认值 --多个参数只有一个默认值调用时 --按位置 这个默认值一般放在最后，调用时只用写不是默认值的，最后的默认值省略不写，就是调用的默认值。 --按名称 如要使用默认值，则默认值不用写，其他按名称调用即可 -
带输出参数的存储过程的创建
如果希望调用存储过程后，返回了一个或多个值，这时就需要使用输出函数了。
--输出参数的存储过程 --根据销售额判断是什么员工 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 编号,姓名,销售额=case when 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 else set @sellsum=0 go --调用有输出参数的存储过程 --调用时，要再定义一个变量接收输出值(类型要与输出值对应)，且调用时变量后要+output declare @a money exec proc_销售额 '1302' ,@a output if @a>=30000 print '优秀员工' else if @a>=10000 print '合格员工' else print '无销售员工' --按名称调用 declare @a money exec proc_销售额 @EmployeesID='1302' ,@sellsum=@a output if @a>=30000 print '优秀员工' else if @a>=10000 print '合格员工' else print '无销售员工' --使用默认值 declare @a money exec proc_销售额 @sellsum=@a output if @a>=30000 print '优秀员工' else if @a>=10000 print '合格员工' else print '无销售员工' -
- 可以使用print语句显示错误信息，但是这些信息是临时的，只能显示给用户
- raiserror 显示用户定义的错误信息时：可指定严重级别，设置系统变量@@error ，记录发生的错误等
-
返回值
调用时要专门定义一个变量返回这个值，然后用输出语句输出
总结：
- 存储过程是一组预编译的SQL语句，它可以包含数据操纵语句、变量、逻辑控制语句等。
- 存储过程允许带参数，参数分为：输入参数 / 输出参数
- 其中，输入参数可以有默认值
- 输入参数：可以在调用时向存储过程传递参数，此类参数可用来向存储过程中传入值
- 输出参数从存储过程中返回(输出)值，后面跟output关键字
- raiserror语句用来向用户报告错误
练习
use XK
go
select * 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_学生选课情况1
with encryption
as
set 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)
begin
print '该学生选课信息如下：'
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
end
else if @StuId in (select StuNo from Student)
print '该学生无选课信息！'
else
print '无此人，请重新输入'
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)
begin
print '该学生选课信息如下：'
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
select @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=5
print '已达到选课计划要求'
else if @Choosesum=4
print '请继续选课，还差1门达到选课要求！'
else if @Choosesum=3
print '请继续选课，还差2门达到选课要求！'
else if @Choosesum=2
print '请继续选课，还差3门达到选课要求！'
else
print '请继续选课，还差4达到选课要求！'
return 1
end
else if @StuId in (select StuNo from Student)
begin
print '该学生没有选过课程！'
return 0
end
else
raiserror('非本校学生，请重新输入！',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))
824
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
