无法在Schema.sql中为Spring Boot应用程序使用“drop TABLE IF exists”

Question

null

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

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;

但是，上述代码引发以下异常：

2016-08-10 14:55:36.232信息9032---[main]o.s.jdbc.datasource.init.scriptutils：从URL[file:/c:/projects/project_a/target/classes/schema.SQL]执行SQL脚本2016-08-10 14:55:36.286 WARN 9032--[main]o.s.w.cs.GenericWebApplicationContext：上下文初始化期间遇到异常-取消刷新尝试：org.springframework.beans.factory.beanCreationException：创建名为“嵌套异常为org.springframework.beans.factory.beanCreationException：无法自动连接字段：private javax.sql.datasource org.springframework.boot.autocigure.orm.jpa.jpabaseConfiguration.datasource；嵌套异常为org.springframework.beans.factory.beanCreationException：创建类路径资源[org/springframework/boot/autocigure/jdbc/datasourceautociguration$nonembeddedconfiguration.class]中定义的名为“data source”的bean时出错：bean初始化失败；嵌套异常为org.springframework.beans.factory.BeanCreationException：创建名为“data sourceinitializer”的bean时出错：调用init方法失败；嵌套异常为org.springframework.jdbc.datasource.init.scriptstatementfailedexception：无法执行URL的SQL脚本语句#1[file:/c:/projects/project_a/target/classes/schema.SQL]：BEGIN执行立即“DROP TABLE TABLE_A”；嵌套的异常是java.sql.sqlexception:ora-06550:第1行，第44:pls-00103:当需要以下内容之一时，遇到符号“文件结束”：

• &=-+；at in is mod remailder not rem return returning<>或！=或~=>=<=<>and或like like2 like4 likec between to using multiset bulk member submultiset

在Spring Boot中有没有更优雅的方法来处理Oracle表的drop/create？

Answer 1

创建存储过程

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语句：

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

而不是包括PL/SQL