实验目的:熟悉存储过程的定义和使用,熟练运用 select ,update ,insert ,delete 命令完成对学生信息数据库的查询、更新、添加、删除操作。
实验内容及要求
图书(书号,书名,作者编号,价格,出版社编号)
作者(编号,姓名,电话)
出版社(编号,出版社名称,地址)
根据上面基本表的信息定义一个存储过程,完成下面功能:
入口参数:出版社编号
1 显示出版社信息
2 如果没书出版,删除该记录
3 如果该出版社的图书平均价格低于20元,所有书涨价10%。
/*建库*/ create database Books /*使用已经建立的数据库建表*/ --Authortable use Books create table Authortable ( Anu Char(11)primary key,--nu Ana Char(32),--na Atel Char(11)unique--tel ) insert into Authortable values('ywm153264','严蔚敏','15302305168'), ('ldm0523621','李冬梅','17859642300'), ('wwm032651','吴伟民','13746902305'), ('ykq156234','杨孔庆','17846520203'), ('hmx023102','何孟星','15326490203'), ('zxr659132','朱晓睿','19932650200'), ('hly032615','黄黎原','19965326041'), ('qnbtl0361','乔恩•本特利','05231648456'), ('zal1532643','赵安笼','13649532100') --Publishertable use Books create table Publishertable ( Pnu Char(11)primary key,--nu Pna Char(32),--na Pid Char(60)--id ) insert into Publishertable values('rmyd15632','人民邮电出版社','北京市丰台区成寿寺路11号'), ('gj4582136','高教出版社','北京市西城区德外大街4号'), ('qhdx25169','清华大学出版社','北京市海淀区双清路学研大厦A座') --Booktable use Books create table Booktable ( Bnu Char(20),--Bnu Bna Char(32),--Bna Anu Char(11) foreign key references Authortable(Anu), Bpri float check (Bpri>0 and Bpri<=300), Pnu Char(11) , primary key(Bnu,Anu) ) insert into Booktable values('sjjgC78712','数据结构(C语言版)','ywm153264','35','rmyd15632'), ('sjjgC78712','数据结构(C语言版)','ldm0523621','35','rmyd15632'), ('sjjgC78712','数据结构(C语言版)','wwm032651','35','rmyd15632'), ('gdsxS65235','高等数学(第三版)','ykq156234','27.8','gj4582136'), ('qcdgdzjsyjn00','汽车电工电子技术与技能(第2版)','hmx023102','23.1','gj4582136'), ('bjldsx0365','不焦虑的数学','zxr659132','74.3','rmyd15632'), ('bysdby022','贝叶斯的博弈','hly032615','78.5','rmyd15632'), ('bczj8456312','编程珠玑','qnbtl0361','41.3','rmyd15632') create procedure proc_Publisher @Pnu char(11) as begin --查询 select * from Publishertable where @Pnu=Pnu --删除 if (select count(*)from Publishertable p,Booktable b where p.Pnu =b.Pnu and p.Pnu=@Pnu)=0 begin delete from Publishertable where Publishertable.Pnu =@Pnu print '已删除该出版社!' end else print '该出版社存在已出版图书,不可删除!' --更新 if (select avg(Bpri) from Booktable where Pnu=@Pnu)<20 begin update Booktable set Bpri=Bpri*1.1 print '该图书馆所有图书的平均价格小于20,已上调10%' end else print '该图书馆所有图书的平均价格大于20,价格不做改变!' end
共有三个出版社,分别为:人民邮电出版社,高教出版社,清华大学出版社。对这三个出版社分别进行操作
1、人民邮电出版社
--存储过程对人民邮电出版社操作
exec proc_Publisher 'rmyd15632'
/*对操作进行验证操作是否正确*/
--看出版社是否被删除,人民邮电出版社已出版图书,所以不应该被删除
select * from Publishertable where Pnu='rmyd15632'
--看图书价格是否改变,由于人民邮电出版社图书均价高于20,所以价格不应该改变
select distinct Bna,Bpri from Booktable b,Publishertable p where b.Pnu=p.Pnu and p.Pnu='rmyd15632'
存储过程查找信息结果为:
验证的两个语句执行结果分别为:
2、高教出版社
--存储过程对人民邮电出版社操作
exec proc_Publisher 'gj4582136'
/*对操作进行验证操作是否正确*/
--看出版社是否被删除,高教出版社已出版图书,所以不应该被删除
select * from Publishertable where Pnu='gj4582136'
--看图书价格是否改变,高教出版社出版图书均价小于20,所以图书价格改变,变为原价的1.1倍
select distinct Bna,Bpri from Booktable b,Publishertable p where b.Pnu=p.Pnu and p.Pnu='gj4582136'
存储过程查找结果为:
两条验证语句执行结果为:
3、清华大学出版社
--运用存储过程对清华大学出版社进行操作
exec proc_Publisher 'qhdx25169'
/*验证存储过程语句执行结果是否正确*/
--验证出版社是否被删除,清华大学出版社并没有出版图书,所以清华大学出版社应该被删除
select * from Publishertable where Pnu='qhdx25169'
--验证图书是否改变,因为清华大学出版社并没有图书被出版,所以该语句执行结果应为空
select distinct Bna,Bpri from Booktable b,Publishertable p where b.Pnu=p.Pnu and p.Pnu='qhdx25169'
存储过程语句执行结果为:
验证语句的两个执行结果分别为: