删除表(如果存在) [英] Drop table if it exists

问题描述

我有以下PL/SQL:

I have the following PL/SQL:

declare
    i_cnt number;
begin
    select count(1) into i_cnt 
      from dba_tables 
     where table_name = upper('foo') 
       and owner = upper('bar'); 

if i_cnt > 0 then 
    drop table foo; -- <--- error this line
end if;
end;

我从中收到此错误.

ORA-06550: line 6, column 5:
PLS-00103: Encountered the symbol "DROP" when expecting one of the following:

   ( begin case declare exit for goto if loop mod null pragma
   raise return select update while with <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   continue close current delete fetch lock insert open rollback
   savepoint set sql execute commit forall merge pipe purge

如何在过程中删除表?

推荐答案

您不能直接从PL/SQL块执行DDL语句-您必须改为使用EXECUTE IMMEDIATE:

You can't directly execute DDL statements from a PL/SQL block - you'll have to use EXECUTE IMMEDIATE instead:

declare
  i_cnt number;
begin
  select count(1) into i_cnt 
  from dba_tables where table_name=upper('foo') and owner=upper('bar'); 
  if i_cnt > 0 then 
    execute immediate 'drop table foo'; 
  end if;
end;

这篇关于删除表(如果存在)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！