在日常开发过程中，有时会遇到上游只提供给我们表结构文档，并不能直接提供给我们建表语句，这样建表过程所耗费的时间就会大大增加，如果只是几张表，那随手写个create table可能就能完成任务，可是如果有几十张、几百张甚至上千张表的话，如果再去手动建表的话，那显然不太现实，一般情况下，这种可能都是通过建模工具进行建表，将表结构的相关文档导入建模工具，最后逐个生成建表语句，在近期的项目中，我手上有个建表的任务，我就想能不能通过写一个存储过程，将表结构文档生成建表语句，这样既能实现需求，又能提升一下自己的SQL技能，说干就干~
目录
1.建表
我的想法是需要建三张表：
TABLE_INFO为Excel中的源数据，TABLE_INFO_TEMP为过程临时表，CREATE_TABLE为最终生成的建表语句和注释语句
create table TABLE_INFO
(
id number,
db_type VARCHAR2(10) default 'oracle',
tab_name VARCHAR2(100),
col_name VARCHAR2(1000),
col_type VARCHAR2(1000),
col_long VARCHAR2(1000),
col_comment VARCHAR2(4000)
);
comment on column TABLE_INFO.id
is '数据序号';
comment on column TABLE_INFO.db_type
is '数据库类型';
comment on column TABLE_INFO.tab_name
is '表名';
comment on column TABLE_INFO.col_name
is '字段名';
comment on column TABLE_INFO.col_type
is '字段类型';
comment on column TABLE_INFO.col_long
is '字段长度';
comment on column TABLE_INFO.col_comment
is '字段注释';
create table TABLE_INFO_TEMP
(
id number,
db_type VARCHAR2(10) default 'oracle',
tab_name_full VARCHAR2(100),
tab_name_eng VARCHAR2(100),
tab_name_cn VARCHAR2(100),
col_name VARCHAR2(1000),
col_type VARCHAR2(1000),
col_long VARCHAR2(1000),
col_comment VARCHAR2(4000)
);
comment on column TABLE_INFO_TEMP.id
is '源表数据序号';
comment on column TABLE_INFO_TEMP.db_type
is '数据库类型';
comment on column TABLE_INFO_TEMP.tab_name_full
is '源表表名如：emp员工表，员工表emp等，插入后不变，以便后期与源表查询对应';
comment on column TABLE_INFO_TEMP.tab_name_eng
is '英文表名';
comment on column TABLE_INFO_TEMP.tab_name_cn
is '中文表名';
comment on column TABLE_INFO_TEMP.col_name
is '字段名';
comment on column TABLE_INFO_TEMP.col_type
is '字段类型';
comment on column TABLE_INFO_TEMP.col_long
is '字段长度';
comment on column TABLE_INFO_TEMP.col_comment
is '字段注释';
create table CREATE_TABLE
(
tab_name VARCHAR2(100),
create_sql VARCHAR2(2000),
tab_comment clob,
create_date DATE default sysdate
);
comment on column CREATE_TABLE.tab_name
is '表名';
comment on column CREATE_TABLE.create_sql
is '建表语句';
comment on column CREATE_TABLE.tab_comment
is '中文表名及字段注释';
comment on column CREATE_TABLE.create_date
is '创建时间';
2.准备数据
2.1最理想的数据源
如果源数据能在Excel中很简单的处理成这样，那是最好的，很快就可以按照表名对应的字段名生成建表语句
| 1 | oracle | 表名 | 字段名 | 字段类型 | 字段长度 | 字段注释 |
| 2 | oracle | emp员工表 | emp_no | number | 10 | 员工编号 |
| 3 | oracle | emp员工表 | emp_name | varchar2 | 30 | 员工姓名 |
| 4 | oracle | emp员工表 | sal | number | 10,2 | 工资 |
| 5 | oracle | 表名 | 字段名 | 字段类型 | 字段长度 | 字段注释 |
| 6 | oracle | dept部门表 | dep_no | number | 10 | 部门编号 |
| 7 | oracle | dept部门表 | dep_name | varchar2 | 20 | 部门名称 |
| 8 | oracle | dept部门表 | loc | varchar2 | 10 | 地址 |
2.2常见的数据源
但是大多数情况下，应该是不会有这么理想的情况，比如我现在手里的数据就是下面这样的，我下面的数据处理，主要也是以我下面这样的数据格式进行处理，但是会兼容上面的理想数据源的情况。
| 1.emp员工表 | |||
| 字段信息： | |||
| 字段代码 | 数据类型 | 主键 | 备注 |
| emp_no | number(10) | 是 | 员工编号 |
| emp_name | varchar2(30) | 员工姓名 | |
| sal | number(10,2) | 工资 | |
| 2.dept部门表 | |||
| 字段信息： | |||
| 字段代码 | 数据类型 | 主键 | 备注 |
| dep_no | number(10) | 是 | 部门编号 |
| dep_name | varchar2(20) | 部门名称 | |
| loc | varchar2(10) | 地址 | |
| 3.emp_temp员工临时表 | |||
| 字段信息： | |||
| 字段代码 | 数据类型 | 主键 | 备注 |
| emp_no | number(10) | 是 | 员工编号 |
3.处理数据
1.将整理好的Excel数据插入到数据库中，我这里直接用PLSQL插入
插入数据后可以看到，字段数据的顺序和想象中的不太一样，因为我这边的表结构中，表名并没有单独放在一列，而是和字段名字在同一列，分两行，调整一下，在Excel中字段名前面再多加一列，然后再重新插入数据
数据源差不多就这样就可以了，接下来我们需要做的就是利用存储过程对数据进行处理，需要处理的有以下几个问题：
1.在table_name字段中插入对应的表字段的表名，因为我这边的数据是表名和字段名放在同一列，也就是说我需要通过判断，把所有的表名从col_name这一列取出来，插入到table_name字段；
2.将col_type中的字段长度取出来，然后插入到col_long中去，然后col_type中只保留字段的字段类型；
3.删除表中的垃圾数据，如：Excel中的表头字段信息，字段代码，索引信息，索引名称，索引等这些数据，还要在提取完表名之后删除表中的表名数据。
简单点来说就是将数据处理成2.1步骤中理想状态的数据就可以，在处理源数据时，我插入了一个编号，每一条数据都有一个固定的编号，这个可以在Excel中就插入，也可以在插入数据库表后自行插入，目的是为了在处理数据的过程中保证数据和源数据始终都保持一致，也可以在发生其他错误时精准的定位到错误数据。
最后处理完的数据是这样
生成建表语句的思路：
利用for 循环遍历获取表名table_name_eng，表名与获取到的表名一致的则为同一表中的字段数据，再循环拼接建表语句，将字段名称，字段类型，字段长度等进行拼接，完成建表语句，最后循环拼接表字段注释。
4.利用存储过程生成建表语句
create or replace procedure create_table_by_excel(
in_create_tab in varchar2, --批量建表时需要返回的指定表的建表语句的表名
o_create_sql out varchar2) --批量建表时需要返回的指定表的建表语句
authid current_user
as
v_count number(5); --统计表是否存在
sql_statement varchar2(4000); --存储SQL
sql_statement2 varchar2(4000); --存储SQL
sql_comment clob; --注释
sql_comment2 clob; --注释 字段注释过多会导致4000的长度超长
table_name_eng varchar2(1000); --英文表名
table_name_cn varchar2(1000); --中文表名
create_sql_comment varchar2(4000); --存储返回的建表语句
begin
v_count := 0; --数量
sql_statement :=''; --存储每张表建表语句
sql_statement2 :=''; --存储每个字段建表语句
sql_comment :=''; --存储每张表注释
sql_comment2 :=''; --存储每个字段注释
table_name_eng :=''; --英文表名
table_name_cn :=''; --中文表名
/*
还未解决的问题：
1.字段类型中不是Oracle类型的需要删除
2.字段注释存在同一个字段，多行的情况
建表语句
TABLE_INFO为Excel中的源数据，TABLE_INFO_TEMP为过程临时表，CREATE_TABLE为最终生成的建表语句和注释语句
表字段的顺序可以根据实际情况进行调整，怎么方便怎么来
create table TABLE_INFO
(
id number,
db_type VARCHAR2(10) default 'oracle',
tab_name VARCHAR2(100),
col_name VARCHAR2(1000),
col_type VARCHAR2(1000),
col_long VARCHAR2(1000),
col_comment VARCHAR2(4000)
);
comment on column TABLE_INFO.id
is '数据序号';
comment on column TABLE_INFO.db_type
is '数据库类型';
comment on column TABLE_INFO.tab_name
is '表名';
comment on column TABLE_INFO.col_name
is '字段名';
comment on column TABLE_INFO.col_type
is '字段类型';
comment on column TABLE_INFO.col_long
is '字段长度';
comment on column TABLE_INFO.col_comment
is '字段注释';
示例数据1（理想状态，如果源数据在Excel能处理成这样最好，然后直接粘贴到TABLE_INFO表中）
id db_type tab_name col_name col_type col_long col_comment
1 oracle emp员工表 BEG_DATE CHAR 10 开始日期
2 oracle emp（员工表） END_DATE CHAR 10 失效日期
3 oracle (emp)员工表 id VARCHAR2 50 id
示例数据2（大多数情况可能只能处理成这样，处理成这样按字段插入源表可以，后面会进行处理）
db_type tab_name col_name col_type col_long col_comment
emp员工表
emp_no number 员工编号
emp_name varchar2(10) 员工姓名
sal number(20,8) 员工工资
dept部门表
dept_no number 部门编号
create table TABLE_INFO_TEMP
(
id number,
db_type VARCHAR2(10) default 'oracle',
tab_name_full VARCHAR2(100),
tab_name_eng VARCHAR2(100),
tab_name_cn VARCHAR2(100),
col_name VARCHAR2(1000),
col_type VARCHAR2(1000),
col_long VARCHAR2(1000),
col_comment VARCHAR2(4000)
);
comment on column TABLE_INFO_TEMP.id
is '源表数据序号';
comment on column TABLE_INFO_TEMP.db_type
is '数据库类型';
comment on column TABLE_INFO_TEMP.tab_name_full
is '源表表名如：emp员工表，员工表emp等，插入后不变，以便后期与源表查询对应';
comment on column TABLE_INFO_TEMP.tab_name_eng
is '英文表名';
comment on column TABLE_INFO_TEMP.tab_name_cn
is '中文表名';
comment on column TABLE_INFO_TEMP.col_name
is '字段名';
comment on column TABLE_INFO_TEMP.col_type
is '字段类型';
comment on column TABLE_INFO_TEMP.col_long
is '字段长度';
comment on column TABLE_INFO_TEMP.col_comment
is '字段注释';
create table CREATE_TABLE
(
tab_name VARCHAR2(100),
create_sql VARCHAR2(2000),
tab_comment VARCHAR2(4000),
create_sql_comment VARCHAR2(4000),
create_time DATE default sysdate
)
-- Add comments to the columns
comment on column CREATE_TABLE.tab_name
is '表名';
comment on column CREATE_TABLE.create_sql
is '建表语句';
comment on column CREATE_TABLE.tab_comment
is '中文表名及字段注释';
comment on column CREATE_TABLE.create_time
is '创建时间';
*/
--清空临时表数据，create_table中已经生成的历史建表语句可删可不删，有时间字段可以区分
--execute immediate 'truncate table create_table';
--execute immediate 'truncate table table_info_temp';
--判断是否建表，这里简单判断中间表
select count(1) into v_count from all_tables t where t.TABLE_NAME in('TABLE_INFO_TEMP');
if v_count = 0 then
return;
end if;
--准备数据，处理数据，将源表数据处理成标准数据
--清空临时表
execute immediate'truncate table table_info_temp';
--清空存储建表语句的表
execute immediate'truncate table create_table';
--将原始数据插入到临时表，利用序号对应，防止数据插入后顺序错乱
insert into table_info_temp
select t.id, t.db_type, trim(t.tab_name), trim(t.tab_name), trim(t.tab_name), trim(t.col_name), trim(t.col_type), trim(t.col_long), trim(t.col_comment) from table_info t order by t.id;
--删除表中的excel表头及无效数据
delete from table_info_temp t
where instr(t.col_name, '列名') > 0 --删除字段中存在该关键字的数据
or instr(t.col_name, '数据类型') > 0 --删除存在索引的情况，有逗号，无括号，如：col_1,col_2
or instr(t.col_name, '说明') > 0 --删除存在索引的情况，有逗号，无括号，如：col_1,col_2
or (instr(t.col_type, ',') > 0 and instr(t.col_type, '(') = 0)
or (t.col_name is null and t.col_type is null and t.col_comment is null)
or (length(t.col_name) - length(replace(t.col_name, '.')) = 2 and t.col_type is null);
--判断col_type中的数据是否包含长度，如varchar2(10)，需要将类型和长度拆分开来
select count(1) into v_count from table_info_temp t where instr(t.col_type, '(') > 0;
if v_count > 0 then
update table_info_temp t --1.先将长度字段清空，防止有垃圾数据
set t.col_long = null;
update table_info_temp t --2.将字段长度从字段类型里面拆出来，放入长度字段,这里的前提是数据是类型+(长度),如：varchar2(10)，number(10,2)
set t.col_long = substr(t.col_type, instr(t.col_type, '(') + 1, length(t.col_type) - instr(t.col_type, '(') - 1)
where instr(t.col_type, '(') > 0;
update table_info_temp t --3.修改字段类型里面的数据，去掉长度，保留类型
set t.col_type = substr(t.col_type, 1, instr(t.col_type, '(') - 1)
where instr(t.col_type, '(') > 0;
end if;
/* --如果表名和字段名在同一列，需要将表名从字段名那一列中分离出来，如：
table_name_eng col_name col_type
emp
emp_no number
emp_name varchar2(10)
dept
dept_no number
*/
--如果存在表名和字段名放在同一列的情况，先将表名提取出来
select count(1) into v_count from table_info_temp t where t.tab_name_eng is null;
if v_count > 0 then
update table_info_temp t --表名存在字段名称中，则字段名称不为空，字段类型为空
set t.tab_name_full = trim(t.col_name),
t.tab_name_eng = trim(t.col_name),
t.tab_name_cn = trim(t.col_name)
where t.col_name is not null and t.col_type is null;
merge into table_info_temp t
using(
select id, nvl(tab_name_full, lag(tab_name_full ignore nulls) over(order by id)) table_name from table_info_temp) q
on (t.id = q.id)
when matched then
update
set t.tab_name_full = q.table_name,
t.tab_name_eng = q.table_name,
t.tab_name_cn = q.table_name;
--删除字段名中表名的数据
delete from table_info_temp t where trim(t.tab_name_eng) = trim(t.col_name);
end if;
--不需要长度的字段类型，如 INTEGER， clob， date等
update table_info_temp t
set t.col_long = null
where lower(trim(t.col_type)) in('integer', 'clob', 'blob', 'date', 'timestamp');
--处理字段名称中的空格
update table_info_temp t
set t.col_name = replace(t.col_name, ' ', '')
where t.col_name like '% %';
--若表名中存在括号，先将括号去掉，考虑括号为中文或英文情况
update table_info_temp t
set t.tab_name_eng = replace(replace(replace(replace(t.tab_name_cn, '(', ''), ')', ''), '（', ''), '）', ''),
t.tab_name_cn = replace(replace(replace(replace(t.tab_name_cn, '(', ''), ')', ''), '（', ''), '）', '')
where instr(t.tab_name_cn, '(') > 0
or instr(t.tab_name_cn, '（') > 0
or instr(t.tab_name_eng, '(') > 0
or instr(t.tab_name_eng, '（') > 0;
--删除表名中的中文字符存到tab_name_eng
update table_info_temp t
set t.tab_name_eng = regexp_replace(ASCIISTR(t.tab_name_full),'\\[[:alnum:]]{4}','');
--删除表名中的英文字符存到tab_name_cn，用于表名注释
update table_info_temp t
set t.tab_name_cn = replace(t.tab_name_full, t.tab_name_eng, '');
--去除表名中的序号，如有的话，如：1.table_name，2.table_name
update table_info_temp t
set t.tab_name_eng = substr(t.tab_name_eng, instr(t.tab_name_eng, '.') + 1);
--如果表名的中文名存在多个词根，如：emp_e1员工表_测试，英文表名会多个_
update table_info_temp t
set t.tab_name_eng = substr(t.tab_name_eng, 1, length(t.tab_name_eng) - 1)
where substr(t.tab_name_eng, -1, 1) = '_';
--默认为Oracle类型
update table_info_temp t
set t.db_type = 'oracle'
where t.db_type is null;
--非Oracle数据库需要处理字段类型与Oracle对应
select count(1) into v_count from table_info_temp t where lower(t.db_type) = 'oracle';
if v_count = 0 then
--MySQL，DB2等库表结构生成Oracle建表语句需先进行数据类型处理
update table_info_temp t
set t.col_type = 'varchar2'
where t.db_type <> 'oracle' and lower(t.col_type) in('varchar', 'char', 'bit', 'character', 'tinyint');
update table_info_temp t
set t.col_type = 'number'
where t.db_type <> 'oracle' and lower(t.col_type) in('int', 'bigint', 'smallint', 'double', 'decimal');
update table_info_temp t
set t.col_type = 'date'
where t.db_type <> 'oracle' and lower(t.col_type) in('datetime', 'timestamp');
update table_info_temp t
set t.col_type = 'clob'
where t.db_type <> 'oracle' and lower(t.col_type) in('text', 'longtext');
--非Oracle库字符串类型扩大到2倍
update table_info_temp t
set t.col_long = to_number(t.col_long) * 2
where lower(t.col_type) = 'varchar2' and lower(db_type) <> 'oracle';
--非Oracle库number类型超过38时，取38
update table_info_temp t
set t.col_long = 38
where lower(t.col_type) = 'number' and to_number(substr(trim(t.col_long), 1, 2)) > 38 and lower(db_type) <> 'oracle';
end if;
--如果处理完还存在其他不规范的数据，数量不多的话可以手动处理
/* 比如我遇到的两种特殊情况，
1.一种是字段注释存在同一字段的注释在Excel中对应多行，字段名存在多行单元格合并
2.另一种是存在建索引的情况，把建索引的数据和建表的混在一起，都存在col_type字段中，此种情况暂时先将索引删除，批量建索引后期找时间再更新
*/
--删除非Oracle数据类型的数据（主要针对数据中有单列索引在上面中无法处理，上面可能会被当成字段名处理）
delete from table_info_temp t
where lower(t.col_type) not in('char', 'varchar2', 'nvarchar2', 'clob', 'blob', 'nclob', 'long', 'number', 'date', 'timestamp'); --常见的数据类型基本上就这么多，其他的有需要可以自己加
commit;
for item in(select tab_name_eng, tab_name_cn from table_info_temp group by tab_name_eng, tab_name_cn)
loop
--表名赋值
table_name_eng := item.tab_name_eng;
table_name_cn := item.tab_name_cn;
------------------------------查看表是否存在，存在删除（begin）-------------------------------------------
--查看表是否存在
sql_statement := 'select count(*) from all_tables a where a.table_name = upper(''' || table_name_eng || ''')';
execute immediate sql_statement into v_count;
--如果表存在
if v_count > 0 then
--execute immediate 'create table ' || table_name_eng || '_bk' || 'as select * from ' || table_name_eng;
execute immediate 'drop table ' || table_name_eng;
end if;
------------------------------查看表是否存在，存在删除（end）---------------------------------------------
--清空sql_statement
sql_statement := '';
sql_statement2 := '';
sql_comment :='';
sql_comment2 :='';
--------------------------------------------创建表（begin）-----------------------------------------------
sql_statement := sql_statement || 'create table '|| table_name_eng || '( ';
for details in(select col_name, col_type, col_long, col_comment from table_info_temp where upper(tab_name_eng) = upper(table_name_eng))
loop
--拼接创建表语句
sql_statement2 := sql_statement2 || details.col_name || ' ' || details.col_type || '(' || details.col_long || '), ';
--拼接字段注释
sql_comment2 :='';
sql_comment2 := sql_comment2 || 'comment on column ' || table_name_eng || '.' || details.col_name || ' is ' ||details.col_comment|| '''' ||';';
sql_comment := sql_comment || chr(13) || sql_comment2;
--dbms_output.put_line(sql_comment);
end loop;
--去掉最后多拼接的逗号
sql_statement := sql_statement || substr(sql_statement2, 0, length(sql_statement2)-2);
--后面的右括号
sql_statement := sql_statement || ');';
--拼接表名注释
sql_comment := 'comment on table ' || table_name_eng || ' is ' || '''' || table_name_cn || '''' || ';' || sql_comment;
--MySQL库表结构生成Oracle建表语句需先进行数据类型处理
sql_statement := replace(replace(sql_statement, 'date(0)', 'date'), 'clob(0)', 'clob');
--优化建表语句，没有长度的字段类型()需要删除
sql_statement := replace(sql_statement, '()', '');
--建表语句留存
insert into create_table(tab_name, create_sql, tab_comment, create_sql_comment) values(table_name_eng, sql_statement, sql_comment, sql_statement || chr(13) || sql_comment);
commit;
-- 执行创建语句和注释语句
--sql_statement := replace(sql_statement, ';', '');
--execute immediate sql_statement;
--execute immediate sql_comment;
--------------------------------------------创建表（end）-----------------------------------------------
end loop;
--返回建表语句+字段注释
--返回建表语句长度
if in_create_tab is not null then
select length(t.create_sql_comment) into v_count from create_table t where t.tab_name = in_create_tab;
else
v_count := length(sql_statement || chr(13) || sql_comment);
end if;
--判断建表语句的字符长度，超过1900的不展示，去表里面查询，不超过1900则展示
if in_create_tab is not null and v_count <= 1900 then
select t.create_sql_comment into o_create_sql from create_table t where t.tab_name = in_create_tab;
elsif in_create_tab is null and v_count <= 1900 and v_count > 0 then
o_create_sql := sql_statement || chr(13) || sql_comment;
elsif in_create_tab is not null and v_count > 1900 then
o_create_sql := 'select * from create_table where tab_name = '|| '''' || in_create_tab || ''';' ;
else
o_create_sql := 'select * from create_table where create_time >= trunc(sysdate);';
end if;
/*注：如需修改建表语句格式
1.将create_sql复制到notepad++，用正则表达式将“，”替换为,\n；
2.将tab_comment复制到notepad++，将“"”替换为空；*/
end create_table_by_excel;
5.执行建表语句完成建表
这里因为我的库里已经有测试的表了，所以改了个表名，注释这个字段我这里用的是clob类型，因为我这边处理的表里面有几百个字段的表，varchar2最多只能存4000个字符，可能会存在超长的情况，所以这里我用了clob类型。
生成建表语句以后，可以将建表语句复制出来，然后执行即可。
如果字段过多觉得一行不好看，或者建表语句需要作为文档上传这种，可以将建表语句拷贝到notepad++之类的工具中进行处理，比如我这里用notepad++处理，处理完之后看起来可能会稍微好看一些。
如果需要使用脚本执行建表语句及注释的话，可以自己在过程中拼写一个执行语句，后续如果有谁需要的话，也可以留言我这边更新~
4490
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
