您所在位置：主题：一个oracle的问题
congjitongID
[专家分：0] 发布于 2006-11-15 01:21:00
PROMPT students table...
DROP TABLE students CASCADE CONSTRAINTS;
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);
......
INSERT INTO STUDENTS(id, first_name, last_name, major, current_credits)
VALUES (10010, 'Rita', 'Razmataz', 'Nutrition', 0);
PROMPT major_stats...
DROP TABLE major_stats;
CREATE TABLE major_stats (
major VARCHAR2(30),
total_credits NUMBER,
total_students NUMBER);
PROMPT rooms table...
DROP TABLE rooms CASCADE CONSTRAINTS;
CREATE TABLE rooms (
room_id NUMBER(5) PRIMARY KEY,
building VARCHAR2(15),
room_number NUMBER(4),
number_seats NUMBER(4),
description VARCHAR2(50)
);
INSERT INTO ROOMS (room_id, building, room_number, number_seats, description)
VALUES (99999, 'Building 7', 310, 1000, 'Large Lecture Hall');
......
INSERT INTO ROOMS (room_id, building, room_number, number_seats, description)
VALUES (99991, 'Building 7', 310, 50, 'Discussion Room E');
PROMPT classes table...
DROP TABLE classes CASCADE CONSTRAINTS;
CREATE TABLE classes (
department CHAR(3),
course NUMBER(3),
description VARCHAR2(2000),
max_students NUMBER(3),
current_students NUMBER(3),
num_credits NUMBER(1),
room_id NUMBER(5),
CONSTRAINT classes_department_course
PRIMARY KEY (department, course),
CONSTRAINT classes_room_id
FOREIGN KEY (room_id) REFERENCES rooms (room_id)
);
INSERT INTO CLASSES(department, course, description, max_students,
current_students, num_credits, room_id)
VALUES ('HIS', 101, 'History 101', 30, 0, 4, 99999);
......(随意插入个数据)
INSERT INTO CLASSES(department, course, description, max_students,
current_students, num_credits, room_id)
VALUES ('NUT', 307, 'Nutrition 307', 20, 0, 4, 99991);
PROMPT registered_students table...
DROP TABLE registered_students CASCADE CONSTRAINTS;
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');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10002, 'CS', 102, 'B');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10003, 'CS', 102, 'C');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10000, 'HIS', 101, 'A');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10001, 'HIS', 101, 'B');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10002, 'HIS', 101, 'B');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10003, 'HIS', 101, 'A');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10004, 'HIS', 101, 'C');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10005, 'HIS', 101, 'C');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10006, 'HIS', 101, 'E');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10007, 'HIS', 101, 'B');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10008, 'HIS', 101, 'A');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10009, 'HIS', 101, 'D');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10010, 'HIS', 101, 'A');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10008, 'NUT', 307, 'A');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10010, 'NUT', 307, 'A');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10009, 'MUS', 410, 'B');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10006, 'MUS', 410, 'E');
PROMPT temp_table...
DROP TABLE temp_table;
CREATE TABLE temp_table (
num_col NUMBER,
char_col VARCHAR2(60)
);
根据以上表的结构，用java语言写一个程序能完成如下功能：
1）学生选课 2）学生退课 3）查询选修某门课的学生名单。
要求：1）用java语言＋oracle完成
2）须用java在前台调用后台存储过程，存储过程可参考附件2
附件2如下：
CREATE OR REPLACE PACKAGE ClassPackage AS
PROCEDURE AddStudent(p_StudentID IN students.id%TYPE,
p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE);
PROCEDURE RemoveStudent(p_StudentID IN students.id%TYPE,
p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE);
e_StudentNotRegistered EXCEPTION;
TYPE t_StudentIDTable IS TABLE OF students.id%TYPE
INDEX BY BINARY_INTEGER;
PROCEDURE ClassList(p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE,
p_IDs OUT t_StudentIDTable,
p_NumStudents IN OUT BINARY_INTEGER);
END ClassPackage;
/
CREATE OR REPLACE PACKAGE BODY ClassPackage AS
PROCEDURE UpdateStudentsAndClasses(p_Add in boolean,
p_StudentID in students.id%type,
p_Department in classes.department%type,
p_Course in classes.course%type) as
v_NumCredits classes.num_credits%type;
begin
select num_credits
into v_NumCredits
from classes
where department=p_Department
AND course=p_Course;
if p_Add then
update students
set current_credits=current_credits+v_NumCredits
where id=p_StudentID;
update classes
set current_students=current_students+1
where department=p_Department
AND course=p_Course;
else
update students
set current_credits=current_credits-v_NumCredits
where id=p_StudentID;
update classes
set current_students=current_students-1
where department=p_Department
AND course=p_Course;
end if;
end UpdateStudentsAndClasses;
PROCEDURE AddStudent(p_StudentID IN students.id%TYPE,
p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE) IS
BEGIN
INSERT INTO registered_students (student_id, department, course)
VALUES (p_StudentID, p_Department, p_Course);
COMMIT;
UpdateStudentsAndClasses(True,p_StudentID,p_Department,p_Course);
END AddStudent;
PROCEDURE RemoveStudent(p_StudentID IN students.id%TYPE,
p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE) IS
BEGIN
DELETE FROM registered_students
WHERE student_id = p_StudentID
AND department = p_Department
AND course = p_Course;
IF SQL%NOTFOUND THEN
RAISE e_StudentNotRegistered;
END IF;
UpdateStudentsAndClasses(false,p_StudentID,p_Department,p_Course);
COMMIT;
END RemoveStudent;
PROCEDURE ClassList(p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE,
p_IDs OUT t_StudentIDTable,
p_NumStudents IN OUT BINARY_INTEGER) IS
v_StudentID registered_students.student_id%TYPE;
CURSOR c_RegisteredStudents IS
SELECT student_id
FROM registered_students
WHERE department = p_Department
AND course = p_Course;
BEGIN
p_NumStudents := 0;
OPEN c_RegisteredStudents;
LOOP
FETCH c_RegisteredStudents INTO v_StudentID;
EXIT WHEN c_RegisteredStudents%NOTFOUND;
p_NumStudents := p_NumStudents + 1;
p_IDs(p_NumStudents) := v_StudentID;
END LOOP;
END ClassList;
END ClassPackage;
/
DECLARE
v_HistoryStudents ClassPackage.t_StudentIDTable;
v_NumStudents BINARY_INTEGER := 20;
BEGIN
ClassPackage.ClassList('HIS', 101, v_HistoryStudents,
v_NumStudents);
FOR v_LoopCounter IN 1..v_NumStudents LOOP
INSERT INTO temp_table (num_col, char_col)
VALUES (v_HistoryStudents(v_LoopCounter),
'In History 101');
END LOOP;
END;
/
这个题应该怎么做啊，请问高手能否给个详细的答案，万分感谢！
DROP TABLE students CASCADE CONSTRAINTS;
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);
......
INSERT INTO STUDENTS(id, first_name, last_name, major, current_credits)
VALUES (10010, 'Rita', 'Razmataz', 'Nutrition', 0);
PROMPT major_stats...
DROP TABLE major_stats;
CREATE TABLE major_stats (
major VARCHAR2(30),
total_credits NUMBER,
total_students NUMBER);
PROMPT rooms table...
DROP TABLE rooms CASCADE CONSTRAINTS;
CREATE TABLE rooms (
room_id NUMBER(5) PRIMARY KEY,
building VARCHAR2(15),
room_number NUMBER(4),
number_seats NUMBER(4),
description VARCHAR2(50)
);
INSERT INTO ROOMS (room_id, building, room_number, number_seats, description)
VALUES (99999, 'Building 7', 310, 1000, 'Large Lecture Hall');
......
INSERT INTO ROOMS (room_id, building, room_number, number_seats, description)
VALUES (99991, 'Building 7', 310, 50, 'Discussion Room E');
PROMPT classes table...
DROP TABLE classes CASCADE CONSTRAINTS;
CREATE TABLE classes (
department CHAR(3),
course NUMBER(3),
description VARCHAR2(2000),
max_students NUMBER(3),
current_students NUMBER(3),
num_credits NUMBER(1),
room_id NUMBER(5),
CONSTRAINT classes_department_course
PRIMARY KEY (department, course),
CONSTRAINT classes_room_id
FOREIGN KEY (room_id) REFERENCES rooms (room_id)
);
INSERT INTO CLASSES(department, course, description, max_students,
current_students, num_credits, room_id)
VALUES ('HIS', 101, 'History 101', 30, 0, 4, 99999);
......(随意插入个数据)
INSERT INTO CLASSES(department, course, description, max_students,
current_students, num_credits, room_id)
VALUES ('NUT', 307, 'Nutrition 307', 20, 0, 4, 99991);
PROMPT registered_students table...
DROP TABLE registered_students CASCADE CONSTRAINTS;
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');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10002, 'CS', 102, 'B');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10003, 'CS', 102, 'C');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10000, 'HIS', 101, 'A');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10001, 'HIS', 101, 'B');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10002, 'HIS', 101, 'B');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10003, 'HIS', 101, 'A');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10004, 'HIS', 101, 'C');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10005, 'HIS', 101, 'C');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10006, 'HIS', 101, 'E');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10007, 'HIS', 101, 'B');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10008, 'HIS', 101, 'A');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10009, 'HIS', 101, 'D');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10010, 'HIS', 101, 'A');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10008, 'NUT', 307, 'A');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10010, 'NUT', 307, 'A');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10009, 'MUS', 410, 'B');
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10006, 'MUS', 410, 'E');
PROMPT temp_table...
DROP TABLE temp_table;
CREATE TABLE temp_table (
num_col NUMBER,
char_col VARCHAR2(60)
);
根据以上表的结构，用java语言写一个程序能完成如下功能：
1）学生选课 2）学生退课 3）查询选修某门课的学生名单。
要求：1）用java语言＋oracle完成
2）须用java在前台调用后台存储过程，存储过程可参考附件2
附件2如下：
CREATE OR REPLACE PACKAGE ClassPackage AS
PROCEDURE AddStudent(p_StudentID IN students.id%TYPE,
p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE);
PROCEDURE RemoveStudent(p_StudentID IN students.id%TYPE,
p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE);
e_StudentNotRegistered EXCEPTION;
TYPE t_StudentIDTable IS TABLE OF students.id%TYPE
INDEX BY BINARY_INTEGER;
PROCEDURE ClassList(p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE,
p_IDs OUT t_StudentIDTable,
p_NumStudents IN OUT BINARY_INTEGER);
END ClassPackage;
/
CREATE OR REPLACE PACKAGE BODY ClassPackage AS
PROCEDURE UpdateStudentsAndClasses(p_Add in boolean,
p_StudentID in students.id%type,
p_Department in classes.department%type,
p_Course in classes.course%type) as
v_NumCredits classes.num_credits%type;
begin
select num_credits
into v_NumCredits
from classes
where department=p_Department
AND course=p_Course;
if p_Add then
update students
set current_credits=current_credits+v_NumCredits
where id=p_StudentID;
update classes
set current_students=current_students+1
where department=p_Department
AND course=p_Course;
else
update students
set current_credits=current_credits-v_NumCredits
where id=p_StudentID;
update classes
set current_students=current_students-1
where department=p_Department
AND course=p_Course;
end if;
end UpdateStudentsAndClasses;
PROCEDURE AddStudent(p_StudentID IN students.id%TYPE,
p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE) IS
BEGIN
INSERT INTO registered_students (student_id, department, course)
VALUES (p_StudentID, p_Department, p_Course);
COMMIT;
UpdateStudentsAndClasses(True,p_StudentID,p_Department,p_Course);
END AddStudent;
PROCEDURE RemoveStudent(p_StudentID IN students.id%TYPE,
p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE) IS
BEGIN
DELETE FROM registered_students
WHERE student_id = p_StudentID
AND department = p_Department
AND course = p_Course;
IF SQL%NOTFOUND THEN
RAISE e_StudentNotRegistered;
END IF;
UpdateStudentsAndClasses(false,p_StudentID,p_Department,p_Course);
COMMIT;
END RemoveStudent;
PROCEDURE ClassList(p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE,
p_IDs OUT t_StudentIDTable,
p_NumStudents IN OUT BINARY_INTEGER) IS
v_StudentID registered_students.student_id%TYPE;
CURSOR c_RegisteredStudents IS
SELECT student_id
FROM registered_students
WHERE department = p_Department
AND course = p_Course;
BEGIN
p_NumStudents := 0;
OPEN c_RegisteredStudents;
LOOP
FETCH c_RegisteredStudents INTO v_StudentID;
EXIT WHEN c_RegisteredStudents%NOTFOUND;
p_NumStudents := p_NumStudents + 1;
p_IDs(p_NumStudents) := v_StudentID;
END LOOP;
END ClassList;
END ClassPackage;
/
DECLARE
v_HistoryStudents ClassPackage.t_StudentIDTable;
v_NumStudents BINARY_INTEGER := 20;
BEGIN
ClassPackage.ClassList('HIS', 101, v_HistoryStudents,
v_NumStudents);
FOR v_LoopCounter IN 1..v_NumStudents LOOP
INSERT INTO temp_table (num_col, char_col)
VALUES (v_HistoryStudents(v_LoopCounter),
'In History 101');
END LOOP;
END;
/
这个题应该怎么做啊，请问高手能否给个详细的答案，万分感谢！