Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources] نسخه متنی

Answer: Your answer should look similar to the following:

CREATE OR REPLACE PACKAGE student_api AS
v_current_date DATE;
PROCEDURE discount;
FUNCTION new_instructor_id
RETURN instructor.instructor_id%TYPE;
FUNCTION total_cost_for_student
(p_student_id IN student.student_id%TYPE)
RETURN course.cost%TYPE;
PRAGMA RESTRICT_REFERENCES
(total_cost_for_student, WNDS, WNPS, RNPS);
PROCEDURE get_student_info
(p_student_id   IN  student.student_id%TYPE,
p_last_name    OUT student.last_name%TYPE,
p_first_name   OUT student.first_name%TYPE,
p_zip          OUT student.zip%TYPE,
p_return_code  OUT NUMBER);
PROCEDURE get_student_info
(p_last_name   IN student.last_name%TYPE,
p_first_name  IN student.first_name%TYPE,
p_student_id  OUT student.student_id%TYPE,
p_zip         OUT student.zip%TYPE,
p_return_code OUT NUMBER);
PROCEDURE remove_student
(p_studid IN student.student_id%TYPE,
p_ri     IN VARCHAR2 DEFAULT 'R');
FUNCTION get_course_descript
(p_cnumber course.course_no%TYPE)
RETURN course.description%TYPE;
END student_api;

Answer: Your answer should look similar to the following:

CREATE OR REPLACE PACKAGE BODY student_api AS
PROCEDURE discount IS
CURSOR c_group_discount IS
SELECT distinct s.course_no, c.description
FROM section s, enrollment e, course c
WHERE s.section_id = e.section_id
GROUP BY s.course_no, c.description,
e.section_id, s.section_id
HAVING COUNT(*) >=8;
BEGIN
FOR r_group_discount IN c_group_discount LOOP
UPDATE course
SET cost = cost * .95
WHERE course_no = r_group_discount.course_no;
DBMS_OUTPUT.PUT_LINE
('A 5% discount has been given to'||
r_group_discount.course_no||' '||
r_group_discount.description);
END LOOP;
END discount;
FUNCTION new_instructor_id
RETURN instructor.instructor_id%TYPE
IS
v_new_instid instructor.instructor_id%TYPE;
BEGIN
SELECT INSTRUCTOR_ID_SEQ.NEXTVAL
INTO v_new_instid
FROM dual;
RETURN v_new_instid;
EXCEPTION
WHEN OTHERS THEN
DECLARE
v_sqlerrm VARCHAR2(250) :=
SUBSTR(SQLERRM,1,250);
BEGIN
RAISE_APPLICATION_ERROR
(-20003, 'Error in instructor_id: '||
v_sqlerrm);
END;
END new_instructor_id;
FUNCTION get_course_descript_private
(p_course_no  course.course_no%TYPE)
RETURN course.description%TYPE
IS
v_course_descript course.description%TYPE;
BEGIN
SELECT description
INTO v_course_descript
FROM course
WHERE course_no = p_course_no;
RETURN v_course_descript;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END get_course_descript_private;
FUNCTION total_cost_for_student
(p_student_id IN student.student_id%TYPE)
RETURN course.cost%TYPE
IS
v_cost course.cost%TYPE;
BEGIN
SELECT sum(cost)
INTO v_cost
FROM course c, section s, enrollment e
WHERE c.course_no = c.course_no
AND e.section_id = s.section_id
AND e.student_id = p_student_id;
RETURN v_cost;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END total_cost_for_student;
PROCEDURE get_student_info
(p_student_id   IN  student.student_id%TYPE,
p_last_name    OUT student.last_name%TYPE,
p_first_name   OUT student.first_name%TYPE,
p_zip          OUT student.zip%TYPE,
p_return_code  OUT NUMBER)
IS
BEGIN
SELECT last_name, first_name, zip
INTO p_last_name, p_first_name, p_zip
FROM student
WHERE student.student_id = p_student_id;
p_return_code := 0;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('Student ID is not valid.');
p_return_code := -100;
p_last_name := NULL;
p_first_name := NULL;
p_zip := NULL;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE
('Error in procedure get_student_info');
END get_student_info;
PROCEDURE get_student_info
(p_last_name   IN student.last_name%TYPE,
p_first_name  IN student.first_name%TYPE,
p_student_id  OUT student.student_id%TYPE,
p_zip         OUT student.zip%TYPE,
p_return_code OUT NUMBER)
IS
BEGIN
SELECT student_id, zip
INTO p_student_id, p_zip
FROM student
WHERE UPPER(last_name)  = UPPER(p_last_name)
AND UPPER(first_name) = UPPER(p_first_name);
p_return_code := 0;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE
('Student name is not valid.');
p_return_code := -100;
p_student_id := NULL;
p_zip := NULL;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE
('Error in procedure get_student_info');
END get_student_info;
PROCEDURE remove_student
-- the parameters student_id and p_ri give user an
-- option of cascade delete or restrict delete for
-- the given students records
(p_studid IN student.student_id%TYPE,
p_ri     IN VARCHAR2 DEFAULT 'R')
IS
-- declare exceptions for use in procedure
enrollment_present EXCEPTION;
bad_pri EXCEPTION;
BEGIN
-- the R value is for restrict delete option
IF p_ri = 'R' THEN
DECLARE
-- a variable is needed to test if the student
-- is in the enrollment table
v_dummy CHAR(1);
BEGIN
-- This is a standard existence check
-- If v_dummy is assigned a value via the
-- SELECT INTO, the exception
-- enrollment_present will be raised
-- If the v_dummy is not assigned a value, the
-- exception no_data_found will be raised
SELECT NULL
INTO v_dummy
FROM enrollment e
WHERE e.student_id = p_studid
AND ROWNUM = 1;
-- The rownum set to 1 prevents the SELECT
-- INTO statement raise to_many_rows
-- exception
-- If there is at least one row in enrollment
-- table with corresponding student_id, the
-- restrict delete parameter will disallow
-- the deletion of the student by raising
-- the enrollment_present exception
RAISE enrollment_present;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- The no_data_found exception is raised
-- when there are no students found in the
-- enrollment table
-- Since the p_ri indicates a restrict
-- delete user choice the delete operation
-- is permitted
DELETE FROM student
WHERE student_id = p_studid;
END;
-- when the user enter "C" for the p_ri
-- he/she indicates a cascade delete choice
ELSIF p_ri = 'C' THEN
-- delete the student from the enrollment and
-- grade tables
DELETE FROM enrollment
WHERE student_id = p_studid;
DELETE FROM grade
WHERE student_id = p_studid;
-- delete from student table only after
-- corresponding records have been removed from
-- the other tables because the student table is
-- the parent table
DELETE
FROM student
WHERE student_id = p_studid;
ELSE
RAISE bad_pri;
END IF;
EXCEPTION
WHEN bad_pri THEN
RAISE_APPLICATION_ERROR
(-20231, 'An incorrect p_ri value was '||
'entered. The remove_student procedure can '||
'only accept a C or R for the p_ri 'parameter.');
WHEN enrollment_present THEN
RAISE_APPLICATION_ERROR
(-20239, 'The student with ID'||p_studid||
' exists in the enrollment table thus records'||
' will not be removed.');
END remove_student;
FUNCTION get_course_descript
(p_cnumber course.course_no%TYPE)
RETURN course.description%TYPE
IS
BEGIN
RETURN get_course_descript_private(p_cnumber);
END get_course_descript;
BEGIN
SELECT trunc(sysdate, 'DD')
INTO v_current_date
FROM dual;
END student_api;

Answer: Your answer should look similar to the following:

CREATE OR REPLACE PACKAGE student_api AS
v_current_date DATE;
PROCEDURE discount;
FUNCTION new_instructor_id
RETURN instructor.instructor_id%TYPE;
FUNCTION total_cost_for_student
(p_student_id IN student.student_id%TYPE)
RETURN course.cost%TYPE;
PRAGMA RESTRICT_REFERENCES
(total_cost_for_student, WNDS, WNPS, RNPS);
PROCEDURE get_student_info
(p_student_id   IN  student.student_id%TYPE,
p_last_name    OUT student.last_name%TYPE,
p_first_name   OUT student.first_name%TYPE,
p_zip          OUT student.zip%TYPE,
p_return_code  OUT NUMBER);
PROCEDURE get_student_info
(p_last_name   IN student.last_name%TYPE,
p_first_name  IN student.first_name%TYPE,
p_student_id  OUT student.student_id%TYPE,
p_zip         OUT student.zip%TYPE,
p_return_code OUT NUMBER);
PROCEDURE remove_student
(p_studid IN student.student_id%TYPE,
p_ri     IN VARCHAR2 DEFAULT 'R');
FUNCTION get_course_descript
(p_cnumber course.course_no%TYPE)
RETURN course.description%TYPE;
PRAGMA RESTRICT_REFERENCES
(get_course_descript,WNDS, WNPS, RNPS);
END student_api;