本章内容：
1. 创建包（创建包规范、包体、调用已存储的包、创建私有对象）
2. 游标变量
3. 用其他程序扩展包
4. 包的实例化和初始化
5. SERIALLY_RESABLE包
代码入下：
1. 创建包规范
SQL> -- For Example ch21_15.sql
SQL> CREATE OR REPLACE PACKAGE MANAGE_GRADES AS
2 -- Cursor to loop through all grade types for a given section.
3 CURSOR c_grade_type
4 (pc_section_id section.section_id%TYPE,
5 PC_student_ID student.student_id%TYPE)
6 IS
7 SELECT GRADE_TYPE_CODE,
8 NUMBER_PER_SECTION,
9 PERCENT_OF_FINAL_GRADE,
10 DROP_LOWEST
11 FROM grade_Type_weight
12 WHERE section_id = pc_section_id
13 AND section_id IN (SELECT section_id
14 FROM grade
15 WHERE student_id = pc_student_id);
16 -- Cursor to loop through all grades for a given student
17 -- in a given section.
18 CURSOR c_grades
19 (p_grade_type_code
20 grade_Type_weight.grade_type_code%TYPE,
21 pc_student_id student.student_id%TYPE,
22 pc_section_id section.section_id%TYPE) IS
23 SELECT grade_type_code,grade_code_occurrence,
24 numeric_grade
25 FROM grade
26 WHERE student_id = pc_student_id
27 AND section_id = pc_section_id
28 AND grade_type_code = p_grade_type_code;
29 -- Function to calcuation a students final grade
30 -- in one section
31 Procedure final_grade
32 (P_student_id IN student.student_id%type,
33 P_section_id IN section.section_id%TYPE,
34 P_Final_grade OUT enrollment.final_grade%TYPE,
35 P_Exit_Code OUT CHAR);
36 END MANAGE_GRADES;
37 /
Package created.2. 创建包体
SQL> -- For Example ch21_17.sql
SQL> CREATE OR REPLACE PACKAGE BODY MANAGE_GRADES AS
2 Procedure final_grade
3 (P_student_id IN student.student_id%type,
4 P_section_id IN section.section_id%TYPE,
5 P_Final_grade OUT enrollment.final_grade%TYPE,
6 P_Exit_Code OUT CHAR)
7 IS
8 v_student_id student.student_id%TYPE;
9 v_section_id section.section_id%TYPE;
10 v_grade_type_code grade_type_weight.grade_type_code%TYPE;
11 v_grade_percent NUMBER;
12 v_final_grade NUMBER;
13 v_grade_count NUMBER;
14 v_lowest_grade NUMBER;
15 v_exit_code CHAR(1) := 'S';
16 v_no_rows1 CHAR(1) := 'N';
17 v_no_rows2 CHAR(1) := 'N';
18 e_no_grade EXCEPTION;
19 BEGIN
20 v_section_id := p_section_id;
21 v_student_id := p_student_id;
22 -- Start loop of grade types for the section.
23 FOR r_grade in c_grade_type(v_section_id, v_student_id)
24 LOOP
25 -- Since cursor is open it has a result
26 -- set, change indicator.
27 v_no_rows1 := 'Y';
28 -- To hold the number of grades per section,
29 -- reset to 0 before detailed cursor loops
30 v_grade_count := 0;
31 v_grade_type_code := r_grade.GRADE_TYPE_CODE;
32 -- Variable to hold the lowest grade.
33 -- 500 will not be the lowest grade.
34 v_lowest_grade := 500;
35 -- Determine what to multiply a grade by to
36 -- compute final grade, must take into consideration
37 -- if the drop lowest grade indicator is Y
38 SELECT (r_grade.percent_of_final_grade /
39 DECODE(r_grade.drop_lowest, 'Y',
40 (r_grade.number_per_section - 1),
41 r_grade.number_per_section
42 ))* 0.01
43 INTO v_grade_percent
44 FROM dual;
45 -- Open cursor of detailed grade for a student in a
46 -- given section.
47 FOR r_detail in c_grades(v_grade_type_code,
48 v_student_id, v_section_id) LOOP
49 -- Since cursor is open it has a result
50 -- set, change indicator.
51 v_no_rows2 := 'Y';
52 v_grade_count := v_grade_count + 1;
53 -- Handle the situation where there are more
54 -- entries for grades of a given grade type
55 -- than there should be for that section.
56 If v_grade_count > r_grade.number_per_section THEN
57 v_exit_code := 'T';
58 raise e_no_grade;
59 END IF;
60 -- If drop lowest flag is Y determine which is lowest
61 -- grade to drop
62 IF r_grade.drop_lowest = 'Y' THEN
63 IF nvl(v_lowest_grade, 0) >=
64 r_detail.numeric_grade
65 THEN
66 v_lowest_grade := r_detail.numeric_grade;
67 END IF;
68 END IF;
69 -- Increment the final grade with percentage of current
70 -- grade in the detail loop.
71 v_final_grade := nvl(v_final_grade, 0) +
72 (r_detail.numeric_grade * v_grade_percent);
73 END LOOP;
74 -- Once detailed loop is finished, if the number of grades
75 -- for a given student for a given grade type and section
76 -- is less than the required amount, raise an exception.
77 IF v_grade_count < r_grade.NUMBER_PER_SECTION THEN
78 v_exit_code := 'I';
79 raise e_no_grade;
80 END IF;
81 -- If the drop lowest flag was Y then you need to take
82 -- the lowest grade out of the final grade, it was not
83 -- known when it was added which was the lowest grade
84 -- to drop until all grades were examined.
85 IF r_grade.drop_lowest = 'Y' THEN
86 v_final_grade := nvl(v_final_grade, 0) -
87 (v_lowest_grade * v_grade_percent);
88 END IF;
89 END LOOP;
90 -- If either cursor had no rows then there is an error.
91 IF v_no_rows1 = 'N' OR v_no_rows2 = 'N' THEN
92 v_exit_code := 'N';
93 raise e_no_grade;
94 END IF;
95 P_final_grade := v_final_grade;
96 P_exit_code := v_exit_code;
97 EXCEPTION
98 WHEN e_no_grade THEN
99 P_final_grade := null;
100 P_exit_code := v_exit_code;
101 WHEN OTHERS THEN
102 P_final_grade := null;
103 P_exit_code := 'E';
104 END final_grade;
105 END MANAGE_GRADES;
106 /
3. 查看包
SQL> desc manage_grades
PROCEDURE FINAL_GRADE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_STUDENT_ID NUMBER(8) IN
P_SECTION_ID NUMBER(8) IN
P_FINAL_GRADE NUMBER(3) OUT
P_EXIT_CODE CHAR OUT4. 测试
SQL> set serverout on
SQL> DECLARE
2 v_student_id student.student_id%TYPE := &sv_student_id;
3 v_section_id section.section_id%TYPE := &sv_section_id;
4 v_final_grade enrollment.final_grade%TYPE;
5 v_exit_code CHAR;
6 BEGIN
7 manage_grades.final_grade(v_student_id, v_section_id,
8 v_final_grade, v_exit_code);
9 DBMS_OUTPUT.PUT_LINE('The Final Grade is '||v_final_grade);
10 DBMS_OUTPUT.PUT_LINE('The Exit Code is '||v_exit_code);
11 END;
12 /
Enter value for sv_student_id: 102
old 2: v_student_id student.student_id%TYPE := &sv_student_id;
new 2: v_student_id student.student_id%TYPE := 102;
Enter value for sv_section_id: 89
old 3: v_section_id section.section_id%TYPE := &sv_section_id;
new 3: v_section_id section.section_id%TYPE := 89;
The Final Grade is 92
The Exit Code is S5. 更新包头
SQL> -- For Example ch21_19.sql
SQL> CREATE OR REPLACE PACKAGE MANAGE_GRADES AS
2 -- Cursor to loop through all grade types for a given section.
3 CURSOR c_grade_type
4 (pc_section_id section.section_id%TYPE,
5 PC_student_ID student.student_id%TYPE)
6 IS
7 SELECT GRADE_TYPE_CODE,
8 NUMBER_PER_SECTION,
9 PERCENT_OF_FINAL_GRADE,
10 DROP_LOWEST
11 FROM grade_Type_weight
12 WHERE section_id = pc_section_id
13 AND section_id IN (SELECT section_id
14 FROM grade
15 WHERE student_id = pc_student_id);
16 -- Cursor to loop through all grades for a given student
17 -- in a given section.
18 CURSOR c_grades
19 (p_grade_type_code
20 grade_Type_weight.grade_type_code%TYPE,
21 pc_student_id student.student_id%TYPE,
22 pc_section_id section.section_id%TYPE) IS
23 SELECT grade_type_code,grade_code_occurrence,
24 numeric_grade
25 FROM grade
26 WHERE student_id = pc_student_id
27 AND section_id = pc_section_id
28 AND grade_type_code = p_grade_type_code;
29 -- Function to calcuation a students final grade
30 -- in one section
31 Procedure final_grade
32 (P_student_id IN student.student_id%type,
33 P_section_id IN section.section_id%TYPE,
34 P_Final_grade OUT enrollment.final_grade%TYPE,
35 P_Exit_Code OUT CHAR);
36 -- ---------------------------------------------------------
37 -- Function to calculate the median grade
38 FUNCTION median_grade
39 (p_course_number section.course_no%TYPE,
40 p_section_number section.section_no%TYPE,
41 p_grade_type grade.grade_type_code%TYPE)
42 RETURN grade.numeric_grade%TYPE;
43 CURSOR c_work_grade
44 (p_course_no section.course_no%TYPE,
45 p_section_no section.section_no%TYPE,
46 p_grade_type_code grade.grade_type_code%TYPE
47 )IS
48 SELECT distinct numeric_grade
49 FROM grade
50 WHERE section_id = (SELECT section_id
51 FROM section
52 WHERE course_no= p_course_no
53 AND section_no = p_section_no)
54 AND grade_type_code = p_grade_type_code
55 ORDER BY numeric_grade;
56 TYPE t_grade_type IS TABLE OF c_work_grade%ROWTYPE
57 INDEX BY BINARY_INTEGER;
58 t_grade t_grade_type;
59 END MANAGE_GRADES;
60 /
Package created.6. 更新包体
-- For Example ch21_20.sql
CREATE OR REPLACE PACKAGE MANAGE_GRADES AS
CREATE OR REPLACE PACKAGE BODY MANAGE_GRADES AS
Procedure final_grade
(P_student_id IN student.student_id%type,
P_section_id IN section.section_id%TYPE,
P_Final_grade OUT enrollment.final_grade%TYPE,
P_Exit_Code OUT CHAR)
IS
v_student_id student.student_id%TYPE;
v_section_id section.section_id%TYPE;
v_grade_type_code grade_type_weight.grade_type_code%TYPE;
v_grade_percent NUMBER;
v_final_grade NUMBER;
v_grade_count NUMBER;
v_lowest_grade NUMBER;
v_exit_code CHAR(1) := 'S';
-- Next two variables are used to calculate whether a cursor
-- has no result set.
v_no_rows1 CHAR(1) := 'N';
v_no_rows2 CHAR(1) := 'N';
e_no_grade EXCEPTION;
BEGIN
v_section_id := p_section_id;
v_student_id := p_student_id;
-- Start loop of grade types for the section.
FOR r_grade in c_grade_type(v_section_id, v_student_id)
LOOP
-- Since cursor is open it has a result
-- set, change indicator.
v_no_rows1 := 'Y';
-- To hold the number of grades per section,
-- reset to 0 before detailed cursor loops
v_grade_count := 0;
v_grade_type_code := r_grade.GRADE_TYPE_CODE;
-- Variable to hold the lowest grade.
-- 500 will not be the lowest grade.
v_lowest_grade := 500;
-- Determine what to multiply a grade by to
-- compute final grade, must take into consideration
-- if the drop lowest grade indicator is Y
SELECT (r_grade.percent_of_final_grade /
DECODE(r_grade.drop_lowest, 'Y',
(r_grade.number_per_section - 1),
r_grade.number_per_section
))* 0.01
INTO v_grade_percent
FROM dual;
-- Open cursor of detailed grade for a student in a
-- given section.
FOR r_detail in c_grades(v_grade_type_code,
v_student_id, v_section_id) LOOP
-- Since cursor is open it has a result
-- set, change indicator.
v_no_rows2 := 'Y';
v_grade_count := v_grade_count + 1;
-- Handle the situation where there are more
-- entries for grades of a given grade type
-- than there should be for that section.
If v_grade_count > r_grade.number_per_section THEN
v_exit_code := 'T';
raise e_no_grade;
END IF;
-- If drop lowest flag is Y determine which is lowest
-- grade to drop
IF r_grade.drop_lowest = 'Y' THEN
IF nvl(v_lowest_grade, 0) >=
r_detail.numeric_grade
THEN
v_lowest_grade := r_detail.numeric_grade;
END IF;
END IF;
-- Increment the final grade with percentage of current
-- grade in the detail loop.
v_final_grade := nvl(v_final_grade, 0) +
(r_detail.numeric_grade * v_grade_percent);
END LOOP;
-- Once detailed loop is finished, if the number of grades
-- for a given student for a given grade type and section
-- is less than the required amount, raise an exception.
IF v_grade_count < r_grade.NUMBER_PER_SECTION THEN
v_exit_code := 'I';
raise e_no_grade;
END IF;
-- If the drop lowest flag was Y then you need to take
-- the lowest grade out of the final grade, it was not
-- known when it was added which was the lowest grade
-- to drop until all grades were examined.
IF r_grade.drop_lowest = 'Y' THEN
v_final_grade := nvl(v_final_grade, 0) -
(v_lowest_grade * v_grade_percent);
END IF;
END LOOP;
-- If either cursor had no rows then there is an error.
IF v_no_rows1 = 'N' OR v_no_rows2 = 'N' THEN
v_exit_code := 'N';
raise e_no_grade;
END IF;
P_final_grade := v_final_grade;
P_exit_code := v_exit_code;
EXCEPTION
WHEN e_no_grade THEN
P_final_grade := null;
P_exit_code := v_exit_code;
WHEN OTHERS THEN
P_final_grade := null;
P_exit_code := 'E';
END final_grade;
FUNCTION median_grade
(p_course_number section.course_no%TYPE,
p_section_number section.section_no%TYPE,
p_grade_type grade.grade_type_code%TYPE)
RETURN grade.numeric_grade%TYPE
IS
BEGIN
FOR r_work_grade
IN c_work_grade(p_course_number, p_section_number, p_grade_type)
LOOP
t_grade(NVL(t_grade.COUNT,0) + 1).numeric_grade := r_work_grade.numeric_grade;
END LOOP;
IF t_grade.COUNT = 0
THEN
RETURN NULL;
ELSE
IF MOD(t_grade.COUNT, 2) = 0
THEN
-- There is an even number of workgrades. Find the middle
-- two and average them.
RETURN (t_grade(t_grade.COUNT / 2).numeric_grade +
t_grade((t_grade.COUNT / 2) + 1).numeric_grade
) / 2;
ELSE
-- There is an odd number of grades. Return the one in the middle.
RETURN t_grade(TRUNC(t_grade.COUNT / 2, 0) + 1).numeric_grade;
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END median_grade;
END MANAGE_GRADES;
7. 重新测试
915
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
