Materialize view 更新資料的程序
2024-07-21 02:33:45
供稿：網(wǎng)友
從AskTom的文章里看到一段關(guān)於mv (materialize view)的操作過程，隨手就把他紀(jì)錄了下來，提供參考。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 insertPL/SQL procedure successfully completed. 上面的lab給了我?guī)讉€新的學(xué)習(xí) 第一：mv每當(dāng)更新資料的程序并非update mv，而是先delete再insert新資料，我想的是那當(dāng)這個mv是非常龐大的時候，即時的refresh不就非常吃resource？ 第二：exec null可以直接取出 DBMS_OUTPUT buffer的資訊。
