无法使用“如果存在则删除表"；在Spring Boot应用程序的schema.sql中

问题描述

我需要有关在schema.sql中对表进行DROP/CREATE的帮助

I need help with DROP/CREATE of tables in my schema.sql

设置:

• Oracle XE
• Spring Boot v1.4.0
• Java 1.8

当我在schema.sql中具有以下条目时:

When I have the following entry in schema.sql:

DROP TABLE table_a;

CREATE TABLE table_a
(
    id                       VARCHAR(5) PRIMARY KEY,
    name                     VARCHAR(100));

我得到了例外

DROP TABLE table_a; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

当我查找有关如何在Oracle中进行DROP TABLE EXISTS的帮助时，我得到的最佳答案是以下内容(在SQLDeveloper中有效):

When I looked up some help on how to do a DROP TABLE IF EXISTS in Oracle, the best answer I got was the following (works in SQLDeveloper):

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

  EXECUTE IMMEDIATE 'CREATE TABLE table_a
  (
    id               VARCHAR(5) PRIMARY KEY,
    name             VARCHAR(100)
  )';
END;

但是，上面的代码引发以下异常:

However, the above code throws the following Exception:

• & =-+; < />在in是mod余数而不是rem返回 返回<>或！=或〜=> =< =<>或 像like2 like4 likec之间使用||多套散装 成员submultiset

• & = - + ; < / > at in is mod remainder not rem return returning <> or != or ~= >= <= <> and or like like2 like4 likec between into using || multiset bulk member submultiset

有人在Spring Boot中有没有更优雅的方式来处理Oracle表的DROP/CREATE?

Does anybody have a more elegant way to handle DROP/CREATE of Oracle Tables in Spring Boot?

推荐答案

创建存储过程

create or replace procedure recreate_table
  (i_table_name in varchar2, i_create_stmt in varchar2)
is
BEGIN
  BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE '||upper(i_table_name);
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
        RAISE;
      END IF;
  END;
  EXECUTE IMMEDIATE i_create_stmt;
END;

然后您的schema.sql可以使用SQL语句:

Then your schema.sql can use the SQL statement:

call recreate_table('TABLE_A','CREATE TABLE TABLE_A (ID NUMBER, VAL VARCHAR2(10))');

而不是包含PL/SQL

rather than including PL/SQL

这篇关于无法使用“如果存在则删除表"；在Spring Boot应用程序的schema.sql中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！