oracle Schema Object Dependencies

  有些schema对象之间互相依赖. 比如基于表(tables)建立的视图.如果把视图依赖的基础表删除,那么视图就无法使用.

存储过程依赖表
# 创建一个表
CREATE TABLE test_table ( col1 INTEGER, col2 INTEGER );

# 创建一个存储过程
CREATE OR REPLACE PROCEDURE test_proc
AS
BEGIN
 FOR x IN ( SELECT col1, col2 FROM test_table )
 LOOP
   -- process data
   NULL;
 END LOOP;
END;
/


# 查询存储过程状态,是合法的
SQL> SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'TEST_PROC';
 
OBJECT_NAME STATUS
----------- -------
TEST_PROC   VALID


# 为表test_table 加上一列
ALTER TABLE test_table ADD col3 NUMBER;
# 再次查询存储过程状态,仍是合法的(because the procedure has no dependencies on this column,因为存储过程没有依赖新加的列)
SQL> SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'TEST_PROC';
 
OBJECT_NAME STATUS
----------- -------
TEST_PROC   VALID


# hanging the data type of the col1 column, which the test_proc procedure depends on in, invalidates the procedure:(改变col1列的数据类型,存储过程就编程不合法的)

SQL> ALTER TABLE test_table MODIFY col1 VARCHAR2(20);
 
Table altered.
 
SQL> SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'TEST_PROC';
 
OBJECT_NAME STATUS
----------- -------
TEST_PROC   INVALID


# 只有重新编译存储过程,存储过程才会再次变成合法(Running or recompiling the procedure makes it valid again)
SQL> EXECUTE test_proc
 
PL/SQL procedure successfully completed.
 
SQL> SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'TEST_PROC';
 
OBJECT_NAME STATUS
----------- -------
TEST_PROC   VALID