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

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).

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;

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

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;

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.

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;

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.

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.

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;

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?

Answer: A private function, student_count_privs, and a public procedure, display_student_count, calling the private function.

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;

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

If you were to run the following, what do you expect to see?

SET SERVEROUTPUT ON
Execute manage_students.display_student_count;

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.

Note that if you forget to include a procedure or function in a package specification, it becomes private. On the other hand, if you declare a procedure or function in the package specification, and then you do not define it when you create the body, you receive the following error message: PLS-00323: subprogram or cursor 'procedure_name' is declared in a package specification and must be defined in the package body

43    FUNCTION get_course_descript_private
44      (i_course_no  course.course_no%TYPE)
45      RETURN course.description%TYPE
46    IS
47       v_course_descript course.description%TYPE;
48    BEGIN
49       SELECT description
50         INTO v_course_descript
51         FROM course
52        WHERE course_no = i_course_no;
53       RETURN v_course_descript;
54    EXCEPTION
55       WHEN OTHERS
56       THEN
57          RETURN NULL;
58    END get_course_descript_private;
59  END school_api;

1  CREATE OR REPLACE PACKAGE  school_api as
2     v_current_date DATE;
3     PROCEDURE Discount;
4     FUNCTION new_instructor_id
5        RETURN instructor.instructor_id%TYPE;
6   END school_api;

59  BEGIN
60    SELECT trunc(sysdate, 'DD')
61      INTO v_current_date
62      FROM dual;
63  END school_api;