一次将SQL脚本执行到oracle db一个语句 [英] Executing SQL script into oracle db one statement at a time

问题描述

假设我有一个如下所示的sql脚本：

Let's say that I have a sql script that looks like this:

--split statement 1
ALTER TABLE abs
  ADD (make    VARCHAR2(2 byte),
     model    varCHAR2(12 BYTE),
     built_on DATE,
     serial    varchar2(123 BYTE));
    /

    --split statement 2 
     declare
     begin
         null;
     end;
     /

     --split statement 3
     insert into test (v,a,c)
     values ('1','jjoe;','232');

     --split statement 4
     create or replace function BLAH_BLAH(i_in varchar2)
     as
         l_one varchar2(12);
         l_two varchar2(12);
         l_three varchar2(12);
     begin
         l_one := 1;
         l_two := 3;

         insert into test (v,a,b)
         values ('1','jjoee;','232');


    exception when no_data_found then
        l_three := 3;


    end;
    /

基本上，脚本可以包含DML，DCL，DDL和匿名块。我希望能够分割每个语句并单独执行它们，但当然是为了它们出现。

Basically the script can have DML, DCL, DDL, and anonymous blocks. I want to be able to split each statement and execute them individually, but of course in order that they appear.

我想使用正则表达式，我相信逻辑必须是这样的：

I thought of using a regular expression, and I believe the logic would have to be something like this:

1）如果字符串以create | alter | drop | declare开头，那么从该字符串的开头到分号后跟一个新行，后跟一个正斜杠（这里的关键是，如果是匿名阻止，我们必须忽略DML，直到我们到达结尾）。

1) if the string starts with create|alter|drop|declare, get everything from the start of that string down to the semi-colon that is followed by a a new line, followed by a forward slash (the key here is that in the event of an anonymous block, we have to ignore DML until we reach the end).

2）如果字符串以insert | delete | update | merge开头（如果我们已经在适用于要求1的块中，则再次忽略），从该字符串的开头到分号后跟一个没有正斜杠的换行符。

2) if the string starts with insert|delete|update|merge (again, ignored if we are already in a block that applies to requirement 1), get everything from the start of that string down to the semi-colon that is followed by a new-line with no forward slash.

到目前为止，我已经用Python编写了这个：

So far I've come up with this in Python:

sql_commands = re.split('(?i)(((create|alter|drop|merge)+)(?m);(\s*)\n(\s*))|(;(\s*)\n(\s*)/)',sql_script)  

但每当我尝试向前推进其他要求时，正则表达式开始不起作用（实际上输出有点像时髦）并且变得足够复杂，让我迷失方向。

but everytime I try to move forward with the other requirements the regular expression starts to not work (and actually the output is kind of funky as it is) and becomes complicated enough for me to get lost in.

我想用Python或Java完成这项工作（Java，我想，如果这是一个oracle数据库，实际上会更受欢迎）

I'd like to have this done in either Python or Java (Java, I suppose, would actually be preferred given this is an oracle db)

如果正则表达式不适用于此任务，则不必是正则表达式。我的最终目标是拆分每个语句并单独运行它以便我能够捕获任何出现的错误并优雅地处理它们。

This doesn't have to be a regular expression if regex isn't really up to this task. My ultimate goal is to split out each statement and run it individually so I can catch any errors that come up and gracefully handle them.

推荐答案

Pseudeo-Code，未经测试，只是为了表明这个想法：

Pseudeo-Code, not tested, just to show the idea:

while (line = readLine()) {
    String cmdString = null;
    if (line.beginsWith("create" || line.beginsWith("alter") ...) {
       String previousLine = line;
       while (line = readLine()) {
          if (line.equals("/") && previousLine.endsWith(";")) {
            executeSQL(cmdString);
            break;
          }
          previousLine = line;
          cmdString = cmdString + line;
       }
    }
    if (line.beginsWith("insert" || line.beginsWith("update") ...) {
       String previousLine = line;
       while (line = readLine()) {
          if (line.equals("\n") && previousLine.endsWith(";")) {
            executeSQL(cmdString);
            break;
          }
          previousLine = line;
          cmdString = cmdString + line;
       }
    }
    // skip others
}

这篇关于一次将SQL脚本执行到oracle db一个语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！