測試預存程序的狀態對執行的影響，測試預存程序狀態

測試預存程序的狀態對執行的影響，測試預存程序狀態
測試目的：

測試預存程序的狀態對該預存程序啟動並執行影響。

環境準備：

1.建立測試表 test_drop_dep

create table test_dep(name varchar2(20));
insert into test_dep(name) values('ABC');
commit;
2.建立依賴表 test_drop_dep的預存程序

create or replace procedure test_drop_dep

as
v_count number;
begin
select count(*) into v_count from test_dep;
dbms_output.put_line('BEFORE SLEEP TIME='||to_char(sysdate,'hh24:mi:ss'));
dbms_lock.sleep(30);
dbms_output.put_line('BEHIND SLEEP TIME='||to_char(sysdate,'hh24:mi:ss'));
dbms_output.put_line('THE ROWCOUNT ='||to_char(v_count));
end;
/

測試案例測試案例1：

session1執行預存程序，session2刪除表test_drop_dep，然後查詢預存程序的狀態。

session1執行預存程序execute test_drop_dep;

session2刪除表test_drop_dep，然後查詢預存程序的狀態

drop table test_dep;

select object_name,status from dba_objects where object_name='TEST_DROP_DEP';
OBJECT_NAME                    STATUS
------------------------------ -------
TEST_DROP_DEP                  INVALID

測試案例1結論：

預存程序依賴的對象失效(刪除)後，該預存程序會立即標記為失效invalid，即使該過程正在執行，已經啟動並執行該預存程序會正常執行完畢。

測試案例2：session1執行預存程序，session2刪除表 test_drop_dep ，建立表test_drop_dep，執行預存程序。

session1執行預存程序execute test_drop_dep;
/*
SQL> execute test_drop_dep;
BEFORE SLEEP TIME=10:06:47
BEHIND SLEEP TIME=10:07:17
THE ROWCOUNT =1
PL/SQL procedure successfully completed.
*/
session2刪除表 test_drop_dep ，建立表test_drop_dep，執行預存程序

drop table test_dep;
select object_name,status from dba_objects where object_name='TEST_DROP_DEP';
create table test_dep(name varchar2(20));
insert into test_dep(name) values('ABC');
commit;
select object_name,status from dba_objects where object_name='TEST_DROP_DEP';
execute test_drop_dep;
select object_name,status from dba_objects where object_name='TEST_DROP_DEP';

/*

SQL> drop table test_dep;
Table dropped.
SQL> select object_name,status from dba_objects where object_name='TEST_DROP_DEP';
OBJECT_NAME                    STATUS
------------------------------ -------
TEST_DROP_DEP                  INVALID
SQL> create table test_dep(name varchar2(20));
insert into test_dep(name) values('ABC');
Table created.
SQL> commit;
1 row created.
SQL> 
Commit complete.
SQL> select object_name,status from dba_objects where object_name='TEST_DROP_DEP';
OBJECT_NAME                    STATUS
------------------------------ -------
TEST_DROP_DEP                  INVALID
SQL> execute test_drop_dep;
select object_name,status from dba_objects where object_name='TEST_DROP_DEP';

BEFORE SLEEP TIME=10:07:17
BEHIND SLEEP TIME=10:07:47
THE ROWCOUNT =1
PL/SQL procedure successfully completed.
SQL> 
OBJECT_NAME                    STATUS
------------------------------ -------
TEST_DROP_DEP                  VALID

*/

測試案例2結論：

有效預存程序執行後，會一直執行完畢，而不管當前是否是valid狀態；
invalid的預存程序第一運行會執行編譯，如果此時有未執行完成的該過程，編譯一直處於library cache pin等待，直到所有過程都執行完才能編譯執行。