Oracle drop if exists

Posted by Alei Blog on November 23, 2017

参考https://stackoverflow.com/questions/1799128/oracle-if-table-exists

场景

  • 删除表,视图等对象时,静默执行,不返回报错信息
  • 类似dorp table if exists,语句可反复执行
  • 开发人员编写sql,让实施人员执行
  • 直接写drop table abc,如果abc表已经被删除或者不存在,返回报错信息,对于不懂sql的实施人员来说,会产生干扰

代码示例

创建存储过程

  • 适用于drop table, procedure, function, trigger, view, sequence
    create or replace procedure dropObject(ObjName varchar2,ObjType varchar2)
    is
     v_counter number := 0;   
    begin    
      if upper(ObjType) = 'TABLE' then
        select count(*) into v_counter from user_tables where table_name = upper(ObjName);
        if v_counter > 0 then          
          execute immediate 'drop table ' || ObjName || ' cascade constraints';        
        end if;   
      end if;
      if upper(ObjType) = 'PROCEDURE' then
        select count(*) into v_counter from User_Objects where object_type = 'PROCEDURE' and OBJECT_NAME = upper(ObjName);
          if v_counter > 0 then          
            execute immediate 'DROP PROCEDURE ' || ObjName;        
          end if; 
      end if;
      if upper(ObjType) = 'FUNCTION' then
        select count(*) into v_counter from User_Objects where object_type = 'FUNCTION' and OBJECT_NAME = upper(ObjName);
          if v_counter > 0 then          
            execute immediate 'DROP FUNCTION ' || ObjName;        
          end if; 
      end if;
      if upper(ObjType) = 'TRIGGER' then
        select count(*) into v_counter from User_Triggers where TRIGGER_NAME = upper(ObjName);
          if v_counter > 0 then          
            execute immediate 'DROP TRIGGER ' || ObjName;
          end if; 
      end if;
      if upper(ObjType) = 'VIEW' then
        select count(*) into v_counter from User_Views where VIEW_NAME = upper(ObjName);
          if v_counter > 0 then          
            execute immediate 'DROP VIEW ' || ObjName;        
          end if; 
      end if;
      if upper(ObjType) = 'SEQUENCE' then
        select count(*) into v_counter from user_sequences where sequence_name = upper(ObjName);
          if v_counter > 0 then          
            execute immediate 'DROP SEQUENCE ' || ObjName;        
          end if; 
      end if;
    end;
    

使用存储过程

  • 创建temp_table表之前,判断,if exists then drop
  • 此语句示例在pl/sql developer中执行
  • ”/”必须在行首,之前不能有空格
    begin
      dropObject('temp_table','table');
    end;
    /
    create table temp_table as select * from dual;