《Oracle PL/SQL开发指南》学习笔记28——源码调试——PL/SQL基础知识（第十二部分——包）

包就比较复杂了，得多花点时间才能看懂。

1. 包规范的原型

CREATE [OR REPLACE] [{EDITIONABLE | NONEDITIONABLE}]
[schema.] package_name
[ACCESSIBLE BY
( [{FUNCTION | PROCEDURE | PACKAGE | TYPE}] [schema.]unit_name)
[,[{FUNCTION | PROCEDURE | PACKAGE | TYPE}] [schema.]unit_name)]
[,... ]]]) {IS | AS}
[TYPE type_name IS
{RECORD (column_list) | VARRAY(n) | TABLE [INDEX BY data_type]}]
[variable_name data_type {DEFAULT | :=} value; [ ...]]
[CURSOR cursor_name
(parameter data_type [, parameter data_type [, ...]) IS
SELECT statement; [ ...]]
[TYPE reference_cursor IS REF CURSOR
[ RETURN {catalog_row | cursor_row | record_structure}] [ ...]]
[user_exception EXCEPTION; [ ...]]
[PRAGMA SERIALLY_REUSABLE;]
[FUNCTION public_prototype;] [ ...]
[PROCEURE public_prototype;] [ ...]
END [package_name];

2. 定义重载（Overloaded）函数

SQL> ed
Wrote file afiedt.buf

  1  -- Create or replace the overloading package specification.
  2  CREATE OR REPLACE PACKAGE overloading IS
  3    -- Force fresh copy of shared cursor.
  4    PRAGMA SERIALLY_REUSABLE;
  5    -- Define a default salutation.
  6    FUNCTION salutation
  7    ( pv_long_phrase VARCHAR2 DEFAULT 'Hello'
  8    , pv_name VARCHAR2 ) RETURN VARCHAR2;
  9    -- Define an overloaded salutation.
 10    FUNCTION salutation
 11    ( pv_long_phrase VARCHAR2 DEFAULT 'Hello'
 12    , pv_name VARCHAR2
 13    , pv_language VARCHAR2 ) RETURN VARCHAR2;
 14* END;
 15  /

Package created.

SQL> ed
Wrote file afiedt.buf

  1  CREATE TABLE salutation_translation
  2  ( short_salutation VARCHAR2(4)
  3  , long_salutation VARCHAR2(12)
  4* , phrase_language VARCHAR2(12))
SQL> /

Table created.


SQL> ed
Wrote file afiedt.buf

  1  begin
  2  -- Insert four records into the table.
  3  INSERT INTO salutation_translation VALUES ('Hi','HELLO','ENGLISH');
  4  INSERT INTO salutation_translation VALUES ('Bye','GOODBYE','ENGLISH');
  5  INSERT INTO salutation_translation VALUES ('Ciao','SALUTE','ITALIAN');
  6  INSERT INTO salutation_translation VALUES ('Ciao','ADDIO','ITALIAN');
  7* end;
  8  /

PL/SQL procedure successfully completed.

3. 包主体的原型

CREATE [OR REPLACE] package_name BODY {IS | AS}
[TYPE type_name IS
{RECORD (column_list) | VARRAY(n) | TABLE [INDEX BY data_type]}]
[variable_name data_type {DEFAULT | :=} value; [ ...]]
[CURSOR cursor_name
(parameter data_type [, parameter data_type [, ...]) IS
SELECT statement; [ ...]]
[TYPE reference_cursor IS REF CURSOR
[ RETURN {catalog_row | cursor_row | record_structure}] [ ...]]
[PRAGMA SERIALLY_REUSABLE;]
[FUNCTION local_implementation;] [ ...]
[PROCEURE local_implementation;] [ ...]
[FUNCTION published_body;] [ ...]
[PROCEDURE published_body;] [ ...]
END [package_name];

示例：

SQL> ed
Wrote file afiedt.buf

  1  -- Create or replace the overloading package body.
  2  CREATE OR REPLACE PACKAGE BODY overloading IS
  3    -- Force fresh copy of shared cursor.
  4    PRAGMA SERIALLY_REUSABLE;
  5    -- Shared cursor.
  6    CURSOR c
  7    ( cv_long_phrase VARCHAR2
  8    , cv_language VARCHAR2 ) IS
  9    SELECT short_salutation
 10    , long_salutation
 11    FROM salutation_translation
 12    WHERE long_salutation = UPPER(cv_long_phrase)
 13    AND phrase_language = UPPER(cv_language);
 14    -- Declare a default salutation.
 15    FUNCTION salutation
 16    ( pv_long_phrase VARCHAR2 DEFAULT 'Hello'
 17    , pv_name VARCHAR2 ) RETURN VARCHAR2 IS
 18      -- Local variables.
 19      lv_short_salutation VARCHAR2(4) := '';
 20      lv_language VARCHAR2(10) DEFAULT 'ENGLISH';
 21    BEGIN
 22      -- Read shared cursor and return concatenated result.
 23      FOR i IN c(pv_long_phrase, lv_language) LOOP
 24        lv_short_salutation := i.short_salutation;
 25      END LOOP;
 26      RETURN lv_short_salutation || ' ' || pv_name || '!';
 27    END;
 28    -- Define an overloaded salutation.
 29    FUNCTION salutation
 30    ( pv_long_phrase VARCHAR2 DEFAULT 'Hello'
 31    , pv_name VARCHAR2
 32    , pv_language VARCHAR2) RETURN VARCHAR2 IS
 33      -- Local variable.
 34      lv_short_salutation VARCHAR2(4) := '';
 35    BEGIN
 36      -- Read shared cursor and return concatenated result.
 37      FOR i IN c(pv_long_phrase, pv_language) LOOP
 38        lv_short_salutation := i.short_salutation;
 39      END LOOP;
 40      RETURN lv_short_salutation || ' ' || pv_name || '!';
 41    END;
 42* END;
 43  /

Package body created.

SQL> VARIABLE message VARCHAR2(30)
SQL> CALL overloading.salutation('Hello','Ringo') INTO :message;

Call completed.

SQL> CALL overloading.salutation('Addio','Lennon','Italian') INTO :message;

Call completed.

SQL> ELECT :message AS "Goodbye Message" FROM dual;
SP2-0734: unknown command beginning "ELECT :mes..." - rest of line ignored.
SQL> SELECT :message AS "Goodbye Message" FROM dual;

Goodbye Message
--------------------------------------------------------------------------------
Ciao Lennon!

SQL> CALL overloading.salutation('Hello','Ringo') INTO :message;

Call completed.

SQL> SELECT :message AS "Goodbye Message" FROM dual;

Goodbye Message
--------------------------------------------------------------------------------
Hi Ringo!

SQL> SELECT overloading.salutation('Addio','Lennon','Italian') AS "Message" FROM dual;
SELECT overloading.salutation('Addio','Lennon','Italian') AS "Message" FROM dual
       *
ERROR at line 1:
ORA-06534: Cannot access Serially Reusable package "C##STUDENT.OVERLOADING"
ORA-06512: at line 1