create or replace procedure proc_dropifexists(
p_i_tablename in varchar2 -----表名字
) is
v_count number(10);
begin
----判断当前用户下是否有对应的表
select count(*)
into v_count
from user_objects
where object_name = upper(p_i_tablename);
if v_count > 0 then
----如果存在则使用动态sql drop掉该表
execute immediate 'drop table ' || p_i_tablename;
end if;
end proc_dropifexists;
----调用办法demo
--SQL> exec proc_dropifexists('T_NEW');