//学生表
create table student(id varchar(10),
name varchar(10),
sex char(2));
//成绩表
create table course(cid varchar(10),
cname varchar(10),
score number(4,1),
id varchar(10));
insert into course values ('c1','java',90,1);
insert into course values ('c2','oracle',80,1);
insert into course values ('c1','java',85,2);
insert into course values ('c1','oracle',88,2);
insert into course values ('c1','java',75,3);
insert into course values ('c1','java',80,4);
insert into student values ('1','张三','男');
insert into student values ('2','李四','男');
insert into student values ('3','王五','男');
insert into student values ('4','钱子','男');
insert into student values ('5','孙悟空','男');
学号 课程名 成绩
-------------------
如果有学号的学生没有考试,同样显示出学号,但是课程名和学号同样显示出为‘未知’
1
set
serveroutput
on
2
DECLARE
3
CURSOR
c_student
is
select
id
from
student;
4
CURSOR
c_course
is
select
ss.id,NVL(cname,
'
未知
'
),
5
NVL(score,
0
)
from
course cc,student ss
where
cc.id (
+
)
=
ss.id;
6
7
v_id_1 student.id
%
type;
8
v_id_2 student.id
%
type;
9
v_cname course.cname
%
type;
10
v_score course.score
%
type;
11
BEGIN
12
13
OPEN
c_course;
14
LOOP
15
fetch c_course into v_id_1,v_cname,v_score;
16 EXIT WHEN c_course%NOTFOUND; --EXIT WHEN 应该和fetch同时写在一起这样才能保证不出错误
17
18 OPEN c_student;
19 LOOP
20 fetch c_student into v_id_2;
21 EXIT WHEN c_student%NOTFOUND; --EXIT WHEN 应该和fetch同时写在一起这样才能保证退出有效
22
23 if v_id_1 = v_id_2 then
24 DBMS_OUTPUT.put_line(v_id_1||' '||v_cname||' '||v_score);
25 end if;
26
27 END LOOP;
28 CLOSE c_student;
29
30 END LOOP;
31 CLOSE c_course;
32END;
33
34
posted on 2006-09-09 16:08
JavaCoffe 阅读(898)
评论(1) 编辑 收藏 所属分类:
Oralce&&PL/SQL