Oracle PL/SQL by Example- P15

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
79
lượt xem
22
download

Oracle PL/SQL by Example- P15

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'oracle pl/sql by example- p15', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:
Lưu

Nội dung Text: Oracle PL/SQL by Example- P15

  1. 672 APPENDIX D: Answers to the Try it Yourself Sections Consider the partial output produced by this script: Deleted 1 rows for course 10 section_id: 80 =============================== Deleted 4 rows for course 20 section_id: 81 section_id: 82 section_id: 83 section_id: 84 =============================== Deleted 9 rows for course 25 section_id: 85 section_id: 86 section_id: 87 section_id: 88 section_id: 89 section_id: 90 section_id: 91 section_id: 92 section_id: 93 =============================== Deleted 5 rows for course 100 section_id: 141 section_id: 142 section_id: 143 section_id: 144 section_id: 145 =============================== Deleted 6 rows for course 120 section_id: 146 section_id: 147 section_id: 148 section_id: 149 section_id: 150 section_id: 151 =============================== Deleted 5 rows for course 122 section_id: 152 section_id: 153 section_id: 154 section_id: 155 section_id: 156 =============================== ... PL/SQL procedure successfully completed. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  2. APPENDIX D: Answers to the Try it Yourself Sections 673 Chapter 19,“Procedures” PART 1 1) Write a procedure with no parameters. The procedure should say whether the current day is a weekend or weekday. Additionally, it should tell you the user’s name and the current time. It also should specify how many valid and invalid procedures are in the database. ANSWER: The procedure should look similar to the following: CREATE OR REPLACE PROCEDURE current_status AS v_day_type CHAR(1); v_user VARCHAR2(30); v_valid NUMBER; v_invalid NUMBER; BEGIN SELECT SUBSTR(TO_CHAR(sysdate, 'DAY'), 0, 1) INTO v_day_type FROM dual; IF v_day_type = 'S' THEN DBMS_OUTPUT.PUT_LINE ('Today is a weekend.'); ELSE DBMS_OUTPUT.PUT_LINE ('Today is a weekday.'); END IF; -- DBMS_OUTPUT.PUT_LINE('The time is: '|| TO_CHAR(sysdate, 'HH:MI AM')); -- SELECT user INTO v_user FROM dual; DBMS_OUTPUT.PUT_LINE ('The current user is '||v_user); -- SELECT NVL(COUNT(*), 0) INTO v_valid FROM user_objects WHERE status = 'VALID' AND object_type = 'PROCEDURE'; DBMS_OUTPUT.PUT_LINE ('There are '||v_valid||' valid procedures.'); -- SELECT NVL(COUNT(*), 0) INTO v_invalid FROM user_objects WHERE status = 'INVALID' AND object_type = 'PROCEDURE'; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  3. 674 APPENDIX D: Answers to the Try it Yourself Sections DBMS_OUTPUT.PUT_LINE ('There are '||v_invalid||' invalid procedures.'); END; SET SERVEROUTPUT ON EXEC current_status; 2) Write a procedure that takes in a zip code, city, and state and inserts the values into the zip code table. It should check to see if the zip code is already in the database. If it is, an exception should be raised, and an error message should be displayed. Write an anonymous block that uses the procedure and inserts your zip code. ANSWER: The script should look similar to the following: CREATE OR REPLACE PROCEDURE insert_zip (I_ZIPCODE IN zipcode.zip%TYPE, I_CITY IN zipcode.city%TYPE, I_STATE IN zipcode.state%TYPE) AS v_zipcode zipcode.zip%TYPE; v_city zipcode.city%TYPE; v_state zipcode.state%TYPE; v_dummy zipcode.zip%TYPE; BEGIN v_zipcode := i_zipcode; v_city := i_city; v_state := i_state; -- SELECT zip INTO v_dummy FROM zipcode WHERE zip = v_zipcode; -- DBMS_OUTPUT.PUT_LINE('The zipcode '||v_zipcode|| ' is already in the database and cannot be'|| ' reinserted.'); -- EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO ZIPCODE VALUES (v_zipcode, v_city, v_state, user, sysdate, user, sysdate); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('There was an unknown error '|| 'in insert_zip.'); END; SET SERVEROUTPUT ON BEGIN insert_zip (10035, 'No Where', 'ZZ'); END; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  4. APPENDIX D: Answers to the Try it Yourself Sections 675 BEGIN insert_zip (99999, 'No Where', 'ZZ'); END; ROLLBACK; PART 2 1) Create a stored procedure based on the script ch17_1c.sql, version 3.0, created in Lab 17.1 of Chapter 17. The procedure should accept two parameters to hold a table name and an ID and should return six parameters with first name, last name, street, city, state, and zip code information. ANSWER: The procedure should look similar to the following. Changes are shown in bold. CREATE OR REPLACE PROCEDURE get_name_address (table_name_in IN VARCHAR2 ,id_in IN NUMBER ,first_name_out OUT VARCHAR2 ,last_name_out OUT VARCHAR2 ,street_out OUT VARCHAR2 ,city_out OUT VARCHAR2 ,state_out OUT VARCHAR2 ,zip_out OUT VARCHAR2) AS sql_stmt VARCHAR2(200); BEGIN sql_stmt := 'SELECT a.first_name, a.last_name, a.street_address'|| ' ,b.city, b.state, b.zip' || ' FROM '||table_name_in||' a, zipcode b' || ' WHERE a.zip = b.zip' || ' AND '||table_name_in||'_id = :1'; EXECUTE IMMEDIATE sql_stmt INTO first_name_out, last_name_out, street_out, city_out, state_out, zip_out USING id_in; END get_name_address; This procedure contains two IN parameters whose values are used by the dynamic SQL statement and six OUT parameters that hold data returned by the SELECT statement. After it is created, this procedure can be tested with the following PL/SQL block: SET SERVEROUTPUT ON DECLARE v_table_name VARCHAR2(20) := '&sv_table_name'; v_id NUMBER := &sv_id; v_first_name VARCHAR2(25); v_last_name VARCHAR2(25); v_street VARCHAR2(50); v_city VARCHAR2(25); v_state VARCHAR2(2); v_zip VARCHAR2(5); Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  5. 676 APPENDIX D: Answers to the Try it Yourself Sections BEGIN get_name_address (v_table_name, v_id, v_first_name, v_last_name, v_street, v_city, v_state, v_zip); DBMS_OUTPUT.PUT_LINE ('First Name: '||v_first_name); DBMS_OUTPUT.PUT_LINE ('Last Name: '||v_last_name); DBMS_OUTPUT.PUT_LINE ('Street: '||v_street); DBMS_OUTPUT.PUT_LINE ('City: '||v_city); DBMS_OUTPUT.PUT_LINE ('State: '||v_state); DBMS_OUTPUT.PUT_LINE ('Zip Code: '||v_zip); END; When run, this script produces the following output. The first run is against the STUDENT table, and the second run is against the INSTRUCTOR table. Enter value for sv_table_name: student old 2: v_table_name VARCHAR2(20) := '&sv_table_name'; new 2: v_table_name VARCHAR2(20) := 'student'; Enter value for sv_id: 105 old 3: v_id NUMBER := &sv_id; new 3: v_id NUMBER := 105; First Name: Angel Last Name: Moskowitz Street: 320 John St. City: Ft. Lee State: NJ Zip Code: 07024 PL/SQL procedure successfully completed. Enter value for sv_table_name: instructor old 2: v_table_name VARCHAR2(20) := '&sv_table_name'; new 2: v_table_name VARCHAR2(20) := 'instructor'; Enter value for sv_id: 105 old 3: v_id NUMBER := &sv_id; new 3: v_id NUMBER := 105; First Name: Anita Last Name: Morris Street: 34 Maiden Lane City: New York State: NY Zip Code: 10015 PL/SQL procedure successfully completed. 2) Modify the procedure you just created. Instead of using six parameters to hold name and address information, the procedure should return a user-defined record that contains six fields that hold name and address information. Note: You may want to create a package in which you define a record type. This record may be used later, such as when the procedure is invoked in a PL/SQL block. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  6. APPENDIX D: Answers to the Try it Yourself Sections 677 ANSWER: The package should look similar to the following. Changes are shown in bold. CREATE OR REPLACE PACKAGE dynamic_sql_pkg AS -- Create user-defined record type TYPE name_addr_rec_type IS RECORD (first_name VARCHAR2(25), last_name VARCHAR2(25), street VARCHAR2(50), city VARCHAR2(25), state VARCHAR2(2), zip VARCHAR2(5)); PROCEDURE get_name_address (table_name_in IN VARCHAR2 ,id_in IN NUMBER ,name_addr_rec OUT name_addr_rec_type); END dynamic_sql_pkg; / CREATE OR REPLACE PACKAGE BODY dynamic_sql_pkg AS PROCEDURE get_name_address (table_name_in IN VARCHAR2 ,id_in IN NUMBER ,name_addr_rec OUT name_addr_rec_type) IS sql_stmt VARCHAR2(200); BEGIN sql_stmt := 'SELECT a.first_name, a.last_name, a.street_address'|| ' ,b.city, b.state, b.zip' || ' FROM '||table_name_in||' a, zipcode b' || ' WHERE a.zip = b.zip' || ' AND '||table_name_in||'_id = :1'; EXECUTE IMMEDIATE sql_stmt INTO name_addr_rec USING id_in; END get_name_address; END dynamic_sql_pkg; / In this package specification, you declare a user-defined record type. The procedure uses this record type for its OUT parameter, name_addr_rec. After the package is created, its procedure can be tested with the following PL/SQL block (changes are shown in bold): SET SERVEROUTPUT ON DECLARE v_table_name VARCHAR2(20) := '&sv_table_name'; v_id NUMBER := &sv_id; name_addr_rec DYNAMIC_SQL_PKG.NAME_ADDR_REC_TYPE; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  7. 678 APPENDIX D: Answers to the Try it Yourself Sections BEGIN dynamic_sql_pkg.get_name_address (v_table_name, v_id, name_addr_rec); DBMS_OUTPUT.PUT_LINE ('First Name: '||name_addr_rec.first_name); DBMS_OUTPUT.PUT_LINE ('Last Name: '||name_addr_rec.last_name); DBMS_OUTPUT.PUT_LINE ('Street: '||name_addr_rec.street); DBMS_OUTPUT.PUT_LINE ('City: '||name_addr_rec.city); DBMS_OUTPUT.PUT_LINE ('State: '||name_addr_rec.state); DBMS_OUTPUT.PUT_LINE ('Zip Code: '||name_addr_rec.zip); END; Notice that instead of declaring six variables, you declare one variable of the user-defined record type, name_addr_rec_type. Because this record type is defined in the package DYNAMIC_SQL_PKG, the name of the record type is prefixed with the name of the package. Similarly, the name of the package is added to the procedure call statement. When run, this script produces the following output. The first output is against the STUDENT table, and the second output is against the INSTRUCTOR table. Enter value for sv_table_name: student old 2: v_table_name VARCHAR2(20) := '&sv_table_name'; new 2: v_table_name VARCHAR2(20) := 'student'; Enter value for sv_id: 105 old 3: v_id NUMBER := &sv_id; new 3: v_id NUMBER := 105; First Name: Angel Last Name: Moskowitz Street: 320 John St. City: Ft. Lee State: NJ Zip Code: 07024 PL/SQL procedure successfully completed. Enter value for sv_table_name: instructor old 2: v_table_name VARCHAR2(20) := '&sv_table_name'; new 2: v_table_name VARCHAR2(20) := 'instructor'; Enter value for sv_id: 105 old 3: v_id NUMBER := &sv_id; new 3: v_id NUMBER := 105; First Name: Anita Last Name: Morris Street: 34 Maiden Lane City: New York State: NY Zip Code: 10015 PL/SQL procedure successfully completed. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  8. APPENDIX D: Answers to the Try it Yourself Sections 679 Chapter 20,“Functions” 1) Write a stored function called new_student_id that takes in no parameters and returns a student.student_id%TYPE. The value returned will be used when inserting a new student into the CTA application. It will be derived by using the formula student_id_seq. NEXTVAL. ANSWER: The function should look similar to the following: CREATE OR REPLACE FUNCTION new_student_id RETURN student.student_id%TYPE AS v_student_id student.student_id%TYPE; BEGIN SELECT student_id_seq.NEXTVAL INTO v_student_id FROM dual; RETURN(v_student_id); END; 2) Write a stored function called zip_does_not_exist that takes in a zipcode. zip%TYPE and returns a Boolean. The function will return TRUE if the zip code passed into it does not exist. It will return a FALSE if the zip code does exist. Hint: Here’s an example of how this might be used: DECLARE cons_zip CONSTANT zipcode.zip%TYPE := '&sv_zipcode'; e_zipcode_is_not_valid EXCEPTION; BEGIN IF zipcode_does_not_exist(cons_zip) THEN RAISE e_zipcode_is_not_valid; ELSE -- An insert of an instructor's record which -- makes use of the checked zipcode might go here. NULL; END IF; EXCEPTION WHEN e_zipcode_is_not_valid THEN RAISE_APPLICATION_ERROR (-20003, 'Could not find zipcode '||cons_zip||'.'); END; ANSWER: The function should look similar to the following: CREATE OR REPLACE FUNCTION zipcode_does_not_exist (i_zipcode IN zipcode.zip%TYPE) RETURN BOOLEAN AS v_dummy char(1); BEGIN SELECT NULL INTO v_dummy Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  9. 680 APPENDIX D: Answers to the Try it Yourself Sections FROM zipcode WHERE zip = i_zipcode; -- Meaning the zipcode does exit RETURN FALSE; EXCEPTION WHEN OTHERS THEN -- The select statement above will cause an exception -- to be raised if the zipcode is not in the database. RETURN TRUE; END zipcode_does_not_exist; 3) Create a new function. For a given instructor, determine how many sections he or she is teaching. If the number is greater than or equal to 3, return a message saying that the instructor needs a vacation. Otherwise, return a message saying how many sections this instructor is teaching. ANSWER: The function should look similar to the following: CREATE OR REPLACE FUNCTION instructor_status (i_first_name IN instructor.first_name%TYPE, i_last_name IN instructor.last_name%TYPE) RETURN VARCHAR2 AS v_instructor_id instructor.instructor_id%TYPE; v_section_count NUMBER; v_status VARCHAR2(100); BEGIN SELECT instructor_id INTO v_instructor_id FROM instructor WHERE first_name = i_first_name AND last_name = i_last_name; SELECT COUNT(*) INTO v_section_count FROM section WHERE instructor_id = v_instructor_id; IF v_section_count >= 3 THEN v_status := 'The instructor '||i_first_name||' '|| i_last_name||' is teaching '||v_section_count|| ' and needs a vaction.'; ELSE v_status := 'The instructor '||i_first_name||' '|| i_last_name||' is teaching '||v_section_count|| ' courses.'; END IF; RETURN v_status; EXCEPTION WHEN NO_DATA_FOUND THEN Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  10. APPENDIX D: Answers to the Try it Yourself Sections 681 -- Note that either of the SELECT statements can raise -- this exception v_status := 'The instructor '||i_first_name||' '|| i_last_name||' is not shown to be teaching'|| ' any courses.'; RETURN v_status; WHEN OTHERS THEN v_status := 'There has been in an error in the function.'; RETURN v_status; END; Test the function as follows: SELECT instructor_status(first_name, last_name) FROM instructor; / Chapter 21,“Packages” 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 if a problem occurs while removing the student (such as a foreign key constraint violation), let the calling program handle it. ANSWER: The package should be 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, Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  11. 682 APPENDIX D: Answers to the Try it Yourself Sections 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); END student_api; / 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; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  12. APPENDIX D: Answers to the Try it Yourself Sections 683 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.'); Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  13. 684 APPENDIX D: Answers to the Try it Yourself Sections 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 (p_studid IN student.student_id%TYPE) IS BEGIN DELETE FROM STUDENT WHERE student_id = p_studid; END; BEGIN SELECT trunc(sysdate, 'DD') INTO v_current_date FROM dual; END student_api; / Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  14. APPENDIX D: Answers to the Try it Yourself Sections 685 2) Alter remove_student in the student_api package body to accept an additional param- eter. This new parameter should be a VARCHAR2 and 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 the tables. It removes them from the database before attempting to remove the student from the student table. Decide how to handle the situation when the user passes in a code other than C or R. ANSWER: The package 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'); END student_api; / 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 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  15. 686 APPENDIX D: Answers to the Try it Yourself Sections 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; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  16. APPENDIX D: Answers to the Try it Yourself Sections 687 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) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  17. 688 APPENDIX D: Answers to the Try it Yourself Sections 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 the user an -- option of cascade delete or restrict delete for -- the given student's 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 -- 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; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  18. APPENDIX D: Answers to the Try it Yourself Sections 689 -- The rownum set to 1 prevents the SELECT -- INTO statement raise to_many_rows -- exception. -- If there is at least one row in the enrollment -- table with a 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 enters "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.'); Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  19. 690 APPENDIX D: Answers to the Try it Yourself Sections END remove_student; BEGIN SELECT trunc(sysdate, 'DD') INTO v_current_date FROM dual; END student_api; Chapter 22,“Stored Code” 1) Add a function to the student_api package specification called get_course_ descript. The caller takes a course.cnumber%TYPE parameter, and it returns a course.description%TYPE. ANSWER: The package 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; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  20. APPENDIX D: Answers to the Try it Yourself Sections 691 2) Create a function in the student_api package body called get_course_description. A caller passes in a course number, and it returns the course description. Instead of searching for the description itself, it makes a call to get_course_descript_private. It passes its course number to get_course_descript_private. It passes back to the caller the description it gets back from get_course_descript_private. ANSWER: The package body 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; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Đồng bộ tài khoản