在 Oracle SQL 中删除所有以“EXT_”开头的表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20141417/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
DROP all tables starting with "EXT_" in Oracle SQL
提问by Ahmet Karakaya
I know this question may ask many times but I could not find one line SQL statement. I remember I did it before but now I could not remember how I did
我知道这个问题可能会问很多次，但我找不到一行 SQL 语句。我记得我以前做过，但现在我不记得我是怎么做的了
I want to drop all tables whose name starts with "EXT_". Is it possibile to make it happen with one line SQL statement.
我想删除名称以“EXT_”开头的所有表。是否有可能用一行 SQL 语句来实现。
回答by tvm
You could use a short anonymous block to do this.
您可以使用简短的匿名块来执行此操作。
BEGIN
FOR c IN ( SELECT table_name FROM user_tables WHERE table_name LIKE 'EXT_%' )
LOOP
EXECUTE IMMEDIATE 'DROP TABLE ' || c.table_name;
END LOOP;
END;
回答by Alen Oblak
It's not possible with only one statement. Usually, I write a sql to get all the tables and then execute the results:
只用一个语句是不可能的。通常，我写一个sql来获取所有的表，然后执行结果：
select 'drop table ' || table_name || ';'
from user_tables
where table_name like 'EXT_%';
回答by abrittaf
This code will DROP not only EXT_% tables, it will act as DROP EXT% also. Underscore is as special wildcard that acts as '%' but for a single character.
此代码不仅会删除 EXT_% 表，还将充当 DROP EXT%。下划线是作为 '%' 的特殊通配符，但用于单个字符。
BEGIN
FOR c IN ( SELECT table_name FROM user_tables WHERE table_name LIKE 'EXT_%' )
LOOP
EXECUTE IMMEDIATE 'DROP TABLE ' || c.table_name;
END LOOP;
END;
In order to achieved desired results you should change your code the way below
为了达到预期的结果，您应该按照以下方式更改代码
BEGIN
FOR c IN ( SELECT table_name FROM user_tables WHERE table_name LIKE 'EXT\_%' ESCAPE '\')
LOOP
EXECUTE IMMEDIATE 'DROP TABLE ' || c.table_name;
END LOOP;
END;
It escapesunderscore char in order to be trated literally, ESCAPE '\'modifier indicates that escape char is '\'
它转义下划线字符以便按字面意思进行处理，ESCAPE '\'修饰符表示转义字符是'\'
回答by user3245888
In most of cases you will find contraints violations. In that case, this script can help you:
在大多数情况下，您会发现违反约束。在这种情况下，此脚本可以帮助您：
DECLARE
c_action CONSTANT VARCHAR2(10) := 'DROP';
BEGIN
FOR c IN ( SELECT table_name FROM user_tables WHERE table_name LIKE 'STARTINGTEXT_%' )
LOOP
FOR reg IN (SELECT uc.table_name,
uc.constraint_name
FROM user_constraints uc
WHERE uc.table_name IN (c.table_name)) LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ' || reg.table_name || ' ' || c_action ||
' CONSTRAINT ' || reg.constraint_name ;
END LOOP;
END LOOP;
COMMIT;
FOR c IN ( SELECT table_name FROM user_tables WHERE table_name LIKE 'STARTINGTEXT_%' )
LOOP
EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || c.table_name;
EXECUTE IMMEDIATE 'DROP TABLE ' || c.table_name;
END LOOP;
END;
