Oracle23ai 新特性IF [NOT] EXISTS Syntax Support

官方文档地址
https://docs.oracle.com/en/database/oracle/oracle-database/23/lnpls/release-changes.html#GUID-9EE96980-43F9-4068-893E-C191CD83ACA6

IF [NOT] EXISTS 语法支持

CREATE、ALTER和DROP DDL语句支持IF NOT EXISTS和IF EXISTS子句。它们用于抑制因给定对象的存在或不存在而引发的潜在错误，允许您编写幂等DDL脚本。

CREATE DDL语句支持IF NOT EXISTS子句，以防止在具有给定名称的对象已存在时抛出错误。如果对象已经存在，则忽略该命令，原始对象保持不变。

另一方面，IF EXISTS子句在与ALTER和DROP DDL语句一起使用时会抑制错误。如果不存在给定名称的对象，则忽略该命令，并且没有对象受到ALTER或DROP的影响。

子句的使用或排除为您提供了更多的控制，具体取决于您是否需要在执行DDL语句之前知道对象是否存在。有了这种灵活性，您可以确定是宁愿忽略该语句，还是在对象存在（或不存在）的情况下引发错误。

注意：在使用CREATE DDL语句的命令中，IF NOT EXISTS不能与OR REPLACE结合使用。
Note:IF NOT EXISTS cannot be used in combination with OR REPLACE in commands using the CREATE DDL statement.

CREATE PROCEDURE with IF NOT EXISTS

假设您的架构中不存在同名过程，则执行一次此语句会创建过程hello。
Executing this statement one time results in the creation of procedure hello, assuming a procedure by the same name does not already exist in your schema.

TESTUSER@FREEPDB1> col BANNER format a100
TESTUSER@FREEPDB1> select BANNER from v$version;

BANNER
----------------------------------------------------------------------------------------------------
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

CREATE PROCEDURE IF NOT EXISTS hello AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello there');
END;
/

即使更改了存储过程体，多次执行语句也不会导致错误。原来的身体保持不变。

Executing the statement additional times, even with an altered procedure body, results in no error. The original body remains unchanged.

TESTUSER@FREEPDB1> CREATE PROCEDURE IF NOT EXISTS hello AS
  2  BEGIN
  3    DBMS_OUTPUT.PUT_LINE('Second hello');
  4  END;
  5  /

Procedure created.


TESTUSER@FREEPDB1> col text format a100
TESTUSER@FREEPDB1> SELECT TEXT FROM USER_SOURCE WHERE NAME='HELLO';

TEXT
----------------------------------------------------------------------------------------------------
PROCEDURE               hello AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello there');
END;

Orcle19c及之前版本都不支持该语法

发现创建时编译不成功。去掉IF NOT EXISTS则成功创建，并执行成功！

HR@orcl> CREATE PROCEDURE hello AS
  2  BEGIN
  3    DBMS_OUTPUT.PUT_LINE('Second hello');
  4  END;
  5  /

Procedure created.

HR@orcl> col BANNER format a100
HR@orcl> select BANNER from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

HR@orcl> set serveroutput on
HR@orcl> begin
  2         hello();
  3       end;
  4      /
Second hello

PL/SQL procedure successfully completed.