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

最新推荐文章于 2024-06-09 21:29:22 发布

qq_32593713

最新推荐文章于 2024-06-09 21:29:22 发布

阅读量5.8k

点赞数 1

文章标签： oracle与postgre之间的迁移 orale中表、字段大小写转换 postgre中表、字段大小写转换 oracle中获取表、字段 postgre中获取表、字段

本文链接：https://blog.csdn.net/qq_32593713/article/details/85235448

版权

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函数

qq_32593713

关注

1
点赞
踩
26

收藏

觉得还不错? 一键收藏
0
评论
oracle数据库与postgre数据库之间的互相迁移

oracle与postgre之间互相迁移之前要明白，postgreSQL中默认使用小写，oracleSQL中默认大写。迁移分成3个步骤：数据及结构迁移、迁移之后的类型及长度变化、不兼容的函数替换。1.数据及结构迁移 1.1数据大小写同步 oracle中使用存储过程将表名和字段名称大写转成小写：create or replace procedure to_lower is...
复制链接

扫一扫