如何在oracle数据库上运行groovy的PL/SQL代码

Question

下面是我想从groovy程序针对oracle数据库运行的PL/SQL代码。

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

我怎么能得到它。我有所有的设置，比如从我的groovy程序连接到oracle数据库。我想做如下的事情：

sql = Sql.newInstance(url, username, password, driver)
String plSql="BEGINn" +
        "   EXECUTE IMMEDIATE 'DROP TABLE Employee';n" +
        "EXCEPTIONn" +
        "   WHEN OTHERS THENn" +
        "      IF SQLCODE != -942 THENn" +
        "         RAISE;n" +
        "      END IF;n" +
        "END;n" +
        "/"
sql.execute(plSql)

来自评论的错误日志

下面是我得到的错误。。。

Jun 29, 2015 9:05:52 PM groovy.sql.Sql execute WARNING: Failed to execute: BEGIN EXECUTE IMMEDIATE 'DROP TABLE Employee'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END; / because: ORA-06550: line 9, column 1: PLS-00103: Encountered the symbol "/" The symbol "/" was ignored. Caught: java.sql.SQLException: ORA-06550: line 9, column 1: PLS-00103: Encountered the symbol "/" The symbol "/" was ignored

Answer 1

使用调用方法执行PL/SQL块。如上所述，不要添加斜线，而是使用终止分号。

groovyCon.call("""BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE Employee';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;""")

此外，您还可以从Groovy多行字符串（"）中获益，它允许在数据库和Groovy之间直接剪切和粘贴PL/SQL块。