oracle rowid 計算陽春版

聽了warehouse課程﹐rowid手動計算麻煩﹐寫個過程來計算﹐清晰明了。

目的不是如何計算出data_object_id,file_number,及block_number,而是理解rowid的組成及如何計算。

oracle提供了package dbms_rowid來計算下述內容。

 

CREATE OR REPLACE PROCEDURE p_rowid(rid IN VARCHAR2) IS
    /**rowid長度為18位﹐64進制來表示﹕****
     對像編號(6位) + 文件編號(3位) + 塊號(6位) + 行號(3位)
     A-Z   0  - 25
     a-z   26 - 51
     0-9   52 - 61
     +     62
     /     63
    **********/
    v_row VARCHAR2(64):='ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/';
    v_flag VARCHAR2(1);
    FUNCTION f_jx(dd IN VARCHAR2) RETURN NUMBER IS
        v_slen NUMBER;      --字串長度
        v_num  NUMBER;      --計算結果
        v_pos  NUMBER;      --所在位置﹐即所代表10進制數﹐當然要減1
        BEGIN
            v_slen :=LENGTH(dd);
            v_num:=0;
            FOR i IN 1..v_slen LOOP
               v_pos :=INSTR(v_row,SUBSTR(dd,i,1)) -1;
               v_num :=v_num+v_pos*POWER(64,(v_slen-i));
            END LOOP;
            RETURN v_num;
        END f_jx;
    BEGIN
       v_flag :='Y';
       IF LENGTH(rid)=18 THEN
           FOR i IN 1..18 LOOP
               IF INSTR(v_row,SUBSTR(rid,i,1))<=0 THEN
                   v_flag :='N';
               END IF;
           END LOOP;
       ELSE
           v_flag :='N';
       END IF;
       IF v_flag='Y' THEN
           DBMS_OUTPUT.PUT_LINE('DATA_OBJECT_ID:= '|| F_JX(SUBSTR(rid,1,6)));
           DBMS_OUTPUT.PUT_LINE('RELATIVE_FILE_NUMBER:= '|| F_JX(SUBSTR(rid,7,3)));
           DBMS_OUTPUT.PUT_LINE('BLOCK_NUMBER:= '  || F_JX(SUBSTR(rid,10,6)));
           DBMS_OUTPUT.PUT_LINE('ROW_NUMBER:= '    || F_JX(SUBSTR(rid,16,3)));
       ELSE
           DBMS_OUTPUT.PUT_LINE('傳入格式不符何rowid');
       END IF;
    END p_rowid;

 

利用oracle dbms_rowid﹐改寫warehouse的script

http://www.itpub.net/thread-912812-1-1.html

CREATE OR REPLACE PROCEDURE P_ROWID2(dd IN VARCHAR2) IS
    v_rowid_type int;
    v_object_number number;
    v_relative_fno int;
    v_block_number number;
    v_row_number int;
  BEGIN
    dbms_rowid.rowid_info(dd,v_rowid_type,v_object_number,v_relative_fno,v_block_number,v_row_number);
    dbms_output.put_line('ROWID_TYPE:'||v_rowid_type||' '||'備注﹕1表示rowid是extended，0表示rowid是restrictedv_rowid_type');
    dbms_output.put_line('DATA_OBJECT_ID:'||v_object_number );
    dbms_output.put_line('RELATIVE_FNO:'||v_relative_fno );
    dbms_output.put_line('BLOCK_NUMBER:'||v_block_number );
    dbms_output.put_line('ROW_NUMBER:'||v_row_number );
 END P_ROWID2;

 

 

如何从obj#,rfile#,block#,row#计算得到rowid

參考﹕http://www.cuug.com/xueyuanzhuanqu/jishuwenzhang/201110/jishuwenzhang-1147.html

 实际上就是将十进制数转化成64进制数，当然，从二进制转化的规则比较简单点。

将二进制数从右到左，6个bit一组，然后将这6个bit组转成10进制数，就是A~Z a~z 0~9 + /这64个字符的位置（从0开始），替换成base64的字符即可。

rowid=AAAX4DAAEAAABBVAAA 來反推﹕

EXECUTE  P_ROWID('AAAX4DAAEAAABBVAAA');
DATA_OBJECT_ID:= 97795
RELATIVE_FILE_NUMBER:= 4
BLOCK_NUMBER:= 4181
ROW_NUMBER:= 0

如何从obj#,rfile#,block#,row#计算得到rowid

根據 97795 ﹑4﹑181﹑0來計算

DATA_OBJECT_ID:= 97795 =10111 111000 000011 =23 56 3 = X 4 D =AAAX4D

RELATIVE_FILE_NUMBER:= 4 =100= 4 = E =AAE

BLOCK_NUMBER:= 4181=000001 000001 010101=1 1 21 = B B V =AAABBV

ROW_NUMBER:= 0 = 0 =A =AAA

整合﹕ AAAX4D AAE AAABBV AAA

 

 

十進制轉換成二進制﹕原來存的﹐找不到是誰寫的了﹐自己也懶得去想了。 

CREATE OR REPLACE FUNCTION NUMBER_2_BIT(V_NUM NUMBER) RETURN VARCHAR IS
V_RTN VARCHAR(2000);
V_N1 NUMBER;
V_N2 NUMBER;
BEGIN
V_N1 := ABS(V_NUM);
--如果为正数
IF SIGN(V_NUM) > 0 THEN
LOOP
V_N2 := MOD(V_N1, 2);
V_N1 := ABS(TRUNC(V_N1 / 2));
V_RTN := TO_CHAR(V_N2) || V_RTN;
EXIT WHEN V_N1 = 0;
END LOOP;

--dbms_output.put_line('正数结果'||V_RTN);

--补全32位高位0
SELECT lpad(V_RTN,32,0)
INTO V_RTN
FROM dual;

--dbms_output.put_line('正数补全结果'||V_RTN);

ELSE
--转换为二进制同时按位取反
LOOP
V_N2 := MOD(V_N1, 2);
IF V_N2 = 1 THEN
V_N2 := 0;
ELSIF V_N2 = 0 THEN
V_N2 := 1;
END IF;
V_N1 := ABS(TRUNC(V_N1 / 2));
V_RTN := TO_CHAR(V_N2) || V_RTN;
EXIT WHEN V_N1 = 0;
END LOOP;

--dbms_output.put_line('负数结果'||V_RTN);

--补全32位高位1
SELECT lpad(V_RTN,32,1)
INTO V_RTN
FROM dual;

--dbms_output.put_line('负数补全1结果'||V_RTN);

--二进制转换为10机制，同时+1
SELECT SUM(data1) + 1
INTO V_N1
FROM (SELECT substr(V_RTN, rownum, 1) * power(2, length(V_RTN) - rownum) data1
FROM dual
CONNECT BY rownum <= length(V_RTN));

-- dbms_output.put_line('转换为十进制数结果'||V_RTN);

----转换为二进制
LOOP
V_N2 := MOD(V_N1, 2);
V_N1 := ABS(TRUNC(V_N1 / 2));
V_RTN := TO_CHAR(V_N2) || V_RTN;
EXIT WHEN V_N1 = 0;
END LOOP;

--dbms_output.put_line('负数转换结果'||V_RTN);

--补全32位高位0
SELECT lpad(V_RTN,32,0)
INTO V_RTN
FROM dual;

--dbms_output.put_line('负数补全0结果'||V_RTN);

END IF;

RETURN V_RTN;
END;