《Oracle PL/SQL实例精讲》学习笔记25——Oracle提供的包 （第四部分）

本章内容：

1. 利用Oracle提供的包扩展功能（使用UTL_FILE访问文件、使用DBMS_JOB调度作业、DBMS_XPLAN生成解释计划、DBMS_SQL产生隐式语句结果 ）

2. 利用Oracle提供的包报告错误

代码如下：

SQL> -- For Example ch24.6.sql
SQL> CREATE OR REPLACE PACKAGE Student_Instructor AS
  2  PROCEDURE show_population
  3          (i_zip IN zipcode.zip%TYPE);
  4  END Student_Instructor;
  5  /

Package created.

SQL>
SQL> CREATE or REPLACE PACKAGE BODY Student_Instructor
  2  AS
  3  PROCEDURE show_population
  4         (i_zip IN zipcode.zip%TYPE)
  5  AS
  6    student_list    SYS_REFCURSOR;
  7    instructor_list SYS_REFCURSOR;
  8  BEGIN
  9    OPEN student_list FOR
 10               SELECT 'Student' type, First_Name, Last_Name
 11                 FROM  student
 12                WHERE  zip = i_zip;
 13          DBMS_SQL.RETURN_RESULT(student_list);
 14    OPEN instructor_list FOR
 15                  SELECT 'Instructor' type, First_Name, Last_Name
 16                    FROM  instructor
 17                   WHERE  zip = i_zip;
 18           DBMS_SQL.RETURN_RESULT(instructor_list);
 19  END show_population;
 20  END Student_Instructor;
 21  /

测试：

SQL> exec student_instructor.show_population('10025');

PL/SQL procedure successfully completed.

ResultSet #1

TYPE                  FIRST_NAME                                                                  LAST_NAME
--------------------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
Student               Jerry                                                                       Abdou
Student               Nicole                                                                      Gillen
Student               Frank                                                                       Pace

ResultSet #2

TYPE                           FIRST_NAME                                                               LAST_NAME
------------------------------ --------------------------------------------------------------------------- ---------------------------------------------------------------------------
Instructor                     Tom                                                                      Wojick
Instructor                     Nina                                                                     Schorin
Instructor                     Todd                                                                     Smythe
Instructor                     Charles                                                                  Lowry