MySQL数据字典&Oracle存储过程迁移数据

1、MySQL生成数据字典

select
ordinal_position as '序列',
column_name as '英文名',
column_comment as '中文名',
column_type as '数据类型',
if(is_nullable = 'NO','Y','N') as '必输',
'' as '备注'
from
information_schema.`columns`
where table_schema = 'XXXXX' and table_name = 'YYYYY';

2、Oracle存储过程迁移数据到临时表

create or replace procedure P_demo is
counts number;
sqlmsg varchar2(256);
CURSOR temp_cursor_name is select * from db_name.old_table_name c where c.create_date >= date'2020-8-1' and c.create_date < date'2020-9-30' order by c.create_time asc;
begin
  for t in temp_cursor_name loop
      sqlmsg:= 'select count(1) from db_name.temp_table_name p where p.A ='''|| t.A||''';
      execute immediate sqlmsg into counts;
      commit;
      if counts < 1 then
          insert into db_name.temp_table_name (A,B,C) values (t.A,t.B,t.C);
      else
          update db_name.temp_table_name s 
        set s.B = NVL(t.B,s.B),s.C=NVL(t.C,s.C)
        where s.A = t.A;
      end if;
  commit;
  end loop;
end;