oracle数据库与postgre数据库之间的互相迁移

oracle与postgre之间互相迁移之前要明白,postgreSQL中默认使用小写,oracleSQL中默认大写。迁移分成3个步骤:数据及结构迁移、迁移之后的类型及长度变化、不兼容的函数替换。

1.数据及结构迁移

  1.1数据大小写同步

    oracle中使用存储过程将表名和字段名称大写转成小写:

create or replace procedure to_lower is

begin   
   /*字段转小写*/
  for t in (select table_name tn from user_tables) loop
      begin
         for c in (select column_name cn from user_tab_columns where table_name=t.tn) loop
             begin
                execute immediate 'alter table '||t.tn||' rename column '||c.cn||' to "'||lower(c.cn)||'"';
             exception
                when others then
                   dbms_output.put_line(t.tn||'.'||c.cn||'已经存在');
             end;
         end loop;
      end;
  end loop; 
  /*表名转小写*/
  for c in (select table_name tn from user_tables where table_name <> lower(table_name)) loop
       begin
          execute immediate 'alter table '||c.tn||' rename to "'||lower(c.tn)||'"';
       exception
          when others then
             dbms_output.put_line(c.tn||'已存在');
       end;
   end loop; 
end;

oracle中使用存储过程将表名和字段名称小写转成大写:

create or replace procedure to_upper is
begin
  /*表名转小写*/
  for t in (select table_name tn from user_tables) loop

      begin

         for c in (select column_name cn from user_tab_columns where table_name=t.tn) loop

             begin

                execute immediate 'alter table "'||t.tn||'" rename column "'||c.cn||'" to '||c.cn;

             exception

                when others then

                   dbms_output.put_line(t.tn||'.'||c.cn||'已经存在');

             end;

         end loop;

      end;

  end loop; 
  
  /*表名转大写*/
  for c in (select table_name tn from user_tables where table_name <> upper(table_name)) loop

       begin

          execute immediate 'alter table "'||c.tn||'" rename to '||c.tn;

       exception

          when others then

             dbms_output.put_line(c.tn||'已存在');

       end;

   end loop; 
end to_upper;

postgre中使用函数将表名和字段名称大写转成小写:

CREATE OR REPLACE FUNCTION "public"."up_lower"()
  RETURNS "pg_catalog"."varchar" AS $BODY$

	 declare 

		t_name varchar;

		t_field record;

		sql varchar;

	BEGIN

		--  获取该数据库下public目录下的所有表名

    for t_name in select upper(tablename) from pg_tables where schemaname='public' loop

			--  根据表名获取该表的字段与字段类型

			sql := 'SELECT format_type(a.atttypid,a.atttypmod) as type,upper(a.attname) as name FROM pg_class as c,pg_attribute as a where c.relname = '''|| t_name ||''' and a.attrelid = c.oid and a.attnum>0';

			for t_field in execute sql loop
		
					--  修改更新字段

					sql := 'alter table "'|| t_name ||E'" rename column \"'|| t_field.name ||E'\" to '|| t_field.name;

					execute sql;

			end loop;
				--  修改表名

			sql := 'alter table "'|| t_name ||'" rename to '|| t_name;
			execute sql;

		end loop;

		return '完成';

END

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

postgre中使用函数将表名和字段名称小写转成大写:

CREATE OR REPLACE FUNCTION "public"."up_upper"()
  RETURNS "pg_catalog"."varchar" AS $BODY$

	 declare 

		t_name varchar;

		t_field record;

		sql varchar;

	BEGIN

		--  获取该数据库下public目录下的所有表名 

    for t_name in select upper(tablename) from pg_tables where schemaname='public' loop

			--  根据表名获取该表的字段与字段类型

			sql := 'SELECT format_type(a.atttypid,a.atttypmod) as type,upper(a.attname) as name FROM pg_class as c,pg_attribute as a where c.relname = lower('''|| t_name ||''') and a.attrelid = c.oid and a.attnum>0';

			for t_field in execute sql loop
		
					--  修改更新字段

					sql := 'alter table '|| t_name ||' rename column '|| t_field.name ||E' to \"'|| t_field.name ||E'\"';
					execute sql;
			end loop;
				--  修改表名
			sql := 'alter table '|| t_name ||' rename to "'|| t_name||'"';
			execute sql;
		end loop;

		return '完成';

END

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

不管哪边迁移到哪边,都需要先字段及表名大小写转化一下,如果有变化可以根据上面的sql去修改,如果表和字段过多可将字段与表名分开使用。然后使用navicat工具将两边数据库的数据表复制到另外一方,navicat连接oracle数据库注意设置:

2.迁移之后的类型及长度变化

oracle迁移到postgre后,如果oracle中类型是NUMBER,在postgre中类型为numeric(1000,53),位数特别长。需要修改其它类型可参照下面,下面函数在postgre中使用:

CREATE OR REPLACE FUNCTION "public"."up_field"()
  RETURNS "pg_catalog"."varchar" AS $BODY$

	 declare 

		t_name varchar;

		t_field record;

		sql varchar;

	BEGIN

		--  获取该数据库public目录下的所有表名

    for t_name in select tablename from pg_tables where schemaname='public' loop

			--  根据表名获取该表的字段与字段类型

			sql := 'SELECT format_type(a.atttypid,a.atttypmod) as type,a.attname as name FROM pg_class as c,pg_attribute as a where c.relname = '''|| t_name ||''' and a.attrelid = c.oid and a.attnum>0';

			for t_field in execute sql loop

				if t_field.type='numeric(1000,53)' then

					--  更新类型numeric(1000,53)的为numeric(1000,0)

					sql := 'alter table '|| t_name ||' alter column '|| t_field.name ||' type numeric(1000,0)';

					execute sql;

				end if;

			end loop;

		end loop;

		return '完成';

END

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

如果需要对比oracle数据库与postgre数据迁移之后的数据结构,可用下面分别对应的sql执行,然后使用bcompare对比工具对比差异。

oracle查询表及字段,下面需要注意下T.database_name='你的数据库名称':

SELECT
  *
FROM
  (
    SELECT
      UB.tablespace_name AS database_name,
      UTC.table_name AS table_name,
      UTC.column_name AS column_name,
      UTC.data_type AS column_type,
      utc.data_length AS data_length,
      ucc.comments AS column_comment
    FROM
      user_tables ub
    LEFT JOIN user_tab_columns utc ON ub.table_name = UTC.table_name
    LEFT JOIN user_col_comments ucc ON utc.column_name = ucc.column_name
    AND utc.table_name = ucc.table_name
  ) T
LEFT JOIN (
  SELECT
    UCC.table_name AS table_name,
    ucc.column_name AS column_name,
    wm_concat (UC.constraint_type) AS constraint_type
  FROM
    user_cons_columns ucc
  LEFT JOIN user_constraints uc ON UCC.constraint_name = UC.constraint_name
  GROUP BY
    UCC.table_name,
    ucc.column_name
) b ON T.table_name = b.TABLE_NAME
AND T.column_name = b.column_name where T.database_name='你的数据库名称' order by  T.table_name,T.column_name

oracle表记录数:

select t.table_name,t.num_rows from user_tables t order by table_name

postgre查询表及字段:

select
    c.relname as table,a.attname as name,format_type(a.atttypid,a.atttypmod) as type
from
    pg_class as c,pg_attribute as a
where
    c.relname in(
	    select tablename from pg_tables where schemaname='public'
    ) 
    and a.attrelid = c.oid
    and a.attnum>0
order by c.relname,a.attname

postgre表记录数统计

select relname as table_name, reltuples as row_counts from pg_class where relnamespace = (select oid from pg_namespace where nspname='public')

3.不兼容的函数替换

迁移之后一些函数到另外一个数据中可能就不兼容了,如oracle中的获取时间 select sysdate from dual与postgre中now函数

 

  • 1
    点赞
  • 26
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值