6. 存储过程：系统存储过程、临时存储过程、本地存储过程

存储过程的类型

1.系统存储过程

主要存储在master数据库中并以sp_开头

1.1显示数据库的参数及其数据类型【exec sp_help 数据库名 】

1.2 更改数据库名【exec sp_rename】

1.3 显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的定义【exec sp_helptext】

---

2.临时存储过程（有一定的使用期限）

(1)本地临时存储过程【以#开头】

存储在tempdb数据库中。
只有创建它的用户可以执行它。一旦用户断开服务器连接，本地临时存储过程会自动删除。

创建临时存储过程#s_g，检索所有学生的成绩记录，包括学号、姓名、所选课程号和成绩：

create procedure #s_g
as
	select student.sno,student.sname,sc.cno,sc.score
	from student,sc
	where student.sno=sc.sno 
	order by student.sno
go

exec #s_g        --执行该存储过程：
go

(2)全局临时存储过程【以##开头】

存储在tempdb数据库中。
连接到服务器的所有用户都可以执行它。创建它的用户断开服务器连接时，服务器检查是否有其他用户在执行它，如果没有删除存储过程；如果有则会等其他用户执行完再删除。

---

3．本地存储过程

即本地服务器上的存储过程，也就是一般所称的用户自定义存储过程。

3.1定义存储过程

① 带参数的存储过程:根据书本的数量修改价格

create procedure price_if_ByNum
	@isbn char(15),
	@num int,
	@prize numeric(4,2)
as
	if @num>100 
		begin
			update Book set Prize=@prize+5 where ISBN=@isbn;
		end
	else  
		begin
			update Book set Prize=@prize+1 where ISBN=@isbn;
		end 
go

exec price_if_ByNum '9787540456030  ',9,90.00

---

② 在存储过程中使用默认值

create proc select_student
    @sno char(6)='1'
as 
    select student.sno,student.sname,sc.cno,sc.score
	from student,sc
	where student.sno=@sno and student.sno=sc.sno
go

exec select_student   --不指定参数调用，则使用默认参数值1
exec select_student '2'  --指定参数调用

drop proc select_student
go

---

③ output的用法

创建一个存储过程average，它返回两个参数@st_name和@st_avg，
分别代表了姓名和平均成绩，即查询指定学号的学生的姓名和平均成绩

create procedure average
	(@st_sno char(6),	 
	 @st_sname char(10) output,
	 @st_avg float output)
as 
    select @st_sname=student.sname, @st_avg=avg(sc.score)
	from student,sc
	where student.sno=sc.sno and student.sno=@st_sno
	group by student.sname
go
-------------------------------------------------------------------------------
执行以上存储过程average，查询学号为“1”的学生姓名和平均分：
declare @st_sname char(10), @st_avg float
exec average '1',@st_sname output, @st_avg output
select @st_sname as '姓名', @st_avg  as '平均分'    --或select '平均分'=@st_avg
go

---

④ return的用法

/*例: 创建存储过程test_ret根据输入的参数来判断返回值：*/
create proc test_ret(@input_int int=0)
as 
begin
	if @input_int=0
		return 0   --输入的参数等于0，则返回0
	if @input_int>0
		return 1000    --输入的参数大于0，则返回1000
	if @input_int<0
		return -1000    --输入的参数大于0，则返回-1000
end
------------------------------------------------------------------------------
declare @ret_int int   --保存返回值
exec @ret_int=test_ret 1   --执行该存储过程
print @ret_int

---

3.2修改存储过程【例子：修改 “加密” 为 “不加密”】

创建加密存储过程s_a，查询学生的平均年龄

create procedure s_a
with encryption
as
	select avg(sage) from student
go

exec s_a                加密存储过程可以执行这行语句
exec sp_helptext s_a    加密存储过程不可以执行这行语句
go

----------------------------------------------------------------------------------------------
/*修改存储过程*/
/*修改存储过程s_a，查询学生的最大年龄，不加密了！*/

alter procedure s_a
as
	select max(sage) from student
go

exec s_a                加密存储过程可以执行这行语句
exec sp_helptext s_a    加密存储过程可以执行这行语句
go

---

3.3删除存储过程【drop proc 存储名】

drop proc maxgrade