CREATE OR REPLACE PROCEDURE AddNewStudent ( p_FirstName students.first_name%TYPE, p_LastName students.last_name%TYPE, p_Major students.major%TYPE) AS BEGIN -- Insert a new row in the students table. Use -- student_sequence to generate the new student ID, and -- 0 for current_credits. INSERT INTO students (ID, first_name, last_name, major, current_credits) VALUES (student_sequence.nextval, p_FirstName, p_LastName, p_Major, 0); COMMIT; END AddNewStudent; /
CREATE OR REPLACE FUNCTION AlmostFull ( p_Department classes.department%TYPE, p_Course classes.course%TYPE) RETURN BOOLEAN IS v_CurrentStudents NUMBER; v_MaxStudents NUMBER; v_ReturnValue BOOLEAN; v_FullPercent CONSTANT NUMBER := 90; BEGIN -- Get the current and maximum students for the requested -- course. SELECT current_students, max_students INTO v_CurrentStudents, v_MaxStudents FROM classes WHERE department = p_Department AND course = p_Course; -- If the class is more full than the percentage given by -- v_FullPercent, return TRUE. Otherwise, return FALSE. IF (v_CurrentStudents / v_MaxStudents * 100) > v_FullPercent THEN v_ReturnValue := TRUE; ELSE v_ReturnValue := FALSE; END IF; RETURN v_ReturnValue; END AlmostFull; /
CREATE OR REPLACE PROCEDURE RecordFullClasses AS CURSOR c_Classes IS SELECT department, course FROM classes; BEGIN FOR v_ClassRecord IN c_Classes LOOP -- Record all classes which don't have very much room left -- in temp_table. IF AlmostFull(v_ClassRecord.department, v_ClassRecord.course) THEN INSERT INTO temp_table (char_col) VALUES (v_ClassRecord.department || ' ' || v_ClassRecord.course || ' is almost full!'); END IF; END LOOP; END RecordFullClasses; /
p_FirstName students.first_name%TYPE,
p_LastName students.last_name%TYPE,
p_Major students.major%TYPE) AS
BEGIN
-- Insert a new row in the students table. Use
-- student_sequence to generate the new student ID, and
-- 0 for current_credits.
INSERT INTO students (ID, first_name, last_name,
major, current_credits)
VALUES (student_sequence.nextval, p_FirstName, p_LastName,
p_Major, 0); COMMIT;
END AddNewStudent;
/
p_Department classes.department%TYPE,
p_Course classes.course%TYPE)
RETURN BOOLEAN IS v_CurrentStudents NUMBER;
v_MaxStudents NUMBER;
v_ReturnValue BOOLEAN;
v_FullPercent CONSTANT NUMBER := 90;
BEGIN
-- Get the current and maximum students for the requested
-- course.
SELECT current_students, max_students
INTO v_CurrentStudents, v_MaxStudents
FROM classes
WHERE department = p_Department
AND course = p_Course; -- If the class is more full than the percentage given by
-- v_FullPercent, return TRUE. Otherwise, return FALSE.
IF (v_CurrentStudents / v_MaxStudents * 100) > v_FullPercent THEN
v_ReturnValue := TRUE;
ELSE
v_ReturnValue := FALSE;
END IF; RETURN v_ReturnValue;
END AlmostFull;
/
CURSOR c_Classes IS
SELECT department, course
FROM classes;
BEGIN
FOR v_ClassRecord IN c_Classes LOOP
-- Record all classes which don't have very much room left
-- in temp_table.
IF AlmostFull(v_ClassRecord.department, v_ClassRecord.course) THEN
INSERT INTO temp_table (char_col) VALUES
(v_ClassRecord.department || ' ' || v_ClassRecord.course ||
' is almost full!');
END IF;
END LOOP;
END RecordFullClasses;
/