sql - 语法错误: expected name_wo_function_call

Question

我正在尝试编写一个脚本，将两个表(table2，table3)的内容合并为一个常见的表(table1)。

我在一个Oracle实例上。

这是我的代码。

DECLARE
region VARCHAR2 :='REG1'; -- just for testing
BEGIN
  CASE
      WHEN region = 'REG1' THEN
          BEGIN
              MERGE INTO table1 USING (
                  SELECT 
                      field1 table2field1,
                      field2 table2field2
                  FROM table2
              ) ON (
                  field1 = table2field1
              )
              WHEN MATCHED THEN 
                  UPDATE SET
                      field2 = table2field2
              WHEN NOT MATCHED THEN
                  INSERT (
                      field1,
                      field2
                  ) VALUES (
                      table2field1,
                      table2field2
                  );
              COMMIT;
          END;
      WHEN region = 'REG2' THEN
          BEGIN
              MERGE INTO table1 USING (
                  SELECT 
                      field1 table3field1,
                      field2 table3field2
                  FROM table3
              ) ON (
                  field1 = table3field1
              )
              WHEN MATCHED THEN 
                  UPDATE SET
                      field2 = table3field2
              WHEN NOT MATCHED THEN
                  INSERT (
                      field1,
                      field2
                  ) VALUES (
                      table3field1,
                      table3field2
                  );
              COMMIT;
          END;
  END;
  EXCEPTION
      WHEN NO_DATA_FOUND THEN
          NULL;
      WHEN OTHERS THEN
         RAISE;
END;

SQL Developer说:

Syntax error. Expected:
    name_wo_function_call
    name
    procedure_call

现在，我对plsql还是很陌生，我敢肯定一定缺少某些东西。

我该如何解决该错误？

Answer 1

小变化:

在WHEN region ='REG2'部分中，您具有:

   SELECT field1 table3field1,
          field2 table3field2,  -- extra comma
    FROM table3

在from子句之前的多余逗号是错误的。

同样与

WHEN NOT MATCHED THEN
                  INSERT (
                      field1,
                      field2,  -- extra comma
                  ) VALUES (
                      table3field1,
                      table3field2,  -- extra comma
                  );
              COMMIT;

尽管我怀疑这更多是复制+粘贴+错误的匿名化错误。

另外，CASE语句应以 END CASE结尾，因此它看起来应该像

DECLARE
  region  VARCHAR2 := 'REG1';                                                              -- just for testing
BEGIN
  CASE
    WHEN region = 'REG1' THEN
      BEGIN
        MERGE INTO table1
             USING (SELECT field1 table2field1, field2 table2field2 FROM table2)
                ON (field1 = table2field1)
        WHEN MATCHED THEN
          UPDATE SET field2 = table2field2
        WHEN NOT MATCHED THEN
          INSERT     (field1, field2)
              VALUES (table2field1, table2field2);

        COMMIT;
      END;
    WHEN region = 'REG2' THEN
      BEGIN
        MERGE INTO table1
             USING (SELECT field1 table3field1, field2 table3field2 FROM table3)
                ON (field1 = table3field1)
        WHEN MATCHED THEN
          UPDATE SET field2 = table3field2
        WHEN NOT MATCHED THEN
          INSERT     (field1, field2)
              VALUES (table3field1, table3field2);

        COMMIT;
      END;

      NULL;
  END CASE; -- end case, not just end
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;
  WHEN OTHERS THEN
    RAISE;
END;