本文共 1657 字，大约阅读时间需要 5 分钟。
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
转载地址：http://kkuki.baihongyu.com/