可以在 Oracle ALTER 语句中使用子查询吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5903632/
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
Can a subquery be used in an Oracle ALTER statement?
提问by Justin Garrick
Given a table name and a column name, I'm trying to dynamically drop an Oracle constraint that I don't know the name of ahead of time.
给定一个表名和一个列名，我试图动态删除一个我不知道提前名称的 Oracle 约束。
I can find the constraint name with this query:
我可以通过以下查询找到约束名称：
SELECT CONSTRAINT_NAME
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'MyTable' AND
COLUMN_NAME='MyColumn' AND POSITION IS NULL
My first thought was to use a subquery, but that doesn't work and results in an ORA-02250 error:
我的第一个想法是使用子查询，但这不起作用并导致 ORA-02250 错误：
ALTER TABLE MyTable
DROP CONSTRAINT (
SELECT CONSTRAINT_NAME
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'MyTable' AND
COLUMN_NAME='MyColumn' AND POSITION IS NULL)
So far, the only working solution I have is the following, but it feels unnecessarily complex:
到目前为止，我唯一可行的解决方案如下，但感觉不必要地复杂：
DECLARE
statement VARCHAR2(2000);
constr_name VARCHAR2(30);
BEGIN
SELECT CONSTRAINT_NAME INTO constr_name
FROM USER_CONS_COLUMNS
WHERE table_name = 'MyTable' AND
column_name = 'MyColumn' AND position is null;
statement := 'ALTER TABLE MyTable DROP CONSTRAINT '|| constr_name;
EXECUTE IMMEDIATE(statement);
END;
/
Is there a way to do this with a subquery, as I originally intended? If not, can anyone suggest a more concise way to do this?
有没有办法用子查询来做到这一点，正如我最初打算的那样？如果没有，有人可以建议一种更简洁的方法来做到这一点吗？
回答by Horus
You cannot. SQL and DDL are basically two separated languages. Your solution is correct.
你不能。SQL 和 DDL 基本上是两种独立的语言。您的解决方案是正确的。
回答by Maheswari
To drop multiple check constraints...
要删除多个检查约束...
declare
i number;
begin
for I in (select CONSTRAINT_NAME from USER_CONS_COLUMNS B where B.CONSTRAINT_NAME in (
select a.constraint_name from USER_CONSTRAINTS a where a.TABLE_NAME = 'MAHI' and a.CONSTRAINT_TYPE = 'C')
AND B.COLUMN_NAME in ('EMP_NAME','EMP_SAL'))
LOOP
EXECUTE IMMEDIATE('alter table DIM_CHR_LOV DROP CONSTRAINT '|| I.CONSTRAINT_NAME);
end LOOP;
end;
