根据实体生成数据模型方案

在日常的项目中，常常会遇到根据表结构或实体的属性向数据库中生成表，然后根据数据库中的表结构反向生成数据模型的情况。这种情况如果直接处理会比较麻烦，我们设计了如下根据实体属性反向生成数据模型的方案。
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_NAME ORDER BY COL_NAME) FLAG,
                       ROW_NUMBER() OVER(PARTITION BY TABLE_NAME ORDER BY ROWID) RN  --取一个表中列的行号，此处以rowid排序，建议在表TAB_STRUCTURE中增加表内部列的行号进行排序
                  FROM (SELECT F_GET_FRIST_PINY(REGEXP_REPLACE(A.TAB_COMMENTS,'\(.+\)|（.+）|','')) TABLE_NAME,  --处理表名中带括号的注释性内容，不作表英文名
                               A.TAB_COMMENTS,
                               F_GET_FRIST_PINY(REGEXP_REPLACE(A.COL_COMMENTS,'\(.+\)|（.+）|^.+：','')) COL_NAME, --处理列名中带括号的注释性内容，不作列英文名
                               'VARCHAR2(500)' DEFAULT_TYPE,
                               A.COL_COMMENTS
                          FROM TAB_STRUCTURE A)) --用到了一个取拼音首字母的函数，此函数见百度博客http://hi.baidu.com/nsj820/item/da7b4c11d3f1f4731109b564
         ORDER BY TABLE_NAME, RN)) LOOP

     DBMS_LOB.WRITEAPPEND(V_CLOB_CREATE_TABLE,LENGTH(C_TAB.CREATE_TAB_HEAD||C_TAB.CREATE_TAB_BODY),C_TAB.CREATE_TAB_HEAD||C_TAB.CREATE_TAB_BODY);
     DBMS_LOB.WRITEAPPEND(V_CLOB_COMMENT_TABCOL,LENGTH(C_TAB.COMMENT_TAB||C_TAB.COMMENT_COL),C_TAB.COMMENT_TAB||C_TAB.COMMENT_COL);
     
    END LOOP;

    DBMS_LOB.APPEND(V_CLOB_CREATE_TABLE,V_CLOB_COMMENT_TABCOL);
    INSERT INTO TAB_CREATESQL(TAB_CREATE_SCRIPT) VALUES(V_CLOB_CREATE_TABLE);
    COMMIT;

    DBMS_LOB.CLOSE(V_CLOB_CREATE_TABLE);
    DBMS_LOB.CLOSE(V_CLOB_COMMENT_TABCOL);
    DBMS_LOB.FREETEMPORARY(V_CLOB_CREATE_TABLE);
    DBMS_LOB.FREETEMPORARY(V_CLOB_COMMENT_TABCOL);

END P_CREATETAB_FROM_TABSTRUCTURE;
/

3、执行建表脚本到数据库中；最后将数据库中的表反向生成PowerDesigner物理模型（表中的字段类型，根据实际需要进行更改即可）。