《OraclePL/SQL实例精讲》学习笔记21——包（第三部分）

本章内容&＃xff1a;

1. 创建包&＃xff08;创建包规范、包体、调用已存储的包、创建私有对象&＃xff09;

2. 游标变量

3. 用其他程序扩展包

4. 包的实例化和初始化

5. SERIALLY_RESABLE包

代码入下&＃xff1a;

1. 创建包规范

SQL> -- For Example ch21_15.sql
SQL> CREATE OR REPLACE PACKAGE MANAGE_GRADES AS2 -- Cursor to loop through all grade types for a given section.3 CURSOR c_grade_type4 (pc_section_id section.section_id%TYPE,5 PC_student_ID student.student_id%TYPE)6 IS7 SELECT GRADE_TYPE_CODE,8 NUMBER_PER_SECTION,9 PERCENT_OF_FINAL_GRADE,10 DROP_LOWEST11 FROM grade_Type_weight12 WHERE section_id &＃61; pc_section_id13 AND section_id IN (SELECT section_id14 FROM grade15 WHERE student_id &＃61; pc_student_id);16 -- Cursor to loop through all grades for a given student17 -- in a given section.18 CURSOR c_grades19 (p_grade_type_code20 grade_Type_weight.grade_type_code%TYPE,21 pc_student_id student.student_id%TYPE,22 pc_section_id section.section_id%TYPE) IS23 SELECT grade_type_code,grade_code_occurrence,24 numeric_grade25 FROM grade26 WHERE student_id &＃61; pc_student_id27 AND section_id &＃61; pc_section_id28 AND grade_type_code &＃61; p_grade_type_code;29 -- Function to calcuation a students final grade30 -- in one section31 Procedure final_grade32 (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 AS2 Procedure final_grade3 (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 IS8 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) :&＃61; &＃39;S&＃39;;16 v_no_rows1 CHAR(1) :&＃61; &＃39;N&＃39;;17 v_no_rows2 CHAR(1) :&＃61; &＃39;N&＃39;;18 e_no_grade EXCEPTION;19 BEGIN20 v_section_id :&＃61; p_section_id;21 v_student_id :&＃61; 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 LOOP25 -- Since cursor is open it has a result26 -- set, change indicator.27 v_no_rows1 :&＃61; &＃39;Y&＃39;;28 -- To hold the number of grades per section,29 -- reset to 0 before detailed cursor loops30 v_grade_count :&＃61; 0;31 v_grade_type_code :&＃61; r_grade.GRADE_TYPE_CODE;32 -- Variable to hold the lowest grade.33 -- 500 will not be the lowest grade.34 v_lowest_grade :&＃61; 500;35 -- Determine what to multiply a grade by to36 -- compute final grade, must take into consideration37 -- if the drop lowest grade indicator is Y38 SELECT (r_grade.percent_of_final_grade /39 DECODE(r_grade.drop_lowest, &＃39;Y&＃39;,40 (r_grade.number_per_section - 1),41 r_grade.number_per_section42 ))* 0.0143 INTO v_grade_percent44 FROM dual;45 -- Open cursor of detailed grade for a student in a46 -- given section.47 FOR r_detail in c_grades(v_grade_type_code,48 v_student_id, v_section_id) LOOP49 -- Since cursor is open it has a result50 -- set, change indicator.51 v_no_rows2 :&＃61; &＃39;Y&＃39;;52 v_grade_count :&＃61; v_grade_count &＃43; 1;53 -- Handle the situation where there are more54 -- entries for grades of a given grade type55 -- than there should be for that section.56 If v_grade_count > r_grade.number_per_section THEN57 v_exit_code :&＃61; &＃39;T&＃39;;58 raise e_no_grade;59 END IF;60 -- If drop lowest flag is Y determine which is lowest61 -- grade to drop62 IF r_grade.drop_lowest &＃61; &＃39;Y&＃39; THEN63 IF nvl(v_lowest_grade, 0) >&＃61;64 r_detail.numeric_grade65 THEN66 v_lowest_grade :&＃61; r_detail.numeric_grade;67 END IF;68 END IF;69 -- Increment the final grade with percentage of current70 -- grade in the detail loop.71 v_final_grade :&＃61; nvl(v_final_grade, 0) &＃43;72 (r_detail.numeric_grade * v_grade_percent);73 END LOOP;74 -- Once detailed loop is finished, if the number of grades75 -- for a given student for a given grade type and section76 -- is less than the required amount, raise an exception.77 IF v_grade_count 100 P_exit_code :&＃61; v_exit_code;
101 WHEN OTHERS THEN
102 P_final_grade :&＃61; null;
103 P_exit_code :&＃61; &＃39;E&＃39;;
104 END final_grade;
105 END MANAGE_GRADES;
106 /


3. 查看包

SQL> desc manage_grades
PROCEDURE FINAL_GRADEArgument Name Type In/Out Default?------------------------------ ----------------------- ------ --------P_STUDENT_ID NUMBER(8) INP_SECTION_ID NUMBER(8) INP_FINAL_GRADE NUMBER(3) OUTP_EXIT_CODE CHAR OUT

4. 测试

SQL> set serverout on
SQL> DECLARE2 v_student_id student.student_id%TYPE :&＃61; &sv_student_id;3 v_section_id section.section_id%TYPE :&＃61; &sv_section_id;4 v_final_grade enrollment.final_grade%TYPE;5 v_exit_code CHAR;6 BEGIN7 manage_grades.final_grade(v_student_id, v_section_id,8 v_final_grade, v_exit_code);9 DBMS_OUTPUT.PUT_LINE(&＃39;The Final Grade is &＃39;||v_final_grade);10 DBMS_OUTPUT.PUT_LINE(&＃39;The Exit Code is &＃39;||v_exit_code);11 END;12 /
Enter value for sv_student_id: 102
old 2: v_student_id student.student_id%TYPE :&＃61; &sv_student_id;
new 2: v_student_id student.student_id%TYPE :&＃61; 102;
Enter value for sv_section_id: 89
old 3: v_section_id section.section_id%TYPE :&＃61; &sv_section_id;
new 3: v_section_id section.section_id%TYPE :&＃61; 89;
The Final Grade is 92
The Exit Code is S

5. 更新包头


SQL> -- For Example ch21_19.sql
SQL> CREATE OR REPLACE PACKAGE MANAGE_GRADES AS2 -- Cursor to loop through all grade types for a given section.3 CURSOR c_grade_type4 (pc_section_id section.section_id%TYPE,5 PC_student_ID student.student_id%TYPE)6 IS7 SELECT GRADE_TYPE_CODE,8 NUMBER_PER_SECTION,9 PERCENT_OF_FINAL_GRADE,10 DROP_LOWEST11 FROM grade_Type_weight12 WHERE section_id &＃61; pc_section_id13 AND section_id IN (SELECT section_id14 FROM grade15 WHERE student_id &＃61; pc_student_id);16 -- Cursor to loop through all grades for a given student17 -- in a given section.18 CURSOR c_grades19 (p_grade_type_code20 grade_Type_weight.grade_type_code%TYPE,21 pc_student_id student.student_id%TYPE,22 pc_section_id section.section_id%TYPE) IS23 SELECT grade_type_code,grade_code_occurrence,24 numeric_grade25 FROM grade26 WHERE student_id &＃61; pc_student_id27 AND section_id &＃61; pc_section_id28 AND grade_type_code &＃61; p_grade_type_code;29 -- Function to calcuation a students final grade30 -- in one section31 Procedure final_grade32 (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 grade38 FUNCTION median_grade39 (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_grade44 (p_course_no section.course_no%TYPE,45 p_section_no section.section_no%TYPE,46 p_grade_type_code grade.grade_type_code%TYPE47 )IS48 SELECT distinct numeric_grade49 FROM grade50 WHERE section_id &＃61; (SELECT section_id51 FROM section52 WHERE course_no&＃61; p_course_no53 AND section_no &＃61; p_section_no)54 AND grade_type_code &＃61; p_grade_type_code55 ORDER BY numeric_grade;56 TYPE t_grade_type IS TABLE OF c_work_grade%ROWTYPE57 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 ASProcedure 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)
ISv_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) :&＃61; &＃39;S&＃39;;-- Next two variables are used to calculate whether a cursor-- has no result set.v_no_rows1 CHAR(1) :&＃61; &＃39;N&＃39;;v_no_rows2 CHAR(1) :&＃61; &＃39;N&＃39;;e_no_grade EXCEPTION;
BEGINv_section_id :&＃61; p_section_id;v_student_id :&＃61; 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 :&＃61; &＃39;Y&＃39;;-- To hold the number of grades per section,-- reset to 0 before detailed cursor loopsv_grade_count :&＃61; 0;v_grade_type_code :&＃61; r_grade.GRADE_TYPE_CODE;-- Variable to hold the lowest grade.-- 500 will not be the lowest grade.v_lowest_grade :&＃61; 500;-- Determine what to multiply a grade by to-- compute final grade, must take into consideration-- if the drop lowest grade indicator is YSELECT (r_grade.percent_of_final_grade /DECODE(r_grade.drop_lowest, &＃39;Y&＃39;,(r_grade.number_per_section - 1),r_grade.number_per_section))* 0.01INTO v_grade_percentFROM 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 :&＃61; &＃39;Y&＃39;;v_grade_count :&＃61; v_grade_count &＃43; 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 THENv_exit_code :&＃61; &＃39;T&＃39;;raise e_no_grade;END IF;-- If drop lowest flag is Y determine which is lowest-- grade to dropIF r_grade.drop_lowest &＃61; &＃39;Y&＃39; THENIF nvl(v_lowest_grade, 0) >&＃61;r_detail.numeric_gradeTHENv_lowest_grade :&＃61; r_detail.numeric_grade;END IF;END IF;-- Increment the final grade with percentage of current-- grade in the detail loop.v_final_grade :&＃61; nvl(v_final_grade, 0) &＃43;(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 RETURN grade.numeric_grade%TYPEISBEGINFOR r_work_grade IN c_work_grade(p_course_number, p_section_number, p_grade_type) LOOPt_grade(NVL(t_grade.COUNT,0) &＃43; 1).numeric_grade :&＃61; r_work_grade.numeric_grade;END LOOP;IF t_grade.COUNT &＃61; 0THENRETURN NULL;ELSEIF MOD(t_grade.COUNT, 2) &＃61; 0THEN-- There is an even number of workgrades. Find the middle-- two and average them.RETURN (t_grade(t_grade.COUNT / 2).numeric_grade &＃43;t_grade((t_grade.COUNT / 2) &＃43; 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) &＃43; 1).numeric_grade;END IF;END IF;EXCEPTIONWHEN OTHERSTHENRETURN NULL;END median_grade;
END MANAGE_GRADES;


7. 重新测试