Oracle Drop Table If Exists

最新推荐文章于 2022-09-25 23:43:49 发布

原创最新推荐文章于 2022-09-25 23:43:49 发布 · 1.2w 阅读

本内容遵循CC 4.0 BY-SA版权协议

19 篇文章

订阅专栏

本文介绍了如何在Oracle数据库中实现类似“如果存在则删除表”的功能。由于Oracle本身不支持直接使用DROP TABLE IF EXISTS这样的语句，文章提供了一个通过捕获表不存在异常的工作绕过方法，并解释了如何针对不同的Oracle对象类型应用该方法。

Sometimes we want to clean up Oracle database, such as to drop a table if there exists, and do nothing if table does not exist; such as:

- drop table table if exists

Unfortunately, there is no such statement we can use, although this is supported by other RDBMS, such as MySQL, but not Oracle.

As a workaround, we can use catching the "table not found" exception:

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE yourtablename';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;

The same solution can be used for other Oracle object types: sequence, function, etc. (just pay attention to the different SQLCODE value)

Reference:

1. http://stackoverflow.com/questions/1799128/oracle-if-table-exists
2. http://ora-exp.blogspot.jp/2013/03/oracle-drop-table-if-exists.html