有2个表,老师表和学生表。
Create Table Teacher(TeachName varchar2(50));
Insert into Teacher
select '张老师' from dual
union
select '李老师' from dual;
Create Table Students(TeachName varchar2(50),StuName varchar2(50));
Insert into Students
select '张老师', '学生A' from dual
union
select '张老师', '学生B' from dual
union
select '李老师', '学生C' from dual
union
select '李老师', '学生D' from dual;
我想打印出的效果如下:
张老师
... 学生A
... 学生B
李老师
... 学生C
... 学生D
我想用游标来实现这个功能(因为正在学习怎么使用oracle的游标),但是只能打印出老师的信息,每个老师带的哪些学生,在写的时候发现需要嵌套1个学生的游标才能实现,不知道哦啊该怎么写了,请问,怎样才能实现上面的打印? 多谢!
declare
cursor cur_Teacher Is
Select TeachName from Teacher;
row_Teacher Teacher%rowtype;
begin
for row_Teacher in cur_Teacher loop
dbms_output.put_line(row_Teacher.TeachName);
--再接着打印这个老师带的学生就不知道怎么写了!
end loop;
end;
------最佳解决方案--------------------
declare
cursor cur_Teacher Is
Select TeachName from Teacher;
row_Teacher Teacher%rowtype;
begin
for row_Teacher in cur_Teacher loop
dbms_output.put_line(row_Teacher.TeachName);
--再接着打印这个老师带的学生
for rec in (select stuname from students where teachname=row_teacher.teachname) loop
dbms_output.put_line(' ... '
------其他解决方案--------------------
rec.stuname);
end loop;
end loop;
end;
------其他解决方案--------------------
declare
cursor cur_Teacher Is
Select * from Teacher;
cursor cur_Student Is
Select * from Students;
row_Teacher Teacher%rowtype;
row_Students Students%rowtype;
begin
for row_Teacher in cur_Teacher loop
dbms_output.put_line(row_Teacher.TeachName);
for row_Student in cur_Student loop
if row_Student.TeachName=row_Teacher.TeachName then
dbms_output.put_line('...'
------其他解决方案--------------------
row_Student.StuName);
end if;
end loop;
end loop;
end;