语法错误:预期的name_wo_function_call

Question

我正在编写一个脚本，它将两个表(table2，table3)的内容合并为一个公共表(table1)。

我在甲骨文的例子里。

这是我的密码。

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;

虽然我怀疑这更像是copy+paste+botched匿名错误。

而且，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;