如果写一个存储过程,使用某表,但是担心某表被同事不小心给删了,为了减少不必要的麻烦,可以判断一下
源代码如下
create or replace procedure pr_test_zzw as
num number;
begin
select count(*) into num from all_tables where table_name='BZ_ZZW_TEST';
if num>0 THEN
execute immediate 'drop table bz_zzw_test';
ELSE
dbms_output.put_line('这张表不存在');
END IF;
end;
如果在数据库中表确实不存在:
执行情况如下:
SQL> create or replace procedure pr_test_zzw as
2 num number;
3 begin
4 select count(*) into num from all_tables where table_name='BZ_ZZW_TEST';
5 if num>0 THEN
6 execute immediate 'drop table bz_zzw_test';
7 ELSE
8 dbms_output.put_line('这张表不存在');
9 END IF;
10 end;
11 /
Procedure created
SQL> set serveroutput on;
SQL> execute pr_test_zzw;
这张表不存在
PL/SQL procedure successfully completed
SQL>
首先建立这张需要判断的表:
create table bz_zzw_test as
select * from bill.t_si_external_id
where latn_id = 543
and rownum<19;
select * from bz_zzw_test;
-----18条记录
重新执行一下那个存储过程,就会看到:
SQL> execute pr_test_zzw;
PL/SQL procedure successfully completed
SQL> select count(*) from bz_zzw_test;
select count(*) from bz_zzw_test
ORA-00942: table or view does not exist
SQL>
这样就可以了。