oracle - 相当于Oracle中的IF NOT EXISTS？

Question

以下语句适用于 MSSQL:

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[FK_StationObjectsID]') AND parent_object_id = OBJECT_ID(N'[Attendance]'))
BEGIN
    ALTER TABLE Attendance ADD CONSTRAINT FK_StationObjectsID FOREIGN KEY (StationObjectsID) REFERENCES stationobjects (stationobjectsid)
END

在 Oracle 中我尝试过:

IF NOT EXISTS (SELECT * FROM USER_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_STATIONOBJECTSID' AND TABLE_NAME = 'ATTENDANCE') THEN
BEGIN
    ALTER TABLE Attendance ADD CONSTRAINT FK_StationObjectsID FOREIGN KEY (StationObjectsID) REFERENCES stationobjects (stationobjectsid);
END;

但它给了我一个错误 PLS-00103“遇到符号'ALTER'...”

Answer 1

使用立即执行来触发 PLSQL block 内的 ddl:

IF <condition> THEN 
    Execute immediate 'ALTER TABLE . . .';
END if;

最接近的方法是 Tom Kyte here 给出的方法。

begin
  for i in (select count(*) cnt from dual 
            where not exists (
                SELECT *
                FROM USER_CONSTRAINTS 
                WHERE CONSTRAINT_NAME = 'FK_STATIONOBJECTSID' 
                AND TABLE_NAME = 'ATTENDANCE'
            )) loop
            if (i.cnt = 1) then
              execute immediate 'ALTER TABLE Attendance ADD CONSTRAINT FK_StationObjectsID FOREIGN KEY (StationObjectsID) REFERENCES stationobjects (stationobjectsid)';
            end if;
  end loop;
end;
/

更简单的方法是:

declare
  n int := 0;
begin
  select count(*) into n
  from user_constraints
  where constraint_name = 'FK_STATIONOBJECTSID'
  and table_name = 'ATTENDANCE';
  if n = 0 then
    execute immediate 'ALTER TABLE Attendance ADD CONSTRAINT FK_StationObjectsID FOREIGN KEY (StationObjectsID) REFERENCES stationobjects (stationobjectsid)';
  end if;
end;
/

查看-https://dba.stackexchange.com/questions/37362/why-cant-we-write-ddl-statement-directly-into-the-pl-sql-block