Oracle应用迁移至AnalyticDB PostgreSQL

基于ora2pg完成初步转换工作

可以使用开源工具ora2pg进行最初的Oracle应用转换。您可以使用ora2pg将Oracle的表DDL，view，package等语法转换成PostgreSQL兼容的语法。具体操作方法请参见ora2pg的用户文档。

使用Orafce插件

AnalyticDB PostgreSQL中提供了Orafce插件，该插件提供了一些兼容Oracle的函数。对于这些函数，您无需任何修改转换即可在AnalyticDB PostgreSQL中使用。

在使用Orafce插件前，请执行如下命令进行安装。

CREATE EXTENSION orafce;

Orafce插件提供的兼容函数如下表所示。

SELECT add_months(current_date, 2);

add_months
------------
2019-08-31
(1 row)

SELECT last_day('2018-06-01');

 last_day
------------
2018-06-30
(1 row)

SELECT next_day(current_date, 'FRIDAY');

 next_day
------------
2019-07-05
(1 row)

SELECT TRUNC('2019-12-11'::timestamp);

        trunc
------------------------
2019-12-11 00:00:00+08
(1 row)

SELECT TRUNC('2019-12-11'::timestamp,'Y');

        trunc
------------------------
2019-01-01 00:00:00+08

SELECT round('2018-10-06 13:11:11'::timestamp, 'YEAR');

        round
------------------------
2019-01-01 00:00:00+08
(1 row)

SELECT round('2018-10-06 13:11:11'::timestamp);

        round
------------------------
2018-10-07 00:00:00+08
(1 row)

SELECT round(TO_DATE('27-FEB-00','DD-MON-YY'));

  round
------------
2000-02-27
(1 row)

SELECT instr('Greenplum', 'e',1);

instr
-------
    3
(1 row)

SELECT instr('Greenplum', 'e');

instr
-------
    3
(1 row)

SELECT plvstr.rvrs('adb4pg', 5,6);

reverse
---------
gp
(1 row)

SELECT plvstr.rvrs('adb4pg', 4);

reverse
---------
gp4
(1 row)

SELECT plvstr.rvrs('adb4pg');

reverse
---------
gp4bda
(1 row)

SELECT concat('adb','4pg');

concat
--------
adb4pg
(1 row)

SELECT listagg(t) FROM (VALUES('abc'), ('def')) as l(t);

listagg
---------
abcdef
(1 row)

SELECT listagg(t, '.') FROM (VALUES('abc'), ('def')) as l(t);

listagg
---------
abc.def
(1 row)

SELECT  dump('adb4pg');

                dump
---------------------------------------
Typ=705 Len=7: 97,100,98,52,112,103,0
(1 row)

CREATE TABLE t1 (name text);
INSERT INTO t1 VALUES('Anne'), ('anne'), ('Bob'), ('bob');

SELECT substr('adb4pg', 5,6);

substr
--------
pg
(1 row)

SELECT  substr('adb4pg'::varchar2, 5,6);

substr
--------
pg
(1 row)

SELECT substr('adb4pg'::varchar2, 4) ;

substr
--------
4pg
(1 row)

Orafce插件除了提供上述兼容函数，还对Oracle的Varchar2数据类型提供了兼容。

对于以下的四个Oracle函数，在AnalyticDB PostgreSQL中，无需安装Orafce插件就可以提供兼容支持。

SELECT sinh(0.1);

      sinh
-------------------
0.100166750019844
(1 row)

SELECT  tanh(3);

      tanh
------------------
0.99505475368673
(1 row)

SELECT cosh(0.2);

      cosh
------------------
1.02006675561908
(1 row)

CREATE TABLE t1(id int, name varchar(20));
INSERT INTO t1 values(1,'alibaba');
INSERT INTO t1 values(2,'adb4pg');

数据类型转换对照表

系统函数转换对照表

PL/SQL迁移指导

PL/SQL（Procedural Language/SQL）是一种过程化的SQL语言，是Oracle对SQL语句的拓展。PL/SQL使得SQL的使用可以具有一般编程语言的特点，可以用来实现复杂的业务逻辑。PL/SQL对应了AnalyticDB PostgreSQL中的PL/PGSQL。

Package

PL/PGSQL不支持Package，需要把Package转换成schema，同时Package里面的所有procedure和function也需要转换成AnalyticDB PostgreSQL的function。

例如：

CREATE OR REPLACE PACKAGE pkg IS 

…

END;

转换成：

CREATE SCHEMA pkg;

• Package定义的变量

  Procedure/Function的局部变量保持不变，全局变量在AnalyticDB PostgreSQL中可以使用临时表进行保存。

• Package初始化块

  请删除，若无法删除请使用Function封装，在需要的时候主动调用该Function。

• Package内定义的Procedure/Function

  Package内定义的Procedure和Function需要转成AnalyticDB PostgreSQL的Function，并把Function定义到Package对应的Schema内。

  例如，有一个Package名为pkg中有如下函数：

  FUNCTION test_func (args int) RETURN int is
  var number := 10;
  BEGIN
  … …
  END;

  转换成如下AnalyticDB PostgreSQL的Function：

  CREATE OR REPLACE FUNCTION pkg. test_func(args int) RETURNS int AS
  $$
  
    … …
  
  $$
   LANGUAGE plpgsql;

Procedure/Function

Oracle中的Procedure和Function，不论属于Package的还是属于全局，都需要转换成AnalyticDB PostgreSQL的Function。

例如：

CREATE OR REPLACE FUNCTION test_func (v_name varchar2, v_version varchar2)
RETURN varchar2 IS
    ret varchar(32);
BEGIN
    IF v_version IS NULL THEN
        ret := v_name;
ELSE
    ret := v_name || '/' || v_version;
    END IF;
    RETURN ret;
END;

转化成：

CREATE OR REPLACE FUNCTION test_func (v_name varchar, v_version varchar)
RETURNS varchar AS
$$

DECLARE
    ret varchar(32);
BEGIN
    IF v_version IS NULL THEN
        ret := v_name;
ELSE
    ret := v_name || '/' || v_version;
    END IF;
    RETURN ret;
END;

$$
 LANGUAGE plpgsql;

Procedure/Function转换的注意事项有以下几点：

• RETURN关键字转成RETURNS。
• 函数体使用$\$ ... $\$进行封装。
• 函数语言声明。
• Subprocedure需要转换成AnalyticDB PostgreSQL的Function。

PL statement

• FOR语句：
  PL/SQL和PL/PGSQL在带有REVERSE的整数FOR循环中的工作方式不同：

  • PL/SQL中是从第二个数向第一个数倒数。
  • PL/PGSQL是从第一个数向第二个数倒数。

  因此在移植时需要交换循环边界，如下所示：

  FOR i IN REVERSE 1..3 LOOP
      DBMS_OUTPUT.PUT_LINE (TO_CHAR(i));
  END LOOP;

  转换成：

  FOR i IN REVERSE 3..1 LOOP
      RAISE ‘%’ ,i;
  END LOOP;

• PRAGMA语句

  AnalyticDB PostgreSQL中没有PRAGMA语句，删除该类语句。

• 事务处理

  AnalyticDB PostgreSQL的Function内部无法使用事务控制语句，如begin，commit，rollback等。

  修改方法如下：

  • 删除函数体内的事务控制语句，把事务控制放在函数体外。
  • 把函数按照commit或rollback拆分成多个。
• EXECUTE语句

  AnalyticDB PostgreSQL支持类似Oracle的动态SQL语句，不同之处如下：

  • 不支持using语法，可通过把参数拼接到SQL串中来解决。
  • 数据库标识符使用quote_ident包裹，数值使用quote_literal包裹。

  示例：

  EXECUTE 'UPDATE employees_temp SET commission_pct = :x' USING a_null;

  转换成：

  EXECUTE 'UPDATE employees_temp SET commission_pct = ' || quote_literal(a_null);

• Pipe row

  Pipe row函数，可使用AnalyticDB PostgreSQL的Table Function函数来替换。

  示例：

  TYPE pair IS RECORD(a int, b int);
  TYPE numset_t IS TABLE OF pair;
  
  FUNCTION f1(x int) RETURN numset_t PIPELINED IS
  DECLARE
      v_p pair;
  BEGIN
      FOR i IN 1..x LOOP
        v_p.a := i;
        v_p.b := i+10;
        PIPE ROW(v_p);
      END LOOP;
      RETURN;
  END;
  
  select * from f1(10);

  转换成：

  CREATE TYPE pair AS (a int, b int);
  
  CREATE OR REPLACE FUNCTION f1(x int) RETURNS SETOF PAIR AS
  $$
  
  DECLARE
  REC PAIR;
  BEGIN
      FOR i IN 1..x loop
          REC := row(i, i+10);
          RETURN NEXT REC;
      END LOOP;
      RETURN ;
  END
  
  $$
   language 'plpgsql';
  
  SELECT * FROM f1(10);

• 异常处理
  • 使用raise抛出异常。
  • Catch异常后，不能rollback事务，只能在udf外做rollback。
  • AnalyticDB PostgreSQL支持的Error，请参见PostgreSQL官网。
• Function中同时有Return和OUT参数

  在AnalyticDB PostgreSQL中，Function无法同时存在return和out参数，需要把返回的参数改写成out类型参数。

  示例：

  CREATE OR REPLACE FUNCTION test_func(id int, name varchar(10), out_id out int) returns varchar(10)
  AS $body$
  BEGIN
        out_id := id + 1;
        return name;
  end
  $body$
  LANGUAGE PLPGSQL;

  转换成：

  CREATE OR REPLACE FUNCTION test_func(id int, name varchar(10), out_id out int, out_name out varchar(10))
  AS $body$
  BEGIN
        out_id := id + 1;
        out_name := name;
  end
  $body$
  LANGUAGE PLPGSQL;

  SELECT * FROM test_func(1,’1’) INTO rec;从rec中取对应字段的返回值即可。

• 字符串连接中变量含有单引号

  在如下示例中，变量param2是一个字符串类型。假设param2的值为adb'-'pg。下面的sql_str直接放到AnalyticDB PostgreSQL中使用会将-识别成一个operator而报错。需要使用quote_literal函数来进行转换。

  示例：

  sql_str := 'SELECT * FROM test1 WHERE col1 = ' || param1 || ' AND col2 = '''|| param2 || '''AND col3 = 3';

  转换成：

  sql_str := 'SELECT * FROM test1 WHERE col1 = ' || param1 || ' AND col2 = '|| quote_literal(param2) || 'AND col3 = 3';

• 获取两个timestamp相减后的天数

  示例：

  SELECT to_date('2019-06-30 16:16:16') - to_date('2019-06-29 15:15:15') + 1 INTO v_days FROM dual;

  转换成：

  SELECT extract('days' from '2019-06-30 16:16:16'::timestamp - '2019-06-29 15:15:15'::timestamp + '1 days'::interval)::int INTO v_days;

PL数据类型

• Record

  使用AnalyticDB PostgreSQL的复合数据类型替换。

  示例：

  TYPE rec IS RECORD (a int, b int);

  转换成：

  CREATE TYPE rec AS (a int, b int);

• Nest table
  • Nest table作为PL变量，可以使用AnalyticDB PostgreSQL的array类型替换。

    示例：

    DECLARE
      TYPE Roster IS TABLE OF VARCHAR2(15);
      names Roster :=
      Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
    BEGIN
      FOR i IN names.FIRST .. names.LAST
      LOOP
          IF names(i) = 'J Hamil' THEN
            DBMS_OUTPUT.PUT_LINE(names(i));
          END IF;
      END LOOP;
    END;

    转换成：

    CREATE OR REPLACE FUNCTION f1() RETURNS VOID AS
    $$
    
    DECLARE
        names varchar(15)[] := '{"D Caruso", "J Hamil", "D Piro", "R Singh"}';
        len int := array_length(names, 1);
    BEGIN
        for i in 1..len loop
            if names[i] = 'J Hamil' then
                raise notice '%', names[i];
            end if;
        end loop;
        return ;
    END
    
    $$
     language 'plpgsql';
    
    SELECT f();

  • 作为Function返回值，可以使用Table Function替换。
• Associative Array

  无替换类型。

• Variable-Size Arrays

  与Nest table类似，使用array类型替换。

• Global variables

  目前AnalyticDB PostgreSQL不支持Global variables，可以把Package中的所有Global variables存入一张临时表（temporary table）中，然后修改定义，获取Global variables的函数。

  示例：

  CREATE TEMPORARY TABLE global_variables (
          id int,
          g_count int,
          g_set_id varchar(50),
          g_err_code varchar(100)
  );
  
  INSERT INTO global_variables VALUES(0, 1, null，null);
  
  CREATE OR REPLACE FUNCTION get_variable() returns setof global_variables AS
  
  $$
  
  DECLARE
      rec global_variables%rowtype;
  BEGIN
      execute 'select * from global_variables' into rec;
      return next rec;
  END;
  
  $$
   LANGUAGE plpgsql;
  
  CREATE OR REPLACE FUNCTION set_variable(in param varchar(50), in value anyelement) returns void AS
  
  $$
  
  BEGIN
      execute 'update global_variables set ' ||  quote_ident(param) || ' = ' || quote_literal(value);
  END;
  
  $$
   LANGUAGE plpgsql;

  临时表Global_variables中，字段ID为这个表的分布列，由于AnalyticDB PostgreSQL中不允许对于分布列的修改，需要加一个tmp_rec record;字段。

  修改一个全局变量时，使用：select * from set_variable(‘g_error_code’, ‘error’::varchar) into tmp_rec;。

  获取一个全局变量时，使用：select * from get_variable() into tmp_rec; error_code := tmp_rec.g_error_code;。

SQL

• Connect by

  Oracle层次查询，AnalyticDB PostgreSQL没有等价替换的SQL语句。可以使用循环按层次遍历这样的转换思路。

  示例：

  CREATE TABLE employee(
         emp_id numeric(18),
         lead_id numeric(18),
         emp_name varchar(200),
         salary numeric(10,2),
         dept_no varchar(8)
  );
  INSERT INTO employee values('1',0,'king','1000000.00','001');
  INSERT INTO employee values('2',1,'jack','50500.00','002');
  INSERT INTO employee values('3',1,'arise','60000.00','003');
  INSERT INTO employee values('4',2,'scott','30000.00','002');
  INSERT INTO employee values('5',2,'tiger','25000.00','002');
  INSERT INTO employee values('6',3,'wudde','23000.00','003');
  INSERT INTO employee values('7',3,'joker','21000.00','003');
  INSERT INTO employee values('3',7,'joker','21000.00','003');

  SELECT emp_id,lead_id,emp_name,prior emp_name as lead_name,salary
       FROM employee
       START WITH  lead_id=0
       CONNECT BY prior emp_id =  lead_id

  转换成：

  CREATE OR REPLACE FUNCTION f1(tablename text, lead_id int, nocycle boolean) RETURNS setof employee AS
  $$
  
  DECLARE
      idx int := 0;
      res_tbl varchar(265) := 'result_table';
      prev_tbl varchar(265) := 'tmp_prev';
      curr_tbl varchar(256) := 'tmp_curr';
  
      current_result_sql varchar(4000);
      tbl_count int;
  
      rec record;
  BEGIN
  
      execute 'truncate ' || prev_tbl;
      execute 'truncate ' || curr_tbl;
      execute 'truncate ' || res_tbl;
      loop
          -- 查询当前层次结果，并插入到tmp_curr表
          current_result_sql := 'insert into ' || curr_tbl || ' select t1.* from ' || tablename || ' t1';
  
          if idx > 0 then
              current_result_sql := current_result_sql || ', ' || prev_tbl || ' t2 where t1.lead_id = t2.emp_id';
          else
              current_result_sql := current_result_sql || ' where t1.lead_id = ' || lead_id;
          end if;
          execute current_result_sql;
  
          -- 如果有环，删除已经遍历过的数据
          if nocycle is false then
              execute 'delete from ' || curr_tbl || ' where (lead_id, emp_id) in (select lead_id, emp_id from ' || res_tbl || ') ';
          end if;
  
          -- 如果没有数据，则退出
          execute 'select count(*) from ' || curr_tbl into tbl_count;
          exit when tbl_count = 0;
  
          -- 把tmp_curr数据保存到result表
          execute 'insert into ' || res_tbl || ' select * from ' || curr_tbl;
          execute 'truncate ' || prev_tbl;
          execute 'insert into ' || prev_tbl || ' select * from ' || curr_tbl;
          execute 'truncate ' || curr_tbl;
          idx := idx + 1;
      end loop;
  
      -- 返回结果
      current_result_sql := 'select * from ' || res_tbl;
      for rec in execute current_result_sql loop
          return next rec;
      end loop;
      return;
  END
  
  $$
   language plpgsql;

• Rownum
  1. 限定查询结果集大小，可以使用limit替换。

     示例：

     SELECT * FROM t WHERE rownum < 10;

     转换成：

     SELECT * FROM t LIMIT 10;

  2. 使用row_number() over()生成rownum。

     示例：

     SELECT rownum, * FROM t;

     转换成：

     SELECT row_number() over() AS rownum, * FROM t;

• Dual表
  1. 去掉Dual。

     示例：

     SELECT sysdate FROM dual;

     转换成：

     SELECT current_timestamp;

  2. 创建一个叫dual的表。
• Select中的udf

  AnalyticDB PostgreSQL支持在select中调用udf，但是udf中不能有SQL语句，否则会收到如下的错误信息：

  ERROR: function cannot execute on segment because it accesses relation "public.t2" (functions.c:155) (seg1 slice1 127.0.0.1:25433 pid=52153) (cdbdisp.c:1326)
  DETAIL:
  SQL statement "select b from t2 where a = $1 "

  可以把select中的udf转换成SQL表达式或者子查询等方法来转换。

  示例：

  CREATE OR REPLACE FUNCTION f1(arg int) RETURN int IS
      v int;
  BEGIN
      SELECT b INTO v FROM t2 WHERE a = arg;
      RETURN v;
  END;
  
  SELECT  a, f1(b) FROM t1;

  转换成：

  SELECT t1.a, t2.b FROM t1, t2 WHERE t1.b = t2.a;

• （+）多表外链接

  AnalyticDB PostgreSQL不支持（+）语法形式，需要转换成标准的Outer Join语法。

  示例：

  SELECT * FROM a,b WHERE a.id=b.id(+)

  转换成：

  SELECT * FROM a LEFT JOIN b ON a.id=b.id

  若在（+）中有三表的Join，需要先用WTE做两表的Join，再用+号那个表跟WTE表做Outer Join。

  示例：

  SELECT * FROM test1 t1, test2 t2, test3 t3 WHERE t1.col1(+) BETWEEN NVL(t2.col1, t3.col1) ADN NVL(t3.col1, t2.col1);

  转换成：

  WITH cte AS (SELECT t2.col1 AS low, t2.col2, t3.col1 AS high, t3.col2 AS c2 FROM t2, t3)
  SELECT * FROM t1 RIGHT OUTER JOIN cte ON t1.col1 BETWEEN coalesce(cte.low, cte.high) AND coalesce(cte.high,cte.low);

• Merge Into

  通常情况下Merge Into语法可以通过INSERT ON CONFLICT语法进行适配，但是Merge Into语法的部分功能INSERT ON CONFLICT语法暂时无法实现，需要使用存储过程解决。

  INSERT ON CONFLICT语法更多介绍，请参见使用INSERT ON CONFLICT覆盖写入数据。

  存储过程的更多介绍，请参见存储过程。

• Sequence

  示例：

  CREATE SEQUENCE seq1;
  SELECT seq1.nextval FROM dual;

  转换成：

  CREATE SEQUENCE seq1;
  SELECT nextval('seq1');

• Cursor的使用
  • 在Oracle中，可以使用下面的语句对cursor进行遍历。

    示例：

    FUNCTION test_func() IS
        Cursor data_cursor IS SELECT * from test1;
    BEGIN
        FOR I IN data_cursor LOOP
            Do something with I;
    END LOOP;
    END;

    转换成：

    CREATE OR REPLACE FUNCTION test_func()
    AS $body$
    DECLARE
    data_cursor cursor for select * from test1;
    I record;
    BEGIN
        Open data_cursor;
        LOOP
           Fetch data_cursor INTO I;
          If not found then
                Exit;
          End if;
          Do something with I;
        END LOOP;
        Close data_cursor;
    END;
    $body$
    LANGUAGE PLPGSQL;

  • Oracle可以在递归调用的函数里重复打开名字相同的cursor。但是在AnalyticDB PostgreSQL中则无法重发重复打开，需要改写成for I in query的形式。

    示例：

    FUNCTION test_func(level IN numer) IS
        Cursor data_cursor IS SELECT * from test1;
    BEGIN
    If level > 5 then
            return;
       End if;
    
        FOR I IN data_cursor LOOP
            Do something with I;
            test_func(level + 1);
    END LOOP;
    END;

    转换成：

    CREATE OR REPLACE FUNCTION test_func(level int) returns void
    AS $body$
    DECLARE
    data_cursor cursor for select * from test1;
    I record;
    BEGIN
        If level > 5 then
            return;
        End if;
        For I in select * from test1 LOOP
          Do something with I;
           PERFORM test_func(level+1);
        END LOOP;
    END;
    $body$
    LANGUAGE PLPGSQL;