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_con1是varchar类型，所以在输入时要加‘’，如   'c02'   */
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;

 5. 创建并执行存储过程，根据学号和课程号返回该学生获得的学分（注：该门课程不及格不能获得学分）
 create or replace procedure course1(var_eno in cjb.sno%type,var_dno in cjb.cno%type,var_grade out cjb.grade%type) is
 begin
 select grade into var_grade from cjb where sno=var_eno and cno=var_dno;
 if var_grade<60 then 
 var_grade:=0;
 dbms_output.put_line('该学生成绩不及格，无法获取学分');
 end if;
 end;
 declare
 var_grade  cjb.grade%type;
 begin
 course1('s07','c02',var_grade);
 dbms_output.put_line(var_grade);
 end;

建立一个触发器，限定学生所选课程不能超过3门

create or replace trigger insert1

before insert on cjb
for each row
declare
num number;
begin
select count(sno) into num from cjb where sno=:new.sno;
if num>=2
then 
 raise_application_error(-20009,'选课不能超过3门');
 end if;
end;


insert into cjb
values('s01','c03',98);
delete from cjb where sno='s01' and cno='c03';

版权所有 https://blog.csdn.net/pengxiang1998 未经允许，禁止转载