聽了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
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
实际上就是将十进制数转化成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;
阅读(1062) | 评论(0) | 转发(0) |