| A1: | Answer: The specification for the package manage_students has been compiled into the database. The specification for the package now indicates that there is one procedure and one function. The procedure find_sname requires one IN parameter, which is the student ID, and it returns two OUT parameters, one being the student's first name and the other being the student's last name. The function id_is_good takes in a single parameter of a student ID and returns a Boolean (true or false). Although the body has not yet been entered into the database, the package is still available for other applications. For example, if you included a call to one of these procedures in another stored procedure, that procedure would compile (but would not execute). | ||
| b) | If the following script was run from a SQL*PLUS session, what would the result be and why? -- ch14_2a.sql SET SERVEROUTPUT ON DECLARE v_first_name student.first_name%TYPE; v_last_name student.last_name%TYPE; BEGIN manage_students.find_sname (125, v_first_name, v_last_name); DBMS_OUTPUT.PUT_LINE(v_first_name||' '||v_last_name); END; | ||
| A2: | Answer: The procedure cannot run because only the specification for the procedure exists in the database, not the body. The SQL*Plus session returns the following: ERROR at line 1: ORA-04068: existing state of packages has been discarded ORA-04067: not executed, package body "STUDENT.MANAGE_STUDENTS" does not exist ORA-06508: PL/SQL: could not find program unit being called ORA-06512: at line 5 | ||
| Chapter 12 and the function new_instructor_id from Chapter 13. | |||
| A3: | Answer: 1 CREATE OR REPLACE PACKAGE school_api as 2 PROCEDURE discount_cost; 3 FUNCTION new_instructor_id 4 RETURN instructor.instructor_id%TYPE; 5 END school_api; | ||
| A1: | Answer: The package body manage_students is compiled into the database. The package contains the procedure manage_students.find_sname, which accepts the parameter student_id and returns the student's last_name and first_name from the Student table. |
| A2: | Answer:
-- ch14_5a.sql
1 CREATE OR REPLACE PACKAGE BODY school_api AS
2 PROCEDURE discount_cost
3 IS
4 CURSOR c_group_discount
5 IS
6 SELECT distinct s.course_no, c.description
7 FROM section s, enrollment e, course c
8 WHERE s.section_id = e.section_id
9 GROUP BY s.course_no, c.description,
10 e.section_id, s.section_id
11 HAVING COUNT(*) >=8;
12 BEGIN
14 FOR r_group_discount IN c_group_discount
14 LOOP
15 UPDATE course
16 SET cost = cost * .95
17 WHERE course_no = r_group_discount.course_no;
18 DBMS_OUTPUT.PUT_LINE
19 ('A 5% discount has been given to'
20 ||r_group_discount.course_no||'
21 '||r_group_discount.description);
22 END LOOP;
23 END discount_cost;
24 FUNCTION new_instructor_id
25 RETURN instructor.instructor_id%TYPE
26 IS
27 v_new_instid instructor.instructor_id%TYPE;
28 BEGIN
29 SELECT INSTRUCTOR_ID_SEQ.NEXTVAL
30 INTO v_new_instid
31 FROM dual;
32 RETURN v_new_instid;
33 EXCEPTION
34 WHEN OTHERS
35 THEN
36 DECLARE
37 v_sqlerrm VARCHAR2(250) :=
SUBSTR(SQLERRM,1,250);
38 BEGIN
39 RAISE_APPLICATION_ERROR(-20003,
40 'Error in instructor_id: '||v_sqlerrm);
41 END;
42 END new_instructor_id;
43 END school_api;
|
| A1: | Answer: This is a correct PL/SQL block for running the function and the procedure in the package manage_students . If an existing student_id is entered, then the name of the student is displayed. If the id is not valid, then the error message is displayed. |
| A2: | Answer: Initially the following appears:
Enter value for v_id:
If you enter "145," then you see:
old 5: IF manage_students.id_is_good(&v_id)
new 5: IF manage_students.id_is_good(145)
old 7: manage_students.find_sname(&&v_id, v_first_name,
new 7: manage_students.find_sname(145, v_first_name,
old 9: DBMS_OUTPUT.PUT_LINE('Student No. '||&&v_id||
' is '
new 9: DBMS_OUTPUT.PUT_LINE('Student No. '||145||' is '
old 14: ('Student ID: '||&&v_id||' is not in the
database.');
new 14: ('Student ID: '||145||' is not in the
database.');
Student No. 145 is Lefkowitz, Paul
PL/SQL procedure successfully completed.
|
The function id_is_good returns TRUE for an existing student_id such as 145. The control then flows to the first part of the IF statement and the procedure manage_students.find_sname finds the first and last name for student_id 145, which happens to be Lefkowitz, Paul.
| A3: | Answer:
SET SERVEROUTPUT ON
DECLARE
V_instructor_id instructor.instructor_id%TYPE;
BEGIN
cta_api.Discount;
v_instructor_id := cta_api.new_instructor_id;
DBMS_OUTPUT.PUT_LINE
('The new id is: '||v_instructor_id);
END;
|
| a) | Replace the last lines of the manage_students package specification with the following and recompile the package specification: 11 PROCEDURE display_student_count; 12 END manage_students; Replace the end of the body with the following and recompile the package body:
37 FUNCTION student_count_priv
38 RETURN NUMBER
39 IS
40 v_count NUMBER;
41 BEGIN
42 select count(*)
43 into v_count
44 from student;
45 return v_count;
46 EXCEPTION
47 WHEN OTHERS
48 THEN
49 return(0);
50 END student_count_priv;
51 PROCEDURE display_student_count
52 is
53 v_count NUMBER;
54 BEGIN
55 v_count := student_count_priv;
56 DBMS_OUTPUT.PUT_LINE
57 ('There are '||v_count||' students.');
58 END display_student_count;
59 END manage_students;
What have you added to the manage_student package? |
| A1: | Answer: A private function, student_count_privs, and a public procedure, display_student_count, calling the private function. |
| b) | If you run the following from your SQL*PLUS session, what are the results? DECLARE V_count NUMBER; BEGIN V_count := Manage_students.student_count_priv; DBMS_OUTPUT.PUT_LINE(v_count); END; |
| A2: | Answer: Since the private function, student_count_privs, cannot be called from outside the package, you receive an error message as follows: ERROR at line 1: ORA-06550: line 4, column 31: PLS-00302: component 'STUDENT_COUNT_PRIV' must be declared ORA-06550: line 4, column 3: PL/SQL: Statement ignored |
It appears as if the private function does not exist. This is important to keep in mind. You can see this can be useful when you are writing PL/SQL packages used by other developers. In order to simplify the package for them, they only need to see the package specification. This way they know what is being passed into the procedures and functions and what is being returned. They do not need to see the inner workings. If a number of procedures make use of the same logic, it may make more sense to put them into a private function called by the procedures.
| c) | If you were to run the following, what do you expect to see? SET SERVEROUTPUT ON Execute manage_students.display_student_count; | |||||||
| A3: | Answer: This is a valid method of running a procedure. A line is displayed indicating the number of students in the database. Note that the procedure in the package manage_ students is using the private function student_count_priv to retrieve the student count.
|