oracle进制转换rowid转36进制字符串
oracle进制转换rowid转36进制字符串
FUNCTION FUN_ROWID_TO_36HEX(p_ID in VARCHAR2) return varchar2 is
V_OBJECT_ID INTEGER;
V_O_S VARCHAR2(7);
V_FILE_ID INTEGER;
V_F_S VARCHAR2(4);
V_BLOCK_ID INTEGER;
V_B_S VARCHAR2(6);
V_ROW_NO INTEGER;
V_R_S VARCHAR(4);
V_TIME INTEGER;
V_T_S VARCHAR(9);
v_r varchar(32);
BEGIN
select dbms_rowid.rowid_object(p_ID) object_id,
dbms_rowid.rowid_relative_fno(p_ID) file_id,
dbms_rowid.rowid_block_number(p_ID) block_id,
dbms_rowid.rowid_row_number(p_ID) num
INTO V_OBJECT_ID, V_FILE_ID, V_BLOCK_ID, V_ROW_NO
from DUAL;
select (sysdate - to_date('1970-01-01', 'yyyy-mm-dd')) * 86400000
into V_TIME
from dual;
V_O_S := FUN_INT_TO_36HEX(V_OBJECT_ID);
V_F_S := FUN_INT_TO_36HEX(V_FILE_ID);
V_B_S := FUN_INT_TO_36HEX(V_BLOCK_ID);
V_R_S := FUN_INT_TO_36HEX(V_ROW_NO);
V_T_S := FUN_INT_TO_36HEX(V_TIME);
V_O_S := lpad(V_O_S, 7, '0');
V_F_S := lpad(V_F_S, 4, '0');
V_B_S := lpad(V_B_S, 6, '0');
V_R_S := lpad(V_R_S, 4, '0');
V_T_S := lpad(V_T_S, 9, '0');
v_r := V_T_S || V_O_S || V_F_S || V_B_S || V_R_S;
v_r := lpad(v_r, 32, '0');
return v_r;
END;
FUNCTION FUN_INT_TO_36HEX(NUM IN INTEGER) RETURN VARCHAR2 IS
BEGIN
RETURN to_base(NUM, 36);
END;
function to_base(p_dec in number, p_base in number) return varchar2 is
l_str varchar2(255) default NULL;
l_num number default p_dec;
l_hex varchar2(36) default '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
begin
if (trunc(p_dec) <> p_dec OR p_dec < 0) then
raise PROGRAM_ERROR;
end if;
loop
l_str := substr(l_hex, mod(l_num, p_base) + 1, 1) || l_str;
l_num := trunc(l_num / p_base);
exit when(l_num = 0);
end loop;
return l_str;
end to_base;
function to_dec(p_str in varchar2, p_from_base in number default 16)
return number is
l_num number default 0;
l_hex varchar2(16) default '0123456789ABCDEF';
begin
for i in 1 .. length(p_str) loop
l_num := l_num * p_from_base +
instr(l_hex, upper(substr(p_str, i, 1))) - 1;
end loop;
return l_num;
end to_dec;
function to_hex(p_dec in number) return varchar2 is
begin
return to_base(p_dec, 16);
end to_hex;
function to_bin(p_dec in number) return varchar2 is
begin
return to_base(p_dec, 2);
end to_bin;
function to_oct(p_dec in number) return varchar2 is
begin
return to_base(p_dec, 8);
end to_oct;
