ITPub博客

首页 > 数据库 > Oracle > 11g_Dependency

11g_Dependency

原创 Oracle 作者:zhyuh 时间:2009-09-27 10:29:06 0 删除 编辑
下面的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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 11g_Virtual_Columns
下一篇: 11g_Result_Cache
请登录后发表评论 登录
全部评论
  • 博文量
    233
  • 访问量
    2009568