首页 > Linux操作系统 > Linux操作系统 > 获取物化视图字段的修改矢量(三)
当表建立了物化视图日志之后,表的DML修改会被记录到物化视图日志中,而物化视图日志则包含了一个修改矢量,来记录哪个列被修改。
这篇文章封装了一个函数来完成这个功能。
获取物化视图字段的修改矢量(一):http://yangtingkun.itpub.net/post/468/495176
获取物化视图字段的修改矢量(二):http://yangtingkun.itpub.net/post/468/495560
虽然利用SQL就可以得到物化视图字段的修改矢量,但是如果每次获取都要执行SQL,有点过于复杂,而且容易出错。这里将SQL语句封装为一个函数,每次需要获取修改变量的时候只需要调用函数就可以了。
还是上一篇文章中的例子:
SQL> CREATE TABLE T_PERSON
2 (ID NUMBER PRIMARY KEY,
3 NAME VARCHAR2(30),
4 ADDR01 VARCHAR2(30),
5 ADDR02 VARCHAR2(30),
6 ADDR03 VARCHAR2(30),
7 ADDR04 VARCHAR2(30),
8 ADDR05 VARCHAR2(30),
9 ADDR06 VARCHAR2(30),
10 ADDR07 VARCHAR2(30),
11 ADDR08 VARCHAR2(30),
12 ADDR09 VARCHAR2(30),
13 ADDR10 VARCHAR2(30),
14 ADDR11 VARCHAR2(30),
15 ADDR12 VARCHAR2(30),
16 ADDR13 VARCHAR2(30),
17 ADDR14 VARCHAR2(30),
18 ADDR15 VARCHAR2(30),
19 ADDR16 VARCHAR2(30),
20 ADDR17 VARCHAR2(30),
21 ADDR18 VARCHAR2(30),
22 AGE NUMBER);
Table created.
SQL> CREATE MATERIALIZED VIEW LOG ON T_PERSON;
Materialized view log created.
下面创建函数:
SQL> CREATE OR REPLACE FUNCTION F_GET_VECTOR
2 (
3 P_OWNER IN VARCHAR2,
4 P_TABLENAME IN VARCHAR2,
5 P_COLUMNNAME IN VARCHAR2
6 ) RETURN RAW
7 AS
8 V_X_NUMBER NUMBER;
9 V_OBJECT_ID NUMBER;
10 V_COLUMNNAME VARCHAR2(32767) := P_COLUMNNAME || ',';
11 V_STR VARCHAR(32767);
12 V_VEC RAW(255);
13 BEGIN
14 SELECT OBJECT_ID
15 INTO V_OBJECT_ID
16 FROM DBA_OBJECTS
17 WHERE WNER = P_OWNER
18 AND OBJECT_NAME = P_TABLENAME
19 AND OBJECT_TYPE = 'TABLE';
20
21 SELECT FLOOR(LOG(256, POWER(2, COUNT(*)))) + 1
22 INTO V_X_NUMBER
23 FROM SYS.COL$
24 WHERE OBJ# = V_OBJECT_ID;
25
26 V_STR := 'SELECT REVERSE(CAST(LTRIM(TO_CHAR(SUM(POWER(2, INTCOL#)), RPAD(''0X'', '
27 || V_X_NUMBER || ' * 2, ''X''))) AS RAW(255))) FROM SYS.COL$ WHERE OBJ# = '
28 || V_OBJECT_ID || ' AND NAME IN (''';
29 WHILE(INSTR(V_COLUMNNAME, ',') > 0) LOOP
30 V_STR := V_STR || TRIM(SUBSTR(V_COLUMNNAME, 1, INSTR(V_COLUMNNAME, ',') - 1)) || ''', ''';
31 V_COLUMNNAME := SUBSTR(V_COLUMNNAME, INSTR(V_COLUMNNAME, ',') + 1);
32 END LOOP;
33 V_STR := V_STR || ''')';
34
35 EXECUTE IMMEDIATE V_STR INTO V_VEC;
36 RETURN V_VEC;
37 END;
38 /
Function created.
SQL> SELECT F_GET_VECTOR('TEST', 'T_PERSON', 'AGE, ADDR01, NAME') FROM DUAL;
F_GET_VECTOR('TEST','T_PERSON','AGE,ADDR01,NAME')
-------------------------------------------------
0C0020
对比物化视图日志中记载的变化矢量:
SQL> INSERT INTO T_PERSON
2 (ID, NAME, ADDR01, AGE)
3 VALUES (1, 'A', 'A', 10);
1 row created.
SQL> UPDATE T_PERSON
2 SET NAME = 'A', ADDR01 = 'B', AGE = 20
3 WHERE ID = 1;
1 row updated.
SQL> SELECT *
2 FROM MLOG$_T_PERSON;
ID SNAPTIME$$ D O CHANGE_VECTOR$$
---------- -------------- - - ------------------------------
1 01-1月 -00 I N FEFFFF
1 01-1月 -00 U U 0C0020
来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/4227/viewspace-624719/ ,如需转载,请注明出处,否则将追究法律责任。