ORACLE同步表结构到mysql8.0建表结构 转化自定义函数，高效快捷

ORACEL和mysql的数据类型存在较大差异（如Oracle的number和mysql的decimal（）
导致我们在数据迁移的时候有不小的麻烦
即使我们使用navicat的同步工具，同步表结构和数据，自动生成的字段类型也并不都是我们想用的，如Oracle的interger 同步过来到mysql变成了decimal（65,30），显然不是我们想要的；

于是乎，参考网上的sql写了一个自定义函数，
调用方式为 select fnc_table_to_mysql（‘’用户名’，‘表名’）from dual;
就可以得到一段创建mysql表结构的SQL。

create or replace function fnc_table_to_mysql
( i_owner                       in string,
  i_table_name                  in STRING
)
/*
  已知问题：
  1.不支持分区
  2.不支持函数索引，位图索引等特殊索引定义
  3.不支持自定义数据类型，不支持ROWID,RAW等特殊数据类型
  4.不支持外键
  5.不支持自定义约束
  6.不支持与空间、事务相关属性
  7.TIMESTAMP转换成datetime，需注意精度
  8.超大NUMBER直接转换为bigint，需注意精度
  */
 return clob is
  Result         clob;
  cnt            number;
  data_type      varchar2(128);
  column_str     varchar2(4000);
  -- pk_str         varchar2(4000);
  table_comments varchar2(4000);
  is_pk_column   number := 0;
begin
  select count(*)
  into cnt
  from all_tables
   where owner = UPPER(i_owner)
   and table_name = UPPER(i_table_name);
  if (cnt = 0) then
  RAISE_APPLICATION_ERROR(-20000,'can''t found table,please check input!');
  else
  Result := 'CREATE TABLE `' || lower(i_table_name) || '`( 
  '; 
  --column
  for c in (select a.column_name,
           a.data_type,
           a.data_length,
           a.data_precision,
           a.data_scale,
           a.nullable,
           a.data_default,
           b.COMMENTS
        from all_tab_cols a, all_col_comments b
         where a.owner = UPPER(i_owner)
         and a.table_name = UPPER(i_table_name)
         and a.HIDDEN_COLUMN = 'NO'
         and a.owner = b.OWNER
         and a.TABLE_NAME = b.TABLE_NAME
         and a.COLUMN_NAME = b.COLUMN_NAME
         order by a.column_id) loop
    if (c.data_type = 'VARCHAR2' or c.data_type = 'NVARCHAR2') then
    data_type := 'varchar(' || c.data_length || ')';
    elsif (c.data_type = 'CHAR' or c.data_type = 'NCHAR') then
    data_type := 'varchar(' || c.data_length || ')';
    elsif (c.data_type = 'NUMBER') then
    if (c.column_name like '%ID' and c.data_scale is null) then
      data_type := 'bigint';
    elsif (c.data_precision<3 and c.data_scale = 0) then
      data_type := 'tinyint';
    elsif (c.data_precision<5 and c.data_scale = 0) then
      data_type := 'smallint';
    elsif (c.data_precision<10 and c.data_scale = 0) then
      data_type := 'int';
    elsif (c.data_precision is not null and c.data_scale = 0) then
      data_type := 'bigint';
    elsif (c.data_precision is not null and c.data_scale is not null) then
      data_type := 'decimal(' || c.data_precision || ',' ||
             c.data_scale || ')';
    else
      data_type := 'decimal(32,4)';
    end if;
    elsif (c.data_type = 'DATE' ) then
    data_type := 'date';
     elsif (c.data_type = 'DATETIME' OR c.data_type like 'TIMESTAMP%') then
    data_type := 'datetime';
    elsif (c.data_type = 'CLOB' or c.data_type = 'NCLOB' or
      c.data_type = 'LONG') then
    data_type := 'text';
    elsif (c.data_type = 'BLOB' or c.data_type = 'LONG RAW') then
    data_type := 'blob';
    elsif (c.data_type = 'BINARY_FLOAT') then
    data_type := 'float';
    elsif (c.data_type = 'BINARY_DOUBLE') then
    data_type := 'double';
    else
    data_type := c.data_type;
    end if;
    column_str := '  `' || lower(c.column_name) || '` ' || data_type;
    if (c.column_name like '%ID' and
     (c.data_scale is null or c.data_scale = 0)) then
    select count(*)
      into is_pk_column
      from all_constraints a, all_cons_columns b
     where a.owner = UPPER(i_owner)
       and a.table_name = UPPER(i_table_name)
       and a.constraint_type = 'P'
       and a.OWNER = b.OWNER
       and a.TABLE_NAME = b.TABLE_NAME
       and a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
       and b.COLUMN_NAME = c.column_name;
    if is_pk_column > 0 then
      column_str := column_str ;
    end if;
    end if;
    if c.nullable = 'NO' then
    column_str := column_str || ' NOT NULL';
    end if;
    if (trim(c.data_default) is not null) then
    column_str := column_str || ' DEFAULT ' ||
            trim(replace(replace(c.data_default, chr(13), ''),
                   chr(10),
                   ''));
    end if;
    if c.comments is not null then
    --column_str := column_str || ' COMMENT ''' || c.comments || '''';
    column_str := column_str || ' COMMENT ''' || c.comments || '''';
    end if;
    Result := Result || chr(10) || column_str || ',';
  end loop;
  --pk
  for c in (select a.constraint_name, wm_concat(a.column_name) pk_columns
        from (select a.CONSTRAINT_NAME,
               '`' || b.COLUMN_NAME || '`' column_name
            from all_constraints a, all_cons_columns b
             where a.owner = UPPER(i_owner)
             and a.table_name = UPPER(i_table_name)
             and a.constraint_type = 'P'
             and a.OWNER = b.OWNER
             and a.TABLE_NAME = b.TABLE_NAME
             and a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
             order by b.POSITION) a
         group by a.constraint_name) loop
    Result := Result || chr(10) || '  PRIMARY KEY (' ||
        lower(c.pk_columns) || '),';
  end loop;
  --unique
  for c in (select a.constraint_name, wm_concat(a.column_name) uk_columns
        from (select a.CONSTRAINT_NAME,
               '`' || b.COLUMN_NAME || '`' column_name
            from all_constraints a, all_cons_columns b
             where a.owner = UPPER(i_owner)
             and a.table_name = UPPER(i_table_name)
             and a.constraint_type = 'U'
             and a.OWNER = b.OWNER
             and a.TABLE_NAME = b.TABLE_NAME
             and a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
             order by b.POSITION) a
         group by a.constraint_name) loop
    Result := Result || chr(10) || '  UNIQUE KEY `' ||
        lower(c.constraint_name) || '`(' || lower(c.uk_columns) || '),';
  end loop;
  -- index
  for c in (select a.index_name, wm_concat(a.column_name) ind_columns
        from (select a.index_name,
               '`' || a.COLUMN_NAME || '`' column_name
            from all_ind_columns a
             where a.table_owner = UPPER(i_owner)
             and a.TABLE_NAME = UPPER(i_table_name)
             and not exists
             (select index_name
                from all_constraints b
                 where a.TABLE_OWNER = b.owner
                 and a.TABLE_NAME = b.TABLE_NAME
                 and a.INDEX_NAME = b.INDEX_NAME)
             order by a.COLUMN_POSITION) a
         group by a.index_name) loop
    Result := Result || chr(10) || '  KEY `' || lower(c.index_name) || '`(' ||
        lower(c.ind_columns) || '),';
  end loop;
  Result := substr(Result, 1, length(result) - 1) || chr(10) || ')';
  --table comments
  select max(a.COMMENTS)
    into table_comments
    from all_tab_comments a
   where owner = UPPER(i_owner)
     and table_name = UPPER(i_table_name);
  if (table_comments is not null) then
    Result := Result || 'COMMENT=''' || table_comments || '''';
  end if;
  Result := Result || ';';
  end if;
  return(Result);
end fnc_table_to_mysql;

参考于：https://blog.csdn.net/weixin_42531396/article/details/113123942?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522165207756116782246448404%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=165207756116782246448404&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2_allfirst_rank_ecpm_v1~rank_v31_ecpm-2-113123942-null-null.142^v9control,157^v4control&utm_term=mysql%E5%BB%BA%E8%A1%A8%E7%9A%84%E6%97%B6%E5%80%99%E5%8F%AF%E4%BB%A5%E7%94%A8trim%EF%BC%88%EF%BC%89%E5%90%97&spm=1018.2226.3001.4187