Oracle 23ai 终于告别烦人的 DDL 报错！

山东Oracle用户组 2025-02-15

在 Oracle 23ai 之前的所有版本中，当我们尝试创建数据库对象（例如用户、表、索引、视图等）时，如果所创建的对象已经存在于数据库中，那么对应的 DDL 语句就会报错。例如，在尝试创建一个已存在的用户时，系统会立即返回错误信息，阻止重复创建。这种情况在早期版本中非常常见，导致很多自动化脚本在执行过程中因为对象重复创建而中断。

SQL> conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba
Connected.
SQL> create user testuser1 identified by testuser1;
create user testuser1 identified by testuser1
            *
ERROR at line 1:
ORA-01920: user name 'TESTUSER1' conflicts with another user or role name
SQL>
SQL> conn testuser1/testuser1@//localhost:1521/freepdb1
Connected.
SQL> create table t1 (id number);
create table t1 (id number)
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL>
SQL> create sequence t1_seq;
create sequence t1_seq
                *
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL>

同样的道理，当我们尝试删除一个不存在的对象时，也会出现类似的问题。举个例子，当使用 DROP TABLE 命令去删除一张数据库中根本不存在的表时，Oracle 数据库同样会抛出错误提示，从而导致操作失败。这种情况在管理数据库对象时也常常给开发者带来困扰。

SQL> drop table t3 purge;
drop table t3 purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>

在实际工作中，我们往往希望在执行创建操作时，只有当对象不存在的情况下才进行创建；而在删除操作时，如果对象本身不存在，则不需要执行删除，也不希望因此而报错。这样一来，整个数据库操作就会更加智能和稳健，能够适应更多场景的需要。那么，有没有一种更为友好的方法来处理这种“有条件”的创建或删除操作呢？

幸运的是，从 Oracle 23ai 开始，DDL 语句中引入了全新的 IF [NOT] EXISTS 子句，这一特性正好解决了上述问题。利用这个子句，我们可以在执行创建操作前自动判断对象是否已经存在；如果存在则跳过创建步骤，而不会抛出任何错误。同理，在删除操作中加入 IF EXISTS 子句，则可以先检测对象是否存在，只有在对象确实存在的情况下才执行删除，从而避免因为删除一个不存在的对象而导致错误。

下面通过一些示例来说明这一新特性的具体应用。

首先看用户创建的场景：假设用户 TESTUSER1 已经存在，通过在 CREATE USER 语句中添加 IF NOT EXISTS 子句，系统就会判断该用户已经存在，因此不会重复创建，同时也不会返回任何错误信息，确保了操作的平稳进行。

同样地，在尝试删除一个不存在的用户（例如 TESTUSER3）时，通过使用 IF EXISTS 子句，Oracle 数据库会自动判断该用户是否存在。如果用户不存在，则直接跳过删除操作，而不会报错，从而使得整个过程更为友好和灵活。

SQL> conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba
Connected.
SQL> create user if not exists testuser1 identified by testuser1;
User created.
SQL>
SQL> drop user if exists testuser3 cascade;
User dropped.
SQL>

这一新语法不仅适用于用户对象，在创建或删除表、视图、序列、存储过程等各种数据库对象时，同样可以使用 IF [NOT] EXISTS 子句来屏蔽不必要的错误。

SQL> conn testuser1/testuser1@//localhost:1521/freepdb1
Connected.
SQL> create table if not exists t1 (id number);
Table created.
SQL>
SQL> create sequence if not exists t1_seq;
Sequence created.
SQL>
SQL> create view if not exists t1_v as
select * from t1;
View created.
SQL>
SQL> create procedure if not exists p1 as
begin
  null;
end;
/
Procedure created.
SQL>


SQL> drop table if exists t3;
Table dropped.
SQL> drop sequence if exists t3_seq;
Sequence dropped.
SQL> drop view if exists t3_v;
View dropped.
SQL> drop procedure if exists p3;
Procedure dropped.
SQL>

不过，需要特别注意的是，在使用 CREATE OR REPLACE 语句时，是不能再配合使用 IF NOT EXISTS 子句的。这是因为 REPLACE 与 IF NOT EXISTS 两者在语法上存在冲突，Oracle 不允许它们在同一个 DDL 语句中同时出现。换句话说，如果你打算使用 CREATE OR REPLACE 来更新或重新定义某个对象，就不能在同一语句中加入 IF NOT EXISTS 子句，否则就会导致语法错误。

SQL> create or replace view if not exists t1_v as
select * from t1;
create or replace view if not exists t1_v as
                                     *
ERROR at line 1:
ORA-11541: REPLACE and IF NOT EXISTS cannot coexist in the same DDL statement
SQL>

通过 Oracle 23ai 中这一新的语法扩展，我们可以显著提高数据库对象管理的灵活性和脚本的容错能力，使得数据库管理操作变得更加高效和友好。

👇👇关注不迷路，技术有深度👇👇

文章转载自山东Oracle用户组，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle 23ai 终于告别烦人的 DDL 报错！

评论