SQL Server知识梳理六存储过程的使用
一、创建存储过程
1、语法
CREATE{ PROC | PROCEDURE } [schema_name.] procedure_name
[ { @parameter [ type_schema_name. ]data_type }
[ VARYING ] [ = default ] [ OUT |OUTPUT | [READONLY]
] [ ,...n ]
[WITH [ ,...n ] ]
[FOR REPLICATION ]
AS sql_statement
[ ENCRYPTION ]
[ RECOMPILE ]
[ EXECUTE AS Clause ]
2、实例
①创建一个名为ProcStudentInfo的存储过程，它返回学生的学号、姓名、性别、班级编号、年级和籍贯信息。
create procedure ProcStudentInfo
as
select 学号,姓名,性别,班级编号,年级,籍贯 from 学生信息
②创建一个存储过程‘学生人数_proc’，用于显示学生表的学生人数
create procedure 学生人数_proc
as
select COUNT(*) from 学生信息
③创建一个存储过程，用于返回“教务管理系统”
数据库上某个班级中所有学生的信息。
create procedure Proc_GetClassStudent1
@classid varchar(14)
as
select 学号,姓名,性别,班级编号,年级,籍贯 from 学生信息
where 班级编号=@classid
④创建一个带参数的存储过程‘课程表数据输入_proc’，来给课程表输入新的课程号，课程名。
create procedure 课程表数据输入_proc
@课程编号 int,
@课程名称 char(40)
as
insert into 课程信息(课程编号,课程名称) values(@课程编号,@课程名称)
⑤创建一个存储过程，用于返回“教务管理系统”数据库上某个班级中所有学生的信息，默认班级编号为'20031340000102' 。
create procedure Proc_GetClassStudent2
@ClassID varchar(14)= '20031340000102'
AS
select 学号,姓名,性别,班级编号,年级,籍贯 from 学生信息 where 班级编号=@ClassID
⑥创建一个
加密的存储过程‘学生成绩_proc，用于查询teachdb数据库中每个参加考试的学生各门课程的成绩，包含字段：姓名，课程名，成绩。
create procedure 学生成绩_proc
with encryption
as
select 姓名,课程名,成绩 from 成绩表
⑦创建一个带输出参数的存储过程‘某学生平均成绩_proc’。用于显示指定学生的平均成绩
create procedure 某学生平均成绩_proc
@num varchar(14),
@result int output
as
select @result=AVG(成绩) from 成绩表
where 学号=@num
--附：执行
declare @result int
exec 某学生平均成绩_proc'200030000041',@result output
print @result
⑧创建一个名为GetTeacherOrStudentInfo的存储过程，用于实现：由执行该存储过程时提供的查询类别(“教师”或“学生”，默认值为“学生”)和学生姓名或教师姓名，查询学生或教师信息。
create procedure GetTeacherOrStudentInfo
@姓名char(10), @class char(4)
as
if(@class='教师')
select * from TeacherInfo where Tname=@姓名
else
select * from StudentInfo where Sname=@姓名
⑨在教务管理
系统中创建一个名为GetStudentScoreInfo的存储过程，用于实现：由执行该存储过程时提供的学生姓名，使用输出参数输出学生成绩的总分、最高分和最低分。
create proc GetStudentScoreInfo
@name varchar(8),
@sum int output,
@max int output,
@min int output
as
select @sum=sum(成绩),@max=max(成绩),@min=min(成绩) from 成绩表,学生信息
where 成绩表.学号=学生信息.学号and 姓名=@name
group by 成绩表.学号
--附：执行
declare @sum int,@max int,@min int
exec GetStudentScoreInfo'冬云',@sum output,@max output,@min output
select @sum 总分,@max 最高分,@min 最低分
二、调用存储过程
1、语法
EXEC 存储过程名 [参数列表]
2、实例
①用EXECUTE或EXEC执行ProcStudentInfo存储过程
exec ProcStudentInfo
②通过‘课程表数据输入_proc’，给课程表添加一条新记录（‘119’,‘连锁超市’）
exec 课程表数据输入_proc'119','连锁超市'
三、修改存储过程
1、语法
ALTER{ PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
[ { @parameter [ type_schema_name. ]data_type }
[ VARYING ] [ = default ] [ OUT |OUTPUT ] [READONLY]
] [ ,...n ]
[WITH [ ,...n ] ]
[FOR REPLICATION ]
AS sql_st