# 如何根据MLOG\$表的CHANGE_VECTOR\$\$找出被更新的列

SQL> DESC MLOG\$_TEST
--------------- ----------- -------- ------- --------
C1 NUMBER Y
SNAPTIME\$\$ DATE Y
DMLTYPE\$\$ VARCHAR2(1) Y
OLD_NEW\$\$ VARCHAR2(1) Y
CHANGE_VECTOR\$\$ RAW(255) Y

SQL> INSERT INTO TEST VALUES(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30);

1 row inserted

SQL> DELETE FROM TEST;

1 row deleted

SQL> SELECT * FROM MLOG\$_TEST;

C1 SNAPTIME\$\$ DMLTYPE\$\$ OLD_NEW\$\$ CHANGE_VECTOR\$\$
---------- ----------- --------- --------- ------------------------
1 4000-1-1 I N FEFFFFFF
1 4000-1-1 D O 00000000

CHANGE_VECTOR\$\$列是RAW类型，其实Oracle采用的方式就是用每个BIT位去映射一个列。

CHANGE_VECTOR\$\$每两个数字或者字母为一个单元，这些单元之间按低位在左，高位在右的规则存放。

CHANGE_VECTOR\$\$都可以的计算公式是：(2的n1次方 + 2的n2次方 + 2的n3次方 .....)

--------------将改变向量转换为从高位到低位的顺序----------------
create or replace function f_CHANGE_VECTOR(p_CHANGE_VECTOR varchar2) return varchar2 is
CHANGE_VECTOR varchar2(255);
-- 将改变向量转换为从高位到低位的顺序
begin
select replace(max(substr(sys_connect_by_path(cv, ','), 2)), ',', '') into CHANGE_VECTOR
from (select cv, rownum rn
from (select substr(p_CHANGE_VECTOR, rn * 2 + 1, 2) cv, rownum rn
from dual,
(select rownum - 1 rn
from all_objects
where rownum <= length(p_CHANGE_VECTOR) / 2) r
order by 2 desc))
connect by rn = rownum;
return(CHANGE_VECTOR);
end ;

----------------根据CHANGE_VECTOR\$\$得到被更新列的列表---------------

create or replace procedure p_get_column(p_bin number, p_table varchar2) is
l_column_id number;
l_log number;
l_bin number;
l_power number;
l_temp_bin number;
l_COLUMN_NAME varchar2(4000);
begin

if p_bin = 0 then
--只更新主键，且键值不变,则主键部分对应的向量值为0
select max(substr(sys_connect_by_path(column_name, ','), 2))
into l_COLUMN_NAME
from (select column_name, rownum rn
from (select column_name
from user_constraints uc, user_cons_columns uuc
where uc.table_name = upper(p_table)
and uc.CONSTRAINT_NAME = uuc.CONSTRAINT_NAME
order by position))
connect by rn = rownum;
dbms_output.put_line(p_table || '(' || l_COLUMN_NAME || ')');

else
/*
--分两种情况：
1、更新列包含主键，但列值不变
2、更新列不一定包含主键(如果包含的话，键值不变)

*/
--log函数有精度损失，所以需要用round来进行四舍五入，6为小数可以保证精度在一个可接受范围内
select round(log(2, p_bin), 6) into l_log from dual;
l_column_id := floor(l_log);
select column_name
into l_COLUMN_NAME
from user_tab_columns
where table_name = upper(p_table)
and column_id = l_column_id;
dbms_output.put_line(p_table || '(' || l_COLUMN_NAME || ')');
l_power := round(l_log);
--由于前面的log函数和round的函数都造成数据精度损失，所以需要重新用power纠正数据
l_temp_bin := power(2, l_power);
if p_bin <> l_temp_bin then

--通过递归求出所有的指数
l_bin := p_bin - power(2, l_power);
p_get_column(l_bin, p_table);
end if;
end if;
end p_get_column;

-----------下面用数据来检验----------------------

SQL> update test set c20=20,c23=23,c12=12;

1 row updated

SQL> update test set c10=10;

1 row updated

SQL> update test set c20=20,c23=23,c12=12,c30=30,c2=2,c5=5,c18=18,c26=26;

1 row updated

SQL> commit;

Commit complete

SQL> select * from mlog\$_test;

C1 SNAPTIME\$\$ DMLTYPE\$\$ OLD_NEW\$\$ CHANGE_VECTOR\$\$
---------- ----------- --------- --------- -----------------------------
1 4000-1-1 U U 00109000
1 4000-1-1 U U 00040000
1 4000-1-1 U U 24109444

SQL> EXEC p_get_column(TO_NUMBER(f_change_vector('00109000'),'XXXXXXXX'),'TEST')

TEST(C23)
TEST(C20)
TEST(C12)

PL/SQL procedure successfully completed

SQL> EXEC p_get_column(TO_NUMBER(f_change_vector('00040000'),'XXXXXXXX'),'TEST')

TEST(C10)

PL/SQL procedure successfully completed

SQL> EXEC p_get_column(TO_NUMBER(f_change_vector('24109444'),'XXXXXXXX'),'TEST')

TEST(C30)
TEST(C26)
TEST(C23)
TEST(C20)
TEST(C18)
TEST(C12)
TEST(C5)
TEST(C2)

PL/SQL procedure successfully completed

• 博文量
245
• 访问量
210156