Chapter 14 Test Your Thinking

In this chapter, you have learned about packages. Here are some projects that will help you test the depth of your understanding.

1)

Add a procedure to the student_api package called remove_student. This procedure accepts a student_id and returns nothing. Based on the student id passed in, it removes the student from the database. If the student does not exist or there is a problem removing the student (such as a foreign key constraint violation), then let the calling program handle it.

2)

Alter remove_student in the student_api package body to accept an additional parameter. This new parameter is a VARCHAR2 and is called p_ri. Make p_ri default to "R." The new parameter may contain a value of "R" or "C." If "R" is received, it represents DELETE RESTRICT and the procedure acts as it does now. If there are enrollments for the student, the delete is disallowed . If a "C" is received, it represents DELETE CASCADE. This functionally means that the remove_student procedure locates all records for the student in all of the CTA tables and removes them from the database before attempting to remove the student from the student table. Decide how to handle the situation where the user passes in a code other than "C" or "R."

The projects in this section are meant to have you utilize all of the skills that you have acquired throughout this chapter. The answers to these projects can be found in Appendix D and at the companion Web site to this book, located at http://authors.phptr.com/rosenzweig3e. Visit the Web site periodically to share and discuss your answers.