存储过程的操作

存储过程的操作

一、实验目的

通过本实验来学习、掌握用户自定义存储过程的创建、修改、删除、执行的方法。

二、实验知识要点

SQL Server 中的存储过程与其它编程语言中的过程类似：
（1）可以以输入参数的形式引用存储过程以外的参数
（2）可以以输出参数的形式将多个值返回给调用它的过程或批处理。
（3）存储过程中包含有执行数据库操作的编程语句，也可调用其它存储过程。
（4）用RETURN向调用过程或批处理返回状态值，以表明成功或失败，以及失败原因。
创建存储过程的SQL语句
CREATE PROCEDURE [拥有者.][存储过程名][；程序编号]
[{ @参数名 数据类型 } [ VARYING ][ = 默认值][ OUTPUT ]]
[，… n ]
[WITH {RECOMPILE | ENCRYPTION | RECOMPILE，ENCRYPTION} ]
AS 程序行
执行存储过程的SQL语句
[ [ EXEC[UTE ] ][ @返回值 = ]{ 程序名[；程序编号]
| @存储程序名的变量 }[ [ @参数名= ]{ 参数值 }
| @变量[OUTPUT ] | [DEFAULT ] ] [,…n ]
[ WITH RECOMPILE ]

三、实验内容

创建一个名为：jiaoxue的数据库，在该库下创建如下的各表以及视图等数据库对象。
STUDENT表

学 号	姓 名	性 别	年 龄	所 在 系
Sno	Sname	Ssex	Sage	Sdept
200215121	李勇	男	20	CS
200215122	刘晨	女	19	CS
200215123	王敏	女	18	MA
200515125	张立	男	19	IS
COURSE表
课程号	课程名	先行课	学分
---------------------------	---------------	--------------	------------
Cno	Cname	Cpno	Ccredit
1	数据库	5	4
2	数学	1	2
3	信息系统	6	4
4	操作系统	7	3
5	数据结构	6	4
6	数据处理		2
7	PASCAL语言		4
SC表
学号	课程号	成绩
---------------------------	---------------	--------------
Sno	Cno	Grade
200215121	1	92
200215121	2	85
200215121	3	88
200215122	2	90
200215122	3	80

四、实验操作及过程

--1.查询某系的学生的最大年龄和最小年龄。
use jiaoxue1
if object_id('Pro_Sage','p') is not null
drop procedure Pro_Sage
go
CREATE PROCEDURE Pro_Sage
	@sdept char(10),@sage_min smallint output,@sage_max smallint output
AS
BEGIN
	select @sage_max=max(sage),@sage_min=min(sage) from student where sdept=@sdept
end
go
declare @sdept char(10),@sage_min smallint ,@sage_max smallint
exec Pro_Sage 'CS',@sage_min output,@sage_max output
print 'CS'+str(@sage_max)+str(@sage_min)

--(2)创建存储过程Pro_Qgrade ：通过学生姓名和课程名查询该生该课程的成绩
if object_id('Pro_Qgrade','o') is not null
drop procedure Pro_Qgrade
go
CREATE PROCEDURE Pro_Qgrade
	@cname char(10),@sname char(8) output,@grade tinyint output
AS
BEGIN
	select @grade=grade from student,sc,course where student.sno=sc.sno and 
	course.cno=sc.cno and sname=@sname and cname=@cname
end
go
declare @cname char(10),@sname char(8),@grade tinyint
exec Pro_Qgrade '数据库','李勇',@grade output
print '数据库'+' '+'李勇'+str(@grade)

--由于在刚开始建库时没有‘程序设计‘这一门课程，因此用“数据库”来代替
--(3)创建存储过程Pro_Qcname:通过学生课程名来查询学生姓名、成绩、系名，并给出“数据库”课程的查询信息
use jiaoxue1
if object_id('Pro_Qcname','t') is not null
drop procedure Pro_Qcname
go
CREATE PROCEDURE Pro_Qcname
	@cname char(10),@sname char(8) output,@sdept char (10) output,@grade int output
AS
BEGIN
	select @sname=sname,@grade=grade,@sdept=sdept from student,sc,course where student.sno=sc.sno and course.cno=sc.cno and cname=@cname
end
go
declare @cname char(10),@sname char(8),@sdept char (10),@grade int
exec Pro_Qcname '数据库',@sname output,@sdept output,@grade output
print '数据库'+ @sname + @sdept + str(@grade)
--*************************************************************************************************
use jiaoxue1
if object_id('pro_s','p') is not null
drop procedure pro_s
go
CREATE PROCEDURE pro_s
	@sname char(10),@cname char(20),@grade int output
AS
BEGIN
	select @grade=grade from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and sname=@sname and cname=@cname
END
GO
	declare @grade int
	exec pro_s '李勇','数据库',@grade output
	print @grade
go
--创建存储过程Pro_Qsinf:通过学生学号来查询学生姓名、年龄、系名
CREATE PROCEDURE Pro_Qsinf
	@sno char(10)='201215121',@sname char(8) output,@sage int output,@sdept char (10) output
AS
BEGIN
	select @sname=sname,@sage=sage,@sdept=sdept from student where sno=@sno
end
go
--执行存储过程Pro_Qsinf。查询并显示出默认学号（即201215121）和学号为201215122学生的姓名和年龄
use jiaoxue1
declare @sno char(8),@sname char(8),@sage int,@sdept char(10)
exec Pro_Qsinf default,@sname output,@sage output,@sdept output     --未加default则会有形参 "@sno" 未声明为 OUTPUT 参数，但传递进来的实参请求输出。
print @sname
print @sage
print @sdept
select @sno='201215122'
exec Pro_Qsinf @sno,@sname output,@sage output,@sdept output
print @sname
print @sage
print @sdept
go
--创建存储过程Pro_Qgrade ：通过学生姓名和课程名查询该生该课程的成绩
CREATE PROCEDURE Pro_Qgrade
	@cname char(10),@sname char(8) output,@grade tinyint output
AS
BEGIN
	select @grade=grade from student,sc,course where student.sno=sc.sno and course.cno=sc.cno and sname=@sname and cname=@cname
end
go
--执行存储过程Pro_Qgrade。查询并显示学生“李勇”的“数学”课程的成绩 
use jiaoxue1
declare @sname char(8),@cname char(8),@grade tinyint
select @sname='李勇'
select @cname='数学'
exec Pro_Qgrade @sname,@cname,@grade output
print rtrim (@sname)+ '=' + ltrim (str(@grade))
go
--用企业管理器修改存储过程Prc_Qsinf  
--修改要求：把定义中的变量sno_in长度修改为2字节；sage_out变量类型改为tinyint。
--go
--alter procedure Pro_Qsinf
--@sno char(2)='201215121',@sname char(8) output,@sage tinyint output,@sdept char (10) output
--AS
--BEGIN
--	select @sname=sname,@sage=sage,@sdept=sdept from student where sno=@sno
--end
--go
--用SQL修改存储过程Prc_Qsinf。存储过程的定义改为：根据学号查询姓名、性别、系名。设默认学号为“S1”。
go
alter procedure Pro_Qsinf
@sno char(2)='201215121',@sname char(8) output,@ssex char(2) output,@sdept char (10) output
AS
BEGIN
	select @sname=sname,@ssex=ssex,@sdept=sdept from student where sno=@sno
end
go
--用SQL删除存储过程Prc_Qscore
use jiaoxue1
drop procedure Pro_Qgrade
go

五、实验小结

本实验用到的语句：
创建存储过程的SQL语句
CREATE PROCEDURE [拥有者.][存储过程名][；程序编号]
[{ @参数名 数据类型 } [ VARYING ][ = 默认值][ OUTPUT ]]
[，… n ]
[WITH {RECOMPILE | ENCRYPTION | RECOMPILE，ENCRYPTION} ]
AS 程序行
2.执行存储过程的SQL语句
[ [ EXEC[UTE ] ][ @返回值 = ]{ 程序名[；程序编号]
| @存储程序名的变量 }[ [ @参数名= ]{ 参数值 }
| @变量[OUTPUT ] | [DEFAULT ] ] [,…n ]
[ WITH RECOMPILE ]
通过用户自定义存储过程的创建、修改、执行等一系列操作了解存储过程的执行语义。