CREATE TABLE students (
id NUMBER(5) PRIMARY KEY,
first_name VARCHAR2(20),
last_name VARCHAR2(20),
major VARCHAR2(30),
current_credits NUMBER(3) 当前已得学分
)
INSERT INTO STUDENTS (id, first_name, last_name, major, current_credits) VALUES (10000, 'Scott', 'Smith', 'Computer Science', 0);
............................................(其他插入数据省略)
学生选修表 Student_id 学生号
Department 开课系别 Course 开课课程 Grade 分数 CREATE TABLE registered_students (
student_id NUMBER(5) NOT NULL,
department CHAR(3) NOT NULL,
course NUMBER(3) NOT NULL,
grade CHAR(1),
CONSTRAINT rs_grade
CHECK (grade IN ('A', 'B', 'C', 'D', 'E')),
CONSTRAINT registeredStudentPK
PRIMARY KEY (student_id,department, course),
CONSTRAINT rs_student_id
FOREIGN KEY (student_id) REFERENCES students (id),
CONSTRAINT rs_department_course
FOREIGN KEY (department, course)
REFERENCES classes (department, course)
);
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10000, 'CS', 102, 'A');
..................................................... SQL> set serveroutput on;
SQL> declare
2 cursor c_student is
3 select major,first_name,last_name,department,course,grade
4 from students,registered_students where students.id=registered_students.stu
dent_id;
5 begin
6 for student_record in c_student loop
7 dbms_output.put_line(student_record.major||' '||student_record.first_name||
' '||student_record.last_name||' '||student_record.department||' '||student_reco
rd.course||' '||student_record.grade);
8 end loop;
9 end;
10 /
Computer Science Scott Smith CS 102 A
Computer Science Joanne Junebug CS 102 B
Economics Manish Murgratroid CS 102 C
Economics Manish Murgratroid HIS 101 A
Computer Science Scott Smith HIS 101 A
History Margaret Mason HIS 101 B
Computer Science Joanne Junebug HIS 101 B
History Patrick Poll HIS 101 C
History Timothy Taller HIS 101 C
Economics Barbara Blues HIS 101 E
Computer Science David Dinsmore HIS 101 B
Nutrition Ester Elegant HIS 101 A
Music Rose Riznit HIS 101 D
Nutrition Rita Razmataz HIS 101 A
Nutrition Ester Elegant NUT 307 A
Nutrition Rita Razmataz NUT 307 A
Music Rose Riznit MUS 410 B
Economics Barbara Blues MUS 410 E
Economics Manish Murgratroid ECN 203 A
Computer Science David Dinsmore CS 101 BPL/SQL 过程已成功完成。
我想循化打印出学生的选课情况,打印的格式如下(major, firstname ,lastname, department,course, grade)
History Scott Smith HIS101 A
HIS106 B
CS102 C
ComputerScience Margaret Mason HIS101 A
CS102 C但我的很明显有如下问题
问题1: 在第四行:from students,registered_students where students.id=registered_students.stu
该块的显示结果应该是: computer Science连在一起输出,然后在第二个专业的连在一起,而不是分开的
问题2: 最近接触的函数还相当有限,请教各位高手,怎样才能打印出我想要的格式了???请教各位高手???
id NUMBER(5) PRIMARY KEY,
first_name VARCHAR2(20),
last_name VARCHAR2(20),
major VARCHAR2(30),
current_credits NUMBER(3) 当前已得学分
)
INSERT INTO STUDENTS (id, first_name, last_name, major, current_credits) VALUES (10000, 'Scott', 'Smith', 'Computer Science', 0);
............................................(其他插入数据省略)
学生选修表 Student_id 学生号
Department 开课系别 Course 开课课程 Grade 分数 CREATE TABLE registered_students (
student_id NUMBER(5) NOT NULL,
department CHAR(3) NOT NULL,
course NUMBER(3) NOT NULL,
grade CHAR(1),
CONSTRAINT rs_grade
CHECK (grade IN ('A', 'B', 'C', 'D', 'E')),
CONSTRAINT registeredStudentPK
PRIMARY KEY (student_id,department, course),
CONSTRAINT rs_student_id
FOREIGN KEY (student_id) REFERENCES students (id),
CONSTRAINT rs_department_course
FOREIGN KEY (department, course)
REFERENCES classes (department, course)
);
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10000, 'CS', 102, 'A');
..................................................... SQL> set serveroutput on;
SQL> declare
2 cursor c_student is
3 select major,first_name,last_name,department,course,grade
4 from students,registered_students where students.id=registered_students.stu
dent_id;
5 begin
6 for student_record in c_student loop
7 dbms_output.put_line(student_record.major||' '||student_record.first_name||
' '||student_record.last_name||' '||student_record.department||' '||student_reco
rd.course||' '||student_record.grade);
8 end loop;
9 end;
10 /
Computer Science Scott Smith CS 102 A
Computer Science Joanne Junebug CS 102 B
Economics Manish Murgratroid CS 102 C
Economics Manish Murgratroid HIS 101 A
Computer Science Scott Smith HIS 101 A
History Margaret Mason HIS 101 B
Computer Science Joanne Junebug HIS 101 B
History Patrick Poll HIS 101 C
History Timothy Taller HIS 101 C
Economics Barbara Blues HIS 101 E
Computer Science David Dinsmore HIS 101 B
Nutrition Ester Elegant HIS 101 A
Music Rose Riznit HIS 101 D
Nutrition Rita Razmataz HIS 101 A
Nutrition Ester Elegant NUT 307 A
Nutrition Rita Razmataz NUT 307 A
Music Rose Riznit MUS 410 B
Economics Barbara Blues MUS 410 E
Economics Manish Murgratroid ECN 203 A
Computer Science David Dinsmore CS 101 BPL/SQL 过程已成功完成。
我想循化打印出学生的选课情况,打印的格式如下(major, firstname ,lastname, department,course, grade)
History Scott Smith HIS101 A
HIS106 B
CS102 C
ComputerScience Margaret Mason HIS101 A
CS102 C但我的很明显有如下问题
问题1: 在第四行:from students,registered_students where students.id=registered_students.stu
该块的显示结果应该是: computer Science连在一起输出,然后在第二个专业的连在一起,而不是分开的
问题2: 最近接触的函数还相当有限,请教各位高手,怎样才能打印出我想要的格式了???请教各位高手???
解决方案 »
- 关于64位机器下调用oci
- 复杂视图(5个表)如何优化 承担高并发量的访问
- java.sql.SQLException: 用尽的 Resultset 如何解决
- 连接池问题
- 弱弱的问:PL/SQL中有类似数组的存储类型吗?
- 简单的一个问题,很急,请大侠们帮帮小弟。。在线等
- 100分请教:如何统计查询,需要嵌套吗?
- 在ORACLE数据库里创建表的时候怎么样保证只能有一条记录?
- 请问在在oracle中类似sqlsever的 left(t_cont,CHARINDEX(':',t_cont)-1)语句要怎么写
- 请问怎么把大量的oracle的dup文件批量导入在导出成txt文件,并且分割符是||
- 日期类型数据比较大小及求差值
- 求一条查询
Computer Science Scott Smith CS 102 A
Computer Science Joanne Junebug CS 102 B
Economics Manish Murgratroid CS 102 C
Economics Manish Murgratroid HIS 101 A
Computer Science Scott Smith HIS 101 A 是一个人的应该连在一起:如 第1行 和 第5行就分开了
Computer Science David Dinsmore CS 101 B
Computer Science David Dinsmore HIS 101 B
Computer Science Joanne Junebug CS 102 B
Computer Science Joanne Junebug HIS 101 B
Computer Science Scott Smith CS 102 A
Computer Science Scott Smith HIS 101 A
Economics Barbara Blues HIS 101 E
Economics Barbara Blues MUS 410 E
Economics Manish Murgratroid CS 102 C
Economics Manish Murgratroid ECN 203 A
Economics Manish Murgratroid HIS 101 A
History Margaret Mason HIS 101 B
History Patrick Poll HIS 101 C
History Timothy Taller HIS 101 C
Music Rose Riznit HIS 101 D
Music Rose Riznit MUS 410 B
Nutrition Ester Elegant HIS 101 A
Nutrition Ester Elegant NUT 307 A
Nutrition Rita Razmataz HIS 101 A
Nutrition Rita Razmataz NUT 307 A但是我想要的效果是:
Computer Science David Dinsmore CS 101 B
HIS 101 B
Computer Science Joanne Junebug CS 102 B
HIS 101 B
Computer Science Scott Smith CS 102 A
HIS 101 A
Economics Barbara Blues HIS 101 E
MUS 410 E
Economics Manish Murgratroid CS 102 C
ECN 203 A
HIS 101 A
History Margaret Mason HIS 101 B
History Patrick Poll HIS 101 C
History Timothy Taller HIS 101 C
Music Rose Riznit HIS 101 D
MUS 410 B
Nutrition Ester Elegant HIS 101 A
NUT 307 A
Nutrition Rita Razmataz HIS 101 A
NUT 307 A即同一个人的不要重复输出姓名和专业,怎样才能输出???
cursor c_student is
select major,first_name,last_name,department,course,grade
from students,registered_students where students.id=registered_students.student_id
order by major,first_name,last_name,department;
var_equal varchar2(70);
begin
var_equal :='';
for student_record in c_student loop
if var_equal = student_record.major||student_record.first_name||student_record.last_name then
dbms_output.put_line(lpad(' ',70,' ')||' '||student_record.department||' '||student_reco
rd.course||' '||student_record.grade);
else
dbms_output.put_line(student_record.major||' '||student_record.first_name||
' '||student_record.last_name||' '||student_record.department||' '||student_reco
rd.course||' '||student_record.grade);
var_equal := student_record.major||student_record.first_name||student_record.last_name;
end if;
end loop;
end;
/
cursor c_student is
select major,first_name,last_name,department,course,grade
from students,registered_students where students.id=registered_students.student_id
order by major,first_name,last_name,department;
var_equal varchar2(70);
begin
var_equal :='';
for student_record in c_student loop
if var_equal = student_record.major||student_record.first_name||student_record.last_name then
dbms_output.put_line(lpad(' ',length(student_record.major||student_record.first_name||student_record.last_name),' ')||' '||student_record.department||' '||student_reco
rd.course||' '||student_record.grade);
else
dbms_output.put_line(student_record.major||' '||student_record.first_name||
' '||student_record.last_name||' '||student_record.department||' '||student_reco
rd.course||' '||student_record.grade);
var_equal := student_record.major||student_record.first_name||student_record.last_name;
end if;
end loop;
end;
/
gaoyu_id students.id%type:=0;
cursor gaoyu_student is
select id,major,first_name,last_name,department,course,grade
from students,registered_students where students.id=registered_students.student_id
order by major,first_name,last_name,department;
begin
for student_record in gaoyu_student loop
if(student_record.id!=gaoyu_id) then
dbms_output.put_line(student_record.major||' '||student_record.first_name||' '||student_record.last_name||' '||student_record.department||' '||student_record.course||' '||student_record.grade);
else
dbms_output.put_line(student_record.department||' '||student_record.course||' '||student_record.grade);
end if;
gaoyu_id:=student_record.id;
end loop;
end;
Computer Science David Dinsmore CS 101 B
HIS 101 B
Computer Science Joanne Junebug CS 102 B
HIS 101 B
Computer Science Scott Smith CS 102 A
HIS 101 A
Economics Barbara Blues HIS 101 E
MUS 410 E
Economics Manish Murgratroid CS 102 C
ECN 203 A
HIS 101 A
History Margaret Mason HIS 101 B
History Patrick Poll HIS 101 C
History Timothy Taller HIS 101 C
Music Rose Riznit HIS 101 D
MUS 410 B
Nutrition Ester Elegant HIS 101 A
NUT 307 A
Nutrition Rita Razmataz HIS 101 A
NUT 307 A
差一点了