仓库2期新增数据库表批量脚本生成方案

在做仓库2期的时候，由于1期2期的建库脚本都在一个PDM模型上，在生成建表语句的时候，一个个划勾(Database/Generate Database/Selection )导出所选表的建库脚本比较麻烦。所以，考虑用以下方案来做，可能会给我们带来一些帮助。

1、  环境说明

新建的两个用户：

(1)     ALLT 所有表的PDM表结构存储用户

(2)NEWT 需要新生成表的表结构存储用户，另：存储过程SP_DW_CREATE_NEW_TABLE、列表清单EDW_NEW_TABLE_LIST也在该用户下。

2、  模型的全部表的表结构生成

(1)     导出PDM中所有表的建库脚本(根据需要，设置Options；PDM表的General/Generate复选框要打勾，否则生成不了建库脚本)

(2)     将脚本打到ALLT用户下

3、  准备要新生成表的列表清单

 1、建表语句(存放2期要新建表的列表清单) -- Create table create table EDW_NEW_TABLE_LIST ( NEW_TABLE_NAME VARCHAR2(30) not null, NEW_TABLE_FLAG VARCHAR2(16) ); -- Add comments to the columns comment on column EDW_NEW_TABLE_LIST.NEW_TABLE_NAME is '要新建表的表名'; comment on column EDW_NEW_TABLE_LIST.NEW_TABLE_FLAG is '是否新建表标志'; 2、插入列表清单数据 TRUNCATE TABLE Edw_New_Table_List; SELECT * FROM Edw_New_Table_List FOR UPDATE; --手工粘贴列表清单到Edw_New_Table_List.NEW_TABLE_NAME字段 UPDATE Edw_New_Table_List SET new_table_flag='1'; COMMIT; SELECT * FROM Edw_New_Table_List 3、列表清单的获取 通过进入开发脚本所在的目录，CMD下运行[dir /s/b/a-d *.SQL >list.txt]，获取脚本路径及名称，再用UltraEdit稍加编辑，即可。

4、  运行存储过程SP_DW_CREATE_NEW_TABLE

直接运行，参数P_SRC_USERNAME为源用户名

SP_DW_CREATE_NEW_TABLE的代码如下：

 CREATE OR REPLACE PROCEDURE SP_DW_CREATE_NEW_TABLE(P_SRC_USERNAME IN VARCHAR2) AS CURSOR CUR_TAB_NEW IS SELECT NEW_TABLE_NAME FROM EDW_NEW_TABLE_LIST WHERE NEW_TABLE_NAME <> 'EDW_NEW_TABLE_LIST' AND NEW_TABLE_FLAG = '1'; V_SRC_USERNAME VARCHAR2(30) := UPPER(P_SRC_USERNAME); V_TABLE_NAME VARCHAR2(30); V_SQLTEXT VARCHAR2(1000); V_NUM NUMBER; V_TAB_COMMENT VARCHAR2(1000); BEGIN BEGIN OPEN CUR_TAB_NEW; LOOP FETCH CUR_TAB_NEW INTO V_TABLE_NAME; EXIT WHEN CUR_TAB_NEW%NOTFOUND; --判断要新建的表在新库里是否已经存在 SELECT COUNT(1) INTO V_NUM FROM USER_TABLES WHERE TABLE_NAME = UPPER(V_TABLE_NAME); --在新用户建新表 IF V_NUM > 0 THEN EXECUTE IMMEDIATE 'DROP TABLE ' || V_TABLE_NAME; V_SQLTEXT := 'CREATE TABLE ' || V_TABLE_NAME || ' AS SELECT * FROM ' || V_SRC_USERNAME || '.' || V_TABLE_NAME || ' WHERE ROWNUM < 1'; EXECUTE IMMEDIATE V_SQLTEXT; COMMIT; ELSE V_SQLTEXT := 'CREATE TABLE ' || V_TABLE_NAME || ' AS SELECT * FROM ' || V_SRC_USERNAME || '.' || V_TABLE_NAME || ' WHERE ROWNUM < 1'; EXECUTE IMMEDIATE V_SQLTEXT; COMMIT; END IF; --为新建的表添加表级注释 SELECT COMMENTS INTO V_TAB_COMMENT FROM ALL_TAB_COMMENTS WHERE TABLE_NAME = V_TABLE_NAME AND OWNER = V_SRC_USERNAME; V_SQLTEXT := 'COMMENT ON TABLE ' || V_TABLE_NAME || ' IS ''' || V_TAB_COMMENT || ''''; EXECUTE IMMEDIATE V_SQLTEXT; --为新建的表添加列级注释 DECLARE V_COL_NAME VARCHAR2(32); V_COL_COMMENTS VARCHAR2(4000); CURSOR CUR_COL_COMMENTS IS SELECT COLUMN_NAME, COMMENTS FROM ALL_COL_COMMENTS WHERE OWNER = V_SRC_USERNAME AND TABLE_NAME = V_TABLE_NAME; BEGIN OPEN CUR_COL_COMMENTS; LOOP FETCH CUR_COL_COMMENTS INTO V_COL_NAME, V_COL_COMMENTS; EXIT WHEN CUR_COL_COMMENTS%NOTFOUND; V_SQLTEXT := 'COMMENT ON COLUMN ' || V_TABLE_NAME || '.' || V_COL_NAME || ' IS ''' || V_COL_COMMENTS || ''''; EXECUTE IMMEDIATE V_SQLTEXT; END LOOP; CLOSE CUR_COL_COMMENTS; END; COMMIT; END LOOP; CLOSE CUR_TAB_NEW; END; END SP_DW_CREATE_NEW_TABLE; /

5、  导出NEWT下的所有表的表结构脚本

PL/SQL下//Tools/Export User Objects …

导出除列表清单表外的所有表。

6、  说明

此方案适用于如仓库系统2期项目的需要；主要用到一个存储过程来转换；中间用到如UE、PL/SQL等。