SQL学习计划五：存储过程+一目了然

存储过程Stored Procedure

简单来说：

• 它是一段程序，可调用，可带参数
• 和java等编程语言的方法差不多，但不是面向对象，而是整一个数据库的全局
• 一个数据库，可建多个存储过程，有系统自带也可以自己创建
• 可以对数据库增删改查等等操作

系统存储过程

在知识栏目的"SQL---数据库"的"查看属性"就用到了系统存储过程

 我们可以选中数据库，如下图所示查看系统自带的存储过程，有很很很多多多
它们都是以sp_开头表示系统，因此我们创建就不要用sp_开头了，进行区分

 然后右键选"修改"，就可以查看代码内容了

自定义存储过程

新建存储过程

这里是存储过程的模板，但我们先不用那么复杂

也可以直接"新建查询"进行编写

---

准备素材

这里直接拿"SQL学习计划二"的素材：SC表、Course表

---

 if exists判断

if exists放在创建上方，好处是可以边创建边运行边修改

if exists(select * from sys.procedures where name='sco_avg')
drop procedure sco_avg
go

--如果存在 (查找全部 所有存储过程 名称为'sco_avg')
--就删除 存储过程 'sco_avg'
--开始

---

创建(不带参数)

在SQL Server中，procedure也可以简写成proc，看你喜欢

if exists(select * from sys.procedures where name='sco_avg')
drop procedure sco_avg
go

create procedure sco_avg	--创建 存储过程 名称
as							--格式
begin
	select * from SC		--内容
end							--格式
go

刷新一下，就有啦

---

执行

exec sco_avg				--执行 存储过程的名称

---

alter修改

如果不想写if exists那么麻烦，就用alter修改
前提是创建好了才能用

alter procedure sco_avg				--修改 存储过程 名称
as									--格式
begin
	select avg(score)平均分 from SC	--内容
end									--格式
go

exec sco_avg						--执行 你的存储过程

---

创建(带参数)

指定的参数@stno，类型是varchar(10)，没有默认值，赋值为01
结果：学号01的同学成绩

create procedure sc_c
@stno varchar(10)						--指定参数，没有默认值
as 
begin
	select * from SC where S# = @stno
end
go

exec sc_c '01'							--执行，@stno赋值01

指定的参数@stno，类型是varchar(10)，默认值02，没有赋值
结果：学号02的同学成绩

alter procedure sc_c
@stno varchar(10) = '02'                --指定参数，默认值02
as 
begin
	select * from SC where S# = @stno
end
go

exec sc_c								--执行

指定的参数@stno，类型是varchar(10)，默认值02，但赋值为01
结果：学号01的同学成绩

alter procedure sc_c
@stno varchar(10) = '02'                --指定参数，默认值02
as 
begin
	select * from SC where S# = @stno
end
go

exec sc_c '01'							--执行，@stno赋值01

---

创建(输出参数) 

若定义过输出参数，则exec执行时必须按顺序表明所有参数的值
结果：学号02的同学成绩平均分

alter procedure sc_c
@stno varchar(10) = '02',		--指定参数@stno，类型是varchar(10)，默认值02
@grade int output				--output输出参数@grade，类型是int
as
begin
	select @grade = avg(score) from SC where S# = @stno
end
go

declare @g int					--创建一个变量，用来存放结果
exec sc_c default, @g output	--执行，default表示@stno默认值，@g为@grade存放
select @g 平均分				--显示@g被赋值后的结果

---

小练习

• 创建一个名为sc_task的存储过程
• 创建@stno默认值02为学号的指定参数
• exec执行后，输出学号01成绩最高分的分数和课程

create procedure sc_task
@stno varchar(10) = '02',
@grade int output,
@cname varchar(10) output
as
begin
	select top 1 @grade = score
	,@cname = (select Cname from Course where C# = SC.C#)
	from SC
	where S#=@stno
	order by score desc
end
go

declare @g int, @c varchar(20)
exec sc_task '01', @g output, @c output
select @g 分数, @c 课程

一些对存储过程的查询方法

sp_helptext sc_task				--代码内容
sp_help sc_task					--基本信息
sp_depends sc_task				--用了哪些表
sp_stored_procedures sc_task	--数据库中的存储过程的清单

删除存储过程

drop procedure sc_task    --删除 存储过程 名称