《Oracle PL/SQL实例精讲》学习笔记21——包（第一部分）-CSDN博客

本文链接：https://blog.csdn.net/hpdlzu80100/article/details/83904768

本章内容：

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;