实验要求:
1.在实验二所创建的选课表(SC)的基础上,创建存储过程,对成绩进行等级转换输出。输入一个学号和课程号(输入参数),根据学号和课程号查询该同学的成绩(Grade),输出不同的评语。评价标准按如下方式进行:条件查询:if-else /// case Grade=100, 评语为“太牛了!” 90<=Grade<100, 评语为“优秀” 80<=Grade<90, 评语为“良好” 70<=Grade<80, 评语为“中等” 60<=Grade<70, 评语为“及格” 40<=Grade<60, 评语为“不及格” Grade<40, 评语为“太渣了!” 如果Grade 为 NULL, 评语为“缺考” 2. 创建函数,输入参数n, 计算1!+2!+ 3!+ …+ n! 的值。 3. 创建存储过程,根据调用时提供的学生姓名查询该学生所修课程的课程信息,在过程体中将课程号、课程名和成绩输出到输出窗口,给出过程调用语句块。 4. 创建存储过程,统计指定学生学号的平均成绩和选课门数,将统计结果用输出参数传递给主程序,在SQL窗口中调用存储过程,输出过程的返回结果。 |
控制台PL/SQL语句:
数据准备:
create database db_test3 default charset utf8mb4;
use db_test3;
create table IF NOT EXISTS student
(
sno char(20) comment '学号',
sname varchar(20),
ssex char(2) ,
sdept varchar(20) ,
sbirthday date not null
);
alter table student add primary key (sno);
insert into student values ('121002','李佳慧','女','计算机系','2001-07-02'),('121001','刘鹏翔','男','计算机系','2000-02-15'),
('121004','周仁超','男','计算机系','1999-03-02'),
('124001','林琴','女','通信学院','2004-02-23'),
('124002','杨春容','女','通信学院','1999-10-05'),
('124003','徐良成','男','通信学院','2001-03-18'),
('124004','刘良成','男','通信学院','2000-12-12'),
('121005','王小红','女','计算机系','2001-12-01'),
('121006','刘晨','男','计算机系','2003-02-14');
select * from student;
create table IF NOT EXISTS course
(cno char(20),
cname varchar(20),
cpno char(20),
credit int,
primary key(cno)
);
insert into course (cno,cname,cpno,credit) values ('1004','数据库系统','1024',4),
('1012','计算机网络','4002',3),
('4002','数字电路','8001',3),
('8001','高等数学',null,6),
('1201','英语',null,5),
('1204','程序设计基础','8001',3);
select * from course;
create table sc
(sno char(20),
cno char(25),
grade int,
primary key(sno,cno)
);
insert into sc values ('121001','1004',92),
('121002','1004',85),
('121004','1004',56),
('124001','4002',34),
('124002','4002',74),
('124003','4002',87),
('121001','8001',94),
('121002','8001',32),
('121004','8001',81),
('124001','8001',58),
('124002','8001',73),
('124003','8001',21),
('121001','1201',93),
('121002','1201',67),
('121004','1201',63),
('124001','1201',92),
('124002','1201',null),
('124003','1201',86),
('121002','1204',50),
('121001','1204',null),
('121004',1204,90),
('124001',1204,89),
('121006',1204,78),
('121005',1012,68);
select * from sc;
执行代码:
-- 设计一
delimiter $$
create procedure pro1(in xh char(20),in kch char(20))
begin
declare score int;
select grade into score from sc where sno=xh and cno=kch;
if score=100 then select '太牛了!';
elseif score>=90 then select '优秀';
elseif score>=80 then select '良好';
elseif score>=70 then select '中等';
elseif score>=60 then select '及格';
elseif score>40 then select '太渣啦!';
elseif score=null then select '缺考';
end if;
end $$
delimiter ;
-- 例子
call pro1(121001,1004);
-- 设计二
delimiter $$
create procedure pro2(in n int)
begin
declare result int default 0;
declare sum int default 1;
declare i int default 1;
while i<=n do
set sum = sum * i;
set result = result + sum;
set i = i + 1;
end while ;
select result;
end $$
delimiter ;
-- 例子
call pro2(3);
-- 设计三
delimiter $$
create procedure pro3(in name char(20))
begin
select s.cno,s.cname,sc.grade from course s,sc
where s.cno=sc.cno
and sc.sno=(select sno from student where name=sname);
end $$
delimiter ;
-- 例子
call pro3('李佳慧');
-- 设计四
delimiter $$
create procedure pro4(in xh char(20),out result1 int,out result2 int)
begin
select avg(grade),count(cno) into result1,result2 from sc group by sno having sno=(select sno from student where xh=sno);
end $$
delimiter ;
-- 例子
call pro4(121002,@re1,@re2);
select @re1,@re2;
兄弟们,有点拖了,本周才发布的;在此先给兄弟姐妹们道个歉,
不过,该来的总会来的,嘿嘿
完成收工,good!
虽然“天不生无用之人,地不长无名之草”。但仍谨记“冰冻三尺,非一日之寒”。
执长剑纵马,执妙笔生花,我王某人在此邀请诸位与我共身!