Materialize view 更新资料的程序

王朝other·作者佚名  2008-05-31
宽屏版  字体: |||超大  

从AskTom的文章里看到一段关於mv (materialize view)的操作过程,随手就把他纪录了下来,提供参考。

ops$tkyte@ORA817DEV.US.Oracle.COM> create table t ( x int primary key, y int);

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>

ops$tkyte@ORA817DEV.US.ORACLE.COM> create materialized view t_mv

2 refresh on commit

3 as

4 select y, count(*) cnt from t

5 group by y;

Materialized view created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace trigger t_mv_trigger

2 after insert or update or delete

3 on t_mv

4 begin

5 if ( inserting )

6 then

7 dbms_output.put_line( 'Hey, I fired!! for insert' );

8 end if;

9 if ( deleting )

10 then

11 dbms_output.put_line( 'Hey, I fired!! for deleting' );

12 end if;

13 if ( updating )

14 then

15 dbms_output.put_line( 'Hey, I fired!! for updating' );

16 end if;

17 end;

18 /

Trigger created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 1, 1 );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec null;

PL/SQL procedure sUCcessfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM>

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;

Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec null;

Hey, I fired!! for deleting

Hey, I fired!! for insert

PL/SQL procedure successfully completed.

上面的lab给了我几个新的学习

第一:mv每当更新资料的程序并非update mv,而是先delete再insert新资料,我想的是那当这个mv是非常庞大的时候,即时的refresh不就非常吃resource?

第二:exec null可以直接取出 DBMS_OUTPUT buffer的资讯。

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
© 2005- 王朝网络 版权所有