本章内容:
1. 创建包(创建包规范、包体、调用已存储的包、创建私有对象)
2. 游标变量
3. 扩展包
4. 包的实例化和初始化
5. SERIALLY_RESABLE包
基本概念:
1. 包:
A package is a collection of PL/SQL objects grouped together under one package name. Packages may include procedures, functions, cursors, declarations, types, and variables. Collecting objects into a package has numerous benefits.
2. 包规范:
The package specification contains information about the contents of the package, but not the code for the procedures and functions. It also contains declarations of global/public variables. Anything placed in the declaration section of a PL/SQL block may be coded in a package specification. All objects placed in the package specification are called public objects. Any function or procedure not in the package specification but coded in a package body is called a private function or procedure. When public procedures and functions are being called from a package, the programmer writing the “calling” process needs only the information in the package specification, as it provides all the information needed to call one of the procedures or functions within the package.
3. 包体:
The package body contains the actual executable code for the objects described in the package specification. It contains the code for all procedures and functions described in the specification and may additionally contain code for objects not declared in the specification; the latter type of packaged object is invisible outside the package and is referred to as “hidden.” When creating stored packages, the package specification and body can be compiled separately.
代码如下:
1. 定义包规范
SQL> -- For Example ch21_1.sql
SQL> /* Formatted on 2018/11/12 23:45:01 (QP5 v5.256.13226.35538) */
SQL> CREATE OR REPLACE PACKAGE manage_students
2 AS
3 PROCEDURE find_sname (i_student_id IN student.student_id%TYPE,
4 o_first_name OUT student.first_name%TYPE,
5 o_last_name OUT student.last_name%TYPE);
6
7 FUNCTION id_is_good (i_student_id IN student.student_id%TYPE)
8 RETURN BOOLEAN;
9 END manage_students;
10 /
Package created.
2. 如果只有包规范,而没有定义包体,调用包中的过程时会出错。
SQL> DECLARE
2 v_first_name student.first_name%TYPE;
3 v_last_name student.last_name%TYPE;
4 BEGIN
5 manage_students.find_sname
6 (125, v_first_name, v_last_name);
7 DBMS_OUTPUT.PUT_LINE(v_first_name||' '||v_last_name);
8 END;
9 /
DECLARE
*
ERROR at line 1:
ORA-04067: not executed, package body "C##STUDENT.MANAGE_STUDENTS" does not
exist
ORA-06508: PL/SQL: could not find program unit being called:
"C##STUDENT.MANAGE_STUDENTS"
ORA-06512: at line 5
3. 创建包体
SQL> -- For Example ch21_4.sql
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY manage_students
2 AS
3 PROCEDURE find_sname (i_student_id IN student.student_id%TYPE,
4 o_first_name OUT student.first_name%TYPE,
5 o_last_name OUT student.last_name%TYPE)
6 IS
7 v_student_id student.student_id%TYPE;
8 BEGIN
9 SELECT first_name, last_name
10 INTO o_first_name, o_last_name
11 FROM student
12 WHERE student_id = i_student_id;
13 EXCEPTION
14 WHEN OTHERS
15 THEN
16 DBMS_OUTPUT.PUT_LINE (
17 'Error in finding student_id: ' || v_student_id);
18 END find_sname;
19
20 FUNCTION id_is_good (i_student_id IN student.student_id%TYPE)
21 RETURN BOOLEAN
22 IS
23 v_id_cnt NUMBER;
24 BEGIN
25 SELECT COUNT (*)
26 INTO v_id_cnt
27 FROM student
28 WHERE student_id = i_student_id;
29
30 RETURN 1 = v_id_cnt;
31 EXCEPTION
32 WHEN OTHERS
33 THEN
34 RETURN FALSE;
35 END id_is_good;
36 END manage_students;
37 /
4. 调用已存储的包 (这个示例很奇怪,在SQLPLUS中调试不出来正确结果,留待后续调查!)
DECLARE
v_first_name student.first_name%TYPE;
v_last_name student.last_name%TYPE;
BEGIN
IF manage_students.id_is_good(&&v_id)
THEN
manage_students.find_sname(&&v_id, v_first_name,
v_last_name);
DBMS_OUTPUT.PUT_LINE('Student No. '||&&v_id||' is '
||v_last_name||', '||v_first_name);
ELSE
DBMS_OUTPUT.PUT_LINE
('Student ID: '||&&v_id||' is not in the database.');
END IF;
END;