在做仓库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等。