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