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

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

本章内容：

1. 创建包（创建包规范、包体、调用已存储的包、创建私有对象）

2. 游标变量

3. 扩展包

4. 包的实例化和初始化

5. SERIALLY_RESABLE包

基本概念：

1. 游标变量：

In the current version of PL/SQL, cursors can be declared and manipulated like any other PL/SQL variable. This type of variable is called a cursor variable or a REF CURSOR. A cursor variable is just a reference or a handle to a static cursor. It permits a programmer to pass this reference to the same cursor among all the program’s units that need access to the cursor. A cursor variable binds the cursor’s SELECT statement dynamically at run time.

2. 使用游标变量的规则：

Rules for Using Cursor Variables The cursor variable cannot be defined in a package specification. You cannot use cursor variables with remote subprograms on another server, so you cannot pass cursor variables to a procedure that is called through a database link. Do not use FOR UPDATE with OPEN FOR in processing a cursor variable. You cannot use comparison operators to test cursor variables for equality, inequality, or nullity. A cursor variable cannot be assigned a null value. A REF CURSOR type cannot be used in CREATE TABLE or VIEW statements as there is no equivalent data type for a database column. A stored procedure that uses a cursor variable can be used only as a query block data source; it cannot be used for a DML block data source. Using a REF CURSOR is ideal for queries that are dependent only on variations in SQL statements and not on PL/SQL statements. You cannot store cursor variables in an associative array, nested table, or varray. If you pass a host cursor variable to PL/SQL, you cannot fetch from it on the server side unless you also open it there on the same server call.

代码：

1. 创建包头

SQL> -- For Example ch21_22.sql
SQL> CREATE OR REPLACE PACKAGE  school_api as
  2     v_current_date DATE;
  3     PROCEDURE Discount_Cost;
  4     FUNCTION new_instructor_id
  5        RETURN instructor.instructor_id%TYPE;
  6  END school_api;
  7  /

Package created.

2. 创建包体

SQL> /* Formatted on 2018/11/13 13:53:37 (QP5 v5.256.13226.35538) */
SQL> -- For Example ch21_5.sql
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY school_api
  2  AS
  3     PROCEDURE discount_cost
  4     IS
  5        CURSOR c_group_discount
  6        IS
  7             SELECT DISTINCT s.course_no, c.description
  8               FROM section s, enrollment e, course c
  9              WHERE s.section_id = e.section_id
 10           GROUP BY s.course_no,
 11                    c.description,
 12                    e.section_id,
 13                    s.section_id
 14             HAVING COUNT (*) >= 8;
 15     BEGIN
 16        FOR r_group_discount IN c_group_discount
 17        LOOP
 18           UPDATE course
 19              SET cost = cost * 0.95
 20            WHERE course_no = r_group_discount.course_no;
 21
 22           DBMS_OUTPUT.PUT_LINE (
 23                 'A    5%    discount    has    been    given    to'
 24              || r_group_discount.course_no
 25              || '
 26      '
 27              || r_group_discount.description);
 28        END LOOP;
 29     END discount_cost;
 30
 31     FUNCTION new_instructor_id
 32        RETURN instructor.instructor_id%TYPE
 33     IS
 34        v_new_instid   instructor.instructor_id%TYPE;
 35     BEGIN
 36        SELECT INSTRUCTOR_ID_SEQ.NEXTVAL INTO v_new_instid FROM DUAL;
 37
 38        RETURN v_new_instid;
 39     EXCEPTION
 40        WHEN OTHERS
 41        THEN
 42           DECLARE
 43              v_sqlerrm   VARCHAR2 (250) := SUBSTR (SQLERRM, 1, 250);
 44           BEGIN
 45              RAISE_APPLICATION_ERROR (
 46                 -20003,
 47                 'Error    in    instructor_id:    ' || v_sqlerrm);
 48           END;
 49     END new_instructor_id;
 50  END school_api;
 51  /

Package body created.

3. 调用已存储的包

SQL> show col size
SP2-0158: unknown SHOW option "col"
SP2-0158: unknown SHOW option "size"
SQL> show linesize
linesize 80
SQL> set linesize 160
SQL> -- For Example ch21_7.sql
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2     V_instructor_id instructor.instructor_id%TYPE;
  3  BEGIN
  4     School_api.Discount_Cost;
  5     v_instructor_id := school_api.new_instructor_id;
  6     DBMS_OUTPUT.PUT_LINE
  7        ('The new id is: '||v_instructor_id);
  8  END;
  9  /
A    5%    discount    has    been    given    to140
    Advanced Unix Admin
A    5%    discount    has    been    given    to25
    Database Design
A    5%    discount    has    been    given    to240
    GUI Programming
A    5%    discount    has    been    given    to230
    DB Programming in Java
A    5%    discount    has    been    given    to230
    Basics of Unix Admin

4. 私有对象不能被包以外的任何程序访问

5. 游标变量的使用

1）创建包头

SQL> -- For Example ch21_10.sql
SQL> CREATE OR REPLACE PACKAGE course_pkg AS
  2    TYPE course_rec_typ IS RECORD
  3      (first_name    student.first_name%TYPE,
  4       last_name     student.last_name%TYPE,
  5       course_no     course.course_no%TYPE,
  6       description   course.description%TYPE,
  7       section_no    section.section_no%TYPE
  8       );
  9    TYPE course_cur IS REF CURSOR RETURN course_rec_typ;
 10    PROCEDURE get_course_list
 11      (p_student_id    NUMBER ,
 12       p_instructor_id NUMBER ,
 13       course_list_cv IN OUT course_cur);
 14  END course_pkg;
 15  /

Package created.

2）创建包体

SQL> CREATE OR REPLACE PACKAGE BODY course_pkg AS
  2    PROCEDURE get_course_list
  3      (p_student_id    NUMBER ,
  4       p_instructor_id NUMBER ,
  5       course_list_cv IN OUT course_cur)
  6    IS
  7    BEGIN
  8      IF p_student_id IS NULL AND p_instructor_id
  9        IS NULL THEN
 10        OPEN course_list_cv FOR
 11          SELECT 'Please choose a student-' First_name,
 12                 'instructor combination'   Last_name,
 13            NULL     course_no,
 14            NULL     description,
 15            NULL     section_no
 16            FROM dual;
 17      ELSIF p_student_id IS NULL  THEN
 18        OPEN course_list_cv FOR
 19          SELECT s.first_name    first_name,
 20            s.last_name     last_name,
 21            c.course_no     course_no,
 22            c.description   description,
 23            se.section_no   section_no
 24     FROM   instructor i, student s,
 25            section se, course c, enrollment e
 26     WHERE  i.instructor_id = p_instructor_id
 27       AND  i.instructor_id = se.instructor_id
 28       AND  se.course_no    = c.course_no
 29       AND  e.student_id    = s.student_id
 30       AND  e.section_id    = se.section_id
 31       ORDER BY  c.course_no, se.section_no;
 32      ELSIF p_instructor_id IS NULL  THEN
 33        OPEN course_list_cv FOR
 34             SELECT i.first_name    first_name,
 35            i.last_name     last_name,
 36            c.course_no     course_no,
 37            c.description   description,
 38            se.section_no   section_no
 39     FROM   instructor i, student s,
 40            section se, course c, enrollment e
 41     WHERE  s.student_id = p_student_id
 42       AND  i.instructor_id = se.instructor_id
 43       AND  se.course_no    = c.course_no
 44       AND  e.student_id    = s.student_id
 45       AND  e.section_id    = se.section_id
 46          ORDER BY  c.course_no, se.section_no;
 47          END IF;
 48       END get_course_list;
 49  END course_pkg;
 50  /

Package body created.

3）创建一个游标变量类型的变量

SQL> VARIABLE course_cv REFCURSOR

4）执行此过程

SQL> exec course_pkg.get_course_list(102,NULL,:course_cv)

PL/SQL procedure successfully completed.

显示游标变量的内容：

SQL> print course_cv

FIRST_NAME LAST_NAME  COURSE_NO DESCRIPTION          SECTION_NO
---------- ---------- --------- -------------------- ----------
Charles    Lowry             25 Intro to Programming          2
Nina       Schorin           25 Intro to Programming          5

5）切换参数，重新执行包方法

SQL> exec course_pkg.get_course_list(NULL,102,:course_cv)

PL/SQL procedure successfully completed.

SQL> print course_cv

FIRST_NAME LAST_NAME  COURSE_NO DESCRIPTION          SECTION_NO
---------- ---------- --------- -------------------- ----------
Jeff       Runyan            10 DP Overview                   2
Dawn       Dennis            25 Intro to Programming          4
Alfred     Hutheesing        25 Intro to Programming          4
Arun       Griffen           25 Intro to Programming          4
Jim        Joas              25 Intro to Programming          4
May        Jodoin            25 Intro to Programming          4
Roger      Snow             100 Hands-On Windows              1
Jenny      Goldsmith        100 Hands-On Windows              1