如何从Oracle中的架构中删除表列表? [英] How to drop list of table from a schema in Oracle?

问题描述

我的Oracle scott模式包含这样的表列表

My Oracle scott schema contains table list like that

'prefix_A'
'prefix_B'
'prefix_C'
'A'
'B'
'C'

现在我想删除表列表，其中包含像"Prefix_"这样的表前缀，但是其他表A，B，C将保持不变.

Now i want to drop list of tables ,containing table prefix like that 'Prefix_',But others table A ,B ,C will be remain same.

怎么可能?

预先感谢.

推荐答案

使用动态SQL删除数据字典.

Use dynamic SQL driving off the data dictionary.

begin
     for trec in ( select table_name
                   from user_tables
                   where table_name like 'PREFIX\_%' escape `\' )
     loop
         dbms_output.put_line('dropping table ' || trec.table_name);
         execute immediate 'drop table '||trec.table_name;
     end loop;
end;

使用LIKE子句精确是一个好主意；使用escape关键字确保下划线不被视为通配符.或者使用substr(table_name, 1, 7) = 'PREFIX_'.

It's a good idea to be precise with the LIKE clause; using the escape keyword to ensure underscores aren't treated as wildcards. Alternatively use substr(table_name, 1, 7) = 'PREFIX_'.

如果您使用的是10g或更高版本，并且已启用回收站，但最好还是不要这样做.显然，您不会在Production中运行这样的代码，但是您将使用该原理来生成drop语句的脚本.

Dropping the wrong table isn't a disaster provided you're working on 10g or later and the RECYCLE BIN is enabled, but it's still better not to. Obviously you wouldn't run code like this in Production, but you would use the principle to generate a script of drop statements.

上面的代码不处理依赖关系.如果您具有引用前缀表的外键，并且想要强制删除表，请使用以下附加逻辑:

The above code doesn't handle dependencies. If you have foreign keys referencing the prefixed tables and you want to force the dropping of the tables use this additional logic:

     execute immediate 'drop table '|| trec.table_name ||' cascade constraint';

这会删除外键约束，但会保留(以前)依赖表.

This drops the foreign key constraints but leaves the (formerly) dependent tables.

这篇关于如何从Oracle中的架构中删除表列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！