测试目的:
测试存储过程的状态对该存储过程运行的影响。
环境准备:
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执行存储过程
/*
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等待,直到所有过程都执行完才能编译执行。