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