Oracle表结构转MySQL

一、原理

在plsql中编写存储过程，查询需转换的表结构信息（列名、列注释、列类型、类型长度等），拼接为MySQL语法。目前是简单转换，只做抛砖引玉，有需求可以自行扩展。

二、实现

declare
  max_col_num int(10);--列数
  col_name    varchar2(4000);--列名
  col_type    varchar2(4000);--列类型
  col_length  varchar2(4000);--类型长度
  is_null     varchar2(4000);--是否可为空
  col_comment varchar2(4000);--列注释
  tab_comment varchar2(4000);--表注释
begin
  for i in (select t.OBJECT_NAME
              from all_objects t
             where t.OBJECT_TYPE = 'TABLE'
              and rownum = 1
              --此处可添加筛选表的条件
               ) loop
    select max(to_number(a.COLUMN_ID))
      into max_col_num
      from dba_tab_columns A
     where TABLE_NAME = i.object_name;
    DBMS_OUTPUT.PUT_LINE('create table ' || i.object_name || ' (');
    for j in (select *
                from dba_tab_columns A
               where TABLE_NAME = i.object_name
               order by to_number(a.COLUMN_ID)) loop
      col_name   := j.column_name || ' ';
      --此处为列类型、类型长度、默认值类型的转换（case when中可扩展）
      col_type := case
                    when j.data_type = 'VARCHAR2' then
                     'VARCHAR' || '(' || case when to_number(j.data_length) >= 255 then '255' else j.data_length end || ')' ||
                     ' default '''' '
                    when j.data_type = 'NUMBER' then
                     'int'
                    when j.data_type = 'DATE' then
                     'datetime'
                    when j.data_type like 'TIMESTAMP%' then
                     'timestamp'
                    when j.data_type = 'CLOB' then
                     'text'
                    else
                     j.data_type
                  end || ' ';
      is_null    := case j.nullable
                      when 'Y' then
                       'null'
                      else
                       'not null'
                    end || ' ';
      --查询列注释
      select t.comments
        into col_comment
        from dba_col_comments t
       where t.table_name = i.object_name
         and t.column_name = j.column_name;
    
      --非末尾用“,”拼接；末尾用“)”结尾
      if j.column_id = max_col_num then
        col_comment := 'comment ' || '''' || col_comment || '''' || ')';
      else
        col_comment := 'comment ' || '''' || col_comment || '''' || ',';
      end if;

      DBMS_OUTPUT.PUT_LINE('   ' || col_name || col_type || is_null ||
                           col_comment);
    end loop;
    --查询表注释
    select t.comments
      into tab_comment
      from dba_tab_comments t
     where t.table_type = 'TABLE'
       and t.table_name = i.object_name;
    DBMS_OUTPUT.PUT_LINE('comment ' || '''' || tab_comment || '''' || ';');
  
  end loop;
end;

三、结果