【学习数据库】用户自定义存储过程

• 存储过程是一种数据库对象，是为了实现某个特定任务，将一组预编译的SQL语句以一个存储单元的形式存储在服务器上，供用户调用。存储过程在第一次执行时进行编译，然后将编译好的代码保存在高速缓存区中便于以后调用，这样可以提高代码的执行效率。
• 存储过程是指封装了可重用代码的模块或例程。存储过程可以接受输入参数、向客户端返回表格或标量结果和消息、调用数据定义语言 (DDL) 和数据操作语言 (DML) 语句，然后返回输出参数 。

符号表示

【N】存储过程的名字
【SQL】sql语句
【@P】@不可少，P为参数名
【T】数据类型
【V】具体的值
【@B】@不可少，B为变量名

创建存储过程

类型	sql语句	说明
无参数	create procedure【N】《with encryption|with recompile》as 【SQL】	创建一个名为N的存储过程，with encryption表示加密， with recompile表示重新编译
带输入参数	create procedure【N】【@P】【T】…《with encryption|with recompile》as 【SQL】	创建一个名为N的存储过程，带有类型为T的输入参数P
带输出参数	create procedure【N】【@P】【T】output as【SQL】	创建一个名为N的存储过程，带有类型为T的输出参数P
带默认值	create procedure【N】【@P】【T=V】（output）…《with encryption|with recompile》as 【SQL】	创建一个名为N的存储过程，带有类型为T的参数P，P的默认值是V

procedure可用proc替换
输入与输出参数可以混合使用

执行存储过程

类型	sql语句	说明
无参数	execute【N】	执行名为N的存储过程
带输入参数1	execute【N】【@P=V】	执行名为N的存储过程，并将V传入参数P
带输入参数2	execute【N】【V1,V2】	执行名为N的存储过程，并按V1，V2顺序依次传入参数P1，P2
带参数	declare【@B1】【T1】【@B2】【T2】 set【@B2=V2】 execute【N】【@B1】output,【@B2】 print【@B1】	声明两个变量B1，B2，类型依次为T1，T2 给B2赋值为V2 执行名为N的存储过程，按顺序将B1，B2依次传入参数P1，P2。 最后打印输出B1

execute可用exec替换

查看存储过程

sql语句	说明
exec sp_helptext【N】	查看存储过程内容
exec sp_depends【N】	查看存储过程处理对象的信息
exec sp_help【N】	查看存储过程本身的信息

修改存储过程

sql语句	说明
alter proc【N】《with encryption|with recompile》as【SQL】	修改名为N的存储过程

删除存储过程

sql语句	说明
drop proc【N1,N2…】	删除名为N1,N2的存储过程

例子

1.基于test1数据库，创建存储过程实现：指定某课程名，统计该课程的平均分、最高分和最低分，将结果返回给用户

course表（课程表：课程号，课程名，先行课号，学分）

sc表（选课表：学号，课程号，成绩）

创建存储过程DISPLAY_GRADE

create proc DISPLAY_GRADE 
@course_name nchar(20),@avg float output,@max float output,@min float output as
select @avg=AVG(sc.sgrade) from sc,course where course.cname=@course_name and sc.cno=course.cno
select @max=MAX(sc.sgrade) from sc,course where course.cname=@course_name and sc.cno=course.cno
select @min=MIN(sc.sgrade) from sc,course where course.cname=@course_name and sc.cno=course.cno

执行1

declare @avg float,@max float,@min float
exec DISPLAY_GRADE '数据库',@avg output,@max output,@min output
print @avg
print @max
print @min

或执行2

declare @course_name nchar(20),@avg float,@max float,@min float
set @course_name='数据库'
exec DISPLAY_GRADE @course_name,@avg output,@max output,@min output
print @avg
print @max
print @min

---

2.查看存储过程DISPLAY_GRADE的内容

exec sp_helptext DISPLAY_GRADE

如果之前设置是加密（with encryption），则无法查看

感谢阅读！