oracle自定义存储过程:删除表(无论表是否存在)和检测表是否存在

oracle删除表，如果表不存在，就报错，在跑大型脚本（脚本长且耗时的时候）比较麻烦，一般希望的是点开始然后脚本运行到结束，不可能一直盯着屏幕等弹出提示手工点掉，mysql就很好有drop table if not exist功能

create or replace procedure p_drop_table_if_exist(p_vc2_tbl_name  in all_tables.table_name%type,
                                    p_vc2_tbl_owner in all_tables.owner%type default user) is
    v_num_tbl_count number(4);
    v_vc2_sql_stmt  varchar2(1000);
  begin
    -- check if table already exists
    p_chk_table_exist(p_vc2_tbl_name, p_vc2_tbl_owner, v_num_tbl_count);
    if (v_num_tbl_count != 0) then
      -- table already exists and must be dropped
      v_vc2_sql_stmt := 'drop table ' || case
                          when p_vc2_tbl_owner is not null then
                           p_vc2_tbl_owner || '.'
                        end || p_vc2_tbl_name || ' purge'
                        ;
      --dbms_output.put_line(v_vc2_sql_stmt);
      execute immediate v_vc2_sql_stmt;
    end if;
  end;




create or replace procedure p_chk_table_exist(p_vc2_tbl_name  in all_tables.table_name%type,
                                p_vc2_tbl_owner in all_tables.owner%type default user,
                                p_num_tbl_count out number -- 1 if table exists, 0 if it doesn't
                                ) is
    v_vc2_tbl_name all_tables.table_name%type := upper(p_vc2_tbl_name);
  begin

    if p_vc2_tbl_owner is not null then
      select count(1)
        into p_num_tbl_count
        from all_tables
       where table_name = v_vc2_tbl_name
         and owner = upper(p_vc2_tbl_owner);
    else
      select count(1)
        into p_num_tbl_count
        from user_tables
       where table_name = v_vc2_tbl_name;
    end if;
  end p_chk_table_exist;

have fun