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) ISv_num_tbl_count NUMBER(4);v_vc2_sql_stmt VARCHAR2(1000);BEGIN-- Check if table already existsp_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 droppedv_vc2_sql_stmt := 'DROP TABLE ' || CASEWHEN p_vc2_tbl_owner IS NOT NULL THENp_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) ISv_vc2_tbl_name all_tables.table_name%TYPE := UPPER(p_vc2_tbl_name);BEGINIF p_vc2_tbl_owner IS NOT NULL THENSELECT COUNT(1)INTO p_num_tbl_countFROM all_tablesWHERE table_name = v_vc2_tbl_nameAND owner = UPPER(p_vc2_tbl_owner);ELSESELECT COUNT(1)INTO p_num_tbl_countFROM user_tablesWHERE table_name = v_vc2_tbl_name;END IF;END p_chk_table_exist;
have fun