有些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