Oracle迁移postgreSQL小记（结构对象部分）

Oracle迁移postgreSQL小记

hey！EveryBody，好久不见，小崔最近接到了一些Oracle迁移到PostgreSQL的一些问题，总结一下，希望能够帮到大家！！！！

首先我们要先确认的是，ORACLE--->PostgreSQL的差异不在数据，而是在结构，结构包括表结构，数据类型，存储过程，视图，触发器，包等结构化对象上的差距。而在ORACLE当中，这些对象都分明的十分明确，每个对象都有自己的创建方法。但是PostgreSQL里面有些对象划分的不是很明确，所以需要使用其他的去替代，我们后面细说一下。

1.数据类型。

ORACLE和PostgreSQL的数据类型有这很大的差别，在迁移数据的时候，一定要保证PostgreSQL的字段的数据类型是和ORACLE的数据类型是可以兼容的，下面我就举例一些数据类型的替换。

2.uuid

ORACLE里面有sys_guid()函数生成uuid，但是在pg当中没有这个选项。需要用到一个contrib模块，生成一个uuid-ossp，这个可以default一个值出来做uuid。

语法为：

md5(random()::text || clock_timestamp()::text)::uuid

3.分区表

分区表在ORACLE当中是一个十分重要的功能，其可以提高数据库的搜索性能等。PostgreSQL也有这个功能，但是这个功能的语法和ORACLE的不太一样

ORACLE：

create table TEST.PARTITION

(

  nvfid    VARCHAR2(36) default sys_guid(),

  tagid    VARCHAR2(50) not null,

  deviceid VARCHAR2(20) not null,

  tagvalue VARCHAR2(20),

  tagunit  NVARCHAR2(10),

  adddate  DATE

)

partition by range (ADDDATE)

(

  partition P0 values less than (TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

    tablespace TEST

    pctfree 10

    initrans 1

    maxtrans 255

    storage

    (

      initial 64K

      next 1M

      minextents 1

      maxextents unlimited

    ),

  partition SYS_P121 values less than (TO_DATE(' 2017-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

    tablespace TEST

    pctfree 10

    initrans 1

    maxtrans 255

    storage

    (

      initial 64K

      next 1M

      minextents 1

      maxextents unlimited

    ),

  partition SYS_P101 values less than (TO_DATE(' 2017-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

    tablespace TEST

    pctfree 10

    initrans 1

    maxtrans 255

    storage

    (

      initial 64K

      next 1M

      minextents 1

      maxextents unlimited

    )

);

POSTGRESQL:

postgresql要想实现分区表的话目前来说还是稍微有些麻烦，具体的步骤如下：

创建主表：

create table TEST.PARTITION 

(

  nvfid    varchar(36) default md5(random()::text || clock_timestamp()::text)::uuid ,     --这里就是替换sys_guid()函数的功能

  tagid    varchar(50) not null,

  deviceid varchar(20) not null,

  tagvalue varchar(20),

  tagunit  varchar(20),

  adddate  DATE

);

创建分区表，这里的分区表归属于主表TEST.PARTITION

create table p0() inherits(TEST.PARTITION);

create table sys_p121() inherits(TEST.PARTITON);

create table sys_p101() inherits(TEST.PARTITION);

创建分区表的约束

alter table p0 add constraint PARTITION_con  check (adddate <= date '2000-01-01 00:00:00');

alter table sys_p121 add constraint  PARTITION_con check (adddate > date '2000-01-01 00:00:00' and adddate <= date '2017-07-01 00:00:00');

alter table sys_p101 add constraint  PARTITION_con check (adddate <= date '2017-09-01 00:00:00' and adddate > date '2017-07-01 00:00:00');

创建分区索引

create index PARTITION_ind1 on p0(adddate);

create index PARTITION_ind2 on sys_p121(adddate);

create index PARTITION_ind3 on sys_p101(adddate);

这里是创建触发器函数，目的是当insert到主表的时候，能够将对应的数据触发到分区表当中，触发器函数是规定了触发规则。

create or replace function PARTITION_partition_insert()

returns trigger as

$$

begin

if (new.adddate <= date '2000-01-01 00:00:00') then

        insert into p0 values (new.*);

elseif (new.adddate > date '2000-01-01 00:00:00' and new.adddate <= '2017-07-01 00:00:00') then

    insert into sys_p121 values (new.*);

elseif (new.adddate > '2017-07-01 00:00:00' and new.adddate <= '2017-09-01 00:00:00') then

    insert into sys_p101 values (new.*);

else

    raise exception ' Date out of range!!!!!';        

end if ;

return null;

end;

$$

language pluxsql ;

创建触发器

create trigger PARTITION_partition_insert_trigger before insert on TEST.PARTITION

for each row execute procedure PARTITION_partition_insert();

4.同义词

针对同义词来说我目前接触到的是试用替换的方法，postgresql官方给出了一些创建的语法，可能会需要一些插件支持，这个我还不太清楚，欢迎大家来讨论。

所以我这里给出的几个方法。

如果是一个 表/视图/物化视图等，我们可以使用创建相同规则的不同名对象去替代，如创建一个view，让这个view和这个表相同。

create view view_name as select * from  table_name ;

这样在使用的时候我们就达到了同义词的目的。

如果是函数之类的想要创建同义词，就使用嵌套的函数进行访问

原函数：

create or replace function function_name(int) returns int as $$

。。。。。。。这里假如有各种规则。。。。。

$$ language plpgsql ;

同义词函数：

create or replace function new_function_name(int) returns int as $$

select function_name($1);

$$ language plpgsql;

这样在调用的时候使用新的函数即可。

如果专门为一些表或者方法等创建了一个schema专门存放这些同义词。那么可以整体调整search_path，从而方面之后的调用。

5.存储过程

针对存储过程，在pg里面可以使用function去替换。我们看个例子：

ORACLE:

CREATE OR REPLACE PROCEDURE TEST.PROCTEST AS

BEGIN

  --  天：

  --ROUND(TO_NUMBER(END_DATE - START_DATE))

  --小时：

  --ROUND(TO_NUMBER(END_DATE - START_DATE) * 24)

  --分钟：

  --ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60)

  --秒：

  --ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60)

  --毫秒：

  --ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60 * 1000)

  update tbdevice t

     set t.onlineremark = 0

   where ROUND(TO_NUMBER(sysdate - t.lastdate) * 24* 60) > t.judegetime*60;

  update tbdevice t

     set t.onlineremark = 1

   where ROUND(TO_NUMBER(sysdate - t.lastdate) * 24* 60) < t.judegetime* 60;

     update tbdevice t

     set t.onlineremark = 2

     where t.devicetypeid=0 or t.devicecode='0';

EXCEPTION

  WHEN OTHERS THEN

    utils.handleerror(SQLCODE, SQLERRM);

END;

/

POSTGRESQL:

    CREATE OR REPLACE function TEST.PROCTEST() returns integer as $$

    BEGIN

      update tbdevice t

         set t.onlineremark = 0

       where ROUND(TO_NUMBER(localtimestamp - t.lastdate) * 24* 60) > t.judegetime*60;

      update tbdevice t

         set t.onlineremark = 1

       where ROUND(TO_NUMBER(localtimestamp - t.lastdate) * 24* 60) < t.judegetime* 60;

         update tbdevice t

         set t.onlineremark = 2

         where t.devicetypeid=0 or t.devicecode='0';

    EXCEPTION

      WHEN OTHERS THEN

        raise exception '(%)(%)', SQLCODE,SQLERRM;

    END;

    $$ language pluxsql;

function要注意一定要有返回值，而且一定注意在异常抛出那块的格式有所变化。

并且在调用postgres的函数的时候，使用的方法为select test.proctest();

6.序列

针对序列没啥说的，语法几乎相同，但是maxvalue没有ORACLE存的多。

create sequence TEST_SEQ

minvalue 1

maxvalue 9999999999999999

start with 425

increment by 1

cache 20;

只是在使用的时候和ORACLE有所区别

select nextval(TEST_SEQ);

select currval(TEST_SEQ);

select lastval(TEST_SEQ);

7.触发器

触发器我们在上面的分区表有过一点的介绍，postgresql的触发器创建和ORACLE的有点差异，postgresql要先创建触发器函数，这个函数规定了这个触发器的触发规则，之后再创建触发器函数。我们看下：

ORACLE:

create or replace trigger ZGZY.SY_APPROVAL_ID_TRIGGER

  before insert on sy_approval  

  for each row

declare

  -- local variables here

begin

      if :new.approvalid is null then

    select SY_APPROVAL_ID_SEQ.nextval into :new.approvalid from sys.dual;

  END IF;

end SY_APPROVAL_ID_TRIGGER;

/

POSTGRES:

创建序列

create sequence SY_APPROVAL_ID_SEQ minvalue 1 maxvalue 9999999999;

创建表

create table SY_APPROVAL(id int);

创建触发器函数

create or replace function PROC_SY_APPROVAL_ID_TRIGGER()

returns trigger

as

$$

   begin

     if new.ID is null

     then

new.ID=(select nextval('SY_APPROVAL_ID_seq'));

     return new;

   else

     return new;

     end if;

   end;

$$ language pluxsql;

创建触发器

create trigger SY_APPROVAL_ID_TRIGGER before insert on SY_APPROVAL for each row  execute procedure PROC_SY_APPROVAL_ID_TRIGGER();

8.package包

postgresql没有办法创建包这种特殊的对象，但是可以使用其他的方法进行替代，比如专门创建一个schema，然后将函数（存储过程）存放到这个schema下，使用的时候就调用这个schema下的函数。

9.其他

下面我说一些零碎的点：

ORACLE当中有dual，但是postgresql没有，查询使用select 1+2 这样既可。

ORACLE有些内部函数包，如DBMS_OUTPUT,DBMS_SQL,UTIL_FILE,UTIL_MAIL，这种函数只能创建，否则能用在postgresql里。

针对当前系统时间的选取，ORACLE可以使用sysdata()函数；postgresql使用localtimestamp或者now()。

创建view的时候，ORACLE可以加force强制创建view，但是postgresql没有这个功能。必须得有基表才可以。

 上面就是ORACLE结构对象迁移到postgresql的步骤，欢迎大家私信讨论（小崔不会开文章留言啊！！！！）。

THAT'S ALL 

BY CUI PEACE！！！