无法使用“如果存在则删除表"；在Spring Boot应用程序的schema.sql中 [英] Unable to use "DROP TABLE IF EXISTS" in schema.sql for a Spring Boot application

问题描述

我需要有关在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:

2016-08-10 14:55:36.232信息9032 --- [main] osjdbc.datasource.init.ScriptUtils:从URL执行SQL脚本[文件:/C:/projects/project_a/target/classes/schema.sql] 2016-08-10 14:55:36.286 WARN 9032 --- [main] oswcsGenericWebApplicationContext:上下文初始化期间遇到异常-取消刷新尝试:org.springframework.beans.factory.BeanCreationException:创建名称为'org.springframework的bean时出错.boot.autoconfigure.orm.jpa.HibernateJpaAutoConfiguration':自动连接的依赖项注入失败；嵌套的异常是org.springframework.beans.factory.BeanCreationException:无法自动连线字段:私有javax.sql.DataSource org.springframework.boot.autoconfigure.orm.jpa.JpaBaseConfiguration.dataSource;嵌套的异常是org.springframework.beans.factory.BeanCreationException:创建在类路径资源[org/springframework/boot/autoconfigure/jdbc/DataSourceAutoConfiguration $ NonEmbeddedConfiguration.class]中定义的名称为"dataSource"的bean时出错.嵌套的异常是org.springframework.beans.factory.BeanCreationException:创建名称为'dataSourceInitializer'的bean时出错:调用init方法失败；嵌套的异常是org.springframework.jdbc.datasource.init.ScriptStatementFailedException:无法执行URL [file:/C:/projects/project_a/target/classes/schema.sql]的SQL脚本语句#1:BEGIN EXECUTE IMMEDIATE'DROP TABLE table_a';嵌套的异常是java.sql.SQLException:ORA-06550:第1行，第44列: PLS-00103:在出现以下情况之一时遇到符号文件结尾" :

2016-08-10 14:55:36.232 INFO 9032 --- [ main] o.s.jdbc.datasource.init.ScriptUtils : Executing SQL script from URL [file:/C:/projects/project_a/target/classes/schema.sql] 2016-08-10 14:55:36.286 WARN 9032 --- [ main] o.s.w.c.s.GenericWebApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaAutoConfiguration': Injection of autowired dependencies failed; nested exception is org.springframework.beans.factory.BeanCreationException: Could not autowire field: private javax.sql.DataSource org.springframework.boot.autoconfigure.orm.jpa.JpaBaseConfiguration.dataSource; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource' defined in class path resource [org/springframework/boot/autoconfigure/jdbc/DataSourceAutoConfiguration$NonEmbeddedConfiguration.class]: Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSourceInitializer': Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of URL [file:/C:/projects/project_a/target/classes/schema.sql]: BEGIN EXECUTE IMMEDIATE 'DROP TABLE table_a'; nested exception is java.sql.SQLException: ORA-06550: line 1, column 44: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

• & =-+; < />在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中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！