是否可以在Oracle ALTER语句中使用子查询？ [英] Can a subquery be used in an Oracle ALTER statement?

问题描述

给定一个表名和一个列名，我试图动态删除一个Oracle约束，我不知道提前的名字。

我可以用这个查询找到约束名：

  SELECT CONSTRAINT_NAME 
 FROM USER_CONS_COLUMNS 
 WHERE TABLE_NAME = MyTable'AND 
 COLUMN_NAME ='MyColumn'AND POSITION IS NULL 
  

使用子查询，但这不工作，并导致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）
  
 
 
 
到目前为止，我唯一可用的解决方案如下，但是感觉不必要的复杂：
  DECLARE 
语句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位置为空; 
 statement：='ALTER TABLE MyTable DROP CONSTRAINT'|| constr_name; 
 EXECUTE IMMEDIATE（statement）; 
 END; 
 / 
  
有没有办法做到这一点与子查询，如果没有，任何人都可以建议一个更简洁的方法来做这个？
解决方案
 SQL和DDL基本上是两种分离的语言。您的解决方案是正确的。
 
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.

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:
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?
解决方案
You cannot.  SQL and DDL are basically two separated languages.  Your solution is correct.

                        
这篇关于是否可以在Oracle ALTER语句中使用子查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！