前同事一个需求,要我帮忙想想办法。
1.需求,oracle数据库中100多张表,每张表建一个视图。视图内容,包含表中全部字段,还要有每个字段的元数据拼成一个字段。比如 create table table_name (name varchar2(40) (名字) ,age number(2) (年龄)) ,那么字段的元数据包含,类型,长度,注释。
2.分析,由于表数量太多,每个表字段太多,需要获取每个字段的元数据。需要拼装sql视图。
3.给100张表的元数据创建视图TEMPMETAVIEW
create view TEMPMETAVIEW as
SELECT a.TABLE_NAME 表名 ,
d.COMMENTS 表名注释,
a.COLUMN_NAME 列名,
a.COMMENTS 字段注释,
b.DATA_TYPE 字典类型,
b.DATA_LENGTH 字典长度,
b.NULLABLE 是否为空
FROM user_objects t, USER_COL_COMMENTS a, USER_TAB_COLUMNS b,user_tab_comments d
where to_char(t.CREATED, 'yyyymmdd') = '20210427'
and object_type = 'TABLE'
and d.TABLE_NAME=t.OBJECT_NAME
and a.TABLE_NAME = t.OBJECT_NAME
and t.OBJECT_NAME = b.TABLE_NAME
and a.COLUMN_NAME = b.COLUMN_NAME
where d.table_name in ('tablename1','tablename2','tablename3',,,,,,,'tablename100')
4.形成100张表的视图sql语句 注意where条件必须指定表名。100个表就有100个表名
select 'create or replace view as select ' || wm_concat (metaview1.列名 || ',' || metaview1.字段注释 || ',' || metaview1.字典类型 || ',' || metaview1.字典长度 || ',' || metaview1.是否为空) || ' from ' || metaview.表名
from TEMPMETAVIEW metaview1 where metaview1.列名 in
(
select '''' || replace(wm_concat(metaview.列名),',',''',''') || '''' from TEMPMETAVIEW metaview where metaview.表名='tablename1'
);
5.本案例说到第4点就结束了。实现了根据表名,生成这个表的视图sql。不过100个表要执行100次。可以写存储过程,一次性输入100个表名。就不继续完善了。