最简单方式: select count(table_name) from user_tables where table_name='要找的表名'; 当然要找的表必须在你的schema下且具有drop table权限。 declare v_c number; begin select count(table_name) into v_exist from dba_tables where table_name='要找的表名' and owner='表所属的schema'; if(v_c>0) then drop table schema.要找的表名 purge; end if; end; / =============== 如果有DBA权限和上面一样(不过一般程序账号不会有) declare v_c number; begin select count(table_name) into v_exist from user_tables where table_name='要找的表名'; if(v_c>0) then drop table schema.要找的表名 purge; end if; end; /
select count(*) from tab where tname='你的表'; select count(*) from user_tables t where t.table_name='你的表';
SQL> set serveroutput on SQL> create table t as select * from dual;
Table created
SQL> SQL> begin 2 for i in (select null from user_tables where table_name = upper('t')) loop 3 execute immediate 'drop table t'; 4 dbms_output.put_line('Dropped Table T'); 5 end loop; 6 end; 7 /
Dropped Table T
PL/SQL procedure successfully completed
SQL> desc t; Object t does not exist.
SQL> SQL> begin 2 for i in (select null from user_tables where table_name = upper('t')) loop 3 execute immediate 'drop table t'; 4 dbms_output.put_line('Dropped Table T'); 5 end loop; 6 end; 7 /
PL/SQL procedure successfully completed
楼主非要个经典的,看这个如何:begin execute immediate ' drop table 表名'; exception when others then null; end;
可以! 我先前想到的只有通过select count(*) into l_count from user_tables where table_name=upper('your_table'); 在通过l_count来判断。
begin execute immediate ' drop table 表名'; exception when others then null; end; 这个太经典了呵呵declare tabName varchar2(100); flag number := 0; str varchar2(100); begin tabName := ''; select count(*) into flag from user_tables where table_name = upper(tabName); /*这里用user_tables还是什么 自己定*/ if flag <> 0 then str := 'drop table ' || tabName; execute immediate str; else dbms_output.put_line('the table is not exist'); end if; end;
select count(table_name) from user_tables where table_name='要找的表名'; 当然要找的表必须在你的schema下且具有drop table权限。
declare
v_c number;
begin
select count(table_name) into v_exist from dba_tables where table_name='要找的表名' and owner='表所属的schema';
if(v_c>0)
then
drop table schema.要找的表名 purge;
end if;
end;
/
===============
如果有DBA权限和上面一样(不过一般程序账号不会有)
declare
v_c number;
begin
select count(table_name) into v_exist from user_tables where table_name='要找的表名';
if(v_c>0)
then
drop table schema.要找的表名 purge;
end if;
end;
/
select count(*) from user_tables t where t.table_name='你的表';
SQL> create table t as select * from dual;
Table created
SQL>
SQL> begin
2 for i in (select null from user_tables where table_name = upper('t')) loop
3 execute immediate 'drop table t';
4 dbms_output.put_line('Dropped Table T');
5 end loop;
6 end;
7 /
Dropped Table T
PL/SQL procedure successfully completed
SQL> desc t;
Object t does not exist.
SQL>
SQL> begin
2 for i in (select null from user_tables where table_name = upper('t')) loop
3 execute immediate 'drop table t';
4 dbms_output.put_line('Dropped Table T');
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed
execute immediate ' drop table 表名';
exception when others then
null;
end;
我先前想到的只有通过select count(*) into l_count from user_tables where table_name=upper('your_table');
在通过l_count来判断。
execute immediate ' drop table 表名';
exception when others then
null;
end;
这个太经典了呵呵declare
tabName varchar2(100);
flag number := 0;
str varchar2(100);
begin
tabName := '';
select count(*)
into flag
from user_tables
where table_name = upper(tabName);
/*这里用user_tables还是什么 自己定*/
if flag <> 0 then
str := 'drop table ' || tabName;
execute immediate str;
else
dbms_output.put_line('the table is not exist');
end if;
end;