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

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

建表、插入测试数据.sql

--1、建存储表结构数据字典表和SQL语句存储表-- Create tablecreate 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 tablecreate 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物理模型（表中的字段类型，根据实际需要进行更改即可）。