实验七创建和操作数据库对象.docx
《实验七创建和操作数据库对象.docx》由会员分享,可在线阅读,更多相关《实验七创建和操作数据库对象.docx(19页珍藏版)》请在冰豆网上搜索。
![实验七创建和操作数据库对象.docx](https://file1.bdocx.com/fileroot1/2022-11/16/286483ca-7408-4161-8e7f-e2c82a96449f/286483ca-7408-4161-8e7f-e2c82a96449f1.gif)
实验七创建和操作数据库对象
云南大学软件学院
实验报告
课程:
数据库原理与实用技术实验任课教师:
张璇,刘宇
专业:
软件工程学号:
姓名:
成绩:
实验7创建和操作数据库对象
分号;SQL语句进行分隔(不是必须的,但是良好的习惯)
带输入/输出参数的存储过程
1.只有输出参数(执行存储过程时按顺序填充参数)
createprocgrad
(@max_gradeintoutput,
@min_gradeintoutput)
as
select@max_grade=MAX(grade)
fromgrades
select@min_grade=MIN(grade)
fromgrades
return
go
declare@max_gradeint,@min_gradeint
execgrad@max_gradeoutput,@min_gradeoutput
select@max_grade,@min_grade
2.有输入输出参数(执行存储过程时按顺序填充参数)
CREATEPROCmy_procage@namenchar(20),@ageintOUTPUT
AS
ifnotexists(SELECT*FROMstudentsWHEREstud_name=@name)
return-101
SELECT@age=age
FROMstudents
WHEREstud_name=@name
return
GO
DECLARE@stud_ageint,@statusint
EXEC@status=my_procage'葛文卿',@stud_ageOUTPUT
if@status=-101
print'nothatperson!
'
else
print'thestudentageis:
'+convert(varchar(10),@stud_age)
withcheckoption
CREATEVIEWcustview
AS
SELECT*FROMcustomersWHEREdiscnt<12.0
withcheckoption
SELECT*FROMcustview
updatecustview
setdiscnt=13.0
wherecid='c001'
select*fromcustomers
实验7-1存储过程
1、使用不带参数的存储过程
(1)创建一个存储过程my_proc,查询“学生表”中所有计算机系女生的学号、姓名、性别、年龄和所在院系
DECLARE@stud_ageint,@statusintmy_proc
AS
SELECTstud_id,stud_name,gender,age,school
FROMstudents
WHEREgender='女'ANDschool='计算机'
Return
(2)执行存储过程
EXECmy_proc
(3)修改存储过程,使其能够查询计算机系女生的所有基本信息
ALTERPROCmy_proc
AS
SELECT*
FROMstudents
WHEREgender='女'ANDschool='计算机'
return
GO
EXECmy_proc
2、带输入参数的存储过程
(1)创建一个存储过程my_procsex,使其能够查询“学生表”中男学生或女学生的学号、姓名、性别、年龄和所在院系
CREATEPROCmy_procsex@sexnchar(20)
AS
SELECTstud_id,stud_name,gender,age,school
FROMstudents
WHEREgender=@sex
Return
(2)执行存储过程
EXECmy_procsex@sex='女'
3、带输入/输出参数的存储过程
(1)创建一个存储过程my_procage,使其能够根据学生姓名,查询学生年龄。
(考虑当学生不存在时给出提示信息)
(2)执行存储过程
CREATEPROCmy_procage@namenchar(20),@ageintOUTPUT
AS
ifnotexists(SELECT*FROMstudentsWHEREstud_name=@name)
return-101
SELECT@age=age
FROMstudents
WHEREstud_name=@name
return
GO
DECLARE@stud_ageint,@statusint
EXEC@status=my_procage@name='葛文卿',@age=@stud_ageOUTPUT
if@status=-101
print'nothatperson!
'
else
print'thestudentageis:
'+convert(varchar(10),@stud_age)
4、返回状态值的存储过程
(1)创建一个存储过程my_procstatus,使其能够根据学生姓名,查询学生的选课信息。
(如果没有输入学生姓名,返回状态码55;如果输入的学生姓名不存在,则返回状态码-155)
(使用参数的默认值)
CREATEPROCmy_procstatus@namenchar(20)=''
AS
if(@name='')
return55
elseifnotexists(SELECT*FROMstudentsWHEREstud_name=@name)
return-155
else
SELECTg.stud_id,cid,grade,credit
FROMgradesg,studentss
WHEREg.stud_id=s.stud_idANDstud_name=@name
Return
(2)执行存储过程:
接收存储过程返回的状态码,如果返回的状态码为55则输出提示信息“没有输入名字!
!
”;如果返回的状态码为-155,则输出“没找到!
!
”。
(使用参数的默认值)
DECLARE@statusint
EXEC@status=my_procstatus
if@status=55
print'没有输入名字!
!
'
elseif@status=-155
PRINT'没找到!
!
'
GO
DECLARE@statusint
EXEC@status=my_procstatus@name='XXX'
if@status=55
print'没有输入名字!
!
'
elseif@status=-155
PRINT'没找到!
!
'
(3)删除存储过程
DROPPROCmy_procstatus
5、在存储过程中使用临时表,不指定题目,请理解全局临时表与局部临时表的区别
createproctemp_course
AS
SELECT*INTO#coursesFROMcourses
SELECT*INTO##coursesFROMcourses
return
GO
EXECPROCtemp_course
SELECT*FROM#courses
(1)局部临时表:
以#开头,只对当前连接有效,当前连接断开时自动删除。
(2)全局临时表:
以##开头,对其他连接也有效,在当前连接和其他访问它的连接断开时自动删除。
实验7-2视图的创建与使用
1.创建一个名为stud_view1的视图,从“学生管理数据库”的学生表中查询出性别为男的所有学生的学号,姓名,性别,班级,家庭住址,入学时间和出生年月。
CREATEVIEWstud_view1AS
SELECTstud_id,stud_name,gender,class,enroll_date,2014-ageASbirth_day
FROMstudents
WHEREgender='男'
SELECT*FROMstud_view1
2.创建一个名为stud_view2的视图,查询出选修了《高等数学》的所有同学的选课信息,包括学号,姓名,成绩。
CREATEVIEWstud_view2AS
SELECTs.stud_id,s.stud_name,g.grade
FROMstudentss,gradesg,coursesc
WHEREs.stud_id=g.stud_idANDg.cid=c.cid
ANDame='高等数学'
GO
SELECT*FROMstud_view2
3.从视图stud_view1中查询出软件2班肖一竹的资料。
SELECT*FROMstud_view1
WHEREclass='软件班'ANDstud_name='肖一竹'
4.向视图stud_view2中插入一行数据:
学号为9940210,姓名为钱丽,成绩为71,结果是什么?
INSERTINTOstud_view2
VALUES(9940210,'钱丽',71)
5.修改视图stud_view1,将20023001号同学的性别更改为女。
然后再次查询学生表和视图stud_view1,看结果有何改变,为什么?
UPDATEstud_view1
SETgender='女'
WHEREstud_id=20023001
GO
SELECT*FROMstudents
GO
SELECT*FROMstud_view1
原因:
上面把学号为20023001的同学的性别改为了女,学生表被更新。
因为视图stud_view1里面存取的是性别为男的学生的信息,当把学号为20023001的同学的性别改为女后,在视图中就找不到他了。
6.完成教科书457页如下习题:
[7.5]a:
答:
三条SQL语句都不合法,因为他们创建视图的语句from后面都跟了多张表,所以创建的视图不是可更新视图。
[7.5]b:
CREATEVIEWagentview
AS
SELECT*FROMagentWHERE[percent]>=0AND[percent]<=10
withcheckoption
[7.9]:
答:
违反了规则5,不能够含有salary1+salary2这种列,这样的话,如果我们对视图进行更新以后就无法使原始表进行同步的更新,因为知道salary1+salary2的值,无法知道salary1与salary2人别的值。
[7.12]:
CREATEVIEWcustview
AS
SELECT*FROMcustomersWHEREdiscnt<12.0
withcheckoption
SELECT*FROMcustview
updatecustview
setdiscnt=13.0
wherecid='c001'
select*fromcustomers
drop