目的:对某表创建物化视图时,不要求它所有字段,因此必须列出要的字段,一个个字段COPY也比较麻烦.因此写一角本
select
' create materialized view ' || max (a.table_name) || chr( 10 ) ||
' tablespace staging refresh fast as select ' || chr( 10 ) ||
substr( max (sys_connect_by_path(a.column_name || chr( 10 ), ' , ' )), 2 ) ||
' from ' || max (a.table_name) || ' @zhengguan t; ' -- 这里是DBLINK
from
( select row_number() over ( PARTITION BY g.table_name order by g.column_id ) rn
,g.table_name,g.column_name from
user_tab_cols g
where g.table_name = ' TB_LVY_INCREASEDECREASEDATA ' -- 这里是要建的表名
) a
start with rn = 1
connect by rn = prior rn + 1
运用:对于一些存储过程中对表的insert时,这样也许可减少些工作量,反正灵活运用吧,一个角本也许通过自己的变化,在工作中可以启到一定的作用。
' create materialized view ' || max (a.table_name) || chr( 10 ) ||
' tablespace staging refresh fast as select ' || chr( 10 ) ||
substr( max (sys_connect_by_path(a.column_name || chr( 10 ), ' , ' )), 2 ) ||
' from ' || max (a.table_name) || ' @zhengguan t; ' -- 这里是DBLINK
from
( select row_number() over ( PARTITION BY g.table_name order by g.column_id ) rn
,g.table_name,g.column_name from
user_tab_cols g
where g.table_name = ' TB_LVY_INCREASEDECREASEDATA ' -- 这里是要建的表名
) a
start with rn = 1
connect by rn = prior rn + 1
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/134308/viewspace-140594/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/134308/viewspace-140594/