oracle 11g PL/SQL Programming学习六

----------------------------------------------------------------------------
-----------------PL/SQL学习笔记系列 By Cryking-----------------
------------------------转载请注明出处,谢谢!------------------------

第8章 大对象(Large Objects)
大对象数据类型是很有用的数据结构,它可以用来存储文本、图片、音乐、视频.
11g对LOB类型进行了改造,使它变更快、更安全了.
通过DBMS_LOB.GET_STORAGE_LIMIT可以得到当前数据库允许的LOB类型的最大长度.

21:36:04 SYS@orcl> declare
21:36:43   2  v clob:='a';
21:36:48   3  begin
21:36:50   4  dbms_output.put_line( DBMS_LOB.GET_STORAGE_LIMIT(v)/1024/1024/1024||'G');
21:37:27   5  end;
21:37:32   6  /
16263.99999621324241161346435546875G

PL/SQL 过程已成功完成。

存储字符串的大对象使用CLOB,存储二进制的大对象使用BLOB,以上2个对象都存储在数据库内部,存储在数据库外部的
大对象类型使用BFILE.BFILE实际存储的是一个指向外部物理文件的指针.


CLOB和NCLOB大对象类型
CLOB和NCLOB可作为表的字段或嵌套表的列.CLOB类型用来存储文本文件.
它们的最大长度在8TB到128TB之间.(受DB_BLOCK_SIZE参数影响,此参数为1KB时,CLOB/NCLOB最大为8TB;此参数为32时,CLOB/NCLOB最大为128TB)
CLOB列通常与表的其他行分开存储.只有描述符或定位符存储在表列的对应位置.(也就是通过定位符来找到实际存储的CLOB对象的内容的).
定位符不仅指向CLOB存储的内容,并且提供SGA私有工作区的引用.
当你给一个CLOB对象赋值数字的时候,数字首先转换为字符类型,然后由字符类型转为CLOB类型.
声明CLOB变量:
var1 CLOB; -- Declare a null reference to a CLOB.
var1 CLOB := empty_clob(); -- Declare an empty CLOB.
var2 CLOB := 'some_string';
CLOB不同如其他的标量数据类型,它不止NULL或NOT NULL状态.它还有以下状态:
NULL--包含NULL值
EMPTY--包含LOB定位符,指向空的实例.此时使用DBMS_LOB.GETLENGTH,返回为0.
populated--包含LOB定位符,并指向内容,此时使用DBMS_LOB.GETLENGTH,返回为正数.

大对象类型的使用
DBMS_LOB包提供了读写大对象的工具.首先需要你定义一个虚拟目录(DIRECTORY).
如:CREATE DIRECTORY generic AS 'C:\tmp';
建立虚拟目录后,你需要把目录的读写权限给指定用户
GRANT READ,WRITE ON DIRECTORY generic TO plsql;
接下来读取文件,并写数据到表的CLOB列.
--使用NDS(本地动态SQL)完成大对象的读写
CREATE OR REPLACE PROCEDURE LOAD_CLOB_FROM_FILE(SRC_FILE_NAME IN VARCHAR2,
TABLE_NAME IN VARCHAR2,
COLUMN_NAME IN VARCHAR2,
PRIMARY_KEY_NAME IN VARCHAR2,
PRIMARY_KEY_VALUE IN VARCHAR2) IS
-- 为DBMS_LOB.LOADCLOBFROMFILE定义的变量.
DES_CLOB CLOB;
SRC_CLOB BFILE := BFILENAME('GENERIC', SRC_FILE_NAME);
DES_OFFSET NUMBER := 1;
SRC_OFFSET NUMBER := 1;
CTX_LANG NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
WARNING NUMBER;
--定义读取内容的大小
SRC_CLOB_SIZE NUMBER;
-- 为NDS定义的变量
STMT VARCHAR2(2000);
BEGIN
--判断文件是否存在及是否打开
IF DBMS_LOB.FILEEXISTS(SRC_CLOB) = 1 AND
NOT DBMS_LOB.ISOPEN(SRC_CLOB) = 1 THEN
SRC_CLOB_SIZE := DBMS_LOB.GETLENGTH(SRC_CLOB);
DBMS_LOB.OPEN(SRC_CLOB, DBMS_LOB.LOB_READONLY);--打开外部文件,读取内容到SRC_CLOB中
END IF;
--定义动态SQL,将CLOB列更新为EMPTY,并返回CLOB列到绑定变量locator中
STMT := 'UPDATE ' || TABLE_NAME || ' ' || 'SET ' || COLUMN_NAME ||
' = empty_clob() ' || 'WHERE ' || PRIMARY_KEY_NAME || ' = ' || '''' ||
PRIMARY_KEY_VALUE || ''' ' || 'RETURNING ' || COLUMN_NAME ||
' INTO :locator';
--执行动态SQL
EXECUTE IMMEDIATE STMT
USING OUT DES_CLOB;
--读写文件到CLOB列,关闭源文件并提交.
DBMS_LOB.LOADCLOBFROMFILE(DEST_LOB => DES_CLOB,
SRC_BFILE => SRC_CLOB,
AMOUNT => DBMS_LOB.GETLENGTH(SRC_CLOB),
DEST_OFFSET => DES_OFFSET,
SRC_OFFSET => SRC_OFFSET,
BFILE_CSID => DBMS_LOB.DEFAULT_CSID,
LANG_CONTEXT => CTX_LANG,
WARNING => WARNING);
DBMS_LOB.CLOSE(SRC_CLOB);
IF SRC_CLOB_SIZE = DBMS_LOB.GETLENGTH(DES_CLOB) THEN
$IF $$DEBUG = 1 $THEN DBMS_OUTPUT.PUT_LINE('Success!');--条件编译
$END COMMIT;
ELSE
$IF $$DEBUG = 1 $THEN DBMS_OUTPUT.PUT_LINE('Failure.');--条件编译
$END RAISE DBMS_LOB.OPERATION_FAILED;
END IF;
END LOAD_CLOB_FROM_FILE;
/


BLOB类型
基本同CLOB,只注意BLOB内存储的是二进制内容.
使用DBMS_LOB.LOADBLOBFROMFILE读文件到BLOB对象变量.


安全文件
安全文件是特殊的大对象.它们使用特定的存储参数声明,让你可以加密、压缩、删除它们.
11G让你使用BLOB、CLOB和NCLOB列存储安全文件.它们工作在传输数据加密和使用oracle钱包的加密中.
传输数据加密(TDE)可以选择使用以下加密算法:
3DES168,AES128,AES192(default),AES256
--查询当前数据库是否使用加密文件
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME LIKE 'db_securefile';


--使用oracle钱包管理器配置oracle钱包来建立加密密码
C:> %ORACLE_HOME%\bin\launch.exe "oracle的标准路径\bin" owm.cl
如果使用菜单的话,选择 开始|程序|Oracle-Oracle Home|集成管理工具|Waller Manager.在这里你可以建立加密密码.
加密的KEY默认保存在目录%USERPROFILE%\ORACLE\WALLETS(windows下).
创建Wallet密码后,再为你的安全文件创建一个指定表空间:
CREATE TABLESPACE securefiles
DATAFILE '<canonical_path>\sec_file.dbf' SIZE 5M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
--给表增加安全文件列
ALTER TABLE item ADD (sec_file CLOB) LOB(sec_file)
STORE AS SECUREFILE sec_file (TABLESPACE securefile);
--对该列按指定方式加密
ALTER TABLE item MODIFY LOB(sec_file) (ENCRYPT USING '3DES168');
任何内部LOB存储都支持安全文件加密.应当注意的是你应当将安全文件放在单独的表空间.




BFILE类型(二进制文件)
BFILE与BLOB、CLOB等的工作方式不同.最大的不同就是BFILE的值是只读的LOB类型,并且存储在数据库外.
BFILE的最大物理大小受操作系统限制.


创建和使用虚拟目录
虚拟目录就像同义词,它们指向操作系统的物理路径.虚拟目录名以及路径名存储在数据库的视图dba_directories里.
数据库用户有SELECT_CATALOG_ROLE角色的能查询这个视图.缺省SYSTEM用户能查看DBA_DIRECTORIES视图.
SYSTEM用户能使用CREATE ANY DIRECTORY给其他用户赋予创建虚拟目录的权限.(你应当禁止DBA用户拥有创建目录的权限)
所有虚拟目录实际上都被SYS所拥有.
CREATE DIRECTORY images AS '/var/www/html/images';(LINUX或UNIX下)
--给用户赋予读取目录的权限
GRANT READ ON DIRECTORY images TO hr;
--查询目录及路径
SELECT * FROM dba_directories WHERE directory_name = 'IMAGES';
--更新BFILE列
UPDATE item
set item_photo = BFILENAME('IMAGES','Raiders3.png')
WHERE item_id = 1055;
--查看BFLIE列指定值所指的文件大小
DECLARE
FILE_LOCATOR BFILE;
BEGIN
SELECT ITEM_PHOTO INTO FILE_LOCATOR FROM ITEM WHERE ITEM_ID = 1055;
IF DBMS_LOB.FILEEXISTS(FILE_LOCATOR) = 1 THEN --判断文件是否存在
DBMS_OUTPUT.PUT_LINE('File is: [' || DBMS_LOB.GETLENGTH(FILE_LOCATOR) || ']');
ELSE
DBMS_OUTPUT.PUT_LINE('No file found.');
END IF;
END;
/


--获取指定BFLIE列里指定值的文件名
CREATE OR REPLACE FUNCTION GET_BFILENAME(TABLE_NAME VARCHAR2,
COLUMN_NAME VARCHAR2,
PRIMARY_KEY_NAME VARCHAR2,
PRIMARY_KEY_VALUE VARCHAR2)
RETURN VARCHAR2 IS
-- Define a locator.
LOCATOR BFILE;
-- Define alias and filename.
DIR_ALIAS VARCHAR2(255);
DIRECTORY VARCHAR2(255);
FILE_NAME VARCHAR2(255);
-- Define local variable for Native Dynamic SQL.
STMT VARCHAR2(2000);
DELIMITER VARCHAR2(1) := '/';
-- Define a local exception for size violation.
DIRECTORY_NUM EXCEPTION;
PRAGMA EXCEPTION_INIT(DIRECTORY_NUM, -22285);
BEGIN
-- Wrap the statement in an anonymous block to create and OUT mode variable.
STMT := 'BEGIN ' || 'SELECT ' || COLUMN_NAME || ' ' || 'INTO :locator ' ||
'FROM ' || TABLE_NAME || ' ' || 'WHERE ' || PRIMARY_KEY_NAME ||
' = ' || '''' || PRIMARY_KEY_VALUE || ''';' || 'END;';
-- Return a scalar query result from a dynamic SQL statement.
EXECUTE IMMEDIATE STMT
USING OUT LOCATOR;
-- Check for available locator.
IF LOCATOR IS NOT NULL THEN
DBMS_LOB.FILEGETNAME(LOCATOR, DIR_ALIAS, FILE_NAME);
END IF;
-- Return filename.
RETURN DELIMITER || LOWER(DIR_ALIAS) || DELIMITER || FILE_NAME;
EXCEPTION
WHEN DIRECTORY_NUM THEN
RETURN NULL;
END GET_BFILENAME;


--调用
SELECT GET_BFILENAME('ITEM', 'ITEM_PHOTO', 'ITEM_ID', '1055') AS DIRECTORY
FROM DUAL;

DIRECTORY
---------------------
/images/Raiders3.png


--根据虚拟目录名获得实际物理路径
CREATE OR REPLACE FUNCTION GET_DIRECTORY_PATH(VIRTUAL_DIRECTORY IN VARCHAR2)
RETURN VARCHAR2 IS
-- Define return variable.
DIRECTORY_PATH VARCHAR2(256) := 'C:\';
-- Define dynamic cursor.
CURSOR GET_DIRECTORY(VIRTUAL_DIRECTORY VARCHAR2) IS
SELECT DIRECTORY_PATH
FROM SYS.DBA_DIRECTORIES
WHERE DIRECTORY_NAME = VIRTUAL_DIRECTORY;
-- Define a local exception for name violation.
DIRECTORY_NAME EXCEPTION;
PRAGMA EXCEPTION_INIT(DIRECTORY_NAME, -22284);
BEGIN
OPEN GET_DIRECTORY(VIRTUAL_DIRECTORY);
FETCH GET_DIRECTORY
INTO DIRECTORY_PATH;
CLOSE GET_DIRECTORY;
-- Return filename.
RETURN DIRECTORY_PATH;
EXCEPTION
WHEN DIRECTORY_NAME THEN
RETURN NULL;
END GET_DIRECTORY_PATH;



DBMS_LOB包
包常量：
包内有4种指定类型:2种是记录类型,另外2种是关联数组类型.( 一个是CLOB,一个是BLOB)
记录结构:
TYPE blob_deduplicate_region IS RECORD (
lob_offset INTEGER,
len INTEGER,
primary_lob BLOB,
primary_lob_offset NUMBER,
mime_type VARCHAR2(80));


TYPE clob_deduplicate_region IS RECORD (
lob_offset INTEGER,
len INTEGER,
primary_lob CLOB,
primary_lob_offset NUMBER,
mime_type VARCHAR2(80));

关联数组:
TYPE blob_deduplicate_region_tab IS TABLE OF blob_deduplicate_region
INDEX BY PLS_INTEGER;
TYPE clob_deduplicate_region_tab IS TABLE OF clob_deduplicate_region
INDEX BY PLS_INTEGER;


包定义了8个异常:
invalid_argval EXCEPTION;
PRAGMA EXCEPTION_INIT(invalid_argval, -21560);
invalid_argval_num NUMBER := 21560;
access_error EXCEPTION;
PRAGMA EXCEPTION_INIT(access_error, -22925);
eccess_error_num NUMBER := 22925;
noexist_directory EXCEPTION;
PRAGMA EXCEPTION_INIT(noexist_directory, -22285);
noexist_directory_num NUMBER := 22285;
nopriv_directory EXCEPTION;
PRAGMA EXCEPTION_INIT(nopriv_directory, -22286);
nopriv_directory_num NUMBER := 22286;
invalid_directory EXCEPTION;
PRAGMA EXCEPTION_INIT(invalid_directory, -22287);
invalid_directory_num NUMBER := 22287;
operation_failed EXCEPTION;
PRAGMA EXCEPTION_INIT(operation_failed, -22288);
operation_failed_num NUMBER := 22288;
unopened_file EXCEPTION;
PRAGMA EXCEPTION_INIT(unopened_file, -22289);
unopened_file_num NUMBER := 22289;
open_toomany EXCEPTION;
PRAGMA EXCEPTION_INIT(open_toomany, -22290);
open_toomany_num NUMBER := 22290;
具体请看DBMS_LOB包定义.


--dbms_lob.issecurefile的使用示例
DECLARE
audit_blob BLOB;
CURSOR c IS
SELECT NVL(item_blob, empty_blob) FROM item WHERE item_id = 1021;
BEGIN
OPEN c;
FETCH c
INTO audit_blob;
IF dbms_lob.issecurefile(audit_blob) THEN
dbms_output.put_line('A secure file.');
ELSE
dbms_output.put_line('Not a secure file.');
END IF;
CLOSE c;
END;

--DBMS_LOB.COMPARE的使用示例
SELECT CASE
WHEN DBMS_LOB.COMPARE(i1.item_blob, i2.item_blob) = 0 THEN
'True'
ELSE
'False'
END AS compared
FROM em i1
CROSS JOIN item i2--CROSS JOIN就是笛卡尔积
WHERE i1.item_id = 1021
AND i2.item_id = 1022;

--DBMS_LOB.GETCHUNKSIZE的使用示例,返回的是一个可用的块大小,一般和参数db_block_size一致
00:09:50 SCOTT@orcl> SELECT DBMS_LOB.GETCHUNKSIZE('aaa')
00:28:54   2    FROM dual;


DBMS_LOB.GETCHUNKSIZE('AAA')
----------------------------
                        8132


已选择 1 行。
00:30:01 SYS@orcl> show parameter db_block_size


NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
db_block_size                        integer     8192

第九章 包(Packages)
包就是把函数和存储组合到一个库.在库里,你可以共享使用变量、类型、组件(就是函数和存储)等.
包包含变量声明、类型声明/定义、函数、存储等.你可以在其他PL/SQL块里使用包定义的变量和类型.
给所有用户执行指定包的权限,如: grant execute on dbms_lob to public;
在包体定义的函数、存储不能访问定义在其之后的包变量.
如:

19:58:24 SYS@orcl> DECLARE
20:38:43   2    PROCEDURE a IS
20:38:43   3    BEGIN
20:38:43   4      dbms_output.put_line(ab);
20:38:43   5    END;
20:38:43   6    ab integer := 2;
20:38:43   7  BEGIN
20:38:43   8    a();
20:38:43   9  END;
20:38:45  10  /
  ab integer := 2;
  *
第 6 行出现错误:
ORA-06550: 第 6 行, 第 3 列:
PLS-00103: 出现符号 "AB"在需要下列之一时：
begin function pragma
procedure
ORA-06550: 第 9 行, 第 4 列:
PLS-00103: 出现符号 "end-of-file"在需要下列之一时：
end not pragma
final instantiable order overriding static member constructor map


20:38:46 SYS@orcl> DECLARE
20:39:28   2    ab integer := 2;
20:39:28   3    PROCEDURE a IS
20:39:28   4    BEGIN
20:39:28   5      dbms_output.put_line(ab);
20:39:28   6    END;
20:39:28   7  BEGIN
20:39:28   8    a();
20:39:28   9  END;
20:39:28  10  /


PL/SQL 过程已成功完成。


已用时间:  00: 00: 00.00
20:39:29 SYS@orcl> SET SERVEROUTPUT ON
20:39:41 SYS@orcl> /
2


PL/SQL 过程已成功完成。

如果是过程/函数调用过程/函数则可使用向前引用.
向前引用:

20:41:24 SYS@orcl> DECLARE
20:41:31   2    PROCEDURE b(caller VARCHAR2); --向前引用.
20:41:31   3    PROCEDURE a(caller VARCHAR2) IS
20:41:31   4      procedure_name VARCHAR2(1) := 'A';
20:41:31   5    BEGIN
20:41:31   6      dbms_output.put_line('Procedure "A" called by [' || caller || ']');
20:41:31   7      b(procedure_name);
20:41:31   8    END;
20:41:31   9    PROCEDURE b(caller VARCHAR2) IS
20:41:31  10      procedure_name VARCHAR2(1) := 'B';
20:41:31  11    BEGIN
20:41:31  12      dbms_output.put_line('Procedure "B" called by [' || caller || ']');
20:41:31  13    END;
20:41:31  14  BEGIN
20:41:31  15    a('Main');
20:41:31  16  END;
20:41:32  17  /
Procedure "A" called by [Main]
Procedure "B" called by [A]


PL/SQL 过程已成功完成。

在其他的PL/SQL块中调用包的存储或函数等,使用方法包名.存储名(或函数名、变量名)
注意:包的变量类型可以包共享游标.而共享游标是互斥的,也就是任何时候只有一个进程来运行它.


重载(Overloading)
重载就是指你可以创建同一个名称而有不同参数(数据类型不同或数量不同)的存储或函数.
但是PL/SQL不允许只简单改变变量的名称的重载形式,如:

--创建的时候不会报错
20:59:48 SCOTT@orcl> CREATE OR REPLACE PACKAGE not_overloading IS
20:59:50   2    FUNCTION adding(a NUMBER, b NUMBER) RETURN NUMBER;
20:59:50   3    FUNCTION adding(one NUMBER, two NUMBER) RETURN BINARY_INTEGER;
20:59:50   4  END not_overloading;
20:59:51   5  /


程序包已创建。


--使用的时候报错
20:59:52 SCOTT@orcl> select not_overloading.adding(1,2) from dual;
select not_overloading.adding(1,2) from dual
       *
第 1 行出现错误:
ORA-06553: PLS-307: 有太多的 'ADDING' 声明与此次调用相匹配


--重载示例
21:05:49 SCOTT@orcl> CREATE OR REPLACE PACKAGE overloading IS
21:06:16   2  FUNCTION adding (a NUMBER, b NUMBER) RETURN NUMBER;
21:06:18   3  FUNCTION adding (a VARCHAR2, b VARCHAR2) RETURN VARCHAR2;
21:06:20   4  END;
21:06:31   5  /


程序包已创建。


已用时间:  00: 00: 00.00
21:06:33 SCOTT@orcl> CREATE OR REPLACE PACKAGE BODY overloading IS
21:06:46   2  FUNCTION adding (a VARCHAR2, b VARCHAR2) RETURN VARCHAR2
21:06:51   3  IS
21:06:55   4  begin RETURN a||','||b; end;
21:07:25   5  FUNCTION adding (a NUMBER, b NUMBER) RETURN NUMBER
21:07:32   6  IS
21:07:34   7  begin RETURN a+b; end;
21:07:45   8  end;
21:07:48   9  /


程序包体已创建。


已用时间:  00: 00: 00.06
--包内函数查看
21:16:29 SCOTT@orcl> desc overloading
FUNCTION ADDING RETURNS NUMBER
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
 A                              NUMBER                  IN
 B                              NUMBER                  IN
FUNCTION ADDING RETURNS VARCHAR2
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
 A                              VARCHAR2                IN
 B                              VARCHAR2                IN
 
21:07:49 SCOTT@orcl> select overloading.adding(1,2) from dual;


OVERLOADING.ADDING(1,2)
-----------------------
                      3


已选择 1 行。
21:10:53 SCOTT@orcl> select overloading.adding('1','2') a from dual;


A
-----
1,2


已选择 1 行。

包头的原型
CREATE [OR REPLACE] PACKAGE package_name [AUTHID {DEFINER | CURRENT_USER}] IS
[PRAGMA SERIALLY_REUSABLE;]
[variable_name [CONSTANT] scalar_datatype [:= value];]
[collection_name [CONSTANT] collection_datatype [:= constructor];]
[object_name [CONSTANT] object_datatype [:= constructor];]
[TYPE record_structure IS RECORD
( field_name1 datatype
[,field_name2 datatype
[,field_name(n+1) datatype]]);]
[CURSOR cursor_name
[(parameter_name1 datatype
[,parameter_name2 datatype
[,parameter_name(n+1) datatype]])] IS select_statement;]
[TYPE ref_cursor IS REF CURSOR [RETURN { catalog_row | record_structure }];]
[user_exception_name EXCEPTION;
[PRAGMA EXCEPTION_INIT(user_exception_name,-20001);]]
[FUNCTION function_name
[( parameter1 [IN][OUT] [NOCOPY] sql_datatype | plsql_datatype
[, parameter2 [IN][OUT] [NOCOPY] sql_datatype | plsql_datatype
[, parameter(n+1) [IN][OUT] [NOCOPY] sql_datatype | plsql_datatype )]
RETURN { sql_data_type | plsql_data_type }
[ DETERMINISTIC | PARALLEL_ENABLED ]
[ PIPELINED ]
[ RESULT_CACHE [ RELIES_ON (table_name) ]];]
[ PRAGMA RESTRICT_REFERENCES ({ DEFAULT | function_name }
, option1 [, option2 [, option(n+1) ]]); ]
[PROCEDURE procedure_name
[( parameter1 [IN][OUT] [NOCOPY] sql_datatype | plsql_datatype
[, parameter2 [IN][OUT] [NOCOPY] sql_datatype | plsql_datatype
[, parameter(n+1) [IN][OUT] [NOCOPY] sql_datatype | plsql_datatype])];]
END package_name;
其中预编译指令SERIALLY_REUSABLE PRAGMA只能用在包的上下文.你必须在包头和包体中同时使用该预编译指令.
在你想共享变量的时候,该预编译指令是很重要的.(因为该预编译指令可以使得包变量在每次调用时都初始化)
CONSTANT表示变量是静态只读的.CONSTANT变量定义后不能被赋值,否则你会获得PLS-00363错误.
如:

00:49:36 SCOTT@orcl> CREATE OR REPLACE PACKAGE PKG_TEST01 AS
00:49:53   2   a CONSTANT NUMBER:=123;
00:50:04   3  END;
00:50:07   4  /


程序包已创建。
00:51:40 SCOTT@orcl> begin
00:51:54   2  pkg_test01.a:=222;
00:51:57   3  dbms_output.put_line(pkg_test01.a);
00:51:59   4  end;
00:52:03   5  /
pkg_test01.a:=222;
           *
第 3 行出现错误:
ORA-06550: 第 3 行, 第 12 列:
PLS-00363: 表达式 'PKG_TEST01.A' 不能用作赋值目标
ORA-06550: 第 3 行, 第 1 列:
PL/SQL: Statement ignored

方案级程序(Schema-Level Programs)
函数、存储、包、对象等都是方案级程序。只有方案级的程序才能被定义为调用者权限或定义者权限。
缺省情况下就是定义者权限,定义者权限是指运行该程序的用户拥有和定义该程序的用户(方案的拥有者--the owner of the schema)一样的权限。
使用AUTHID CURRENT_USER标识程序使用调用者权限.调用者权限是指调用该程序的当前用户的权限.
包里的函数或过程不用定义调用权限,因为它们不是方案级程序。
例:

20:09:31 SCOTT@ORCL> create or replace package body gw_test01 as
20:09:32   2      function f_test_gw(pvar varchar2) RETURN VARCHAR2 AUTHID CURRENT_USER IS
20:09:32   3      begin
20:09:32   4      return pvar || ' Just Test!';
20:09:32   5      end f_test_gw;
20:09:32   6    end;
20:09:34   7    /


警告: 创建的包体带有编译错误。


已用时间:  00: 00: 00.03
20:09:35 SCOTT@orcl> show err
PACKAGE BODY GW_TEST01 出现错误:


LINE/COL ERROR
-------- -----------------------------------------------------------------
2/60     PLS-00157: AUTHID 只允许在方案级程序中使用




在使用结果集缓存(RESULT_CACHE)的时候,不能同时使用AUTHID CURRENT_USER
如:
21:33:49 SCOTT@orcl> create or replace function f_add(a int, b int)
21:55:10   2  return int authid current_user RESULT_CACHE
21:55:10   3  is
21:55:10   4  begin
21:55:10   5    return a + b;
21:55:10   6  end;
21:55:11   7  /


警告: 创建的函数带有编译错误。


已用时间:  00: 00: 00.56
21:55:12 SCOTT@orcl> show err
FUNCTION F_ADD 出现错误:


LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
1/10     PLS-00999: 实施限制 (可能是临时) RESULT_CACHE is disallowed on
         subprograms in Invoker-Rights modules

变量
使用SERIALLY_REUSABLE PRAGMA参数指定包为连续可重用的.(使用SERIALLY_REUSABLE,每次调用都会初始化包变量)
使用SERIALLY_REUSABLE后,包变量存储在SGA而不是UGA.
SERIALLY_REUSABLE需要在包头和包体中同时使用.
缺省情况下是非连续可重用的.
连续可重用的会在其他程序单元调用该变量时产生一个副本值,而非连续可重用的会直接重用变量。
包级变量可被其他PL/SQL单元访问,也就是共享的变量.包级变量可跨会话存在。
在SGA中,不常用的包会被逐渐移出SGA.
注:包变量不能使用SQL来访问,只能通过PL/SQL块来访问.
例:

--非SERIALLY_REUSABLE的包
15:42:51 SCOTT@orcl> CREATE OR REPLACE PACKAGE shared_variables IS
15:45:53   2    protected CONSTANT NUMBER := 1;
15:45:53   3    unprotected NUMBER := 1;
15:45:53   4  END shared_variables;
15:45:54   5  /


SP2-0808: 程序包已创建, 但带有编译警告


已用时间:  00: 00: 00.03
15:45:55 SCOTT@orcl> CREATE OR REPLACE PROCEDURE change_unprotected(value NUMBER) IS
15:46:01   2  BEGIN
15:46:01   3    shared_variables.unprotected := shared_variables.unprotected + value;
15:46:01   4    dbms_output.put_line('Unprotected [' || shared_variables.unprotected || ']');
15:46:01   5  END change_unprotected;
15:46:02   6  /


SP2-0804: 过程已创建, 但带有编译警告


已用时间:  00: 00: 00.04
--每执行一次使用存储change_unprotected,都会直接重用包变量unprotected
--所以每次都会递增,直到当前会话结束或者包被重新编译
15:46:03 SCOTT@orcl> EXECUTE change_unprotected(2);
Unprotected [3]


PL/SQL 过程已成功完成。


已用时间:  00: 00: 00.00
15:46:19 SCOTT@orcl> EXECUTE change_unprotected(2);
Unprotected [5]


PL/SQL 过程已成功完成。


已用时间:  00: 00: 00.00
16:28:10 SCOTT@orcl> alter package shared_variables compile SPECIFICATION;


SP2-0809: 程序包已变更, 但带有编译警告


已用时间:  00: 00: 00.04
16:28:19 SCOTT@orcl> EXECUTE change_unprotected(2);
Unprotected [3]


PL/SQL 过程已成功完成。


已用时间:  00: 00: 00.00


--使用SERIALLY_REUSABLE的包
15:46:21 SCOTT@orcl> CREATE OR REPLACE PACKAGE shared_variables IS
15:46:45   2    PRAGMA SERIALLY_REUSABLE;
15:46:45   3    protected CONSTANT NUMBER := 1;
15:46:45   4    unprotected NUMBER := 1;
15:46:45   5  END shared_variables;
15:46:47   6  /


SP2-0808: 程序包已创建, 但带有编译警告

--每次执行change_unprotected存储,包变量都是一样的值,每调用一次存储,都会产生包变量unprotected的一个副本
--直到SGA相应内存被耗完
15:46:48 SCOTT@orcl> EXECUTE change_unprotected(2);
Unprotected [3]


PL/SQL 过程已成功完成。


已用时间:  00: 00: 00.00
15:46:52 SCOTT@orcl> EXECUTE change_unprotected(2);
Unprotected [3]


PL/SQL 过程已成功完成。


已用时间:  00: 00: 00.01

连续可重用变量和常量的不同在于常量不可修改(即不能被赋值).

类型
通常在包的定义里有两种常见的数据类型。一种静态的,一种是动态的。
动态的数据类型是指:使用%ROWTYPE或%TYPE来定义变量的数据类型、共享型游标
静态的数据类型是指:显示的使用DATE,INTEGER,NUMBER,VARCHAR2来定义变量的数据类型
引用包的记录类型变量或者集合类型变量的PL/SQL块依赖于包的状态,如果包无效了,那么
依赖它的PL/SQL单元也会无效。

22:18:24 SCOTT@orcl> select status from user_objects where object_name='PKG_TEST';


STATUS
-------
INVALID


已选择 1 行。


已用时间:  00: 00: 00.03
22:19:06 SCOTT@orcl> begin
22:19:13   2  dbms_output.put_line(pkg_test.i);
22:19:14   3  end;
22:19:14   4  /
dbms_output.put_line(pkg_test.i);
                     *
第 2 行出现错误:
ORA-06550: 第 2 行, 第 22 列:
PLS-00905: 对象 SCOTT.PKG_TEST 无效
ORA-06550: 第 2 行, 第 1 列:
PL/SQL: Statement ignored

共享游标可以同时被多个程序单元查询.
在11g之前没有游标的读一致性,需要你声明包为连续可重用才能确保游标的读一致性.

--共享游标的使用
22:56:54 SCOTT@orcl> CREATE OR REPLACE PACKAGE shared_cursors IS
22:57:26   2    CURSOR v_cursor IS
22:57:26   3      SELECT EMPLOYEE_ID, LAST_NAME FROM PLCH_EMPLOYEES;
22:57:26   4  END shared_cursors;
22:57:27   5  /


程序包已创建。


已用时间:  00: 00: 00.34
22:58:16 SCOTT@orcl> BEGIN
22:58:34   2    FOR i IN shared_cursors.v_cursor LOOP
22:58:34   3      dbms_output.put_line('[' || i.EMPLOYEE_ID || '][' || i.LAST_NAME || ']');
22:58:34   4    END LOOP;
22:58:34   5  END;
22:58:35   6  /
[1][Emp 1]
[2][Emp 2]
[3][Emp 3]
[4][Emp 4]
[5][Emp 5]


PL/SQL 过程已成功完成。


已用时间:  00: 00: 00.09


--共享类型的使用
23:03:27 SCOTT@orcl> CREATE OR REPLACE PACKAGE shared_types IS
23:03:43   2    CURSOR v_cursor IS
23:03:43   3      SELECT EMPLOYEE_ID, LAST_NAME FROM PLCH_EMPLOYEES;
23:03:43   4    TYPE item_type IS RECORD(
23:03:43   5      v_id    PLCH_EMPLOYEES.EMPLOYEE_ID%TYPE,
23:03:43   6      v_title PLCH_EMPLOYEES.LAST_NAME%TYPE);
23:03:43   7  END shared_types;
23:03:44   8  /


程序包已创建。


已用时间:  00: 00: 00.10
23:03:45 SCOTT@orcl> VARIABLE refcur REFCURSOR --定义引用游标类型变量
23:04:30 SCOTT@orcl> DECLARE
23:05:27   2    TYPE package_typed IS REF CURSOR RETURN shared_types.item_type;--引用游标只支持显示游标
23:05:27   3    quick PACKAGE_TYPED;
23:05:27   4  BEGIN
23:05:27   5    OPEN quick FOR
23:05:27   6      SELECT EMPLOYEE_ID, LAST_NAME FROM PLCH_EMPLOYEES;
23:05:27   7     :refcur := quick;
23:05:27   8  END;
23:05:27   9  /


PL/SQL 过程已成功完成。


已用时间:  00: 00: 00.09
23:05:28 SCOTT@orcl> SELECT :refcur FROM dual;


:REFCUR
--------------------
CURSOR STATEMENT : 1


CURSOR STATEMENT : 1


EMPLOYEE_ID LAST_NAME
----------- --------------------
          1 Emp 1
          2 Emp 2
          3 Emp 3
          4 Emp 4
          5 Emp 5


已选择5行。
--注意此时不能使用动态语句,否则会获得PLS-00455错误
23:13:57 SCOTT@orcl> DECLARE
23:14:01   2    TYPE package_typed IS REF CURSOR RETURN shared_types.item_type;
23:14:01   3    quick PACKAGE_TYPED;
23:14:01   4  BEGIN
23:14:01   5    OPEN quick FOR
23:14:01   6      'SELECT EMPLOYEE_ID, LAST_NAME FROM PLCH_EMPLOYEES';
23:14:01   7     :refcur := quick;
23:14:01   8  END;
23:14:01   9  /
  OPEN quick FOR
       *
第 5 行出现错误:
ORA-06550: 第 5 行, 第 8 列:
PLS-00455: 游标 'QUICK' 不能在动态 SQL OPEN 语句中使用
ORA-06550: 第 5 行, 第 3 列:
PL/SQL: Statement ignored

包内的函数和存储
在USER_ARGUMENTS、ALL_ARGUMENTS、DBA_ARGUMENTS视图里存放着包内的函数和存储的参数定义.
当在RESTRICT_REFERENCES预编译指令中使用DEFAULT而不是函数名的时候,RESTRICT_REFERENCES将应用到所有函数.
如:PRAGMA RESTRICT_REFERENCES(DEFAULT, WNDS);--约束包内所有函数的行为,确保它们不能写任何数据库状态.


包体
包体包含包规格(包头)声明的实现.
必须严格匹配,包括形参的缺省值都需要和包规格里一样.
9i之前,你应当在包规格里声明形参,并且不用强制出现在包体里.
所以你从8i迁移到高版本的数据库时,应当注意.
在包体的函数里你不能定义PRAGMA指令,否则会出现PLS-00708错误.
包体原型如下:
CREATE [OR REPLACE] PACKAGE package_name [AUTHID {DEFINER | CURRENT_USER}] IS
[PRAGMA SERIALLY_REUSABLE;]
[variable_name [CONSTANT] scalar_datatype [:= value];]
[collection_name [CONSTANT] collection_datatype [:= constructor];]
[object_name [CONSTANT] object_datatype [:= constructor];]
[TYPE record_structure IS RECORD
( field_name1 datatype
[,field_name2 datatype
[,field_name(n+1) datatype]]);]
[CURSOR cursor_name
[(parameter_name1 datatype
[,parameter_name2 datatype
[,parameter_name(n+1) datatype]])] IS
select_statement;]
[TYPE ref_cursor IS REF CURSOR [RETURN { catalog_row | record_structure }];]
[user_exception_name EXCEPTION;
[PRAGMA EXCEPTION_INIT(user_exception_name,-20001);]]
-- This is a forward referencing stub to a function implemented later.
[FUNCTION function_name
[( parameter1 [IN][OUT] [NOCOPY] sql_datatype | plsql_datatype
[, parameter2 [IN][OUT] [NOCOPY] sql_datatype | plsql_datatype
[, parameter(n+1) [IN][OUT] [NOCOPY] sql_datatype | plsql_datatype )]
RETURN { sql_data_type | plsql_data_type }
[ DETERMINISTIC | PARALLEL_ENABLED ]
[ PIPELINED ]
[ RESULT_CACHE [ RELIES_ON (table_name) ]];]
-- This is a forward referencing stub to a procedure implemented later.
[PROCEDURE procedure_name
[( parameter1 [IN][OUT] [NOCOPY] sql_datatype | plsql_datatype
[, parameter2 [IN][OUT] [NOCOPY] sql_datatype | plsql_datatype
[, parameter(n+1) [IN][OUT] [NOCOPY] sql_datatype | plsql_datatype])];]
[FUNCTION function_name
[( parameter1 [IN][OUT] [NOCOPY] sql_datatype | plsql_datatype
[, parameter2 [IN][OUT] [NOCOPY] sql_datatype | plsql_datatype
[, parameter(n+1) [IN][OUT] [NOCOPY] sql_datatype | plsql_datatype )]
RETURN { sql_data_type | plsql_data_type }
[ DETERMINISTIC | PARALLEL_ENABLED ]
[ PIPELINED ]
[ RESULT_CACHE [ RELIES_ON (table_name) ]] IS
[ PRAGMA AUTONOMOUS_TRANSACTION;] -- Check rules in Chapter 6.
some_declaration_statement; -- Check rules in Chapter 6.
BEGIN
some_execution_statement; -- Check rules in Chapter 6.
[EXCEPTION
WHEN some_exception THEN
some exception_handling_statement;] -- Check rules in Chapter 5.
END [function_name];]
[PROCEDURE procedure_name
[( parameter1 [IN][OUT] [NOCOPY] sql_datatype | plsql_datatype
[, parameter2 [IN][OUT] [NOCOPY] sql_datatype | plsql_datatype
[, parameter(n+1) [IN][OUT] [NOCOPY] sql_datatype | plsql_datatype])] IS
[ PRAGMA AUTONOMOUS_TRANSACTION;] -- Check rules in Chapter 6.
some_declaration_statement; -- Check rules in Chapter 6.
BEGIN
some_execution_statement; -- Check rules in Chapter 6.
[EXCEPTION
WHEN some_exception THEN
some exception_handling_statement;] -- Check rules in Chapter 5.
END [procedure_name];]
END [package_name];
/


包体内的全局变量,包外的PL/SQL块不能访问.
如:

23:02:33 SCOTT@orcl> CREATE OR REPLACE PACKAGE package_variables IS
23:07:37   2    -- Declare package components.
23:07:37   3    PROCEDURE set(value VARCHAR2);
23:07:37   4    FUNCTION get RETURN VARCHAR2;
23:07:37   5  END package_variables;
23:07:39   6  /


程序包已创建。


已用时间:  00: 00: 00.57
23:07:40 SCOTT@orcl> CREATE OR REPLACE PACKAGE BODY package_variables IS
23:10:07   2    -- Declare package scope variable.
23:10:07   3    variable VARCHAR2(20) := 'Initial Value';
23:10:07   4    -- Define function
23:10:07   5    FUNCTION get RETURN VARCHAR2 IS
23:10:07   6    BEGIN
23:10:07   7      RETURN variable;
23:10:07   8    END get;
23:10:07   9    -- Define procedure.
23:10:07  10    PROCEDURE set(value VARCHAR2) IS
23:10:07  11    BEGIN
23:10:07  12      variable := value;
23:10:07  13    END set;
23:10:07  14  END package_variables;
23:10:09  15  /


程序包体已创建。


已用时间:  00: 00: 00.04
23:10:10 SCOTT@orcl> VARIABLE outcome VARCHAR2(20)
23:10:50 SCOTT@orcl> CALL package_variables.get() INTO :outcome;


调用完成。


已用时间:  00: 00: 00.01
23:11:08 SCOTT@orcl> SELECT :outcome AS outcome FROM dual;


OUTCOME
--------------------------------
Initial Value


已选择 1 行。


已用时间:  00: 00: 00.00
23:11:18 SCOTT@orcl> EXECUTE package_variables.set('New Value');


PL/SQL 过程已成功完成。


已用时间:  00: 00: 00.00
23:11:36 SCOTT@orcl> CALL package_variables.get() INTO :outcome;


调用完成。


已用时间:  00: 00: 00.00
23:11:46 SCOTT@orcl> SELECT :outcome AS outcome FROM dual;


OUTCOME
--------------------------------
New Value


已选择 1 行。


已用时间:  00: 00: 00.00
--包体的公共变量不可访问
23:11:53 SCOTT@orcl> begin
23:12:40   2  dbms_output.put_line(package_variables.variable);
23:13:09   3  end;
23:13:11   4  /
dbms_output.put_line(package_variables.variable);
                                       *
第 2 行出现错误:
ORA-06550: 第 2 行, 第 40 列:
PLS-00302: 必须声明 'VARIABLE' 组件
ORA-06550: 第 2 行, 第 1 列:
PL/SQL: Statement ignored


已用时间:  00: 00: 00.00

单例模式(Singleton Design Pattern)
单例模式中的一个对象只能创建一个实例.直到原实例对象被废弃,否则你不能新建实例.
这种模式在C++,C#,JAVA等面向对象编程中用的很广泛.
你同样可以使用这种模式确保在任何会话中只能实例化一个包.

包体中同样可以声明自己的函数和存储,不过你需要在包体中完成声明(定义)与实现.
只有包头中定义的函数/存储才能被外部PL/SQL块访问.
函数几乎总是声明在存储之前,但在包头中是无顺序的,在局部使用时,为了尽可能的向前引用,函数声明在
存储之前是有意义的.
--单例模式演示 共享包变量variable在会话中只能实例化一个(因为内部有locked函数及unlock存储)
SQL> CREATE OR REPLACE PACKAGE components IS
2 PROCEDURE set(value VARCHAR2); -- Declare published procedure.
3 FUNCTION get RETURN VARCHAR2; -- Declare published function.
4 END components;
5 /


程序包已创建。


SQL> CREATE OR REPLACE PACKAGE BODY components IS
2 -- Declare package scoped shared variables.
3 key NUMBER := 0;
4 variable VARCHAR2(20) := 'Initial Value';
5 -- Define package-only function and procedure.
6 FUNCTION locked RETURN BOOLEAN IS
7 key NUMBER := 0;
8 BEGIN
9 IF components.key = key THEN
10 components.key := 1;
11 RETURN FALSE;
12 ELSE
13 RETURN TRUE;
14 END IF;
15 END locked;
16 PROCEDURE unlock IS
17 key NUMBER := 1;
18 BEGIN
19 IF components.key = key THEN
20 components.key := 0; -- Reset the key.
21 variable := 'Initial Value'; -- Reset initial value of shared variable.
22 END IF;
23 END unlock;
24 -- Define published function and procedure.
25 FUNCTION get RETURN VARCHAR2 IS
26 BEGIN
27 RETURN variable;
28 END get;
29 PROCEDURE set(value VARCHAR2) IS
30 BEGIN
31 IF NOT locked THEN
32 variable := value;
33 dbms_output.put_line('The new value until release is [' || get || '].');
34 unlock;
35 END IF;
36 END set;
37 END components;
38 /


程序包体已创建。
SQL> VARIABLE current_content VARCHAR2(20)
SQL> CALL components.get() INTO :current_content;


调用完成。


SQL> SELECT :current_content AS contents FROM dual;


CONTENTS
--------------------------------
Initial Value


SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> EXECUTE components.set('New Value');
The new value until release is [New Value].


PL/SQL 过程已成功完成。


SQL> CALL components.get() INTO :current_content;


调用完成。


SQL> SELECT :current_content AS contents FROM dual;


CONTENTS
--------------------------------
Initial Value

--注意对比
SQL> CREATE OR REPLACE PACKAGE BODY components IS
2 -- Declare package scoped shared variables.
3 key NUMBER := 0;
4 variable VARCHAR2(20) := 'Initial Value';
5 -- Define published function and procedure.
6 FUNCTION get RETURN VARCHAR2 IS
7 BEGIN
8 RETURN variable;
9 END get;
10 PROCEDURE set(value VARCHAR2) IS
11 BEGIN
12 variable := value;
13 dbms_output.put_line('The new value until release is [' || get || '].');
14 END set;
15 END components;
16 /


程序包体已创建。


SQL> VARIABLE current_content VARCHAR2(20)
SQL> CALL components.get() INTO :current_content;


调用完成。


SQL> SELECT :current_content AS contents FROM dual;


CONTENTS
--------------------------------
Initial Value


SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> EXECUTE components.set('New Value');
The new value until release is [New Value].


PL/SQL 过程已成功完成。


SQL> CALL components.get() INTO :current_content;


调用完成。


SQL> SELECT :current_content AS contents FROM dual;


CONTENTS
--------------------------------
New Value




授权与同义词
如果你在plsql模式下以定义者权限创建了一个包manage_items,在另一个模式purchasing里想执行这个包.
你需要首先在plsql模式下给purchasing用户赋予包manage_items的执行权限:
GRANT EXECUTE ON manage_items TO purchasing;
此时你在purchasing用户下可以正常使用包manage_item,但是每次使用都需要带上plsql的模式名,如:
DESCRIBE plsql.manage_items
你可以通过同义词来避免每次都写模式名.同义词就是对象的别名。
CREATE SYNONYM manage_items FOR plsql.manage_items;
你可以使用PUBLIC关键字来给其他所有的用户来赋予包manage_item的执行权限:
GRANT EXECUTE ON manage_items TO PUBLIC;


远程调用(Remote Calls)
远程调用就是从一个数据库实例调用另一个数据库实例的对象.
通过DB_LINK实现远程调用,建立DB_LINK需要有CREATE DATABASE LINK权限.
12:51:53 SYS@orcl> GRANT CREATE DATABASE LINK TO CRY;


授权成功。


DB_LINK创建:
--原型
CREATE DATABASE LINK db_link_name
CONNECT TO schema_name IDENTIFIED BY schema_password
USING 'tns_names_alias'


DB_LINK是数据库的静态对象.它存储模式名以及对应的密码,以便可以连接到远程的实例。
当远程实例的密码发生改变时,你需要手动更新DB_LINK的对应密码.
DB_LINK可以连接同一个数据库的不同schema,也可以连接其他数据库的实例.
使用: SELECT * FROM TAB_NAME@db_link_name;--查询远程数据库的表TAB_NAME
注意:远程实例表中不能有LOB对象,否则会报ORA-22992错误.


数据库目录中包的管理
查找包、验证包是否有效、描述包
管理视图all_objects,dba_objects,user_objects让你可以查找包对象,并且验证包头或包体是否有效.

13:09:34 SCOTT@orcl> col object_name format a18
13:09:53 SCOTT@orcl> SELECT object_name
13:20:18   2        ,object_type
13:20:18   3        ,last_ddl_time
13:20:18   4        ,TIMESTAMP
13:20:18   5        ,status
13:20:18   6    FROM user_objects
13:20:18   7   WHERE object_name IN ('FN_GETNAME', 'PKG_SERIALLY_TEST');


OBJECT_NAME        OBJECT_TYPE         LAST_DDL_TIME  TIMESTAMP           STATUS
------------------ ------------------- -------------- ------------------- -------
FN_GETNAME         FUNCTION            09-1月 -13     2013-01-09:11:42:21 VALID
PKG_SERIALLY_TEST  PACKAGE             08-5月 -13     2013-05-08:15:41:21 VALID
PKG_SERIALLY_TEST  PACKAGE BODY        22-5月 -13     2013-05-22:13:20:05 VALID


已选择3行。

如果包体编译出错,则对应的STATUS字段会变为INVALID.但是包内所使用的函数/存储仍然是VALID的.
因为这些函数、存储是直接依赖于包头的.包体无效,包头的状态仍然是VALID的.

13:20:19 SCOTT@orcl> /


OBJECT_NAME        OBJECT_TYPE         LAST_DDL_TIME  TIMESTAMP           STATUS
------------------ ------------------- -------------- ------------------- -------
PKG_SERIALLY_TEST  PACKAGE BODY        22-5月 -13     2013-05-22:13:20:50 INVALID
PKG_SERIALLY_TEST  PACKAGE             08-5月 -13     2013-05-08:15:41:21 VALID
FN_GETNAME         FUNCTION            09-1月 -13     2013-01-09:11:42:21 VALID


已选择3行。

如果包头无效的话,那么依赖它的包体也会变无效.

13:24:24 SCOTT@orcl> /


OBJECT_NAME        OBJECT_TYPE         LAST_DDL_TIME  TIMESTAMP           STATUS
------------------ ------------------- -------------- ------------------- -------
PKG_SERIALLY_TEST  PACKAGE BODY        22-5月 -13     2013-05-22:13:24:11 INVALID
PKG_SERIALLY_TEST  PACKAGE             22-5月 -13     2013-05-22:13:24:38 INVALID
FN_GETNAME         FUNCTION            09-1月 -13     2013-01-09:11:42:21 VALID


已选择3行。


描述包
13:27:15 SCOTT@orcl> desc PKG_SERIALLY_TEST
FUNCTION INIT_PKG_STATE RETURNS VARCHAR2
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
 N                              NUMBER                  IN
PROCEDURE PRINT_PKG_STATE

你会发现包头内声明的记录、集合等变量类型无法通过desc查看到。你需要通过查询视图all_source,dba_source,user_source其中之一
来查看完整的包头定义。(注意加密的包将会看到乱码)


依赖性检查
根据管理视图all_dependencies,dba_dependencies,user_dependencies来检查对象间的依赖.

SQL> SELECT NAME
  2        ,TYPE
  3        ,referenced_name
  4        ,referenced_type
  5        ,dependency_type
  6    FROM USER_DEPENDENCIES
  7   WHERE NAME = 'DBMS_LOB';
 
NAME       TYPE            REFERENCED_NAME                REFERENCED DEPE
---------- --------------- ------------------------------ ---------- ----
DBMS_LOB   PACKAGE BODY    STANDARD                       PACKAGE    HARD
DBMS_LOB   PACKAGE         STANDARD                       PACKAGE    HARD
DBMS_LOB   PACKAGE BODY    DBMS_LOB                       PACKAGE    HARD

使用时间戳或签名方法使得依赖的对象有效或无效
缺省是使用时间戳方法:
时间戳方法是对比all_objects,dba_objects,user_objects其中之一的视图中的LAST_DDL_TIME字段。
当基础对象比依赖对象有更新的时间戳时,依赖对象将被重编译.
但是当两个数据库实例在不同的时区时,这种对比将是无效的.
签名方法:
这种方法对比模式级别和包级别的函数/存储的定义.改变数据库使用签名方法进行对象生效.
ALTER SYSTEM SET REMOTE_DEPENDENCIES_MODE = SIGNATURE;--需要有执行ALTER SYSTEM命令的权限
它会在编译事件中对比基础对象,如果发现发生改变,它将强制编译依赖包.你可以从视图

all_arguments,dba_arguments,user_arguments中查询对象的签名信息.