记一次ORACLE到PsotgreSQL数据迁移过程

一、本次使用的是navcat工具中的 “数据传输”，当然看情况可以使用kettle等其它软件。

 填写完成源和目标的连接信息，点击下一步傻瓜是操作可以了。

二、完成操作后发现项目报错，没有找到对应表和字段：

         1、查找原因：

                由于ORACLE库中表名和字段全部是大写，迁移到pg库，pg库中的表名和字段也全部是大写，而pgsql如果是大写需要在操作的表名和字段上加双引号（这个问题可以自行百度）。这下可咋整，表很多，如果手动改那不是gameover了。

        2、想办法解决：

                编写自定义函数来实现表名和字段名的大写转小写脚本如下：   

CREATE OR REPLACE FUNCTION exec("sqlstring" varchar)
  RETURNS varchar AS $BODY$
    declare
        res varchar(50);
    BEGIN
        EXECUTE sqlstring;
        RETURN 'success';
    END
$BODY$
  LANGUAGE plpgsql;

创建完成后就是修改，具体操作如下

先查询那些需要修改的表模式中那些字段是大写：

SELECT * FROM information_schema.COLUMNS 
WHERE table_schema = '自己的模式名'  AND TABLE_NAME <> 'pg_stat_statements'  
    AND COLUMN_NAME <> LOWER ( COLUMN_NAME )

    使用自定义函数修改字段： 

SELECT
    exec('alter table "' || table_name || '" rename column  "' || column_name || '" to ' || lower( column_name ) || ';')
FROM
    information_schema.columns 
WHERE
	table_schema = '自己的模式名' 
	AND TABLE_NAME <> 'pg_stat_statements' 
	AND COLUMN_NAME <> LOWER ( COLUMN_NAME );

   同理修改表名：

SELECT
    exec ( 'alter table "' || table_name || '" rename to ' || lower( table_name ) || ';' ) 
FROM
    information_schema.tables 
WHERE
table_schema = '自己的模式名' 

自己的模式名

 三、表迁移完成后还有视图、函数等，视图函数就比较麻烦了。        

        1、java、mysql、oracle、pgsql数据类型对应关系

Java	mysql	oracle	pgsql
int	int、tinyint	INTGER、NUMBER	int2、int4
long	bigint	NUMBER	int8
float	float	NUMBER、BINARY_FLOA	float4
double	double	NUMBER、BINARY_DOUBL	Float8
boolean	bit	NUMBER	bool
string	char、varchar、text	CHAR、VARCHAR、VARCHAR2	varchar、text
Java.sql.date	Date、time、datetime	DATE、TIMESTAMP	date、time、timestamp
Java.math.bgidecimal	decimal	NUMBER	numeric
Java.sql.blob	blob	BLOB	blob
Java.sql.clob	clob	CLOB	clob

2.Postgre与Oracle编写SQL的区别与转换方法

（1）.instr函数可以用like替换

也可以用 POSITION ('试油' in T1.WELLTESTMETHOD)>0替换

（2）.decode可以用case when 2 then 2 when 1 then -1替换

SELECT
M.DESIGN_ID,
MAX( DECODE( M.AUDIT_CODE, 2, 2, 1,- 1, 0, 0 ) ) AUDIT_CODE
FROM
PC_BUILD_DAILY_REPORT_DAY M
WHERE
to_date ( m.control_date, 'yyyy-mm-dd' ) = to_date ( '2019-05-21', 'yyyy-mm-dd' )
GROUP BY
M.DESIGN_ID

SELECT
A.design_id,
MAX(
CASE A.AUDIT_CODE
WHEN '2' THEN '2'
WHEN '1' THEN '-1'
WHEN '0' THEN '0'
END) AS AUDIT_CODE
FROM PC_BUILD_DAILY_REPORT_DAY A
WHERE
to_date ( A.control_date, 'yyyy-mm-dd' ) = to_date ( '2019-05-21', 'yyyy-mm-dd' )
GROUP BY
A.DESIGN_ID

（3）.SUBSTR 截取字符串的开始位置必须为1 例如截取aaaaa的四位 SUBSTR ('aaaaa',1,4) 若为SUBSTR ('aaaaa',0,4) 则无效

（4）.PostgreSQL中不需要dual虚拟表 select语句可以没有from

（5）.类似于select *from(select...) from里面的子查询必须要起别名,多层嵌套一样都要起别名

如select *from(select *from(select...) as A)as B

（6）.postgre中没有trunc、addmonths、lastday函数可以用date_trunc、interval替代(已经编写了f_add_months f_last_day函数可用)

例子：

求日期该年第一天
trunc(TO_DATE('20190714', 'yyyymmdd'),'yyyy')
date_trunc('year',TO_DATE('20190714', 'yyyymmdd'))

求日期该年最后一天 两种方式替换（第二种比较好）
last_day(add_months(trunc(TO_DATE('20190714', 'yyyymmdd'),'y'),11))
date_trunc('day',(date_trunc('year',TO_DATE('20190714', 'yyyymmdd'))+ interval '11 month')+interval '30 day')
(date_trunc('MONTH',date_trunc('year',TO_DATE('2019-07-14', 'yyyy-mm-dd'))+ interval '11 month') + INTERVAL '1 MONTH - 1 day')
求日期该月第一天
TRUNC(TO_DATE('20190414', 'yyyymmdd'),'mm')
TRUNC(ADD_MONTHS(LAST_DAY(TO_DATE('20190414', 'yyyymmdd')),-1)) + 1
date_trunc('MONTH',(date_trunc('MONTH',to_date('2019-07-09','yyyy-mm-dd')) - INTERVAL '1 MONTH')+ INTERVAL '1 MONTH')

（7）.postgre中没有nvl函数，可以用coalesce函数替代

nvl(collect_result,0) as collect_result
coalesce(collect_result,0) as collect_result

（8）.字符串去掉空格

oracle
Select LTRIM(' sql_in_a_nutshell'),
Select RTRIM('sql_in_a_nutshell '),
TRIM(' sql_in_a_nutshell ')
FROM dual;

PostgreSQL
Select TRIM(LEADING FROM ' sql_in_a_nutshell'),
TRIM(TRAILING FROM 'sql_in_a_nutshell '),
TRIM(BOTH FROM ' sql_in_a_nutshell ');

（9）.上面清除空格相反的操作，添加空格

oracle
Select LPAD(('sql', 5, ' '),
RPAD(('sql', 5, ' ')
FROM dual;

PostgreSQL
Select LPAD('sql', 5, ' '),
LPAD('sql', 2, ' '),
RPAD('sql', 5, ' '),
RPAD('sql', 2, ' '),;
//结果为 ' sql' 'sq' 'sql ' 'ql'

（10）.字符串替换

oracle
Select
REPLACE('wabbit_season','it_','it_hunting_')
FROM dual;

PostgreSQL
Select TRANSLATE('wabbit_season','it_','it_hunting_');
Select replace('wabbit_season','it_','it_hunting_');

（11）.NULLIF(expression1, expression2) 如果 expression1 等于 expression2则返回 NULL，如果expression1的值为null,也返回NULL

oracle
Select DECODE(foo,'Wabbits!',NULL)
FROM dual;

PostgreSQL
Select NULLIF(foo, 'Wabbits!');

（12）.postgre中没有nvl2函数，可以用case when is not null then 2 else 1替换

nvl2(name, '有人', '无人')

case when name is not null then '有人' else '无人' end

（13） REGEXP_SUBSTR可以替换为substring (匹配正则表达式)

REGEXP_SUBSTR(ST.STRAT_UNIT_NAME,'[^'|| UNISTR('\4e00') ||'-'||UNISTR('\9fa5') || ']',1,1)
SELECT substring(ST.STRAT_UNIT_NAME from '[^\u4e00-\u9fa5]')

太多了不一一列举了

3、视图迁移过程中有使用到oracle库的decode函数和nvl2函数等，这里我是通过编写自定义函数来实现的，当然你也可以通过前面的pg内置函数替代oracle函数：

pg库中模拟oracle库的decode函数：

create or replace function decode(variadic p_decode_list text[])
returns text
 as
$$
declare
 -- 获取数组长度(即入参个数)
 v_len integer := array_length(p_decode_list, 1);
 -- 声明存放返回值的变量
 v_ret text;
begin
 /*
 * 功能说明:模拟Oracle中的DECODE功能(字符串处理, 其它格式可以自行转换返回值)
 * 参数说明:格式同Oracle相同,至少三个参数
 * 实现原理: 1、VARIADIC 允许变参; 2、Oracle中的DECODE是拿第一个数依次和之后的偶数位值进行比较,相同则取偶数位+1的数值,否则取最后一位值(最后一位为偶数为,否则为null)
 */
 
 -- 同Oracle相同当参数不足三个抛出异常
 if v_len >= 3 then
  -- Oracle中的DECODE是拿第一个数依次和之后的偶数位值进行比较,相同则取偶数位+1的数值
  for i in 2..(v_len - 1) loop
   v_ret := null;
   if mod(i, 2) = 0 then
    if p_decode_list[1] = p_decode_list[i] then
     v_ret := p_decode_list[i+1];
    elsif p_decode_list[1] <> p_decode_list[i] then
     if v_len = i + 2 and v_len > 3 then
      v_ret := p_decode_list[v_len];
     end if;
    end if;
   end if;
   exit when v_ret is not null;
  end loop;
 else
  raise exception 'UPG-00938: not enough args for function.';
 end if;
 return v_ret;
end;
$$
 language plpgsql;	

pg库中模拟oracle的nvl2函数：

CREATE OR REPLACE FUNCTION nvl2("exp1" int4, "exp2" int4, "exp3" int4)
  RETURNS varchar AS $BODY$
    DECLARE
        
    BEGIN
        IF (exp1 is NULL) THEN
						RETURN exp2;
				ELSE		
						RETURN exp3;
				END IF;
		END;
$BODY$
  LANGUAGE plpgsql;

如以上描述存在问题，请多多指教。