学习PL/SQL语言时遇到的常见例题分析
在学习pl/sql语言时接触到几种常见的例题，主要还是游标和函数的基本应用，以下是相关的程序代码(ps:编者能力有限，如有错误之处，请指正)：
准备材料：
create table student
(sno char(6),
sname varchar2(8),
ssex char(4),
sbirth date,
class number(1)
)
drop table cjb
create table course
(cno char(4),
cname varchar2(20),
credit number(1)
)
create table cjb
(sno char(6),
cno char(6),
grade number(3)
)
begin
insert into student
values('s01','郭靖','男',to_date('1999-8-5','yyyy-mm-dd'),1);
insert into student
values('s02','黄蓉','女',to_date('1998-2-5','yyyy-mm-dd'),1);
insert into student
values('s03','欧阳峰','男',to_date('1998-3-5','yyyy-mm-dd'),2);
insert into student
values('s04','黄药师','男',to_date('1999-11-5','yyyy-mm-dd'),2);
insert into student
values('s05','乔峰','男',to_date('1999-8-5','yyyy-mm-dd'),3);
insert into student
values('s06','小龙女','女',to_date('1998-2-5','yyyy-mm-dd'),3);
insert into student
values('s07','杨过','男',to_date('1998-3-5','yyyy-mm-dd'),2);
insert into student
values('s08','李莫愁','女',to_date('1997-11-5','yyyy-mm-dd'),1);
insert into course
values('c01','高数',4);
insert into course
values('c02','原理',3);
insert into course
values('c03','c语言',3);
insert into cjb
values('s01','c01',78);
insert into cjb
values('s01','c02',89);
insert into cjb
values('s02','c01',78);
insert into cjb
values('s02','c03',48);
insert into cjb
values('s02','c02',98);
insert into cjb
values('s03','c02',85);
insert into cjb
values('s03','c03',72);
insert into cjb
values('s04','c02',60);
insert into cjb
values('s05','c02',85);
insert into cjb
values('s05','c03',72);
insert into cjb
values('s06','c02',60);
insert into cjb
values('s07','c02',85);
insert into cjb
values('s07','c01',50);
insert into cjb
values('s08','c02',60);
end;
练习：
1.编写pl/sql程序，查询是否有选修“指定号1(如c01)”课程的学生，如果有，统计成绩大于90分的学生个数;
如果没有，查询是否有是否有选修“指定号2(如c05)”课程的学生，如果存在，则显示所有选修该课程的学生数;
否则利用自定义异常返回信息：“尚无选修“指定号1(如c01)”和““指定号1(如c01)”课程的学生”。
--select count(sno) from cjb where cno='c02' and grade>90;
set serveroutput on;
declare
var_cno1 course.cno%type:=&h;
var_cno2 course.cno%type:=&m;
null_exeption exception;
m number;
begin
select count(sno) into m from cjb where cno=var_cno1 and grade>90;
if m!=0 then dbms_output.put_line('选修'||var_cno1||'且成绩大于90的人数为'||m);
elsif m=0
then select count(sno) into m from cjb where cno=var_cno2; dbms_output.put_line('选修'||var_cno2||'的人数为'||m);
end if;
if m=0
then raise null_exeption;
end if;
exception
when null_exeption then
dbms_output.put_line('尚无选修'||var_cno1||'和'||var_cno2||'的学生');
end;
例如:输入c02、c01，输出结果：选修c02 课程且分数在90分以上的学生人数：1
输入c05、c01，输出结果：选修c01 课程的学生人数：3
输入c07、c08，输出结果：尚无选修c07 和c08 课程的学生
2. 使用函数根据学生的姓名和课程名返回该学生该门课程的成绩等级(90以上优秀;75-90良好;60-75中等，60以下不及格);
例如： “黄蓉”，“高数”，输出：黄蓉高数课程的成绩等级为良好
select grade from cjb where cno=(select cno from course where cname='高数') and sno=(select sno from student where sname='黄蓉')
create or replace function search_function(var_sname student.sname%type,var_cname course.cname%type) return number is
sgrade cjb.grade%type;
begin
select grade into sgrade from cjb where cno=(select cno from course where cname=var_cname) and sno=(select sno from student where sname=var_sname);
if sgrade>90
then dbms_output.put_line(var_sname||'的'||var_cname||'课程成绩为优秀');
elsif sgrade<90 and sgrade>75
then dbms_output.put_line(var_sname||'的'||var_cname||'课程成绩为良好');
elsif sgrade<75 and sgrade>60
then dbms_output.put_line(var_sname||'的'||var_cname||'课程成绩为及格');
elsif sgrade<60
then dbms_output.put_line(var_sname||'的'||var_cname||'课程成绩为不及格');
end if;
return null;
end;
declare
var_sname course.cname%type:=&h;
var_cname student.sname%type:=&b;
m number;
begin
m:=search_function(var_sname,var_cname);
end;
3.编写pl/sql程序，输出所有不及格学生的学号、姓名、选修的课程名及成绩;
declare
cursor cur_cjb is
select cjb.sno,sname,cname,grade from cjb,course,student where cjb.sno=student.sno and cjb.cno=course.cno and grade<60;
type record_cjb is record(
var_sno student.sno%type,
var_sname student.sname%type,
var_cname course.cname%type,
sgrade cjb.grade%type);
cjb1 record_cjb;
begin
open cur_cjb;
fetch cur_cjb into cjb1;
while cur_cjb%found loop
dbms_output.put_line('不及格的学生学号为'||cjb1.var_sno||'姓名是'||cjb1.var_sname||' 课程名为'||cjb1.var_cname||' 成绩为'||cjb1.sgrade);
fetch cur_cjb into cjb1;
end loop;
close cur_cjb;
end;
4.编写pl/sql程序，输出所有课程的选课人数和平均分;
declare
cursor cur_cjb is
select cno,count(sno),round(avg(grade),1) from cjb group by cno;
type record_cjb is record(
var_cno cjb.cno%type,
num number,
sgrade cjb.grade%type);
cjb1 record_cjb;
begin
open cur_cjb;
fetch cur_cjb into cjb1;
while cur_cjb%found loop
dbms_output.put_line('所选课程号'||cjb1.var_cno||'人数'||cjb1.num||'平均分'||cjb1.sgrade);
fetch cur_cjb into cjb1;
end loop;
close cur_cjb;
end;