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;