查看Oracle、MySQL、PostGreSQL中的依赖关系

查看Oracle、MySQL、PostGreSQL中的依赖关系

在有些程序员开发习惯中，喜欢为了应用代码的简洁或复用，而在数据库创建一个复杂关连查询的VIEW，甚至是VIEW套VIEW嵌套使用， 这里就有个问题如果上线后如发现依赖的表字段类型或长度不足时，修复一个view依赖的table列时发现在oracle、mysql、postgresql（本篇等同pg）中有不同的表现， 尤其是使用postgresql的用户需要格外注意， 因为pg 不允许直接修改， 学术派的严谨， 子之琼浆,彼之砒霜. 喜忧参半。

Oracle

SQL> CREATE TABLE t (id integer PRIMARY KEY);
Table created.

SQL> CREATE VIEW v AS SELECT * FROM t;
View created.

SQL> select status from user_objects where object_name='V';
STATUS
-------
VALID

SQL> DROP TABLE T;
Table dropped.

SQL> select status from user_objects where object_name='V';
STATUS
-------
INVALID

SQL> CREATE TABLE t (id integer PRIMARY KEY);
Table created.

SQL> select status from user_objects where object_name='V';
STATUS
-------
INVALID

SQL> select * from v;
no rows selected

SQL> select status from user_objects where object_name='V';
STATUS
-------
VALID

SQL> alter table t add name varchar2(20);
Table altered.

SQL> select status from user_objects where object_name='V';
STATUS
-------
VALID

SQL> desc v
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      ID                              NOT NULL NUMBER(38)

SQL> desc t
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      ID                              NOT NULL NUMBER(38)
    2      NAME                                     VARCHAR2(20)

SQL> @ddl v
PL/SQL procedure successfully completed.

DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OWNER)
--------------------------------------------------------------------------------
  CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."V" ("ID") AS
  SELECT "ID" FROM t;

SQL> alter table t modify id number(10);
Table altered.

SQL> select * from v;
no rows selected

SQL> desc v;
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      ID                              NOT NULL NUMBER(10)

SQL> alter table t rename to t100;
Table altered.

SQL> select status from user_objects where object_name='V';
STATUS
-------
INVALID

SQL> select * from v;
select * from v
              *
ERROR at line 1:
ORA-04063: view "SYS.V" has errors

Note:
在oracle中VIEW依赖的TABLE发生DDL后会变为INVALID状态，在下次调用时自动recompile， 如果依赖的表名，列名存在可以正常查询。修改依赖列长度正常修改，在依赖表rename后VIEW无法查询.

查找依赖关系

SQL> desc dba_dependencies
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      OWNER                           NOT NULL VARCHAR2(128)
    2      NAME                            NOT NULL VARCHAR2(128)
    3      TYPE                                     VARCHAR2(19)
    4      REFERENCED_OWNER                         VARCHAR2(128)
    5      REFERENCED_NAME                          VARCHAR2(128)
    6      REFERENCED_TYPE                          VARCHAR2(19)
    7      REFERENCED_LINK_NAME                     VARCHAR2(128)
    8      DEPENDENCY_TYPE                          VARCHAR2(4)


SQL> @dep % v % %
OWNER            DEPENDENT_NAME                 DEPENDENT_TY REF_OWNER        REF_NAME                       REF_TYPE     DEP_
---------------- ------------------------------ ------------ ---------------- ------------------------------ ------------ ----
SYS              V                              VIEW         SYS              T100                           TABLE        HARD

SQL> l
  1  select
  2     owner                   dep_owner,
  3     name                    dep_name,
  4     type                    dep_type,
  5     referenced_owner        dep_referenced_owner,
  6     referenced_name         dep_referenced_name,
  7     referenced_type         dep_referenced_type,
  8     dependency_type         dep_dependency_type
  9  --
 10  --from dba_dependencies where owner like '&1' and referenced_owner like '&2'
 11  --from dba_dependencies where owner like '&1' and name like '&2'
 12  --
 13  from
 14     dba_dependencies
 15  where
 16     lower(owner) like lower('&1'