修改表結構後一定會另依賴物件失效嗎？
create table wxh_tbd as select * from dba_objects;

Table created.

create or replace PROCEDURE wxh_test_tbd
  2    is
  3    a VARCHAR2(4000);
  4 BEGIN
  5       SELECT object_name INTO a FROM wxh_tbd WHERE object_id=2 and object_type='TABLE';
  6    Dbms_Lock.sleep(3000);
  7 END;
  8 /

Procedure created.

1）新增欄位，有效
alter table wxh_tbd add col1 varchar2(100);

select object_name,status
  2    from dba_objects
  3    where object_name='WXH_TEST_TBD';

OBJECT_NAME                   STATUS
------------------------------ --------------
WXH_TEST_TBD                VALID

2）刪除欄位，但是儲存過程中不包含這個欄位。有效
select object_name,status
  2 from dba_objects
  3 where object_name='WXH_TEST_TBD';

OBJECT_NAME                   STATUS
------------------------------ --------------
WXH_TEST_TBD                VALID

3）修改欄位，儲存過程中包含這個欄位。無效。
alter table wxh_tbd modify object_name varchar2(300);

Table altered.

select object_name,status
  2    from dba_objects
  3    where object_name='WXH_TEST_TBD';

OBJECT_NAME                   STATUS
------------------------------ --------------
WXH_TEST_TBD                INVALID

4）刪除欄位，儲存過程中包含。無效。
alter table wxh_tbd drop column object_type;

Table altered.

select object_name,status
  2    from dba_objects
  3    where object_name='WXH_TEST_TBD';

OBJECT_NAME                   STATUS
------------------------------ --------------
WXH_TEST_TBD                INVALID

修改表結構後一定會另依賴物件失效嗎？

相關文章