sql - Firebird SQL : Subquery within an ALTER statement?

Question

我有一个 firebird 数据库架构，它是由各种 CREATE TABLE 脚本设置的。这些脚本已提交到源代码控制系统(请参阅 Scott Allen's 站点)。

现在我想创建一个新脚本，在其中我希望数据库删除特定表中某一列的 NOT NULL 约束:

ALTER TABLE myTable DROP CONSTRAINT c

约束的名称(例如 INTEG_219)可以通过查询系统表来获取:

SELECT rc.rdb$constraint_name
FROM   rdb$relation_constraints rc
       join rdb$check_constraints cc
       on rc.rdb$constraint_name = cc.rdb$constraint_name
WHERE  rc.rdb$constraint_type   = 'NOT NULL'
       and rc.rdb$relation_name = 'MYTABLE'
       and cc.rdb$trigger_name  = 'ACOLUMN'

这些语句可以组合成一个 ALTER 命令吗？

Answer 1

我建议你看看EXECUTE STATEMENT .

这里是firebird faq

execute BLOCK 
as declare variable stmt VARCHAR(2500) = 'SELECT ''ALTER TABLE MYTABLE myTable DROP CONSTRAINT ''|| rc.rdb$constraint_name||'';'' FROM   rdb$relation_constraints rc
           join rdb$check_constraints cc
           on rc.rdb$constraint_name = cc.rdb$constraint_name WHERE  rc.rdb$constraint_type   = ''NOT NULL''
           and rc.rdb$relation_name = ''MYTABLE''
           and cc.rdb$trigger_name  = ''ACOLUMN'''; 
begin   
  execute STATEMENT stmt; 
end;