一、实验目的
熟练掌握存储过程的定义和使用，理解存储过程在数据库的使用中能发挥出什么样的作用。
二、实验环境
硬件环境：PC
软件环境：Windows 10 、SQL Server 2008
--1、调用全局变量@@version和系统函数getdate（）
--来显示SQL Server的版本号及当前系统时间。
print '版本号:'+@@version+'系统时间：'+convert(char(50),getdate())
--2、使用SQL语句创建无参数的存储过程并执行
--（1）问题描述：班主任经常会查询学生的学号、姓名、电话号码、家庭住址等信息（需设置别名），
--创建存储过程p1，并执行实现查询的操作。
create procedure p1
as
select s_id '学号',s_name '姓名',s_telephone '电话号码',s_address '家庭住址'
from student
go
exec p1
--（2）问题描述：系主任要查询“计算机16-1”班的学生姓名、班级、课程名称、成绩等信息
--（需设置别名），创建存储过程p2，并执行实现查询的操作。
create procedure p2
as
select s_name '姓名',c_name '班级',course_name '课程名称',grade '成绩'
from class cl,student s,s_c,course co
where
cl.c_id=s.c_id and s.s_id=s_c.s_id and s_c.course_id=co.course_id
and c_name='计算机16-1'
go
exec p2
--（3）问题描述：创建存储过程p3，
--若存在学号为“2016010101”的学生记录，则删除此学生的基本信息及其选课信息，
--若不存在该学生，则显示“没有这个学生！”，同时显示学生表和成绩表中的信息，
--执行此存储过程实现以上操作。
create procedure p3
as
begin
if exists (select *from student where s_id='2016010101')
begin
delete from s_c where s_id='2016010101'
delete from student where s_id='2016010101'
print '删除该学生基本信息及选课信息成功！'
end
else
begin
print '没有这个学生！'
select *from student
select *from s_c
end
end
go
exec p3
--（4）使用系统存储过程sp_helptext查看存储过程p3的定义文本。
exec sp_helptext p3
--（5）问题描述：创建存储过程p4，检索学生的学号、姓名、性别、出生日期等信息（需设置别名），
--对定义语句进行加密处理，再使用系统存储过程sp_helptext查看定义文本。
create procedure p4
with encryption
as
select s_id '学号',s_name '姓名',s_sex '性别',s_borndate '出生日期' from student
go
exec sp_helptext p4
--对象 'p4' 的文本已加密。
--3、使用SQL语句创建带参数的存储过程并执行
--（1）问题描述：查询指定学生姓名的学生学号、姓名、电话号码和家庭住址等信息，
--创建存储过程pd1。并使用参数“王丽”执行该存储过程。
create procedure pd1
@sname varchar
as
select s_id '学号',s_name '姓名',s_telephone '电话号码',s_address '家庭住址'
from student
where s_name=@sname
go
exec pd1 '王丽'
--2016010201 王丽 13079452444 山东省烟台市
--（2）问题描述：创建存储过程pd2，
--检索指定学生姓名的学号、姓名、课程号、成绩信息，使用参数“王丽”执行存储过程。
create procedure pd2
@sname varchar
as
select s_c.s_id '学号',s_name '姓名',c_id '课程号',grade '成绩'
from student s,s_c
where s.s_id=s_c.s_id and s_name=@sname
go
exec pd2 '王丽'
--（3）问题描述：创建存储过程pd3，
--检索指定班级名称和课程名称的学生学号、姓名、班级名称、课程名称、成绩信息，
--使用参数“计算机16-1”和“数据库原理与应用”，执行该存储过程。
--（使用顺序法和提示法两种传参方式执行存储过程）
create procedure pd3
@cl_name varchar,
@co_name varchar
as
select s_c.s_id '学号',s_name '姓名',c_name '班级',course_name '课程名称',grade '成绩'
from class cl,student s,s_c,course co
where
cl.c_id=s.c_id and s.s_id=s_c.s_id and s_c.course_id=co.course_id
and c_name=@cl_name and co.course_name=@co_name
go
--使用两种传参方式执行存储过程
--（1）指定名称传递
-- exec 过程名 @参数名=值1，@参数名=值2……--与定义时的顺序可以不同
exec pd3 @co_name='数据库原理与应用',@cl_name='计算机16-1'
--（2）按位置传递
-- exec 过程名 值1，值2 --与定义时的顺序必须相同
exec pd3 '计算机16-1','数据库原理与应用'
--4、使用SQL语句创建带默认参数、output参数的存储过程并执行
--（1）问题描述：创建存储过程pds1，
--向student表中插入记录，性别默认为“女”，
--若没有指定家庭住址，则默认值为“山东济南”，执行该存储过程。
create procedure pds1
@sno char,@sname varchar,@sex char='女',
@sborndate datetime,@senrolldate datetime,
@telephone char,@address varchar='山东济南',@cid char
as
begin
insert into student values
(@sno,@sname,@sex,@sborndate,@senrolldate,@telephone,@address,@cid)
print '学生表插入新记录成功!'
end
go
exec pds1
'2016010200','李小白',default,'2000-01-01','2018-09-01','12345678900',default,'20160101'
--（2）问题描述：创建存储过程pds2，根据某学生的学号输出该学生的姓名、联系方式和家庭住址，
--使用2016010102作为实参，执行存储过程。
create procedure pds2
@sno char,
@sname varchar output,
@telephone charoutput,
@address varchar output
as
begin
select @sname=s_name,@telephone=s_telephone,@address=s_address from student where s_id=@sno
select '姓名'=@sname,'联系方式'=@telephone,'家庭住址'=@address
end
go
declare @sname char
declare @telephone char
declare @address varchar
exec pds2 '2016010102',@sname output,@telephone output,@address output
--（3）创建存储过程pds3，向teacher表中插入记录，性别默认为“男”，
--若没有指定职称，则默认值为“讲师”，执行此存储过程。
create procedure pds3
@tno char,@tname varchar,@tsex char='男',@tentrydate datetime,
@tprofessor varchar='讲师',@tsaraly decimal,@depid char
as
begin
insert into teacher values(@tno,@tname,@tsex,@tentrydate,@tprofessor,@tsaraly,@depid)
print '教师表插入新记录成功!'
end
go
exec pds3 '0203','李大白',default,'2003-07-15',default,5000,'01'
--（4）创建存储过程pds4，根据输入的课程号将该门课程的学分增加1学分，并将修改后的学分输出，
--使用参数“0001”，执行该存储过程。
create procedure pds4
@cno char,
@credit decimal output
as
begin
update course set course_credit+=1 where course_id=@cno
select @credit=course_credit from course where course_id=@cno
select '修改后的学分'=@credit
end
go
declare @credit decimal
exec pds4 '0001',@credit
--5、使用管理平台执行存储过程pds4.
--找到数据库->'可编程性'->'存储过程'->'dbo.pds4'
--右击选择'执行存储过程'，输入参数执行即可。
--6、分别使用管理平台和SQL 命令删除存储过程p1和p2。
--①管理平台
--找到数据库->'可编程性'->'存储过程'->'dbo.p1'
--右击选择'删除'即可。
--②使用SQL 命令
drop procedure p1
drop procedure p2
--要求:提交创建存储过程的SQL脚本,并标注必要的注释。
--保证程序能够正确编译和运行,并有相应的测试代码。
京公网安备 11010502036488号