oracle drop 所有,oracle 删除当前schema下的所有对象！

CREATE OR REPLACE PROCEDURE DROP_SCHEMA_OBJECTS (verification VARCHAR2)

IS

CURSOR c1

IS

SELECT *

FROM (SELECT object_name, object_type, 1 AS grp

FROM user_objects

WHERE object_type IN

('PACKAGE',

'PROCEDURE',

'FUNCTION',

'TABLE',

'VIEW',

'TYPE',

'SEQUENCE'

)

AND object_name <> 'BO_SYS_DATAPUMP_PKG'

AND object_name <> 'GO_UTIL_DROP_SCHEMA_OBJECTS'

AND object_name <> 'SYS_BACKUP_PARAM_TBL'

UNION ALL

SELECT object_name, object_type, 2 AS grp

FROM user_objects

WHERE object_type IN ('TYPE')

AND object_name <> 'BO_SYS_DATAPUMP_PKG'

AND object_name <> 'GO_UTIL_DROP_SCHEMA_OBJECTS'

AND object_name <> 'SYS_BACKUP_PARAM_TBL')

ORDER BY grp,

CASE object_type

WHEN 'PACKAGE'

THEN 1

WHEN 'PROCEDURE'

THEN 2

WHEN 'FUNCTION'

THEN 3

WHEN 'VIEW'

THEN 4

ELSE 5

END;

objname VARCHAR2 (255);

objtype VARCHAR2 (255);

objgrp PLS_INTEGER;

verification_str CONSTANT VARCHAR2 (10) := 'sure';

BEGIN

IF verification = verification_str

THEN

OPEN c1;

LOOP

FETCH c1

INTO objname, objtype, objgrp;

EXIT WHEN c1%NOTFOUND;

BEGIN

NULL;

IF objtype = 'TABLE'

THEN

EXECUTE IMMEDIATE 'drop '

|| objtype

|| ' '

|| objname

|| ' cascade constraints purge';

ELSE

EXECUTE IMMEDIATE 'drop ' || objtype || ' ' || objname;

END IF;

DBMS_OUTPUT.put_line ( objtype

|| ' - '

|| objname

|| ' dropped successfully'

);

EXCEPTION

WHEN OTHERS

THEN

NULL;

DBMS_OUTPUT.put_line ( objtype

|| ' - '

|| objname

|| ' dropped failed - '

|| SQLERRM

);

END;

END LOOP;

CLOSE c1;

ELSE

DBMS_OUTPUT.put_line

('Failed to verify the operation, please input verification.');

END IF;

END;