transfer oracle table's define to mysql create table SQL

convert_oracle_to_mysql.sql

-- Created on 2019-09-26 by USER 
declare 
  -- Local variables here
  v_pre_space varchar2(10);
  
  v_tname varchar2(100);
  v_sql varchar2(4000);
  v_int_type varchar2(10);
  v_comment varchar2(1000);
  v_pk varchar2(1000);
  
  v_tnames varchar2(10000);
  
begin
  v_pre_space := '  ';
  
  v_tnames := 'adm_info,inf_games';
  
  for all_tab in (select * from table(dbtool.strsplit(v_tnames))) loop
  
    v_tname := upper(all_tab.COLUMN_VALUE);
    -- Test statements here
    v_sql := 'DROP TABLE IF EXISTS ' || v_tname || ';' || chr(13);
    v_sql := v_sql || 'create table ' || v_tname || ' (' || chr(13);
    
    -- 处理列、注释和是否空值
    for tab in (select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, NULLABLE, CHAR_LENGTH from user_tab_cols where TABLE_NAME = v_tname order by column_id) loop
      v_sql := v_sql || v_pre_space;
      v_sql := v_sql || tab.column_name || ' ';
      
      -- 字符
      if tab.DATA_TYPE in ('VARCHAR2', 'NVARCHAR2') then
        v_sql := v_sql || 'VARCHAR' || '(' || tab.CHAR_LENGTH || ') ';
      end if;
      if tab.DATA_TYPE = 'CHAR' then
        v_sql := v_sql || 'CHAR' || '(' || tab.CHAR_LENGTH || ') ';
      end if;
          
      -- 数字
      if tab.DATA_TYPE = 'DATE' then
        v_sql := v_sql || 'datetime' || ' ';
      end if;
      if tab.DATA_TYPE = 'NUMBER' then
        if tab.DATA_PRECISION <= 2 then
          v_int_type := 'TINYINT';
        elsif tab.DATA_PRECISION <= 4 then
          v_int_type := 'SMALLINT';
        elsif tab.DATA_PRECISION <= 6 then
          v_int_type := 'MEDIUMINT';
        elsif tab.DATA_PRECISION <= 9 then
          v_int_type := 'INT';
        else
          v_int_type := 'BIGINT';
        end if;
        
        v_sql := v_sql || v_int_type || ' ';
      end if;
      
      -- NULL value
      if tab.NULLABLE = 'Y' then
        v_sql := v_sql || 'not null ';
      end if;
      
      -- comment
      select COMMENTS into v_comment 
        from user_col_comments 
       where TABLE_NAME = v_tname
         and COLUMN_NAME = tab.column_name;
      if v_comment is not null then
        v_sql := v_sql || 'comment ''' || v_comment || ''' ';
      end if;
      
      v_sql := v_sql || ',' || chr(13);
    end loop;
    
    -- 处理主键
    select wm_concat(COLUMN_NAME) 
      into v_pk
      from user_cons_columns 
     where table_name = v_tname 
       and CONSTRAINT_NAME like 'PK%' 
     order by POSITION;
    v_sql := v_sql || v_pre_space;
    v_sql := v_sql || 'PRIMARY KEY (' || v_pk || ')' || chr(13);
    
    -- 收尾
    v_sql := v_sql || ');';
    
    dbtool.p(v_sql);
  end loop;
end;