在MV上建立触发器实验

2019-05-25 19:43:50 浏览数 (1)
  1. -- 建立MV测试表
  2. CREATE TABLE tbl1
  3. (
  4. a NUMBER,
  5. b VARCHAR2 (20)
  6. );
  8. CREATE UNIQUE INDEX tbl1_pk ON tbl1 (a);
  10. ALTER TABLE tbl1 ADD (CONSTRAINT tbl1_pl PRIMARY KEY(a));
  12. -- 建立MV日志，单一表聚合视图的快速刷新需要指定including new values子句
  13. CREATE MATERIALIZED VIEW LOG ON tbl1 INCLUDING NEW VALUES;
  15. -- 建立MV
  16. CREATE MATERIALIZED VIEW mv_tbl1
  17. BUILD IMMEDIATE
  18. REFRESH FAST
  19. START WITH TO_DATE('2013-06-01 08:00:00','yyyy-mm-dd hh24:mi:ss')
  20. NEXT SYSDATE 1/24
  21. AS
  22. SELECT * FROM tbl1;
  24. -- 建立trigger测试表
  25. CREATE TABLE mv_tbl1_tri
  26. (
  27. a NUMBER,
  28. b VARCHAR (20),
  29. c VARCHAR (20)
  30. );
  32. -- 建立trigger
  33. CREATE OR REPLACE TRIGGER tri_mv
  34. AFTER DELETE OR INSERT OR UPDATE
  35. ON mv_tbl1
  36. REFERENCING NEW AS new OLD AS old
  37. FOR EACH ROW
  38. BEGIN
  39. CASE
  40. WHEN INSERTING THEN
  41. INSERT INTO mv_tbl1_tri VALUES (:new.a, :new.b, 'insert');
  42. WHEN UPDATING THEN
  43. INSERT INTO mv_tbl1_tri VALUES (:new.a, :new.b, 'update');
  44. WHEN DELETING THEN
  45. INSERT INTO mv_tbl1_tri VALUES (:old.a, :old.b, 'delete');
  46. END CASE;
  47. EXCEPTION
  48. WHEN OTHERS THEN
  49. RAISE;
  50. END tri_mv;
  51. /
  53. -- 新增MV测试表数据
  54. INSERT INTO tbl1 VALUES (1, '测试数据1');
  55. INSERT INTO tbl1 VALUES (2, '测试数据2');
  56. INSERT INTO tbl1 VALUES (3, '测试数据3');
  58. COMMIT;
  61. SELECT * FROM tbl1;
  62. SELECT * FROM mlog$_tbl1; -- 生成3条“I”MV日志记录
  64. -- 手工刷新MV
  65. EXEC dbms_mview.refresh('mv_tbl1'); -- 刷新MV，触发3条insert，清空MV日志
  66. SELECT * FROM mlog$_tbl1;
  68. -- 检查trigger测试表
  69. SELECT * FROM mv_tbl1_tri;
  72. -- 修改MV测试表数据
  73. UPDATE tbl1
  74. SET a = 13
  75. WHERE a = 3;
  77. UPDATE tbl1
  78. SET a = 3
  79. WHERE a = 13;
  81. UPDATE tbl1
  82. SET a = 13
  83. WHERE a = 3;
  85. UPDATE tbl1
  86. SET a = 3
  87. WHERE a = 13;
  89. UPDATE tbl1
  90. SET a = 13
  91. WHERE a = 3;
  93. UPDATE tbl1
  94. SET a = 13
  95. WHERE a = 13;
  97. UPDATE tbl1
  98. SET a = 13
  99. WHERE a = 13;
  101. UPDATE tbl1
  102. SET b = '测试数据13'
  103. WHERE a = 13;
  105. UPDATE tbl1
  106. SET b = '测试数据3'
  107. WHERE a = 13;
  109. UPDATE tbl1
  110. SET b = '测试数据13';
  112. COMMIT;
  115. SELECT * FROM tbl1;
  116. SELECT * FROM mlog$_tbl1; -- 对每条记录的update生成2条MV日志记录：当主键发生改变时，记录一对“D”、“I”型记录，表示先删除后插入；当主键不变时，记录一对“U”、“U”型记录，分别表示新值和旧值；
  118. -- 手工刷新MV
  119. EXEC dbms_mview.refresh('mv_tbl1'); -- 刷新MV，对MV日志进行归并处理，对每一个主键，只执行其归并后结果的操作
  120. SELECT * FROM mlog$_tbl1;
  122. -- 检查trigger测试表
  123. SELECT * FROM mv_tbl1_tri; -- 对修改后的新主键13的所有后续修改只触发了1条insert；
  126. DELETE FROM tbl1; -- 生成3条“D”MV日志记录
  127. COMMIT;
  129. SELECT * FROM tbl1;
  130. SELECT * FROM mlog$_tbl1;
  132. -- 手工刷新MV
  133. EXEC dbms_mview.refresh('mv_tbl1'); -- 刷新MV，触发3条delete，清空MV日志
  134. SELECT * FROM mlog$_tbl1;
  136. -- 检查trigger测试表
  137. SELECT * FROM mv_tbl1_tri;
  140. /***
  141. 结论
  142. 1. 在MV上可以建立触发器
  143. 2. MV触发器基于刷新时间点的MV日志归并结果，在一些场景（只要记录两次刷新时间点数据的差异，不需要记录两次刷新之间的历史变化）可以简化应用处理。
  144. ***/
云推荐引擎 腾讯云测试服务 sql linux

0 人点赞