-- Created on 2019-09-26 by USER
declare
  -- Local variables here
  v_pre_space varchar2(10);

  v_tname varchar2(100);
  v_sql varchar2(1000);
  v_int_type varchar2(10);
  v_comment varchar2(1000);
  v_pk varchar2(1000);

begin
  v_pre_space := '  ';

  v_tname := 'ACC_AGENCY_ACCOUNT';
  -- 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;