11g_Dependency

下面的SQL看上去比较长，简单来说就是做了以下事情：
1. 建表trans
2. 建基于表trans的视图vw_trans
3. 建package pkg_trans,期间调用trans表
4. 建函数adjust，调用pkg_trans[@more@]

SQL> create table trans (
2 trans_id number,
3 cust_name varchar2(20),
4 trans_dt date,
5 trans_amt number(12,2),
6 store_id number(2)
7 )
8 /

Table created.

SQL> create or replace view vw_trans
2 as
3 select trans_id, trans_amt
4 from trans;

View created.

SQL> create or replace package pkg_trans
2 is
3 procedure upd_trans_amt
4 (
5 p_trans_id trans.trans_id%type,
6 p_trans_amt trans.trans_amt%type
7 );
8 end;
9 /

Package created.

SQL> create or replace package body pkg_trans
2 is
3 procedure upd_trans_amt
4 (
5 p_trans_id trans.trans_id%type,
6 p_trans_amt trans.trans_amt%type
7 ) is
8 begin
9 update trans
10 set trans_amt = p_trans_amt
11 where trans_id = p_trans_id;
12 end;
13 end;
14 /

Package body created.

SQL> create or replace function adjust
2 (
3 p_trans_id number,
4 p_percentage number
5 )
6 return boolean
7 is
8 l_new_trans_amt number(12);
9 begin
10 select trans_amt * (1 + p_percentage/100)
11 into l_new_trans_amt
12 from trans
13 where trans_id = p_trans_id;
14 pkg_trans.upd_trans_amt (
15 p_trans_id,
16 p_percentage
17 );
18 return TRUE;
19 exception
20 when OTHERS then
21 return FALSE;
22 end;
23 /

Function created.

检查一下这些对象的依赖关系
SQL> select d.name,d.referenced_name,o.status,o.object_type
2 from user_dependencies d, user_objects o
3 where d.name=o.object_name
4 and d.REFERENCED_NAME in ('TRANS','PKG_TRANS')
5 order by 2;

NAME REFERENCED_NAME STATUS OBJECT_TYPE
--------------- -------------------- ------- ----------------
PKG_TRANS PKG_TRANS VALID PACKAGE BODY
PKG_TRANS PKG_TRANS VALID PACKAGE
ADJUST PKG_TRANS VALID FUNCTION
VW_TRANS TRANS VALID VIEW
PKG_TRANS TRANS VALID PACKAGE BODY
PKG_TRANS TRANS VALID PACKAGE BODY
PKG_TRANS TRANS VALID PACKAGE
ADJUST TRANS VALID FUNCTION
PKG_TRANS TRANS VALID PACKAGE

9 rows selected.

************
* 10g
************

SQL> alter table trans add col_6 number;

Table altered

SQL>
SQL> select d.name,d.referenced_name,o.status,o.object_type
2 from user_dependencies d, user_objects o
3 where d.name=o.object_name
4 and d.REFERENCED_NAME in ('TRANS','PKG_TRANS')
5 order by 2;

NAME REFERENCED_NAME STATUS OBJECT_TYPE
----------------- ---------------- ------- -------------------
......
ADJUST PKG_TRANS INVALID FUNCTION
PKG_TRANS TRANS INVALID PACKAGE
PKG_TRANS TRANS INVALID PACKAGE BODY
VW_TRANS TRANS INVALID VIEW

在10g和以前的版本中，基表trans做任何改动（比如增加一个字段，对已经存在的字段和数据不做任何修改），都会引起所有相关的视图，存储过程，函数，包等的失效。

************
* 11g
************

SQL> alter table trans add col_6 number;

Table altered.

SQL> select d.name,d.referenced_name,o.status,o.object_type
2 from user_dependencies d, user_objects o
3 where d.name=o.object_name
4 and d.REFERENCED_NAME in ('TRANS','PKG_TRANS')
5 order by 2;

NAME REFERENCED_NAME STATUS OBJECT_TYPE
--------------- -------------------- ------- -----------------
......
ADJUST PKG_TRANS INVALID FUNCTION
VW_TRANS TRANS VALID VIEW
PKG_TRANS TRANS VALID PACKAGE
PKG_TRANS TRANS INVALID PACKAGE BODY

9 rows selected.

SQL> create or replace package pkg_trans
2 is
3 procedure upd_trans_amt
4 (
5 p_trans_id trans.trans_id%type,
6 p_trans_amt trans.trans_amt%type
7 );
8 procedure upd_vendor_name
9 (
10 p_trans_id trans.trans_id%type,
11 p_vendor_name trans.col_6%type
12 );
13
14 end;
15 /

Package created.

SQL> create or replace package body pkg_trans
2 is
3 procedure upd_trans_amt
4 (
5 p_trans_id trans.trans_id%type,
6 p_trans_amt trans.trans_amt%type
7 ) is
8 begin
9 update trans
10 set trans_amt = p_trans_amt
11 where trans_id = p_trans_id;
12 end;
13 procedure upd_vendor_name
14 (
15 p_trans_id trans.trans_id%type,
16 p_vendor_name trans.col_6%type
17 ) is
18 begin
19 update trans
20 set col_6 = col_6
21 where trans_id = p_trans_id;
22 end;
23 end;
24 /

Package body created.

SQL> select object_name,status,object_type from dba_objects where object_name='ADJUST';

OBJECT_NAME STATUS OBJECT_TYPE
-------------- ------- -------------------
ADJUST VALID FUNCTION

......

还有其他一些测试，所有过程都贴出来太啰嗦，罗列一些结论：
1. 11g中，如果基表中增加一个字段，不会引起相应视图变成无效(INVALID)
2. 如果基表修改/删除一个跟视图无关的字段，也不会引起视图变成无效(INVALID)
3. 如果在package最后增加一个procedure/function，则基于该package的package/procedure/function...不会变成无效；如果加在中间或者前面就会引起调用对象失效。
......
可能还有一些，太啰嗦了。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/207/viewspace-1027392/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/207/viewspace-1027392/