本章内容:
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