1 数据源配置

1.1 驱动包配置

​ 在pom文件中添加pgsql数据驱动依赖:

1
2
3
4
5
<dependency>
	<groupId>org.postgresql</groupId>       
	<artifactId>postgresql</artifactId>
	<version>42.2.14.jre7</version>      
</dependency>

1.2 修改数据源

​ 在配置文件中修改数据源连接:

1
2
3
4
jdbc.driverClassName=org.postgresql.Driver
jdbc.url=jdbc:postgresql://xxx.xxx.xxx.xxx:xxxxx/xxxx
jdbc.username=********
jdbc.password=********

2 数据迁移

2.1 导出表结构及数据

2.1.1 使用navicat视图工具（不推荐）

​ 使用navicat视图工具导出Oracle表结构及所有数据，PLSQL 工具导出数据需要修改的东西太多,数据迁移效率很低

2.1.2 使用ora2pg工具（推荐）

​ 推荐使用ora2pg工具进行数据迁移

2.2 数据迁移，表结构字段类型对应

2.3 数据处理

2.3.1 表名及字段名大小写修改

​ Oracle会自动将表名及字段名转换为大写,在pgsql中使用要求在表名及字段名上添加””,故将表名及字段名全部转换成小写

表名转换sql：

1
2
3
4
5
6
select 
	'alter table "' || table_name || '" rename to ' || LOWER(table_name) || ';'
from information_schema.tables
where table_name <> LOWER(table_name)
	and "table_schema" = 'public’ 
	and "table_catalog" = ‘XXXX';

列名转换sql：

1
2
3
4
5
6
7
select 
	'alter table "' || table_name || '" rename column  "' || column_name || '"   to   ' || LOWER(column_name) || ';'
from information_schema.columns
where column_name <> LOWER(column_name)
  	and "table_schema" = 'public’
	and "table_catalog" = ‘XXXX';

3 SQL替换

3.1 索引

3.1.1 基本语法

1
CREATE INDEX index_name ON table_name;

3.1.2 单列索引

1
CREATE INDEX index_name ON table_name(column);

3.1.3 组合索引

1
CREATE INDEX index_name ON table_name(column1, column2...);

3.1.4 唯一索引

1
CREATE UNIQUE INDEX index_name ON table_name(column);

3.1.5 局部索引

1
2
CREATE INDEX index_name ON table_name(conditional_expression);
例:create index flag_index on test(falg) where flag = ‘B’ or flag = ‘C’;

3.1.6 隐式索引

​ 在创建表时由数据库服务器自动创建的索引(主键约束和唯一约束).

3.2 序列

3.2.1 创建方式一：直接在表中指定字段为serial类型

1
2
3
4
5
CREATE TABLE table_name(
	column1 serial,
	column2 varchar
	...
);

3.2.2 创建方式二：创建序列语法

1
2
3
4
CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]    
	[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]    
	[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]    
	[ OWNED BY { table.column | NONE } ];

3.3 视图

3.3.1 创建视图语法

1
2
3
CREATE [TEMP | TEMPORARY] VIEW view_name AS 
SELECT column1, column2.....FROM table_name
WHERE [condition];

3.3.2 视图实例

1
2
3
4
5
实现Oracle中dual虚表:

CREATE VIEW public.dual AS SELECT 'X'::varchar AS dummy;
REVOKE ALL ON public.dual FROM PUBLIC;
GRANT SELECT, REFERENCES ON public.dual TO PUBLIC;

3.4 函数

3.4.1 创建函数语法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE [ OR REPLACE ] FUNCTION  funcname ([IN parameters,OUT parameters]) 
RETURNS  return_value_type AS $body$
DECLARE
declaration;
[...]
BEGIN
 statement   --逻辑块（pl/pgsql代码块）
[ RETURN return_value;]
END;
$body$ LANGUAGE plpgsql;

说明: 
parameters为参数（由 参数名 类型组成，如v_id int）
declaration 函数变量列表（变量名 类型)
return_value_type  返回值类型（无返回值使用void）
$body$ 表示里面引用的内容是函数逻辑块
return_value  返回值变量

3.5 常用函数替换

3.5.1 实现Oracle中instr函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
CREATE OR REPLACE FUNCTION "public"."instr"(varchar, varchar)
  RETURNS "pg_catalog"."int4" AS $BODY$ DECLARE pos integer;
  BEGIN
    pos := instr($1, $2, 1);
    RETURN pos;
  END;
  $BODY$
  LANGUAGE plpgsql IMMUTABLE STRICT
  COST 100;


CREATE OR REPLACE FUNCTION "public"."instr"("string" varchar, "string_to_search" varchar, "beg_index" int4, "occur_index" int4)
  RETURNS "pg_catalog"."int4" AS $BODY$           DECLARE pos integer NOT NULL DEFAULT 0;
    occur_number integer NOT NULL DEFAULT 0;
    temp_str     varchar;
    beg          integer;
    i            integer;
    length       integer;
    ss_length    integer;
  BEGIN
    IF beg_index > 0 THEN
      beg      := beg_index;
      temp_str := substring(string FROM beg_index);
      FOR i IN 1 .. occur_index LOOP
        pos := position(string_to_search IN temp_str);
        IF i = 1 THEN
          beg := beg + pos - 1;
        ELSE
          beg := beg + pos;
        END IF;
        temp_str := substring(string FROM beg + 1);
      END LOOP;
    
      IF pos = 0 THEN
        RETURN 0;
      ELSE
        RETURN beg;
      END IF;
    ELSIF beg_index < 0 THEN
      ss_length := char_length(string_to_search);
      length    := char_length(string);
      beg       := length + beg_index - ss_length + 2;
      WHILE beg > 0 LOOP
        temp_str := substring(string FROM beg FOR ss_length);
        pos      := position(string_to_search IN temp_str);
        IF pos > 0 THEN
          occur_number := occur_number + 1;
          IF occur_number = occur_index THEN
            RETURN beg;
          END IF;
        END IF;
        beg := beg - 1;
      END LOOP;
      RETURN 0;
    ELSE
      RETURN 0;
    END IF;
  END;
  $BODY$
  LANGUAGE plpgsql IMMUTABLE STRICT
  COST 100;

CREATE OR REPLACE FUNCTION "public"."instr"("string" varchar, "string_to_search" varchar, "beg_index" int4)
  RETURNS "pg_catalog"."int4" AS $BODY$        DECLARE pos integer NOT NULL DEFAULT 0;
    temp_str  varchar;
    beg       integer;
    length    integer;
    ss_length integer;
  BEGIN
    IF beg_index > 0 THEN
      temp_str := substring(string FROM beg_index);
      pos      := position(string_to_search IN temp_str);
    
      IF pos = 0 THEN
        RETURN 0;
      ELSE
        RETURN pos + beg_index - 1;
      END IF;
    ELSIF beg_index < 0 THEN
      ss_length := char_length(string_to_search);
      length    := char_length(string);
      beg       := length + beg_index - ss_length + 2;
      WHILE beg > 0 LOOP
        temp_str := substring(string FROM beg FOR ss_length);
        pos      := position(string_to_search IN temp_str);
        IF pos > 0 THEN
          RETURN beg;
        END IF;
        beg := beg - 1;
      END LOOP;
      RETURN 0;
    ELSE
      RETURN 0;
    END IF;
  END;
  $BODY$
  LANGUAGE plpgsql IMMUTABLE STRICT
  COST 100;

3.5.2 实现Oracle中decode函数(目前只能使用varchar等字符型数据类型)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
CREATE OR REPLACE FUNCTION "public"."decode"(VARIADIC "p_decode_list" _text)
  RETURNS "pg_catalog"."text" AS $BODY$
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;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

3.5.3 实现Oracle中trunc函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
CREATE OR REPLACE FUNCTION "public"."trunc"("p_timestamp" timestamptz, "p_formart" varchar='DD'::character varying)
  RETURNS "pg_catalog"."timestamp" AS $BODY$
declare
 v_timestamp timestamp := null;
 v_formart varchar(10) := upper(p_formart);
begin
 /*
 * 函数功能:对日期值进行格式化
 * 参数说明:
 *   P_TIMESTAMP ( 需要格式话的日期值 )
 *   P_FORMART  ( YYYY:年第一天; MM|MONTH|MON|RM:月第一天;  NULL|DD:当日; D:当前周第一天;  ....)
 * 返回格式:YYYY-MM-DD HH24(12):MI:SS (具体值由第二个参数决定)
 */
 if p_timestamp is not null then
  if v_formart in ('YYYY', 'YEAR') then
   -- 当前年的第一天(YYYY-01-01 00:00:00)
   v_timestamp := date_trunc('year', p_timestamp);
  elsif v_formart in ('MONTH', 'MON', 'MM', 'RM') then
   -- 当前月第一天(YYYY-MM-01 00:00:00)
   v_timestamp := date_trunc('month', p_timestamp);
  elsif v_formart in ('DD', 'DAY', 'DY') then
   -- 当天(YYYY-MM-DD 00:00:00)
   v_timestamp := date_trunc('day', p_timestamp);
  elsif v_formart = 'D' then
   -- 当前周第一天[周日为第一天](YYYY-MM-DD 00:00:00)
   v_timestamp := (date_trunc('WEEK', p_timestamp) - interval'1 day');
  elsif v_formart in ('W1', 'W2', 'W3', 'W4', 'W5', 'W6', 'W7') then
   -- 当前周第几天[周日为第一天](YYYY-MM-DD 00:00:00)
   v_timestamp := date_trunc('WEEK', p_timestamp)::date + substr(v_formart, 2, 1)::integer - 2;
  elsif v_formart ~ '^D\+?[0-9]*$' then
   -- 当年第几天(YYYY-MM-DD 00:00:00)
   if substr(v_formart, 2, length(v_formart)-1)::integer between 1 and 366 then
    v_timestamp := date_trunc('year', p_timestamp)::date + substr(v_formart, 2, length(v_formart)-1)::integer - 1;
    if date_trunc('year', v_timestamp)::date > date_trunc('year', p_timestamp)::date then
     v_timestamp := date_trunc('year', v_timestamp)::date - interval'1 day';
    end if;
   else
    raise exception 'U-2001 [%] is not recognize. please enter "D[1~366]"', p_formart;
   end if;
  elsif v_formart in ('HH', 'HH24') then
   v_timestamp := date_trunc('hour', p_timestamp);
  elsif v_formart = 'HH12' then
   v_timestamp := to_char(p_timestamp, 'yyyy-mm-dd hh12:00:00')::timestamp;
  elsif v_formart in ('MINUTE', 'MI') then
   v_timestamp := date_trunc('minute', p_timestamp);
  elsif v_formart = 'CC' then
   v_timestamp := to_date((trunc(date_part('years', p_timestamp)::integer/100)*100+1)::varchar, 'yyyy');
  elsif v_formart in ('HELP', '?') then
   raise exception 'U-2001 please enter formart code in ( YYYY|YEAR, MONTH|MON|MM|RM, DD|DAY|DY, D, W[1~7], D[1~366], HH|HH24, HH12, MINUTE|MI, CC )';
  else
   raise exception 'U-2001 [%] is not recognize. you can try [help]', p_formart;
  end if;
 else
  v_timestamp := p_timestamp;
 end if;
 return v_timestamp;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

3.6 SQL语句替换

4 具体代码实现

4.1 表名，字段名大小写

4.1.1 表名，字段名为大写，必须使用双引号“”，并且“”中表名，字段名必须为大写

1
2
3
4
select user_id, user_name from tb_user;
select "user_id", "user_name" from "tb_user";

select "USER_ID", "USER_NAME" from "TB_USER";

4.1.2 表名及字段名为小写，则不区分大小写

1
2
select user_id, user_name from tb_user_sm;
select USER_ID, USER_NAME from TB_USER_SM;

4.2 自动转型

​ Oracle某些情况下会支持自动转型,但PGSQL不支持

1
2
3
4
-- 数值类型
select * from tb_user_test where sex = 1;
-- 字符串类型
select * from tb_user_test where sex = '1';

4.3 序列(序列名必须使用双单引号’‘)

1
2
3
4
5
-- 错误例子
select nextval(tb_user_user_id_seq) from dual;

-- 正确例子
select nextval('tb_user_user_id_seq') from dual;

4.4 别名

4.4.1 子查询必须使用别名

1
2
3
4
5
-- 错误例子
select * from (select * from tb_user_sm);

-- 正确例子
select * from (select * from tb_user_sm) tus;

4.4.2 单表的DML不能使用别名

1
2
3
4
5
-- 正确例子
update tb_user_sm set user_name = 'test' where user_id = 2;

-- 错误例子
update tb_user_sm tus set tus.user_name = 'test' where tus.user_id = 2;

4.5 分页

1
2
3
4
5
6
-- Oracle:    
select * from (select rownum rowno, t.* from test t where rownum <= 10) t_alias where t_alias.rowno > 0;

-- PGSQL:    
select * from tb_user_sm limit 10 offset 0;
-- limit:每页数据的行数        offset:从第几行数据开始取数据,为0时可省略

4.6 列转行

1
2
3
4
-- Oracle:    
wmsys.wm_concat()
-- PGSQL:    
string_agg()