Oracle迁移到MogDB之MULTISET UNION

MULTISET UNION

MULTISET UNION返回两个集合变量的并集，但因MogDB与Oracle有语法差异，迁移时可能会遇到报错，我们需要根据MogDB的语法进行修正。

Oracle

下面代码返回三个集合的并集

DECLARE
  TYPE nest IS TABLE OF INT;
  a nest := nest(1, 2);
  b nest := nest(2, 3);
  c nest := nest(4, 5);
BEGIN
  a := a MULTISET UNION b MULTISET UNION c;
  FOR idx IN 1 .. a.count LOOP
    dbms_output.put_line(a(idx));
  END LOOP;
END;
/

结果显示

 12  /
1
2
2
3
4
5

PL/SQL procedure successfully completed.

MogDB

原代码直接运行会报错

orcl=> DECLARE
orcl->   TYPE nest IS TABLE OF INT;
orcl->   a nest := nest(1, 2);
orcl->   b nest := nest(2, 3);
orcl->   c nest := nest(4, 5);
orcl-> BEGIN
orcl$>   a := a MULTISET UNION b MULTISET UNION c;
orcl$>   FOR idx IN 1 .. a.count LOOP
orcl$>     dbms_output.put_line(a(idx));
orcl$>   END LOOP;
orcl$> END;
orcl$> /
ERROR:  syntax error at or near "c"
LINE 8:   FOR idx IN 1 .. a.count LOOP
        ^

这并不是MogDB不支持MULTISET UNION，而是语法有差异。我们需要更改原语句，分两次使用MULTISET UNION。

orcl=> DECLARE
orcl->   TYPE nest IS TABLE OF INT;
orcl->   a nest := nest(1, 2);
orcl->   b nest := nest(2, 3);
orcl->   c nest := nest(4, 5);
orcl-> BEGIN
orcl$>   a := a MULTISET UNION b;
orcl$>   a := a MULTISET UNION c;
orcl$>   FOR idx IN 1 .. a.count LOOP
orcl$>     dbms_output.put_line(a(idx));
orcl$>   END LOOP;
orcl$> END;
orcl$> /
1
2
2
3
4
5
ANONYMOUS BLOCK EXECUTE
orcl=>