根据excel表格生成建表语句及注释

应用场景：有时需要根据第三方数据索要方提供的表结构，提供其要求表格的数据。提供时只给出excel表。如：

 

解决思路：

1、excel的vb编码，导出。

2、百度了一下，提供了另一种思路，写存储过程来实现。

新建表把表结构说明都写入表中，写过程，按每个表分组，变量表的字段描述，做字符拼接。

存储到clob字段中。

 

自己的实现例子：

建表：

create table wanda_midtable_col(
   xh number,
   tablename varchar2(20),
   colname varchar2(20),
   colcomment varchar2(500),
   coltype varchar2(100),
   isnull varchar2(10),
   isprimarykey varchar2(10),
   codetype varchar(500),
   coldesc varchar2(500),
   writedesc varchar2(500),
   bz varchar2(500)
);
create table wanda_OUTString
(
Tname        varchar2(50),
OUTString    clob, --建表语句
OUTSTRING2   clob, --注释语句
createddate  date
);

 编写过程：

create or replace procedure pro_exltosql_mintables is
  OUTString       varchar2(30000);
  OUTString2       varchar2(30000);
  v_tabledesc     varchar2(500);
  v_index         number(10);
  cursor c_tables is
    select distinct a.tablename from wanda_midtable_col a;
  cursor c_tcolms(pi_tname varchar2) is
    select * from wanda_midtable_col a where a.tablename=pi_tname order by a.xh;
begin
  begin
    for v_tname in c_tables loop
      OUTString := '';
      outstring2 := '';
      v_tabledesc := '';
      for v_tcolms in c_tcolms(v_tname.tablename) loop
        OUTString :=OUTString||'COMMENT ON COLUMN '||v_tcolms.tablename||'.'||v_tcolms.colname||' is '''||v_tcolms.colcomment||''';'||chr(10);
        select outstring2||' '||v_tcolms.colname||'   '||v_tcolms.coltype||' '||decode(upper(v_tcolms.isnull),'N','NOT NULL','') ||','||chr(10)
          into outstring2
          from dual;
        if upper(v_tcolms.isprimarykey) = 'Y' then
          null;
        end if;
      end loop;
      --获取中间表的表说明
      select a.table_desc
        into v_tabledesc
        from wanda_sjqx_table a
       where a.table_name = v_tname.tablename;
      OUTString := 'COMMENT ON TABLE '||v_tname.tablename||' is '''||v_tabledesc||''';'||chr(10)||OUTString;
      outstring2:='create table '||v_tname.tablename||''||chr(10)||'('||outstring2||');';
      --create table去掉最后的逗号
      --最后一个逗号的下标
      select instr(outstring2,',',-1,1) into v_index from dual;
      outstring2:= substr(outstring2,1,v_index-1)||substr(outstring2,v_index+1,length(outstring2)-v_index);
      insert into wanda_OUTString(Tname,OUTString,OUTSTRING2,createddate)
      values(v_tname.tablename,OUTString,OUTSTRING2,sysdate);
      commit;
    end loop;
  exception
    when others then
      null;
  end;
end pro_exltosql_mintables;

 

得到：

COMMENT ON TABLE IC08 is '历史档案审核信息';
COMMENT ON COLUMN IC08.AAZ261 is '历史档案审核ID';
COMMENT ON COLUMN IC08.AAE140 is '险种类型';
COMMENT ON COLUMN IC08.AAC001 is '人员ID';
COMMENT ON COLUMN IC08.AAB001 is '单位ID';
COMMENT ON COLUMN IC08.AAC058 is '证件类型';
COMMENT ON COLUMN IC08.AAC147 is '证件号码';
COMMENT ON COLUMN IC08.AAC003 is '姓名';
COMMENT ON COLUMN IC08.AAC004 is '性别';
COMMENT ON COLUMN IC08.AAC006 is '出生日期';
....

 

create table IC08
( AAZ261   NUMBER(16) NOT NULL,
 AAE140   VARCHAR2(6) NOT NULL,
 AAC001   NUMBER(20) NOT NULL,
 AAB001   NUMBER(20) ,
 AAC058   VARCHAR2(3) NOT NULL,
 AAC147   VARCHAR2(20) NOT NULL,
 AAC003   VARCHAR2(50) NOT NULL,
 AAC004   VARCHAR2(1) NOT NULL,
 AAC006   DATE NOT NULL,
 AAC005   VARCHAR2(3) ,
 AAC024   VARCHAR2(3) ,
....