Oracle 检测表过程

CREATE OR REPLACE PROCEDURE PROC_DROP_TABLE(T_NAME IN VARCHAR2) IS
  C_TYPE  VARCHAR2(20);
  T_COUNT INT;
  V_COUNT INT;
  I_COUNT INT;
  S_COUNT INT;
BEGIN
  BEGIN
    SELECT OBJECT_TYPE
      INTO C_TYPE
      FROM ALL_OBJECTS
     WHERE OBJECT_NAME = UPPER(T_NAME);
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.put_line('THE  ' || T_NAME ||
                           '  IS  NOT  IN THIS  DATABASE !');
  END;

  SELECT COUNT(*)
    INTO T_COUNT
    FROM USER_TABLES
   WHERE TABLE_NAME = UPPER(T_NAME);
  SELECT COUNT(*)
    INTO V_COUNT
    FROM USER_VIEWS
   WHERE VIEW_NAME = UPPER(T_NAME);
  SELECT COUNT(*)
    INTO I_COUNT
    FROM USER_INDEXES
   WHERE INDEX_NAME = UPPER(T_NAME);
  SELECT COUNT(*)
    INTO S_COUNT
    FROM USER_SEQUENCES
   WHERE SEQUENCE_NAME = UPPER(T_NAME);
  -----------TABLE  
  IF C_TYPE = 'TABLE' THEN
    IF T_COUNT > 0 THEN
      EXECUTE IMMEDIATE 'DROP TABLE ' || T_NAME || ' purge';
      DBMS_OUTPUT.put_line('THE TABLE  :' || T_NAME || '  IS DROP !');
    END IF;
    COMMIT;
  END IF;
  --------- VIEW 
  IF C_TYPE = 'VIEW' THEN
    IF V_COUNT > 0 THEN
      EXECUTE IMMEDIATE 'DROP VIEW ' || T_NAME;
      DBMS_OUTPUT.put_line('THE  VIEW  :' || T_NAME || '  IS DROP !');
    END IF;
  END IF;
  --------- SQE
  IF C_TYPE = 'SEQUENCE' THEN
    IF S_COUNT > 0 THEN
      EXECUTE IMMEDIATE 'DROP SEQUENCE ' || T_NAME;
      DBMS_OUTPUT.put_line('THE  SEQUENCE  :' || T_NAME || '  IS DROP !');
    END IF;
  END IF;

  -------- INDEX 
  IF C_TYPE = 'INDEX' THEN
    IF I_COUNT > 0 THEN
      EXECUTE IMMEDIATE 'DROP INDEX ' || T_NAME;
      DBMS_OUTPUT.put_line('THE  INDEX  :' || T_NAME || '  IS DROP !');
    END IF;
  END IF;
END PROC_DROP_TABLE;