在日常的项目中，常常会遇到根据表结构或实体的属性向数据库中生成表，然后根据数据库中的表结构反向生成数据模型的情况。这种情况如果直接处理会比较麻烦，我们设计了如下根据实体属性反向生成数据模型的方案。
1、建表及插入测试数据

建表、插入测试数据.sql

--1、建存储表结构数据字典表和SQL语句存储表
-- Create table
create table TAB_STRUCTURE
(
  TABLE_NAME   VARCHAR2(30) not null,
  TAB_COMMENTS VARCHAR2(4000),
  COLUMN_NAME  VARCHAR2(30) not null,
  COL_COMMENTS VARCHAR2(4000)
);
-- Add comments to the table 
comment on table TAB_STRUCTURE
  is '表结构字典表';
-- Add comments to the columns 
comment on column TAB_STRUCTURE.TAB_COMMENTS
  is '等同表中文名';
comment on column TAB_STRUCTURE.COL_COMMENTS
  is '等同列中文名';
  
-- Create table
create table TAB_CREATESQL
(
  TAB_CREATE_SCRIPT CLOB
);
--2、插入测试数据
insert into tab_structure (TABLE_NAME, TAB_COMMENTS, COLUMN_NAME, COL_COMMENTS)
values ('学生表', '学生表', '学生编号', '学生编号');
insert into tab_structure (TABLE_NAME, TAB_COMMENTS, COLUMN_NAME, COL_COMMENTS)
values ('学生表', '学生表', '姓名', '姓名');
insert into tab_structure (TABLE_NAME, TAB_COMMENTS, COLUMN_NAME, COL_COMMENTS)
values ('学生表', '学生表', '性别', '性别');
insert into tab_structure (TABLE_NAME, TAB_COMMENTS, COLUMN_NAME, COL_COMMENTS)
values ('学生表', '学生表', '年级', '年级');
insert into tab_structure (TABLE_NAME, TAB_COMMENTS, COLUMN_NAME, COL_COMMENTS)
values ('学生表', '学生表', '身高', '身高');
insert into tab_structure (TABLE_NAME, TAB_COMMENTS, COLUMN_NAME, COL_COMMENTS)
values ('学生表', '学生表', '体重', '体重');
insert into tab_structure (TABLE_NAME, TAB_COMMENTS, COLUMN_NAME, COL_COMMENTS)
values ('科目表', '科目表', '科目编号', '科目编号');
insert into tab_structure (TABLE_NAME, TAB_COMMENTS, COLUMN_NAME, COL_COMMENTS)
values ('科目表', '科目表', '科目名称', '科目名称');
insert into tab_structure (TABLE_NAME, TAB_COMMENTS, COLUMN_NAME, COL_COMMENTS)
values ('科目表', '科目表', '代课老师', '代课老师');
insert into tab_structure (TABLE_NAME, TAB_COMMENTS, COLUMN_NAME, COL_COMMENTS)
values ('成绩表', '成绩表', '科目编号', '科目编号');
insert into tab_structure (TABLE_NAME, TAB_COMMENTS, COLUMN_NAME, COL_COMMENTS)
values ('成绩表', '成绩表', '学生编号', '学生编号');
insert into tab_structure (TABLE_NAME, TAB_COMMENTS, COLUMN_NAME, COL_COMMENTS)
values ('成绩表', '成绩表', '成绩', '成绩');

2、根据在表中存储的表结构信息生成建表脚本

P_CREATETAB_FROM_TABSTRUCTURE.prc

CREATE OR REPLACE PROCEDURE P_CREATETAB_FROM_TABSTRUCTURE AS
  V_CLOB_CREATE_TABLE CLOB;
  V_CLOB_COMMENT_TABCOL CLOB;
BEGIN
  DBMS_LOB.CREATETEMPORARY(V_CLOB_CREATE_TABLE,TRUE,DBMS_LOB.CALL);
  DBMS_LOB.OPEN(V_CLOB_CREATE_TABLE,DBMS_LOB.LOB_READWRITE);
  DBMS_LOB.CREATETEMPORARY(V_CLOB_COMMENT_TABCOL,TRUE,DBMS_LOB.CALL);
  DBMS_LOB.OPEN(V_CLOB_COMMENT_TABCOL,DBMS_LOB.LOB_READWRITE);
  
  EXECUTE IMMEDIATE 'TRUNCATE TABLE TAB_CREATESQL';
  /*
  CREATE TABLE TAB_CREATESQL (TAB_CREATE_SCRIPT CLOB);
  SELECT * FROM TAB_CREATESQL;
  */
  FOR C_TAB IN (
SELECT (CASE WHEN RN = MINRN THEN 
       '--表'||TABLE_NAME||'的结构生成'||CHR(10)||'CREATE TABLE ' || SUBSTR(TABLE_NAME,1,32) || '('||CHR(10) ELSE NULL END) AS CREATE_TAB_HEAD,
       COL_NAME || ' ' || DEFAULT_TYPE || (CASE WHEN RN = MAXRN THEN ');'||CHR(10) ELSE ','||CHR(10) END) CREATE_TAB_BODY,
       (CASE WHEN RN=MINRN THEN 
       '--表'||TABLE_NAME||'的表及列注释的生成'||CHR(10)||'COMMENT ON TABLE '||TABLE_NAME||' IS '''||TAB_COMMENTS||''';'||CHR(10) ELSE NULL END) COMMENT_TAB,
       'COMMENT ON COLUMN '||TABLE_NAME||'.'||COL_NAME ||' IS '''||COL_COMMENTS||''';'||CHR(10) COMMENT_COL
  FROM (SELECT TABLE_NAME, (CASE WHEN CNT=1 THEN COL_NAME ELSE COL_NAME||FLAG END) COL_NAME, DEFAULT_TYPE, TAB_COMMENTS,COL_COMMENTS,RN,
               MIN(RN) OVER(PARTITION BY TABLE_NAME) MINRN,
               MAX(RN) OVER(PARTITION BY TABLE_NAME) MAXRN
          FROM (SELECT TABLE_NAME, COL_NAME, DEFAULT_TYPE, TAB_COMMENTS,COL_COMMENTS,
                       COUNT(COL_NAME) OVER(PARTITION BY TABLE_NAME, COL_NAME) CNT,
                       ROW_NUMBER() OVER(PARTITION BY TABLE_NAME, COL_NAM