Plsql Project Enhancement Semester 2 Teacher 106xv
This document was ed by and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this report form. Report 3i3n4
Overview 26281t
& View Plsql Project Enhancement Semester 2 Teacher as PDF for free.
More details 6y5l6z
- Words: 5,563
- Pages: 32
MidTerm Project Part II Instructions for Instructors The midterm project is divided into 5 parts. You can use the 5 parts in various ways depending on how much time you wish to devote to this project or on the technical level of your students.
All 5 parts can be assigned to individual students. There are dependencies between the parts, so 1 must be completed before 2 and 3; 4 must be completed before 5. The class can be divided into teams and the teams can complete all 5 parts. There are dependencies between the parts, so 1 must be completed before 2 and 3; 4 must be completed before 5.
The code for two packages enrollments_package and _tools_package must be created from the anonymous blocks your students wrote in the first MidTerm Project. If these files are lost, there were suggested solutions in the teacher document for Part I; you can use these solutions to re-create the programs. You are instructed to modify the existing programs from Part I, to incorporate the required topics for this project. The students are also required to create a new package manage_triggers_package to disable/enable triggers for a table or to compile a specific trigger.
Project setup: The Data This project will build on the case study called STUDENT ISTRATION or SA. A set of database tables is used to manage schools‟ course offerings as delivered by instructors in many classes over time. Information is stored about classes that are offered, the students who take classes, and the grades the students receive on various assessments. The school s can use the SA database to manage the class offerings and to assign instructors. Teachers can also use the SA database to track student performance. The database objects for this project are already in the students‟ s and they are as follows: Tables: INSTRUCTORS SECTIONS COURSES CLASSES ASSESSMENTS STUDENTS ENROLLMENTS CLASS_ASSESSMENTS ERROR_LOG GRADE_CHANGES
Oracle Academy
1 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
Sequence: ASSESSMENT_ID_SEQ
Part 1: Procedures, Functions and Packages In this section the students start by re-writing the anonymous blocks from MidTerm I to become procedures and functions. 1. Find the file saved from called enroll_student_in_class.sql from MidTerm I. Convert this to a procedure and have it accept a STU_ID and CLASS_ID as input parameters. Use “today‟s date” for the ENROLLMENT_DATE and the string „Enrolled‟ for the STATUS. Raise an exception if the accepted student is already enrolled in the accepted class. In your exception handler, display a message stating the student is already enrolled in the class. Tables used: ENROLLMENTS Topics Incorporated ==> Scalar variable; %TYPE, Procedure with IN parameters; INSERT; COMMIT; Exception handling if provided stu_id is already enrolled in provided Class_id. This can be a -defined exception which is raised after doing a SELECT which is then followed by an IF statement. To test, run twice, once to enroll the student and again to test the exception: BEGIN enroll_student_in_class(103, 3); END; Suggested Solution: --This procedure will enroll a student in a class. CREATE OR REPLACE PROCEDURE enroll_student_in_class (p_stu_id IN enrollments.stu_id%TYPE, p_class_id IN enrollments.class_id%TYPE) IS v_times_enrolled PLS_INTEGER; e_already_enrolled EXCEPTION; BEGIN SELECT COUNT(*) INTO v_times_enrolled FROM enrollments WHERE class_id = p_class_id AND stu_id = p_stu_id; IF v_times_enrolled <> 0 THEN RAISE e_already_enrolled; END IF; INSERT INTO enrollments (enrollment_date, class_id, stu_id, status) VALUES (SYSDATE, p_class_id, p_stu_id, 'Enrolled'); Oracle Academy
2 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
COMMIT; EXCEPTION WHEN e_already_enrolled THEN DBMS_OUTPUT.PUT_LINE('Student ' || p_stu_id || ' is already enrolled in class '|| p_class_id); END enroll_student_in_class; 2. Find the file called drop_student_from_class.sql from MidTerm I. Convert it to a procedure that accepts a STU_ID and CLASS_ID as input parameters. If the DELETE fails because the student is not in the class, raise a _defined exception to display a message stating the student is not in the class. Tables used: ENROLLMENTS Topics Incorporated ==> %TYPE; Procedure with IN parameters, DELETE; SQL%ROWCOUNT; COMMIT; -defined exception. To test, run twice, once to drop the student and again to test the exception: BEGIN drop_student_from_class(103, 3); END; Suggested Solution: -- This procedure will drop a student from a class. CREATE OR REPLACE PROCEDURE drop_student_from_class (p_stu_id IN enrollments.stu_id%TYPE, p_class_id IN enrollments.class_id%TYPE) IS e_not_enrolled EXCEPTION; BEGIN DELETE FROM enrollments WHERE class_id = p_class_id AND stu_id = p_stu_id; IF SQL%ROWCOUNT = 0 THEN RAISE e_not_enrolled; END IF; COMMIT; EXCEPTION WHEN e_not_enrolled THEN DBMS_OUTPUT.PUT_LINE('Student ' || p_stu_id || ' is not enrolled in class '|| p_class_id); END drop_student_from_class;
Oracle Academy
3 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
3. Find the file called student_class_list.sql from MidTerm I. Rewrite it to be a procedure that displays all of the classes a student has been enrolled in within the most recent 10 years. For example: If you run your procedure on May 10, 2010, you should display all enrollments between May 10, 2000 and May 10, 2010. Accept the STU_ID as an input parameter. For each enrollment, display the ENROLLMENT_DATE, CLASS_ID and STATUS. Tables used: ENROLLMENTS Topics Incorporated ==> %TYPE; Procedure with IN parameters; SYSDATE, ADD_MONTHS, Explicit cursor to find all courses for the provided Student ID;DBMS_OUTPUT to display the list of classes. To test: BEGIN student_class_list(101); END; Suggested Solution: -- This procedure will list all students in a class. CREATE OR REPLACE PROCEDURE student_class_list (p_stu_id IN enrollments.stu_id%TYPE) IS CURSOR stu_class_cur IS SELECT enrollment_date, class_id, status FROM enrollments WHERE stu_id = p_stu_id AND enrollment_date between ADD_MONTHS (SYSDATE,-120) and SYSDATE; BEGIN DBMS_OUTPUT.PUT_LINE('Student ' || p_stu_id || ' is enrolled in the following classes:'); FOR stu_class_rec IN stu_class_cur LOOP DBMS_OUTPUT.PUT_LINE('Class: ' ||stu_class_rec.class_id || ' Enrolled on: ' || stu_class_rec.enrollment_date || ' and has a status of: '|| stu_class_rec.status ); END LOOP; END student_class_list;
Oracle Academy
4 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
4. Find the file called add_new_classes.sql from MidTerm I. Rewrite it as a Procedure and have it accept the following IN parameters: a. number of new classes required. Set a default value of 1. b. Course id; For each new class, use “today”as the START_DATE. c. Period, to specify what days the class meets. d. Frequency, to specify how often it meets. e. Instructor id, to specify who is teaching the class(s). Note for the teacher: It is probably more likely and safer to use a sequence number for the class_id. If 2 sessions are calling this procedure at the same time, there is a small chance the same MAX(class_id) could be found. This would cause the second set of INSERTS to fail. The first option will work for this project, but you might discuss this interesting subtlety with your students. Tables used: CLASSES Topics Incorporated ==> Creating a procedure; SELECT; INSERT; COMMIT; DEFAULT value; Using a LOOP to only insert “n” new rows. To test: BEGIN add_new_classes (2, 1001, 'TUE_TR', 'Once', 3003) ; END; Suggested Solution 1: CREATE OR REPLACE PROCEDURE add_new_classes (p_number_new_classes IN PLS_INTEGER DEFAULT 1, p_course_id IN classes.course_id%TYPE, p_period IN classes.period%TYPE, p_frequency IN classes.frequency%TYPE, p_instr_id IN classes.instr_id%TYPE ) IS v_current_max_class_id classes.class_id%TYPE; BEGIN SELECT MAX(class_id) INTO v_current_max_class_id FROM classes; FOR loop_counter IN 1..p_number_new_classes LOOP INSERT INTO classes (class_id, start_date,course_id, period, frequency,instr_id) VALUES (v_current_max_class_id + loop_counter, SYSDATE, p_course_id, p_period, p_frequency, p_instr_id); COMMIT; END LOOP; END add_new_classes;
Oracle Academy
5 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
Suggested Solution 2: SELECT MAX(class_id) FROM classes; DROP SEQUENCE class_id_seq; CREATE SEQUENCE class_id_seq START WITH (number_returned_from_SELECT + 1) INCREMENT BY 1 NOCACHE; CREATE OR REPLACE PROCEDURE add_new_classes (p_number_new_classes IN PLS_INTEGER DEFAULT 1, p_course_id IN classes.course_id%TYPE, p_period IN classes.period%TYPE, p_frequency IN classes.frequency%TYPE, p_instr_id IN classes.instr_id%TYPE ) IS BEGIN FOR loop_counter IN 1..p_number_new_classes LOOP INSERT INTO classes (class_id, start_date,course_id, period, frequency,instr_id) VALUES (class_id_seq.NEXTVAL,SYSDATE, p_course_id, p_period,p_frequency,p_instr_id); COMMIT; END LOOP; END add_new_classes;
5. Find the file called course_roster.sql from MidTerm I and rewrite it as a procedure. Accept the INSTR_ID and COURSE_ID as input parameters. For each ENROLLMENT, display: CLASS_ID, STATUS, Student FIRST_NAME and LAST_NAME. Tables used: ENROLLMENTS; CLASSES; STUDENTS Topics Incorporated ==> SELECT with ; Explicit Cursor To test: BEGIN course_roster(3003, 1002); END;
Oracle Academy
6 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
Suggested Solution: CREATE OR REPLACE PROCEDURE course_roster (p_instr_id IN classes.instr_id%TYPE, p_course_id IN classes.course_id%TYPE) IS CURSOR stu_course_cur IS SELECT e.class_id, e.status, s.first_name, s.last_name FROM enrollments e, classes c, students s WHERE e.class_id = c.class_id AND e.stu_id = s.stu_id AND c.course_id = p_course_id AND c.instr_id = p_instr_id; BEGIN DBMS_OUTPUT.PUT_LINE('Course ' || p_course_id || ' has the following students:'); FOR stu_course_rec IN stu_course_cur LOOP DBMS_OUTPUT.PUT_LINE('Class: ' || stu_course_rec.class_id || ' Student: '|| stu_course_rec.first_name || ' ' || stu_course_rec.last_name || ' with a status of: ' || stu_course_rec.status ); END LOOP; END course_roster;
6. Find the file called convert_grade.sql from MidTerm I and rewrite it to be a function Use an IN parameter to enter the number grade. RETURN a CHAR value. Use the following rules: A:90 or above, B: >=80 and<90 , C: >=70 and < 80, D: >=60 and < 70, F:<60. Tables used: None Topics Incorporated ==> Scalar variables...NUMBER IN; CHAR RETURNED; IF or CASE Statement (A=90 to 100, B=80-90, C=70-80, D=60-70, F=<60; To Test: BEGIN DBMS_OUTPUT.PUT_LINE(convert_grade (92)); END; -- Should return 'A'.
Oracle Academy
7 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
Suggested Solution 1: CREATE OR REPLACE FUNCTION convert_grade (p_numeric_grade IN NUMBER) RETURN varchar2 IS v_letter_grade CHAR(1); BEGIN IF p_numeric_grade >= 90.0 THEN v_letter_grade := 'A'; ELSIF p_numeric_grade >= 80.0 THEN v_letter_grade := 'B'; ELSIF p_numeric_grade >= 70.0 THEN v_letter_grade := 'C'; ELSIF p_numeric_grade >= 60.0 THEN v_letter_grade := 'D'; ELSE v_letter_grade := 'F'; END IF; RETURN v_letter_grade; END convert_grade; Suggested Solution 2: CREATE OR REPLACE FUNCTION convert_grade (p_numeric_grade IN NUMBER) RETURN varchar2 IS v_letter_grade CHAR(1); BEGIN v_letter_grade := CASE WHEN p_numeric_grade >= 90.0 THEN 'A' WHEN p_numeric_grade >= 80.0 THEN 'B' WHEN p_numeric_grade >= 70.0 THEN 'C' WHEN p_numeric_grade >= 60.0 THEN 'D' ELSE 'F' END; RETURN v_letter_grade; END convert_grade;
Oracle Academy
8 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
7. Find the file called student_count.sql and rewrite it as a function that will RETURN the number of students in a particular class. Accept a CLASS_ID as an IN parameter. Tables used: ENROLLMENTS Topics Incorporated ==> Scalar variable...INTEGER; SELECT COUNT(*) INTO variable FROM enrollments where class_id = ...; using a -defined function in SQL. Testing the function: SELECT class_id, student_count(class_id) AS "Student Count", stu_id FROM enrollments; Suggested Solution: CREATE OR REPLACE FUNCTION student_count (p_class_id IN classes.class_id%TYPE) RETURN NUMBER IS v_student_count PLS_INTEGER; BEGIN SELECT COUNT(*) INTO v_student_count FROM enrollments WHERE class_id = p_class_id; RETURN v_student_count; END student_count; 8. Create a package called enrollments_package which will contain the procedures you created in A, B, and C. Make all procedures public. Comment your procedures to explain their purpose and functionality. Tables used: ENROLLMENTS Topics Incorporated ==> Creating a package; incorporating comments. Suggested Solution: --PACKAGE SPEC CREATE OR REPLACE PACKAGE enrollments_package IS -- This procedure will enroll a student in a class. PROCEDURE enroll_student_in_class (p_stu_id IN enrollments.stu_id%TYPE, p_class_id IN enrollments.class_id%TYPE ) ; -- This procedure will drop a student from a class Oracle Academy
9 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
PROCEDURE drop_student_from_class (p_stu_id IN enrollments.stu_id%TYPE, p_class_id IN enrollments.class_id%TYPE); -- This procedure will list all students in a class PROCEDURE student_class_list (p_stu_id IN enrollments.stu_id%TYPE); END enrollments_package; --PACKAGE BODY CREATE OR REPLACE PACKAGE BODY enrollments_package IS -- This procedure will enroll a student in a class. PROCEDURE enroll_student_in_class (p_stu_id IN enrollments.stu_id%TYPE, p_class_id IN enrollments.class_id%TYPE) IS v_times_enrolled PLS_INTEGER; e_already_enrolled EXCEPTION; BEGIN SELECT COUNT(*) INTO v_times_enrolled FROM enrollments WHERE class_id = p_class_id AND stu_id = p_stu_id; IF v_times_enrolled <> 0 THEN RAISE e_already_enrolled; END IF; INSERT INTO enrollments (enrollment_date, class_id, stu_id, status) VALUES (SYSDATE, p_class_id, p_stu_id, 'Enrolled'); COMMIT; EXCEPTION WHEN e_already_enrolled THEN DBMS_OUTPUT.PUT_LINE('Student ' || p_stu_id || ' is already enrolled in class '|| p_class_id); END enroll_student_in_class; -- This procedure will drop a student from a class PROCEDURE drop_student_from_class (p_stu_id IN enrollments.stu_id%TYPE, p_class_id IN enrollments.class_id%TYPE) IS e_not_enrolled EXCEPTION; BEGIN DELETE FROM enrollments Oracle Academy
10 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
WHERE class_id = p_class_id AND stu_id = p_stu_id; IF SQL%ROWCOUNT = 0 THEN RAISE e_not_enrolled; END IF; COMMIT; EXCEPTION WHEN e_not_enrolled THEN DBMS_OUTPUT.PUT_LINE('Student ' || p_stu_id || ' is not enrolled in class '|| p_class_id); END drop_student_from_class; -- This procedure will list all students in a class PROCEDURE student_class_list (p_stu_id IN enrollments.stu_id%TYPE) IS CURSOR stu_class_cur IS SELECT enrollment_date, class_id, status FROM enrollments WHERE stu_id = p_stu_id AND enrollment_date between ADD_MONTHS (SYSDATE,-120) and SYSDATE; BEGIN DBMS_OUTPUT.PUT_LINE('Student ' || p_stu_id || ' is enrolled in the following classes:'); FOR stu_class_rec IN stu_class_cur LOOP DBMS_OUTPUT.PUT_LINE('Class: ' ||stu_class_rec.class_id || ' Enrolled on: ' || stu_class_rec.enrollment_date || ' and has a status of: '|| stu_class_rec.status ); END LOOP; END student_class_list; END enrollments_package; 9. Find the program saved in the file create_assignment.sql. Rewrite it as a procedure that accepts the assignment description as an input parameter. Tables used: ASSESSMENTS Topics Incorporated ==> SQL INSERT; IN Argument, Use of sequence in INSERT; To test: BEGIN create_assignment('this is to test the procedure'); END;
Oracle Academy
11 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
Suggested Solution: CREATE OR REPLACE PROCEDURE create_assignment (p_assign_descrip IN assessments.description%TYPE) IS BEGIN INSERT INTO assessments (assessment_id, description) VALUES (assessment_id_seq.NEXTVAL, p_assign_descrip); COMMIT; END create_assignment; 10. Find the file called enter_student_grade.sql and rewrite it as a procedure that a teacher can run to insert the student's grade on a particular assignment. Accept a NUMERIC_GRADE, CLASS_ASSESSMENT_ID, CLASS_ID, STU_ID and ASSESSMENT_ID as IN parameters. Use “today‟s” date for the DATE_TURNED_IN.
Tables used: CLASS_ASSESSMENTS Topics Incorporated ==> SQL INSERT; Many IN arguments; SYSDATE. To test: BEGIN Enter_student_grade(102,87,1,101,1); END; Suggested Solution: CREATE OR REPLACE PROCEDURE enter_student_grade (p_cl_assessment_id IN class_assessments.class_assessment_id%TYPE, p_numeric_grade IN class_assessments.numeric_grade%TYPE, p_class_id IN class_assessments.class_id%TYPE, p_stu_id IN class_assessments.stu_id%TYPE, p_assessment_id IN class_assessments.assessment_id%TYPE) IS BEGIN INSERT INTO class_assessments (class_assessment_id, date_turned_in, numeric_grade, class_id, stu_id, assessment_id) VALUES (p_cl_assessment_id, SYSDATE, p_numeric_grade,p_class_id, p_stu_id, p_assessment_id); COMMIT; END enter_student_grade;
Oracle Academy
12 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
11. Rewrite the program stored in the file show_missing_grades.sql to be a procedure. Accept a start_date and end_date to establish a date range. Display only enrollments between those two dates. Write your procedure so the start_date and end_date are optional. If both dates are not entered, display all applicable enrollments for the past year, and include a note about the date range. For each enrollment, list the CLASS_ID, STU_ID, and STATUS. Order the output by ENROLLMENT_DATE with the most recent enrollments first. Tables used: ENROLLMENTS Topics Incorporated ==> SQL with BETWEEN; Dates; Default values with IN parameters, NULL values in a database column, MONTHS_BETWEEN or other way to find the “past year.” To test: BEGIN show_missing_grades('01-AUG-01', '01-AUG-05'); END; Suggested Solution: CREATE OR REPLACE PROCEDURE show_missing_grades (p_start_date IN DATE DEFAULT NULL, p_end_date IN DATE DEFAULT NULL) IS CURSOR no_grades_cur IS SELECT class_id, stu_id, status FROM enrollments WHERE final_numeric_grade IS NULL AND final_letter_grade IS NULL AND enrollment_date BETWEEN p_start_date AND p_end_date ORDER BY enrollment_date DESC; v_start_date DATE; v_end_date DATE; BEGIN IF p_start_date IS NULL OR p_end_date IS NULL THEN v_start_date := ADD_MONTHS(SYSDATE,-12); v_end_date := SYSDATE; DBMS_OUTPUT.PUT_LINE ('You have not specified both dates. The listing will show all enrollments for the past year.'); ELSE v_start_date := p_start_date; v_end_date := P_end_date; END IF; DBMS_OUTPUT.PUT_LINE Oracle Academy
13 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
('Date range: Between ' || v_start_date || ' and ' || v_end_date || '.'); DBMS_OUTPUT.PUT_LINE ('The following enrollments have no grade.'); FOR no_grades_rec IN no_grades_cur LOOP DBMS_OUTPUT.PUT_LINE ('Class ID ' || no_grades_rec.class_id || ' – Student ID ' || no_grades_rec.stu_id || ' with a status of: ' ||no_grades_rec.status); END LOOP; END show_missing_grades;
12. Find the file called compute_average_grade.sql and rewrite it as a function. Accept a CLASS_ID. Return the average grade. Tables used: ENROLLMENTS Topics Incorporated ==> SQL with AVG function To test: (there will be no output since there are no final grades in the table) DECLARE v_grade NUMBER; BEGIN v_grade := compute_average_grade(6); DBMS_OUTPUT.PUT_LINE(v_grade); END; Suggested Solution: CREATE OR REPLACE FUNCTION compute_average_grade (p_class_id IN enrollments.class_id%TYPE) RETURN NUMBER IS v_avg_grade enrollments.final_numeric_grade%TYPE; BEGIN SELECT AVG(final_numeric_grade) INTO v_avg_grade FROM enrollments WHERE class_id = p_class_id; RETURN v_avg_grade; END compute_average_grade;
Oracle Academy
14 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
13. Find the file called count_classes_per_course.sql and rewrite it as a function. Accept a COURSE_ID. Return the number of classes offered for that course. Tables used: CLASSES Topics Incorporated ==> SQL SELECT with COUNT.
To test: DECLARE v_classes PLS_INTEGER; BEGIN v_classes := count_classes_per_course(1001); DBMS_OUTPUT.PUT_LINE(v_classes); END; Suggested Solution: CREATE OR REPLACE FUNCTION count_classes_per_course (p_course_id IN classes.course_id%TYPE) RETURN NUMBER IS num_classes PLS_INTEGER; BEGIN SELECT COUNT(*) INTO v_num_classes FROM classes WHERE course_id = p_course_id; RETURN v_num_classes; END count_classes_per_course; 14. Convert the file show_class_offerings.sql to a procedure. Accept a start date and end date. For each class found, display the CLASS_ID, START_DATE, instructor FIRST_NAME and LAST_NAME, course TITLE and SECTION_CODE, and average grade. Find the average grade by a call to the function compute_average_grade. Tables used: CLASSES, INSTRUCTORS, COURSES,ENROLLMENTS Topics Incorporated ==> Calling a function from a procedure; using dates; SQL ; %TYPE. To test: BEGIN show_class_offerings('01-AUG-01','01-AUG-06'); END;
Oracle Academy
15 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
Suggested Solution: CREATE OR REPLACE PROCEDURE show_class_offerings (p_start_date IN DATE, p_end_date IN DATE) IS v_avg_grade NUMBER; CURSOR classes_info_cur IS SELECT cl.class_id, cl.start_date, i.first_name, i.last_name, cour.title, cour.section_code FROM classes cl, courses cour, instructors i WHERE start_date BETWEEN p_start_date AND p_end_date AND cl.course_id = cour.course_id AND cl.instr_id = i.instructor_id ORDER BY 1,2,4; BEGIN DBMS_OUTPUT.PUT_LINE ('Date range: Between ' || p_start_date || ' and ' || p_end_date || '.'); DBMS_OUTPUT.PUT_LINE ('Classes Information.'); FOR classes_info_rec IN classes_info_cur LOOP -- call the compute_average_grade function v_avg_grade := compute_average_grade (classes_info_rec.class_id); DBMS_OUTPUT.PUT_LINE ( 'Class ID ' || classes_info_rec.class_id || ' – Average Grade '|| v_avg_grade || ' - Start Date ' || classes_info_rec.start_date || ' – Instructor ' || classes_info_rec.first_name || ' ' || classes_info_rec.last_name || ' – Course Title '|| classes_info_rec.title || ' – Offering Section ' || classes_info_rec.section_code ); END LOOP; END show_class_offerings; 15. Create a package called _tools_package incorporating procedure and functions you wrote in steps K to N. Make the following public: show_missing_grades, show_class_offerings, count_classes_per_course. Make the following private: compute_average_grade. Tables used: CLASSES, INSTRUCTORS, COURSES Topics Incorporated: Creating a package; Public vs. Private package constructs.
Oracle Academy
16 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
--PACKAGE SPEC CREATE OR REPLACE PACKAGE _tools_package IS -- This procedure will show missing grades for a class. PROCEDURE show_missing_grades (p_start_date IN DATE DEFAULT ADD_MONTHS(SYSDATE,-12), p_end_date IN DATE DEFAULT SYSDATE); -- This procedure will list classes offered PROCEDURE show_class_offerings (p_start_date IN DATE, p_end_date IN DATE); -- This function will count the number of classes per course. FUNCTION count_classes_per_course (p_course_id IN classes.course_id%TYPE) RETURN NUMBER; END _tools_package; --PACKAGE BODY CREATE OR REPLACE PACKAGE BODY _tools_package IS --This function is private in this package and it will compute --the average grade for a class. FUNCTION compute_average_grade (p_class_id IN enrollments.class_id%TYPE) RETURN NUMBER IS v_avg_grade enrollments.final_numeric_grade%TYPE; BEGIN SELECT AVG(final_numeric_grade) INTO v_avg_grade FROM enrollments WHERE class_id = p_class_id; RETURN v_avg_grade; END compute_average_grade; --This procedure will show missing grades for a class. -------------------------------------------------------PROCEDURE show_missing_grades (p_start_date IN DATE DEFAULT ADD_MONTHS(SYSDATE,-12), Oracle Academy
17 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
p_end_date IN DATE DEFAULT SYSDATE) IS CURSOR no_grades_cur IS SELECT class_id, stu_id, status FROM enrollments WHERE final_numeric_grade IS NULL AND final_letter_grade IS NULL AND enrollment_date BETWEEN p_start_date AND p_end_date ORDER BY enrollment_date DESC; BEGIN IF p_start_date IS NULL OR p_end_date IS NULL THEN DBMS_OUTPUT.PUT_LINE ('You have not specified both dates. The listing will show all enrollments for the past year.'); END IF; DBMS_OUTPUT.PUT_LINE ('Date range: Between ' || p_start_date || ' and ' || p_end_date || '.'); DBMS_OUTPUT.PUT_LINE ('The following enrollments have no grade.'); FOR no_grades_rec IN no_grades_cur LOOP DBMS_OUTPUT.PUT_LINE ('Class ID ' || no_grades_rec.class_id || ' – Student ID ' || no_grades_rec.stu_id || ' with a status of: ' ||no_grades_rec.status); END LOOP; END show_missing_grades; --This procedure will list classes offered -------------------------------PROCEDURE show_class_offerings (p_start_date IN DATE, p_end_date IN DATE) IS v_avg_grade NUMBER; CURSOR classes_info_cur IS SELECT cl.class_id, cl.start_date, i.first_name, i.last_name, cour.title, cour.section_code FROM classes cl, courses cour, instructors i WHERE start_date BETWEEN p_start_date and p_end_date AND cl.course_id = cour.course_id AND cl.instr_id = i.instructor_id ORDER BY 1,2,4; -BEGIN Oracle Academy
18 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
DBMS_OUTPUT.PUT_LINE ('Date range: Between ' || p_start_date || ' and ' || p_end_date || '.'); DBMS_OUTPUT.PUT_LINE ('Classes Information.'); FOR classes_info_rec IN classes_info_cur LOOP v_avg_grade := compute_average_grade (classes_info_rec.class_id); DBMS_OUTPUT.PUT_LINE ( 'Class ID ' || classes_info_rec.class_id || ' – Average Grade '|| v_avg_grade || ' - Start Date ' || classes_info_rec.start_date || ' – Instructor ' || classes_info_rec.first_name || ' ' || classes_info_rec.last_name || ' – Course Title '|| classes_info_rec.title || ' – Offering Section ' || classes_info_rec.section_code ); END LOOP; END show_class_offerings; --This function will count the number of classes per course. -----------------------------------------------------FUNCTION count_classes_per_course (p_course_id IN classes.course_id%TYPE) RETURN NUMBER IS v_num_classes PLS_INTEGER; BEGIN SELECT COUNT(*) INTO v_num_classes FROM classes WHERE course_id = p_course_id; RETURN v_num_classes; END count_classes_per_course; END _tools_package;
Oracle Academy
19 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
Part 2: Managing Students and Grades (enrollments_package) The enrollments_package you created in Part 1 contains the following public procedures: 1. Procedure enroll_student_in_class (p_stu_id IN enrollments.stu_id%TYPE, p_class_id IN enrollments.class_id%TYPE) 2. Procedure drop_student_from_class(p_stu_id IN enrollments.stu_id%TYPE, p_class_id IN enrollments.class_id%TYPE) 3. Procedure student_class_list (p_stu_id IN enrollments.stu_id%TYPE)
The Assignment and Deliverables: Modify the student_class_list procedure in the package, to add the following functionality: 1. Utilize the overloading feature of the PLSQL package, to overload the student_class_list procedure as follows: When the STU_ID parameter is ed, the procedure should display a list of classes in which the student has been enrolled, within the most recent 6 years. When the procedure is called without a parameter, the procedure should display a list of classes for all students in which they have been enrolled, within the most recent 6 years. 2. Create a procedure read_external_file, to read an external text file stored outside the database as an operating system text file. Use DBMS_OUTPUT to display the content of the external file. The external file is named „student_class_list.txt’ and is stored in the operating system directory referenced by the Oracle directory object ‘'WF_FLAGS'. Your output should look something like this: Enrollment Report Student Id Enrollment Date Class id 101 12-AUG-04 1 102 12-AUG-04 1 103 12-AUG-04 1 104 12-AUG-04 1 *** END OF REPORT ***
Hints: The Exceptions used with UTL_FILE.GET_LINE: INVALID_FILEHANDLE INVALID_OPERATION READ_ERROR NO_DATA_FOUND VALUE_ERROR Directory object name is: 'WF_FLAGS' and must be referenced in capital letters.
Oracle Academy
20 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
Incorporated Topics for problems 1 and 2: Overloading procedures in a package. Reading external files with the UTL_FILE package. Students must use a Directory object WF_FLAGS that is part of the standard course setup. Oracle Directory names are case sensitive, so the students must type it in exactly as above. Oracle Corporation security rules do allow Academy participants to read but not create external text files (using UTL_FILE) on database server machines. This is why the text file „student_class_list.txt’ is provided for you. To test: BEGIN enrollments_package.read_external_file; END; Part 2: Suggested Solution --PACKAGE SPEC CREATE OR REPLACE PACKAGE enrollments_package IS -- This procedure will enroll a student in a class. PROCEDURE enroll_student_in_class (p_stu_id IN enrollments.stu_id%TYPE, p_class_id IN enrollments.class_id%TYPE ) ; -- This procedure will drop a student from a class PROCEDURE drop_student_from_class (p_stu_id IN enrollments.stu_id%TYPE, p_class_id IN enrollments.class_id%TYPE); -- Overloaded procedure will list Enrollment date and class ids for a student PROCEDURE student_class_list (p_stu_id IN enrollments.stu_id%TYPE); -- Overloaded procedure will list Enrollment date and class ids for all students PROCEDURE student_class_list; PROCEDURE read_external_file; END enrollments_package;
Oracle Academy
21 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
--PACKAGE BODY CREATE OR REPLACE PACKAGE BODY enrollments_package IS -- This procedure will enroll a student in a class. PROCEDURE enroll_student_in_class (p_stu_id IN enrollments.stu_id%TYPE, p_class_id IN enrollments.class_id%TYPE) IS v_times_enrolled PLS_INTEGER; e_already_enrolled EXCEPTION; BEGIN SELECT COUNT(*) INTO v_times_enrolled FROM enrollments WHERE class_id = p_class_id AND stu_id = p_stu_id; IF v_times_enrolled <> 0 THEN RAISE e_already_enrolled; END IF; INSERT INTO enrollments (enrollment_date, class_id, stu_id, status) VALUES( SYSDATE, p_class_id, p_stu_id, 'Enrolled'); COMMIT; EXCEPTION WHEN e_already_enrolled THEN DBMS_OUTPUT.PUT_LINE('Student ' || p_stu_id || ' is already enrolled in class '|| p_class_id); END enroll_student_in_class; -- This procedure will drop a student from a class PROCEDURE drop_student_from_class (p_stu_id IN enrollments.stu_id%TYPE, p_class_id IN enrollments.class_id%TYPE) IS e_not_enrolled EXCEPTION; BEGIN DELETE FROM enrollments WHERE class_id = p_class_id AND stu_id = p_stu_id; IF SQL%ROWCOUNT = 0 THEN RAISE e_not_enrolled; END IF; COMMIT; EXCEPTION Oracle Academy
22 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
WHEN e_not_enrolled THEN DBMS_OUTPUT.PUT_LINE('Student ' || p_stu_id ||' is not enrolled in class '|| p_class_id); END drop_student_from_class; -- This procedure will list a student‟s enrollments -- Overloaded procedure with one parameter PROCEDURE student_class_list (p_stu_id IN enrollments.stu_id%TYPE) IS CURSOR stu_class_cur IS SELECT enrollment_date, class_id, status FROM enrollments WHERE stu_id = p_stu_id AND enrollment_date BETWEEN ADD_MONTHS (SYSDATE,-72) and SYSDATE; BEGIN DBMS_OUTPUT.PUT_LINE('Student ' || p_stu_id || ' is enrolled in the following classes:'); FOR stu_class_rec IN stu_class_cur LOOP DBMS_OUTPUT.PUT_LINE('Class: ' ||stu_class_rec.class_id || ' Enrolled on: ' || stu_class_rec.enrollment_date || ' and has a status of: '|| stu_class_rec.status ); END LOOP; END student_class_list; -- This procedure will list all students -- Overloaded procedure with no parameter PROCEDURE student_class_list IS CURSOR stu_class_cur IS SELECT enrollment_date, class_id, status, stu_id FROM enrollments WHERE enrollment_date BETWEEN ADD_MONTHS (SYSDATE,-72) and SYSDATE ORDER BY stu_id, enrollment_date ,class_id; BEGIN DBMS_OUTPUT.PUT_LINE('Students are enrolled in the following classes:'); FOR stu_class_rec IN stu_class_cur LOOP DBMS_OUTPUT.PUT_LINE('Student: ' || stu_class_rec.stu_id || ' Class: ' ||stu_class_rec.class_id || ' Enrolled on: ' || stu_class_rec.enrollment_date || ' and has a status of: '|| stu_class_rec.status ); END LOOP; Oracle Academy
23 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
END student_class_list; --Procedure read_external_file to read and display an external operating system text file. -PROCEDURE read_external_file IS file UTL_FILE.FILE_TYPE; v_line varchar2(1024); BEGIN --the number of bytes returned will be 1024 or less if a line terminator is seen. file := UTL_FILE.FOPEN ('WF_FLAGS', 'student_class_list.txt', 'r'); -- Nested Block to handle end of file errors locally. BEGIN LOOP UTL_FILE.GET_LINE(file , v_line); DBMS_OUTPUT.PUT_LINE( v_line ); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; EXCEPTION --If no text was read due to end of file, the NO_DATA_FOUND exception is raised WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20005, 'END OF FILE.'); WHEN UTL_FILE.INVALID_FILEHANDLE THEN RAISE_APPLICATION_ERROR(-20006,'Invalid File.'); WHEN UTL_FILE.INVALID_OPERATION THEN RAISE_APPLICATION_ERROR (-20007, 'Unable to read or operate as requested.'); WHEN UTL_FILE.READ_ERROR THEN RAISE_APPLICATION_ERROR (-20008, 'Unable to read the file.'); END read_external_file; END enrollments_package;
Oracle Academy
24 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
Part 3: School ’s Tools (_tools_package) The _tools_package you created in Part I contains the following programs: 1. Procedure show_missing_grades (p_start_date IN DATE DEFAULT NULL, p_end_date IN DATE DEFAULT NULL) 2. Procedure show_class_offerings (p_start_date IN DATE, p_end_date IN DATE) 3. Function count_classes_per_course (p_course_id IN classes.course_id%TYPE) RETURN NUMBER 4. Function compute_average_grade (p_class_id IN enrollments.class_id%TYPE) RETURN NUMBER This function is private in this package.
The Assignment and Deliverables: 1. Utilize the forward declaration concept to be able to move the body of the private function compute_average_grade to anywhere in the package body. Recompile and test the package. To test: BEGIN _tools_package.show_class_offerings('01-AUG-01','01-AUG-06'); END; Part 3: Suggested Solution --PACKAGE SPEC CREATE OR REPLACE PACKAGE _tools_package IS -- This procedure will show missing grades for a class. PROCEDURE show_missing_grades(p_start_date IN DATE DEFAULT NULL, p_end_date IN DATE DEFAULT NULL);
-- This procedure will list classes offered PROCEDURE show_class_offerings (p_start_date IN DATE, p_end_date IN DATE); -- This function will count the number of classes per course. FUNCTION count_classes_per_course (p_course_id IN classes.course_id%TYPE) RETURN NUMBER;
Oracle Academy
25 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
END _tools_package; --PACKAGE BODY CREATE OR REPLACE PACKAGE BODY _tools_package IS --This function is private in this package and it will compute --The average grade for a class. --Forward Declaration FUNCTION compute_average_grade (p_class_id IN enrollments.class_id%TYPE) RETURN NUMBER; --This procedure will show missing grades for a class. ---------------------------------------------------PROCEDURE show_missing_grades (p_start_date IN DATE DEFAULT NULL, p_end_date IN DATE DEFAULT NULL) IS CURSOR no_grades_cur IS SELECT class_id, stu_id, status FROM enrollments WHERE final_numeric_grade IS NULL AND final_letter_grade IS NULL AND enrollment_date BETWEEN p_start_date AND p_end_date ORDER BY enrollment_date DESC; v_start_date DATE; v_end_date DATE; BEGIN IF p_start_date IS NULL OR p_end_date IS NULL THEN v_start_date := ADD_MONTHS(SYSDATE,-12); v_end_date := SYSDATE; DBMS_OUTPUT.PUT_LINE ('You have not specified both dates. The listing will show all enrollments for the past year.'); ELSE v_start_date := p_start_date; v_end_date := P_end_date; END IF; DBMS_OUTPUT.PUT_LINE ('Date range: Between ' || v_start_date || ' and ' || v_end_date || '.'); DBMS_OUTPUT.PUT_LINE Oracle Academy
26 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
('The following enrollments have no grade.'); FOR no_grades_rec IN no_grades_cur LOOP DBMS_OUTPUT.PUT_LINE ('Class ID ' || no_grades_rec.class_id || ' – Student ID ' || no_grades_rec.stu_id || ' with a status of: ' ||no_grades_rec.status); END LOOP; END show_missing_grades; --This procedure will list classes offered ---------------------------------PROCEDURE show_class_offerings (p_start_date IN DATE, p_end_date IN DATE) IS v_avg_grade NUMBER; CURSOR classes_info_cur IS SELECT cl.class_id, cl.start_date, i.first_name, i.last_name, cour.title, cour.section_code FROM classes cl, courses cour, instructors i WHERE start_date BETWEEN p_start_date AND p_end_date AND cl.course_id = cour.course_id AND cl.instr_id = i.instructor_id ORDER BY 1,2,4; -BEGIN DBMS_OUTPUT.PUT_LINE ('Date range: Between ' || p_start_date || ' AND ' || p_end_date || '.'); DBMS_OUTPUT.PUT_LINE ('Classes Information.'); FOR classes_info_rec IN classes_info_cur LOOP v_avg_grade := compute_average_grade (classes_info_rec.class_id); DBMS_OUTPUT.PUT_LINE ( 'Class ID ' || classes_info_rec.class_id || ' – Average Grade '|| v_avg_grade || ' - Start Date ' || classes_info_rec.start_date || ' – Intructor ' || classes_info_rec.first_name || ' ' || classes_info_rec.last_name || ' – Course Title '|| classes_info_rec.title || ' – Offering Section ' || classes_info_rec.section_code ); END LOOP; END show_class_offerings;
Oracle Academy
27 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
--This function will count the number of classes per course. FUNCTION count_classes_per_course (p_course_id IN classes.course_id%TYPE) RETURN NUMBER IS v_num_classes PLS_INTEGER; BEGIN SELECT COUNT(*) INTO v_num_classes FROM classes WHERE course_id = p_course_id; RETURN v_num_classes; END count_classes_per_course; FUNCTION compute_average_grade (p_class_id IN enrollments.class_id%TYPE) RETURN NUMBER IS v_avg_grade enrollments.final_numeric_grade%TYPE; BEGIN SELECT AVG(final_numeric_grade) INTO v_avg_grade FROM enrollments WHERE class_id = p_class_id; RETURN v_avg_grade; END compute_average_grade; END _tools_package;
Part 4: Create Database Triggers 1. Create the grade_change_history table as follows: CREATE TABLE grade_change_history (time_stamp DATE, stu_id NUMBER(7,0), class_id NUMBER(6,0), enroll_date DATE, old_final_grade CHAR(1), new_final_grade CHAR(1)); 2. Create a row level trigger audit_grade_change to keep a history of all the changes made to students‟ final letter grade. The grade change is recorded every time the FINAL_LETTER_GRADE field is updated in the ENROLLMENTS table.
Oracle Academy
28 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
Every time the trigger is fired, it should insert a record in the GRADE_CHANGE_HISTORY table, recording the old grade and the new grade for each student.
Test your trigger by updating the final_letter_grade for a student, in the ENROLLMENTS table.
Topics Incorporated: Row level DML trigger Tables Used: ENROLLMENTS, GRADE_CHANGE_HISTORY To test: 1. UPDATE enrollments SET final_letter_grade = 'A' WHERE stu_id = 101; 2. SELECT * FROM grade_change_history;
Part 4: Suggested Solution --Trigger Code -----------------CREATE OR REPLACE TRIGGER audit_grade_change AFTER UPDATE OF final_letter_grade ON enrollments FOR EACH ROW BEGIN INSERT INTO grade_change_history(time_stamp,stu_id, class_id, enroll_date, old_final_grade, new_final_grade) VALUES(SYSDATE, :OLD.stu_id, :OLD.class_id, :OLD.enrollment_date, :OLD.final_letter_grade, :NEW.final_letter_grade); END;
Oracle Academy
29 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
Part 5: Create manage_triggers_package The Assignment and Deliverables: 1. Create a package manage_triggers_package that contains two overloaded functions called manage_triggers. The functions are invoked to disable/enable all triggers for a table, or to compile a trigger. Use Native Dynamic SQL to execute the DDL commands programmatically. Code an exception handling block to display a message if the DDL command fails. Use the following guidelines: When the function is called with two parameters: manage_triggers (p_tablename, p_action) a table name to P_TABLENAME parameter. „disable‟ or „enable‟ string to P_ACTION parameter.
When the function is called with only one parameter manage_triggers (p_trigger_name) a trigger name to P_TRIGGER_NAME parameter.
Hints: Use the ALTER TABLE command to disable/enable all triggers of a table programmatically. Use the ALTER TRIGGER command to compile the trigger programmatically. Topics Incorporated: Forward Declaration, Native Dynamic SQL, Overloading, exception handling. Tables used: CLASSES, INSTRUCTORS, COURSES To test: - a table name to P_TABLENAME parameter - „disable‟ or „enable‟ string to P_ACTION parameter. - a trigger name to P_TRIGGER_NAME parameter. BEGIN manage_triggers_package.manage_triggers('ENROLLMENTS', 'ENABLE'); END; BEGIN manage_triggers_package.manage_triggers('audit_grade_change'); END;
Oracle Academy
30 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
Part 5: Suggested Solution
--Create a new package to manage the database triggers. --PACKAGE SPEC CREATE OR REPLACE PACKAGE manage_triggers_package IS PROCEDURE manage_triggers(p_tablename IN VARCHAR2, p_mode IN VARCHAR2); PROCEDURE manage_triggers(p_trigger_name IN VARCHAR2); END manage_triggers_package; --PACKAGE BODY CREATE OR REPLACE PACKAGE BODY manage_triggers_package IS --Overloaded Procedure manage triggers -----------------------------PROCEDURE manage_triggers(p_tablename IN VARCHAR2, p_mode IN VARCHAR2) IS BEGIN EXECUTE IMMEDIATE 'ALTER TABLE '||p_tablename||' '|| p_mode || ' All TRIGGERS'; DBMS_OUTPUT.PUT_LINE(p_tablename || ' Altered.'); EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001,'DDL Command Failed.'); END; --Overloaded Procedure manage triggers -----------------------------PROCEDURE manage_triggers(p_trigger_name IN VARCHAR2) IS BEGIN EXECUTE IMMEDIATE 'ALTER TRIGGER '||p_trigger_name|| ' COMPILE'; DBMS_OUTPUT.PUT_LINE(p_trigger_name|| ' Trigger Compiled. '); EXCEPTION WHEN OTHERS THEN Oracle Academy
31 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
RAISE_APPLICATION_ERROR(-20001,'DDL Command Failed.'); END; END manage_triggers_package;
Oracle Academy
32 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
All 5 parts can be assigned to individual students. There are dependencies between the parts, so 1 must be completed before 2 and 3; 4 must be completed before 5. The class can be divided into teams and the teams can complete all 5 parts. There are dependencies between the parts, so 1 must be completed before 2 and 3; 4 must be completed before 5.
The code for two packages enrollments_package and _tools_package must be created from the anonymous blocks your students wrote in the first MidTerm Project. If these files are lost, there were suggested solutions in the teacher document for Part I; you can use these solutions to re-create the programs. You are instructed to modify the existing programs from Part I, to incorporate the required topics for this project. The students are also required to create a new package manage_triggers_package to disable/enable triggers for a table or to compile a specific trigger.
Project setup: The Data This project will build on the case study called STUDENT ISTRATION or SA. A set of database tables is used to manage schools‟ course offerings as delivered by instructors in many classes over time. Information is stored about classes that are offered, the students who take classes, and the grades the students receive on various assessments. The school s can use the SA database to manage the class offerings and to assign instructors. Teachers can also use the SA database to track student performance. The database objects for this project are already in the students‟ s and they are as follows: Tables: INSTRUCTORS SECTIONS COURSES CLASSES ASSESSMENTS STUDENTS ENROLLMENTS CLASS_ASSESSMENTS ERROR_LOG GRADE_CHANGES
Oracle Academy
1 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
Sequence: ASSESSMENT_ID_SEQ
Part 1: Procedures, Functions and Packages In this section the students start by re-writing the anonymous blocks from MidTerm I to become procedures and functions. 1. Find the file saved from called enroll_student_in_class.sql from MidTerm I. Convert this to a procedure and have it accept a STU_ID and CLASS_ID as input parameters. Use “today‟s date” for the ENROLLMENT_DATE and the string „Enrolled‟ for the STATUS. Raise an exception if the accepted student is already enrolled in the accepted class. In your exception handler, display a message stating the student is already enrolled in the class. Tables used: ENROLLMENTS Topics Incorporated ==> Scalar variable; %TYPE, Procedure with IN parameters; INSERT; COMMIT; Exception handling if provided stu_id is already enrolled in provided Class_id. This can be a -defined exception which is raised after doing a SELECT which is then followed by an IF statement. To test, run twice, once to enroll the student and again to test the exception: BEGIN enroll_student_in_class(103, 3); END; Suggested Solution: --This procedure will enroll a student in a class. CREATE OR REPLACE PROCEDURE enroll_student_in_class (p_stu_id IN enrollments.stu_id%TYPE, p_class_id IN enrollments.class_id%TYPE) IS v_times_enrolled PLS_INTEGER; e_already_enrolled EXCEPTION; BEGIN SELECT COUNT(*) INTO v_times_enrolled FROM enrollments WHERE class_id = p_class_id AND stu_id = p_stu_id; IF v_times_enrolled <> 0 THEN RAISE e_already_enrolled; END IF; INSERT INTO enrollments (enrollment_date, class_id, stu_id, status) VALUES (SYSDATE, p_class_id, p_stu_id, 'Enrolled'); Oracle Academy
2 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
COMMIT; EXCEPTION WHEN e_already_enrolled THEN DBMS_OUTPUT.PUT_LINE('Student ' || p_stu_id || ' is already enrolled in class '|| p_class_id); END enroll_student_in_class; 2. Find the file called drop_student_from_class.sql from MidTerm I. Convert it to a procedure that accepts a STU_ID and CLASS_ID as input parameters. If the DELETE fails because the student is not in the class, raise a _defined exception to display a message stating the student is not in the class. Tables used: ENROLLMENTS Topics Incorporated ==> %TYPE; Procedure with IN parameters, DELETE; SQL%ROWCOUNT; COMMIT; -defined exception. To test, run twice, once to drop the student and again to test the exception: BEGIN drop_student_from_class(103, 3); END; Suggested Solution: -- This procedure will drop a student from a class. CREATE OR REPLACE PROCEDURE drop_student_from_class (p_stu_id IN enrollments.stu_id%TYPE, p_class_id IN enrollments.class_id%TYPE) IS e_not_enrolled EXCEPTION; BEGIN DELETE FROM enrollments WHERE class_id = p_class_id AND stu_id = p_stu_id; IF SQL%ROWCOUNT = 0 THEN RAISE e_not_enrolled; END IF; COMMIT; EXCEPTION WHEN e_not_enrolled THEN DBMS_OUTPUT.PUT_LINE('Student ' || p_stu_id || ' is not enrolled in class '|| p_class_id); END drop_student_from_class;
Oracle Academy
3 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
3. Find the file called student_class_list.sql from MidTerm I. Rewrite it to be a procedure that displays all of the classes a student has been enrolled in within the most recent 10 years. For example: If you run your procedure on May 10, 2010, you should display all enrollments between May 10, 2000 and May 10, 2010. Accept the STU_ID as an input parameter. For each enrollment, display the ENROLLMENT_DATE, CLASS_ID and STATUS. Tables used: ENROLLMENTS Topics Incorporated ==> %TYPE; Procedure with IN parameters; SYSDATE, ADD_MONTHS, Explicit cursor to find all courses for the provided Student ID;DBMS_OUTPUT to display the list of classes. To test: BEGIN student_class_list(101); END; Suggested Solution: -- This procedure will list all students in a class. CREATE OR REPLACE PROCEDURE student_class_list (p_stu_id IN enrollments.stu_id%TYPE) IS CURSOR stu_class_cur IS SELECT enrollment_date, class_id, status FROM enrollments WHERE stu_id = p_stu_id AND enrollment_date between ADD_MONTHS (SYSDATE,-120) and SYSDATE; BEGIN DBMS_OUTPUT.PUT_LINE('Student ' || p_stu_id || ' is enrolled in the following classes:'); FOR stu_class_rec IN stu_class_cur LOOP DBMS_OUTPUT.PUT_LINE('Class: ' ||stu_class_rec.class_id || ' Enrolled on: ' || stu_class_rec.enrollment_date || ' and has a status of: '|| stu_class_rec.status ); END LOOP; END student_class_list;
Oracle Academy
4 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
4. Find the file called add_new_classes.sql from MidTerm I. Rewrite it as a Procedure and have it accept the following IN parameters: a. number of new classes required. Set a default value of 1. b. Course id; For each new class, use “today”as the START_DATE. c. Period, to specify what days the class meets. d. Frequency, to specify how often it meets. e. Instructor id, to specify who is teaching the class(s). Note for the teacher: It is probably more likely and safer to use a sequence number for the class_id. If 2 sessions are calling this procedure at the same time, there is a small chance the same MAX(class_id) could be found. This would cause the second set of INSERTS to fail. The first option will work for this project, but you might discuss this interesting subtlety with your students. Tables used: CLASSES Topics Incorporated ==> Creating a procedure; SELECT; INSERT; COMMIT; DEFAULT value; Using a LOOP to only insert “n” new rows. To test: BEGIN add_new_classes (2, 1001, 'TUE_TR', 'Once', 3003) ; END; Suggested Solution 1: CREATE OR REPLACE PROCEDURE add_new_classes (p_number_new_classes IN PLS_INTEGER DEFAULT 1, p_course_id IN classes.course_id%TYPE, p_period IN classes.period%TYPE, p_frequency IN classes.frequency%TYPE, p_instr_id IN classes.instr_id%TYPE ) IS v_current_max_class_id classes.class_id%TYPE; BEGIN SELECT MAX(class_id) INTO v_current_max_class_id FROM classes; FOR loop_counter IN 1..p_number_new_classes LOOP INSERT INTO classes (class_id, start_date,course_id, period, frequency,instr_id) VALUES (v_current_max_class_id + loop_counter, SYSDATE, p_course_id, p_period, p_frequency, p_instr_id); COMMIT; END LOOP; END add_new_classes;
Oracle Academy
5 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
Suggested Solution 2: SELECT MAX(class_id) FROM classes; DROP SEQUENCE class_id_seq; CREATE SEQUENCE class_id_seq START WITH (number_returned_from_SELECT + 1) INCREMENT BY 1 NOCACHE; CREATE OR REPLACE PROCEDURE add_new_classes (p_number_new_classes IN PLS_INTEGER DEFAULT 1, p_course_id IN classes.course_id%TYPE, p_period IN classes.period%TYPE, p_frequency IN classes.frequency%TYPE, p_instr_id IN classes.instr_id%TYPE ) IS BEGIN FOR loop_counter IN 1..p_number_new_classes LOOP INSERT INTO classes (class_id, start_date,course_id, period, frequency,instr_id) VALUES (class_id_seq.NEXTVAL,SYSDATE, p_course_id, p_period,p_frequency,p_instr_id); COMMIT; END LOOP; END add_new_classes;
5. Find the file called course_roster.sql from MidTerm I and rewrite it as a procedure. Accept the INSTR_ID and COURSE_ID as input parameters. For each ENROLLMENT, display: CLASS_ID, STATUS, Student FIRST_NAME and LAST_NAME. Tables used: ENROLLMENTS; CLASSES; STUDENTS Topics Incorporated ==> SELECT with ; Explicit Cursor To test: BEGIN course_roster(3003, 1002); END;
Oracle Academy
6 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
Suggested Solution: CREATE OR REPLACE PROCEDURE course_roster (p_instr_id IN classes.instr_id%TYPE, p_course_id IN classes.course_id%TYPE) IS CURSOR stu_course_cur IS SELECT e.class_id, e.status, s.first_name, s.last_name FROM enrollments e, classes c, students s WHERE e.class_id = c.class_id AND e.stu_id = s.stu_id AND c.course_id = p_course_id AND c.instr_id = p_instr_id; BEGIN DBMS_OUTPUT.PUT_LINE('Course ' || p_course_id || ' has the following students:'); FOR stu_course_rec IN stu_course_cur LOOP DBMS_OUTPUT.PUT_LINE('Class: ' || stu_course_rec.class_id || ' Student: '|| stu_course_rec.first_name || ' ' || stu_course_rec.last_name || ' with a status of: ' || stu_course_rec.status ); END LOOP; END course_roster;
6. Find the file called convert_grade.sql from MidTerm I and rewrite it to be a function Use an IN parameter to enter the number grade. RETURN a CHAR value. Use the following rules: A:90 or above, B: >=80 and<90 , C: >=70 and < 80, D: >=60 and < 70, F:<60. Tables used: None Topics Incorporated ==> Scalar variables...NUMBER IN; CHAR RETURNED; IF or CASE Statement (A=90 to 100, B=80-90, C=70-80, D=60-70, F=<60; To Test: BEGIN DBMS_OUTPUT.PUT_LINE(convert_grade (92)); END; -- Should return 'A'.
Oracle Academy
7 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
Suggested Solution 1: CREATE OR REPLACE FUNCTION convert_grade (p_numeric_grade IN NUMBER) RETURN varchar2 IS v_letter_grade CHAR(1); BEGIN IF p_numeric_grade >= 90.0 THEN v_letter_grade := 'A'; ELSIF p_numeric_grade >= 80.0 THEN v_letter_grade := 'B'; ELSIF p_numeric_grade >= 70.0 THEN v_letter_grade := 'C'; ELSIF p_numeric_grade >= 60.0 THEN v_letter_grade := 'D'; ELSE v_letter_grade := 'F'; END IF; RETURN v_letter_grade; END convert_grade; Suggested Solution 2: CREATE OR REPLACE FUNCTION convert_grade (p_numeric_grade IN NUMBER) RETURN varchar2 IS v_letter_grade CHAR(1); BEGIN v_letter_grade := CASE WHEN p_numeric_grade >= 90.0 THEN 'A' WHEN p_numeric_grade >= 80.0 THEN 'B' WHEN p_numeric_grade >= 70.0 THEN 'C' WHEN p_numeric_grade >= 60.0 THEN 'D' ELSE 'F' END; RETURN v_letter_grade; END convert_grade;
Oracle Academy
8 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
7. Find the file called student_count.sql and rewrite it as a function that will RETURN the number of students in a particular class. Accept a CLASS_ID as an IN parameter. Tables used: ENROLLMENTS Topics Incorporated ==> Scalar variable...INTEGER; SELECT COUNT(*) INTO variable FROM enrollments where class_id = ...; using a -defined function in SQL. Testing the function: SELECT class_id, student_count(class_id) AS "Student Count", stu_id FROM enrollments; Suggested Solution: CREATE OR REPLACE FUNCTION student_count (p_class_id IN classes.class_id%TYPE) RETURN NUMBER IS v_student_count PLS_INTEGER; BEGIN SELECT COUNT(*) INTO v_student_count FROM enrollments WHERE class_id = p_class_id; RETURN v_student_count; END student_count; 8. Create a package called enrollments_package which will contain the procedures you created in A, B, and C. Make all procedures public. Comment your procedures to explain their purpose and functionality. Tables used: ENROLLMENTS Topics Incorporated ==> Creating a package; incorporating comments. Suggested Solution: --PACKAGE SPEC CREATE OR REPLACE PACKAGE enrollments_package IS -- This procedure will enroll a student in a class. PROCEDURE enroll_student_in_class (p_stu_id IN enrollments.stu_id%TYPE, p_class_id IN enrollments.class_id%TYPE ) ; -- This procedure will drop a student from a class Oracle Academy
9 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
PROCEDURE drop_student_from_class (p_stu_id IN enrollments.stu_id%TYPE, p_class_id IN enrollments.class_id%TYPE); -- This procedure will list all students in a class PROCEDURE student_class_list (p_stu_id IN enrollments.stu_id%TYPE); END enrollments_package; --PACKAGE BODY CREATE OR REPLACE PACKAGE BODY enrollments_package IS -- This procedure will enroll a student in a class. PROCEDURE enroll_student_in_class (p_stu_id IN enrollments.stu_id%TYPE, p_class_id IN enrollments.class_id%TYPE) IS v_times_enrolled PLS_INTEGER; e_already_enrolled EXCEPTION; BEGIN SELECT COUNT(*) INTO v_times_enrolled FROM enrollments WHERE class_id = p_class_id AND stu_id = p_stu_id; IF v_times_enrolled <> 0 THEN RAISE e_already_enrolled; END IF; INSERT INTO enrollments (enrollment_date, class_id, stu_id, status) VALUES (SYSDATE, p_class_id, p_stu_id, 'Enrolled'); COMMIT; EXCEPTION WHEN e_already_enrolled THEN DBMS_OUTPUT.PUT_LINE('Student ' || p_stu_id || ' is already enrolled in class '|| p_class_id); END enroll_student_in_class; -- This procedure will drop a student from a class PROCEDURE drop_student_from_class (p_stu_id IN enrollments.stu_id%TYPE, p_class_id IN enrollments.class_id%TYPE) IS e_not_enrolled EXCEPTION; BEGIN DELETE FROM enrollments Oracle Academy
10 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
WHERE class_id = p_class_id AND stu_id = p_stu_id; IF SQL%ROWCOUNT = 0 THEN RAISE e_not_enrolled; END IF; COMMIT; EXCEPTION WHEN e_not_enrolled THEN DBMS_OUTPUT.PUT_LINE('Student ' || p_stu_id || ' is not enrolled in class '|| p_class_id); END drop_student_from_class; -- This procedure will list all students in a class PROCEDURE student_class_list (p_stu_id IN enrollments.stu_id%TYPE) IS CURSOR stu_class_cur IS SELECT enrollment_date, class_id, status FROM enrollments WHERE stu_id = p_stu_id AND enrollment_date between ADD_MONTHS (SYSDATE,-120) and SYSDATE; BEGIN DBMS_OUTPUT.PUT_LINE('Student ' || p_stu_id || ' is enrolled in the following classes:'); FOR stu_class_rec IN stu_class_cur LOOP DBMS_OUTPUT.PUT_LINE('Class: ' ||stu_class_rec.class_id || ' Enrolled on: ' || stu_class_rec.enrollment_date || ' and has a status of: '|| stu_class_rec.status ); END LOOP; END student_class_list; END enrollments_package; 9. Find the program saved in the file create_assignment.sql. Rewrite it as a procedure that accepts the assignment description as an input parameter. Tables used: ASSESSMENTS Topics Incorporated ==> SQL INSERT; IN Argument, Use of sequence in INSERT; To test: BEGIN create_assignment('this is to test the procedure'); END;
Oracle Academy
11 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
Suggested Solution: CREATE OR REPLACE PROCEDURE create_assignment (p_assign_descrip IN assessments.description%TYPE) IS BEGIN INSERT INTO assessments (assessment_id, description) VALUES (assessment_id_seq.NEXTVAL, p_assign_descrip); COMMIT; END create_assignment; 10. Find the file called enter_student_grade.sql and rewrite it as a procedure that a teacher can run to insert the student's grade on a particular assignment. Accept a NUMERIC_GRADE, CLASS_ASSESSMENT_ID, CLASS_ID, STU_ID and ASSESSMENT_ID as IN parameters. Use “today‟s” date for the DATE_TURNED_IN.
Tables used: CLASS_ASSESSMENTS Topics Incorporated ==> SQL INSERT; Many IN arguments; SYSDATE. To test: BEGIN Enter_student_grade(102,87,1,101,1); END; Suggested Solution: CREATE OR REPLACE PROCEDURE enter_student_grade (p_cl_assessment_id IN class_assessments.class_assessment_id%TYPE, p_numeric_grade IN class_assessments.numeric_grade%TYPE, p_class_id IN class_assessments.class_id%TYPE, p_stu_id IN class_assessments.stu_id%TYPE, p_assessment_id IN class_assessments.assessment_id%TYPE) IS BEGIN INSERT INTO class_assessments (class_assessment_id, date_turned_in, numeric_grade, class_id, stu_id, assessment_id) VALUES (p_cl_assessment_id, SYSDATE, p_numeric_grade,p_class_id, p_stu_id, p_assessment_id); COMMIT; END enter_student_grade;
Oracle Academy
12 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
11. Rewrite the program stored in the file show_missing_grades.sql to be a procedure. Accept a start_date and end_date to establish a date range. Display only enrollments between those two dates. Write your procedure so the start_date and end_date are optional. If both dates are not entered, display all applicable enrollments for the past year, and include a note about the date range. For each enrollment, list the CLASS_ID, STU_ID, and STATUS. Order the output by ENROLLMENT_DATE with the most recent enrollments first. Tables used: ENROLLMENTS Topics Incorporated ==> SQL with BETWEEN; Dates; Default values with IN parameters, NULL values in a database column, MONTHS_BETWEEN or other way to find the “past year.” To test: BEGIN show_missing_grades('01-AUG-01', '01-AUG-05'); END; Suggested Solution: CREATE OR REPLACE PROCEDURE show_missing_grades (p_start_date IN DATE DEFAULT NULL, p_end_date IN DATE DEFAULT NULL) IS CURSOR no_grades_cur IS SELECT class_id, stu_id, status FROM enrollments WHERE final_numeric_grade IS NULL AND final_letter_grade IS NULL AND enrollment_date BETWEEN p_start_date AND p_end_date ORDER BY enrollment_date DESC; v_start_date DATE; v_end_date DATE; BEGIN IF p_start_date IS NULL OR p_end_date IS NULL THEN v_start_date := ADD_MONTHS(SYSDATE,-12); v_end_date := SYSDATE; DBMS_OUTPUT.PUT_LINE ('You have not specified both dates. The listing will show all enrollments for the past year.'); ELSE v_start_date := p_start_date; v_end_date := P_end_date; END IF; DBMS_OUTPUT.PUT_LINE Oracle Academy
13 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
('Date range: Between ' || v_start_date || ' and ' || v_end_date || '.'); DBMS_OUTPUT.PUT_LINE ('The following enrollments have no grade.'); FOR no_grades_rec IN no_grades_cur LOOP DBMS_OUTPUT.PUT_LINE ('Class ID ' || no_grades_rec.class_id || ' – Student ID ' || no_grades_rec.stu_id || ' with a status of: ' ||no_grades_rec.status); END LOOP; END show_missing_grades;
12. Find the file called compute_average_grade.sql and rewrite it as a function. Accept a CLASS_ID. Return the average grade. Tables used: ENROLLMENTS Topics Incorporated ==> SQL with AVG function To test: (there will be no output since there are no final grades in the table) DECLARE v_grade NUMBER; BEGIN v_grade := compute_average_grade(6); DBMS_OUTPUT.PUT_LINE(v_grade); END; Suggested Solution: CREATE OR REPLACE FUNCTION compute_average_grade (p_class_id IN enrollments.class_id%TYPE) RETURN NUMBER IS v_avg_grade enrollments.final_numeric_grade%TYPE; BEGIN SELECT AVG(final_numeric_grade) INTO v_avg_grade FROM enrollments WHERE class_id = p_class_id; RETURN v_avg_grade; END compute_average_grade;
Oracle Academy
14 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
13. Find the file called count_classes_per_course.sql and rewrite it as a function. Accept a COURSE_ID. Return the number of classes offered for that course. Tables used: CLASSES Topics Incorporated ==> SQL SELECT with COUNT.
To test: DECLARE v_classes PLS_INTEGER; BEGIN v_classes := count_classes_per_course(1001); DBMS_OUTPUT.PUT_LINE(v_classes); END; Suggested Solution: CREATE OR REPLACE FUNCTION count_classes_per_course (p_course_id IN classes.course_id%TYPE) RETURN NUMBER IS num_classes PLS_INTEGER; BEGIN SELECT COUNT(*) INTO v_num_classes FROM classes WHERE course_id = p_course_id; RETURN v_num_classes; END count_classes_per_course; 14. Convert the file show_class_offerings.sql to a procedure. Accept a start date and end date. For each class found, display the CLASS_ID, START_DATE, instructor FIRST_NAME and LAST_NAME, course TITLE and SECTION_CODE, and average grade. Find the average grade by a call to the function compute_average_grade. Tables used: CLASSES, INSTRUCTORS, COURSES,ENROLLMENTS Topics Incorporated ==> Calling a function from a procedure; using dates; SQL ; %TYPE. To test: BEGIN show_class_offerings('01-AUG-01','01-AUG-06'); END;
Oracle Academy
15 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
Suggested Solution: CREATE OR REPLACE PROCEDURE show_class_offerings (p_start_date IN DATE, p_end_date IN DATE) IS v_avg_grade NUMBER; CURSOR classes_info_cur IS SELECT cl.class_id, cl.start_date, i.first_name, i.last_name, cour.title, cour.section_code FROM classes cl, courses cour, instructors i WHERE start_date BETWEEN p_start_date AND p_end_date AND cl.course_id = cour.course_id AND cl.instr_id = i.instructor_id ORDER BY 1,2,4; BEGIN DBMS_OUTPUT.PUT_LINE ('Date range: Between ' || p_start_date || ' and ' || p_end_date || '.'); DBMS_OUTPUT.PUT_LINE ('Classes Information.'); FOR classes_info_rec IN classes_info_cur LOOP -- call the compute_average_grade function v_avg_grade := compute_average_grade (classes_info_rec.class_id); DBMS_OUTPUT.PUT_LINE ( 'Class ID ' || classes_info_rec.class_id || ' – Average Grade '|| v_avg_grade || ' - Start Date ' || classes_info_rec.start_date || ' – Instructor ' || classes_info_rec.first_name || ' ' || classes_info_rec.last_name || ' – Course Title '|| classes_info_rec.title || ' – Offering Section ' || classes_info_rec.section_code ); END LOOP; END show_class_offerings; 15. Create a package called _tools_package incorporating procedure and functions you wrote in steps K to N. Make the following public: show_missing_grades, show_class_offerings, count_classes_per_course. Make the following private: compute_average_grade. Tables used: CLASSES, INSTRUCTORS, COURSES Topics Incorporated: Creating a package; Public vs. Private package constructs.
Oracle Academy
16 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
--PACKAGE SPEC CREATE OR REPLACE PACKAGE _tools_package IS -- This procedure will show missing grades for a class. PROCEDURE show_missing_grades (p_start_date IN DATE DEFAULT ADD_MONTHS(SYSDATE,-12), p_end_date IN DATE DEFAULT SYSDATE); -- This procedure will list classes offered PROCEDURE show_class_offerings (p_start_date IN DATE, p_end_date IN DATE); -- This function will count the number of classes per course. FUNCTION count_classes_per_course (p_course_id IN classes.course_id%TYPE) RETURN NUMBER; END _tools_package; --PACKAGE BODY CREATE OR REPLACE PACKAGE BODY _tools_package IS --This function is private in this package and it will compute --the average grade for a class. FUNCTION compute_average_grade (p_class_id IN enrollments.class_id%TYPE) RETURN NUMBER IS v_avg_grade enrollments.final_numeric_grade%TYPE; BEGIN SELECT AVG(final_numeric_grade) INTO v_avg_grade FROM enrollments WHERE class_id = p_class_id; RETURN v_avg_grade; END compute_average_grade; --This procedure will show missing grades for a class. -------------------------------------------------------PROCEDURE show_missing_grades (p_start_date IN DATE DEFAULT ADD_MONTHS(SYSDATE,-12), Oracle Academy
17 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
p_end_date IN DATE DEFAULT SYSDATE) IS CURSOR no_grades_cur IS SELECT class_id, stu_id, status FROM enrollments WHERE final_numeric_grade IS NULL AND final_letter_grade IS NULL AND enrollment_date BETWEEN p_start_date AND p_end_date ORDER BY enrollment_date DESC; BEGIN IF p_start_date IS NULL OR p_end_date IS NULL THEN DBMS_OUTPUT.PUT_LINE ('You have not specified both dates. The listing will show all enrollments for the past year.'); END IF; DBMS_OUTPUT.PUT_LINE ('Date range: Between ' || p_start_date || ' and ' || p_end_date || '.'); DBMS_OUTPUT.PUT_LINE ('The following enrollments have no grade.'); FOR no_grades_rec IN no_grades_cur LOOP DBMS_OUTPUT.PUT_LINE ('Class ID ' || no_grades_rec.class_id || ' – Student ID ' || no_grades_rec.stu_id || ' with a status of: ' ||no_grades_rec.status); END LOOP; END show_missing_grades; --This procedure will list classes offered -------------------------------PROCEDURE show_class_offerings (p_start_date IN DATE, p_end_date IN DATE) IS v_avg_grade NUMBER; CURSOR classes_info_cur IS SELECT cl.class_id, cl.start_date, i.first_name, i.last_name, cour.title, cour.section_code FROM classes cl, courses cour, instructors i WHERE start_date BETWEEN p_start_date and p_end_date AND cl.course_id = cour.course_id AND cl.instr_id = i.instructor_id ORDER BY 1,2,4; -BEGIN Oracle Academy
18 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
DBMS_OUTPUT.PUT_LINE ('Date range: Between ' || p_start_date || ' and ' || p_end_date || '.'); DBMS_OUTPUT.PUT_LINE ('Classes Information.'); FOR classes_info_rec IN classes_info_cur LOOP v_avg_grade := compute_average_grade (classes_info_rec.class_id); DBMS_OUTPUT.PUT_LINE ( 'Class ID ' || classes_info_rec.class_id || ' – Average Grade '|| v_avg_grade || ' - Start Date ' || classes_info_rec.start_date || ' – Instructor ' || classes_info_rec.first_name || ' ' || classes_info_rec.last_name || ' – Course Title '|| classes_info_rec.title || ' – Offering Section ' || classes_info_rec.section_code ); END LOOP; END show_class_offerings; --This function will count the number of classes per course. -----------------------------------------------------FUNCTION count_classes_per_course (p_course_id IN classes.course_id%TYPE) RETURN NUMBER IS v_num_classes PLS_INTEGER; BEGIN SELECT COUNT(*) INTO v_num_classes FROM classes WHERE course_id = p_course_id; RETURN v_num_classes; END count_classes_per_course; END _tools_package;
Oracle Academy
19 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
Part 2: Managing Students and Grades (enrollments_package) The enrollments_package you created in Part 1 contains the following public procedures: 1. Procedure enroll_student_in_class (p_stu_id IN enrollments.stu_id%TYPE, p_class_id IN enrollments.class_id%TYPE) 2. Procedure drop_student_from_class(p_stu_id IN enrollments.stu_id%TYPE, p_class_id IN enrollments.class_id%TYPE) 3. Procedure student_class_list (p_stu_id IN enrollments.stu_id%TYPE)
The Assignment and Deliverables: Modify the student_class_list procedure in the package, to add the following functionality: 1. Utilize the overloading feature of the PLSQL package, to overload the student_class_list procedure as follows: When the STU_ID parameter is ed, the procedure should display a list of classes in which the student has been enrolled, within the most recent 6 years. When the procedure is called without a parameter, the procedure should display a list of classes for all students in which they have been enrolled, within the most recent 6 years. 2. Create a procedure read_external_file, to read an external text file stored outside the database as an operating system text file. Use DBMS_OUTPUT to display the content of the external file. The external file is named „student_class_list.txt’ and is stored in the operating system directory referenced by the Oracle directory object ‘'WF_FLAGS'. Your output should look something like this: Enrollment Report Student Id Enrollment Date Class id 101 12-AUG-04 1 102 12-AUG-04 1 103 12-AUG-04 1 104 12-AUG-04 1 *** END OF REPORT ***
Hints: The Exceptions used with UTL_FILE.GET_LINE: INVALID_FILEHANDLE INVALID_OPERATION READ_ERROR NO_DATA_FOUND VALUE_ERROR Directory object name is: 'WF_FLAGS' and must be referenced in capital letters.
Oracle Academy
20 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
Incorporated Topics for problems 1 and 2: Overloading procedures in a package. Reading external files with the UTL_FILE package. Students must use a Directory object WF_FLAGS that is part of the standard course setup. Oracle Directory names are case sensitive, so the students must type it in exactly as above. Oracle Corporation security rules do allow Academy participants to read but not create external text files (using UTL_FILE) on database server machines. This is why the text file „student_class_list.txt’ is provided for you. To test: BEGIN enrollments_package.read_external_file; END; Part 2: Suggested Solution --PACKAGE SPEC CREATE OR REPLACE PACKAGE enrollments_package IS -- This procedure will enroll a student in a class. PROCEDURE enroll_student_in_class (p_stu_id IN enrollments.stu_id%TYPE, p_class_id IN enrollments.class_id%TYPE ) ; -- This procedure will drop a student from a class PROCEDURE drop_student_from_class (p_stu_id IN enrollments.stu_id%TYPE, p_class_id IN enrollments.class_id%TYPE); -- Overloaded procedure will list Enrollment date and class ids for a student PROCEDURE student_class_list (p_stu_id IN enrollments.stu_id%TYPE); -- Overloaded procedure will list Enrollment date and class ids for all students PROCEDURE student_class_list; PROCEDURE read_external_file; END enrollments_package;
Oracle Academy
21 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
--PACKAGE BODY CREATE OR REPLACE PACKAGE BODY enrollments_package IS -- This procedure will enroll a student in a class. PROCEDURE enroll_student_in_class (p_stu_id IN enrollments.stu_id%TYPE, p_class_id IN enrollments.class_id%TYPE) IS v_times_enrolled PLS_INTEGER; e_already_enrolled EXCEPTION; BEGIN SELECT COUNT(*) INTO v_times_enrolled FROM enrollments WHERE class_id = p_class_id AND stu_id = p_stu_id; IF v_times_enrolled <> 0 THEN RAISE e_already_enrolled; END IF; INSERT INTO enrollments (enrollment_date, class_id, stu_id, status) VALUES( SYSDATE, p_class_id, p_stu_id, 'Enrolled'); COMMIT; EXCEPTION WHEN e_already_enrolled THEN DBMS_OUTPUT.PUT_LINE('Student ' || p_stu_id || ' is already enrolled in class '|| p_class_id); END enroll_student_in_class; -- This procedure will drop a student from a class PROCEDURE drop_student_from_class (p_stu_id IN enrollments.stu_id%TYPE, p_class_id IN enrollments.class_id%TYPE) IS e_not_enrolled EXCEPTION; BEGIN DELETE FROM enrollments WHERE class_id = p_class_id AND stu_id = p_stu_id; IF SQL%ROWCOUNT = 0 THEN RAISE e_not_enrolled; END IF; COMMIT; EXCEPTION Oracle Academy
22 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
WHEN e_not_enrolled THEN DBMS_OUTPUT.PUT_LINE('Student ' || p_stu_id ||' is not enrolled in class '|| p_class_id); END drop_student_from_class; -- This procedure will list a student‟s enrollments -- Overloaded procedure with one parameter PROCEDURE student_class_list (p_stu_id IN enrollments.stu_id%TYPE) IS CURSOR stu_class_cur IS SELECT enrollment_date, class_id, status FROM enrollments WHERE stu_id = p_stu_id AND enrollment_date BETWEEN ADD_MONTHS (SYSDATE,-72) and SYSDATE; BEGIN DBMS_OUTPUT.PUT_LINE('Student ' || p_stu_id || ' is enrolled in the following classes:'); FOR stu_class_rec IN stu_class_cur LOOP DBMS_OUTPUT.PUT_LINE('Class: ' ||stu_class_rec.class_id || ' Enrolled on: ' || stu_class_rec.enrollment_date || ' and has a status of: '|| stu_class_rec.status ); END LOOP; END student_class_list; -- This procedure will list all students -- Overloaded procedure with no parameter PROCEDURE student_class_list IS CURSOR stu_class_cur IS SELECT enrollment_date, class_id, status, stu_id FROM enrollments WHERE enrollment_date BETWEEN ADD_MONTHS (SYSDATE,-72) and SYSDATE ORDER BY stu_id, enrollment_date ,class_id; BEGIN DBMS_OUTPUT.PUT_LINE('Students are enrolled in the following classes:'); FOR stu_class_rec IN stu_class_cur LOOP DBMS_OUTPUT.PUT_LINE('Student: ' || stu_class_rec.stu_id || ' Class: ' ||stu_class_rec.class_id || ' Enrolled on: ' || stu_class_rec.enrollment_date || ' and has a status of: '|| stu_class_rec.status ); END LOOP; Oracle Academy
23 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
END student_class_list; --Procedure read_external_file to read and display an external operating system text file. -PROCEDURE read_external_file IS file UTL_FILE.FILE_TYPE; v_line varchar2(1024); BEGIN --the number of bytes returned will be 1024 or less if a line terminator is seen. file := UTL_FILE.FOPEN ('WF_FLAGS', 'student_class_list.txt', 'r'); -- Nested Block to handle end of file errors locally. BEGIN LOOP UTL_FILE.GET_LINE(file , v_line); DBMS_OUTPUT.PUT_LINE( v_line ); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; EXCEPTION --If no text was read due to end of file, the NO_DATA_FOUND exception is raised WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20005, 'END OF FILE.'); WHEN UTL_FILE.INVALID_FILEHANDLE THEN RAISE_APPLICATION_ERROR(-20006,'Invalid File.'); WHEN UTL_FILE.INVALID_OPERATION THEN RAISE_APPLICATION_ERROR (-20007, 'Unable to read or operate as requested.'); WHEN UTL_FILE.READ_ERROR THEN RAISE_APPLICATION_ERROR (-20008, 'Unable to read the file.'); END read_external_file; END enrollments_package;
Oracle Academy
24 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
Part 3: School ’s Tools (_tools_package) The _tools_package you created in Part I contains the following programs: 1. Procedure show_missing_grades (p_start_date IN DATE DEFAULT NULL, p_end_date IN DATE DEFAULT NULL) 2. Procedure show_class_offerings (p_start_date IN DATE, p_end_date IN DATE) 3. Function count_classes_per_course (p_course_id IN classes.course_id%TYPE) RETURN NUMBER 4. Function compute_average_grade (p_class_id IN enrollments.class_id%TYPE) RETURN NUMBER This function is private in this package.
The Assignment and Deliverables: 1. Utilize the forward declaration concept to be able to move the body of the private function compute_average_grade to anywhere in the package body. Recompile and test the package. To test: BEGIN _tools_package.show_class_offerings('01-AUG-01','01-AUG-06'); END; Part 3: Suggested Solution --PACKAGE SPEC CREATE OR REPLACE PACKAGE _tools_package IS -- This procedure will show missing grades for a class. PROCEDURE show_missing_grades(p_start_date IN DATE DEFAULT NULL, p_end_date IN DATE DEFAULT NULL);
-- This procedure will list classes offered PROCEDURE show_class_offerings (p_start_date IN DATE, p_end_date IN DATE); -- This function will count the number of classes per course. FUNCTION count_classes_per_course (p_course_id IN classes.course_id%TYPE) RETURN NUMBER;
Oracle Academy
25 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
END _tools_package; --PACKAGE BODY CREATE OR REPLACE PACKAGE BODY _tools_package IS --This function is private in this package and it will compute --The average grade for a class. --Forward Declaration FUNCTION compute_average_grade (p_class_id IN enrollments.class_id%TYPE) RETURN NUMBER; --This procedure will show missing grades for a class. ---------------------------------------------------PROCEDURE show_missing_grades (p_start_date IN DATE DEFAULT NULL, p_end_date IN DATE DEFAULT NULL) IS CURSOR no_grades_cur IS SELECT class_id, stu_id, status FROM enrollments WHERE final_numeric_grade IS NULL AND final_letter_grade IS NULL AND enrollment_date BETWEEN p_start_date AND p_end_date ORDER BY enrollment_date DESC; v_start_date DATE; v_end_date DATE; BEGIN IF p_start_date IS NULL OR p_end_date IS NULL THEN v_start_date := ADD_MONTHS(SYSDATE,-12); v_end_date := SYSDATE; DBMS_OUTPUT.PUT_LINE ('You have not specified both dates. The listing will show all enrollments for the past year.'); ELSE v_start_date := p_start_date; v_end_date := P_end_date; END IF; DBMS_OUTPUT.PUT_LINE ('Date range: Between ' || v_start_date || ' and ' || v_end_date || '.'); DBMS_OUTPUT.PUT_LINE Oracle Academy
26 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
('The following enrollments have no grade.'); FOR no_grades_rec IN no_grades_cur LOOP DBMS_OUTPUT.PUT_LINE ('Class ID ' || no_grades_rec.class_id || ' – Student ID ' || no_grades_rec.stu_id || ' with a status of: ' ||no_grades_rec.status); END LOOP; END show_missing_grades; --This procedure will list classes offered ---------------------------------PROCEDURE show_class_offerings (p_start_date IN DATE, p_end_date IN DATE) IS v_avg_grade NUMBER; CURSOR classes_info_cur IS SELECT cl.class_id, cl.start_date, i.first_name, i.last_name, cour.title, cour.section_code FROM classes cl, courses cour, instructors i WHERE start_date BETWEEN p_start_date AND p_end_date AND cl.course_id = cour.course_id AND cl.instr_id = i.instructor_id ORDER BY 1,2,4; -BEGIN DBMS_OUTPUT.PUT_LINE ('Date range: Between ' || p_start_date || ' AND ' || p_end_date || '.'); DBMS_OUTPUT.PUT_LINE ('Classes Information.'); FOR classes_info_rec IN classes_info_cur LOOP v_avg_grade := compute_average_grade (classes_info_rec.class_id); DBMS_OUTPUT.PUT_LINE ( 'Class ID ' || classes_info_rec.class_id || ' – Average Grade '|| v_avg_grade || ' - Start Date ' || classes_info_rec.start_date || ' – Intructor ' || classes_info_rec.first_name || ' ' || classes_info_rec.last_name || ' – Course Title '|| classes_info_rec.title || ' – Offering Section ' || classes_info_rec.section_code ); END LOOP; END show_class_offerings;
Oracle Academy
27 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
--This function will count the number of classes per course. FUNCTION count_classes_per_course (p_course_id IN classes.course_id%TYPE) RETURN NUMBER IS v_num_classes PLS_INTEGER; BEGIN SELECT COUNT(*) INTO v_num_classes FROM classes WHERE course_id = p_course_id; RETURN v_num_classes; END count_classes_per_course; FUNCTION compute_average_grade (p_class_id IN enrollments.class_id%TYPE) RETURN NUMBER IS v_avg_grade enrollments.final_numeric_grade%TYPE; BEGIN SELECT AVG(final_numeric_grade) INTO v_avg_grade FROM enrollments WHERE class_id = p_class_id; RETURN v_avg_grade; END compute_average_grade; END _tools_package;
Part 4: Create Database Triggers 1. Create the grade_change_history table as follows: CREATE TABLE grade_change_history (time_stamp DATE, stu_id NUMBER(7,0), class_id NUMBER(6,0), enroll_date DATE, old_final_grade CHAR(1), new_final_grade CHAR(1)); 2. Create a row level trigger audit_grade_change to keep a history of all the changes made to students‟ final letter grade. The grade change is recorded every time the FINAL_LETTER_GRADE field is updated in the ENROLLMENTS table.
Oracle Academy
28 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
Every time the trigger is fired, it should insert a record in the GRADE_CHANGE_HISTORY table, recording the old grade and the new grade for each student.
Test your trigger by updating the final_letter_grade for a student, in the ENROLLMENTS table.
Topics Incorporated: Row level DML trigger Tables Used: ENROLLMENTS, GRADE_CHANGE_HISTORY To test: 1. UPDATE enrollments SET final_letter_grade = 'A' WHERE stu_id = 101; 2. SELECT * FROM grade_change_history;
Part 4: Suggested Solution --Trigger Code -----------------CREATE OR REPLACE TRIGGER audit_grade_change AFTER UPDATE OF final_letter_grade ON enrollments FOR EACH ROW BEGIN INSERT INTO grade_change_history(time_stamp,stu_id, class_id, enroll_date, old_final_grade, new_final_grade) VALUES(SYSDATE, :OLD.stu_id, :OLD.class_id, :OLD.enrollment_date, :OLD.final_letter_grade, :NEW.final_letter_grade); END;
Oracle Academy
29 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
Part 5: Create manage_triggers_package The Assignment and Deliverables: 1. Create a package manage_triggers_package that contains two overloaded functions called manage_triggers. The functions are invoked to disable/enable all triggers for a table, or to compile a trigger. Use Native Dynamic SQL to execute the DDL commands programmatically. Code an exception handling block to display a message if the DDL command fails. Use the following guidelines: When the function is called with two parameters: manage_triggers (p_tablename, p_action) a table name to P_TABLENAME parameter. „disable‟ or „enable‟ string to P_ACTION parameter.
When the function is called with only one parameter manage_triggers (p_trigger_name) a trigger name to P_TRIGGER_NAME parameter.
Hints: Use the ALTER TABLE command to disable/enable all triggers of a table programmatically. Use the ALTER TRIGGER command to compile the trigger programmatically. Topics Incorporated: Forward Declaration, Native Dynamic SQL, Overloading, exception handling. Tables used: CLASSES, INSTRUCTORS, COURSES To test: - a table name to P_TABLENAME parameter - „disable‟ or „enable‟ string to P_ACTION parameter. - a trigger name to P_TRIGGER_NAME parameter. BEGIN manage_triggers_package.manage_triggers('ENROLLMENTS', 'ENABLE'); END; BEGIN manage_triggers_package.manage_triggers('audit_grade_change'); END;
Oracle Academy
30 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
Part 5: Suggested Solution
--Create a new package to manage the database triggers. --PACKAGE SPEC CREATE OR REPLACE PACKAGE manage_triggers_package IS PROCEDURE manage_triggers(p_tablename IN VARCHAR2, p_mode IN VARCHAR2); PROCEDURE manage_triggers(p_trigger_name IN VARCHAR2); END manage_triggers_package; --PACKAGE BODY CREATE OR REPLACE PACKAGE BODY manage_triggers_package IS --Overloaded Procedure manage triggers -----------------------------PROCEDURE manage_triggers(p_tablename IN VARCHAR2, p_mode IN VARCHAR2) IS BEGIN EXECUTE IMMEDIATE 'ALTER TABLE '||p_tablename||' '|| p_mode || ' All TRIGGERS'; DBMS_OUTPUT.PUT_LINE(p_tablename || ' Altered.'); EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001,'DDL Command Failed.'); END; --Overloaded Procedure manage triggers -----------------------------PROCEDURE manage_triggers(p_trigger_name IN VARCHAR2) IS BEGIN EXECUTE IMMEDIATE 'ALTER TRIGGER '||p_trigger_name|| ' COMPILE'; DBMS_OUTPUT.PUT_LINE(p_trigger_name|| ' Trigger Compiled. '); EXCEPTION WHEN OTHERS THEN Oracle Academy
31 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
RAISE_APPLICATION_ERROR(-20001,'DDL Command Failed.'); END; END manage_triggers_package;
Oracle Academy
32 Database Programming with PL/SQL Copyright © 2010, Oracle. All rights reserved.
Related Documents 3h463d
Plsql Project Enhancement Semester 2 Teacher 106xv
May 2020 5
Plsql Project Enhancement Semester 2 Student 362d1o
November 2019 73
Plsql Project Semester 2 Student c1x46
December 2019 32
Lathe Machine Project Semester 2 4x356v
October 2019 92
Plsql 72422i
August 2021 0