Materialize view 更新资料的程序

　　 

　　从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 suclearcase/" target="_blank" >ccessfully 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的资讯。

原文转自：http://www.ltesting.net