rowid含义的自我理解及实现

说明：
8i后的rowid组成结构如下：OOOOOO FFF BBBBBB RRR
其中O是指data_object_id, F是指rfile#, B是指block_id, R是指rowid
共有10个字节(10*8=80bit)组成：32bit+10bit rfile#+22bit+16bit
关于这些组成我还不是很理解，待后面继续...

 

--创建基本函数
--其中w_64_trans是64进制的字典表
--字典表说明：
--A-Z    <-->  0-25 (26)
--a-z     <--> 26-51(26)
--0-9    <--> 52-61(10)
--+/       <--> 62-63(2)
构造字典表：
create table w_64_trans(
  c64 varchar2(2),
  i10 integer
)
建立字典数据：
insert into w_64_trans
select chr(ascii('A') + rownum - 1), ascii('A') + rownum - 66
from user_objects
where rownum <= 26
union all
select chr(ascii('a') + rownum - 1), ascii('a') + rownum - 72
from user_objects
where rownum <= 26
union all
select chr(ascii('0') + rownum - 1), ascii('0') + rownum + 3
from user_objects
where rownum <= 10
union all
select chr(ascii('+') + rownum - 1), ascii('+') + rownum + 18
from user_objects
where rownum <= 1
union all
select chr(ascii('/') + rownum - 1), ascii('/') + rownum + 15
from user_objects
where rownum <= 1

---创建64->10的转换函数
create or replace function w_func_getrowinfo(v_rowid varchar2)
return number as
  i integer := 1;
  v_temp number(32) := 0;
  v_value number(32) := 0;
  v_rowlen number(32) := length(v_rowid);
begin
  while i <= v_rowlen loop
    select w.i10 into v_temp from w_64_trans w where w.c64 = substr(v_rowid,i,1);
    dbms_output.put(v_value || ' + ');
    v_value := v_value + v_temp * power(64, v_rowlen - i);
    dbms_output.put_line(v_temp || '*power(' || 64 || ',' || (v_rowlen - i) || ')=' || v_value);
    i := i + 1;
  end loop;
  return v_value;
end w_func_getrowinfo;

--创建包头
create or replace package w_pack_getrowidinfo is
  function file_id(v_rowid varchar2) return number;
  function data_object_id(v_rowid varchar2) return number;
  function block_id(v_rowid varchar2) return number;
  function row_id(v_rowid varchar2) return number;
end;

--创建包体，用户获取rowid的各个信息
create or replace package body w_pack_getrowidinfo is
  function data_object_id(v_rowid varchar2) return number is
  begin
    return w_func_getrowinfo(substr(v_rowid, 1, 6));
  end data_object_id;
 
  function file_id(v_rowid varchar2) return number is
  begin
      return w_func_getrowinfo(substr(v_rowid, 7, 3));
  end file_id;
 
  function block_id(v_rowid varchar2) return number is
  begin
      return w_func_getrowinfo(substr(v_rowid, 10, 6));
  end block_id;
 
  function row_id(v_rowid varchar2) return number is
  begin
      return w_func_getrowinfo(substr(v_rowid, 16, 3));
  end row_id;
end w_pack_getrowidinfo;

 

其实Oracle本身已经有了更好的功能包来实现我的这个功能：dbms_rowid。
自己写的目的是更好的了解rowid的组成。