日常工作中也经常会需要根据查询结果中的rowid来找到对应的对象编号、所在文件号、块号等信息,今天也不怎么忙,就写了2个函数,来实现这个功能,具体代码如下:
1.转换rowid的函数:
create or replace function FUN_GETNUM(RID in VARCHAR2) return number is
Result NUMBER;
begin
IF rid = 'A' THEN
RESULT := 0;
ELSIF rid = 'B' THEN
RESULT := 1;
ELSIF rid = 'C' THEN
RESULT := 2;
ELSIF rid = 'D' THEN
RESULT := 3;
ELSIF rid = 'E' THEN
RESULT := 4;
ELSIF rid = 'F' THEN
RESULT := 5;
ELSIF rid = 'G' THEN
RESULT := 6;
ELSIF rid = 'H' THEN
RESULT := 7;
ELSIF rid = 'I' THEN
RESULT := 8;
ELSIF rid = 'J' THEN
RESULT := 9;
ELSIF rid = 'K' THEN
RESULT := 10;
ELSIF rid = 'L' THEN
RESULT := 11;
ELSIF rid = 'M' THEN
RESULT := 12;
ELSIF rid = 'N' THEN
RESULT := 13;
ELSIF rid = 'O' THEN
RESULT := 14;
ELSIF rid = 'P' THEN
RESULT := 15;
ELSIF rid = 'Q' THEN
RESULT := 16;
ELSIF rid = 'R' THEN
RESULT := 17;
ELSIF rid = 'S' THEN
RESULT := 18;
ELSIF rid = 'T' THEN
RESULT := 19;
ELSIF rid = 'U' THEN
RESULT := 20;
ELSIF rid = 'V' THEN
RESULT := 21;
ELSIF rid = 'W' THEN
RESULT := 22;
ELSIF rid = 'X' THEN
RESULT := 23;
ELSIF rid = 'Y' THEN
RESULT := 24;
ELSIF rid = 'Z' THEN
RESULT := 25;
ELSIF rid = 'a' THEN
RESULT := 26;
ELSIF rid = 'b' THEN
RESULT := 27;
ELSIF rid = 'c' THEN
RESULT := 28;
ELSIF rid = 'd' THEN
RESULT := 29;
ELSIF rid = 'e' THEN
RESULT := 30;
ELSIF rid = 'f' THEN
RESULT := 31;
ELSIF rid = 'g' THEN
RESULT := 32;
ELSIF rid = 'h' THEN
RESULT := 33;
ELSIF rid = 'i' THEN
RESULT := 34;
ELSIF rid = 'j' THEN
RESULT := 35;
ELSIF rid = 'k' THEN
RESULT := 36;
ELSIF rid = 'l' THEN
RESULT := 37;
ELSIF rid = 'm' THEN
RESULT := 38;
ELSIF rid = 'n' THEN
RESULT := 39;
ELSIF rid = 'o' THEN
RESULT := 40;
ELSIF rid = 'p' THEN
RESULT := 41;
ELSIF rid = 'q' THEN
RESULT := 42;
ELSIF rid = 'r' THEN
RESULT := 43;
ELSIF rid = 's' THEN
RESULT := 44;
ELSIF rid = 't' THEN
RESULT := 45;
ELSIF rid = 'u' THEN
RESULT := 46;
ELSIF rid = 'v' THEN
RESULT := 47;
ELSIF rid = 'w' THEN
RESULT := 48;
ELSIF rid = 'x' THEN
RESULT := 49;
ELSIF rid = 'y' THEN
RESULT := 50;
ELSIF rid = 'z' THEN
RESULT := 51;
ELSIF rid = '0' THEN
RESULT := 52;
ELSIF rid = '1' THEN
RESULT := 53;
ELSIF rid = '2' THEN
RESULT := 54;
ELSIF rid = '3' THEN
RESULT := 55;
ELSIF rid = '4' THEN
RESULT := 56;
ELSIF rid = '5' THEN
RESULT := 57;
ELSIF rid = '6' THEN
RESULT := 58;
ELSIF rid = '7' THEN
RESULT := 59;
ELSIF rid = '8' THEN
RESULT := 60;
ELSIF rid = '9' THEN
RESULT := 61;
ELSIF rid = '+' THEN
RESULT := 62;
ELSIF rid = '/' THEN
RESULT := 63;
END IF;
return(Result);
end FUN_GETNUM;
2. 根据rowid实现相关功能的函数:
create or replace function fun_getinfo(vrowid varchar2) return varchar2 is
vChar varchar2(20);
vobjid number;
vfileid number;
vblockid number;
vblknum number;
vobname varchar2(200);
result varchar2(1000);
begin
vChar := vrowid;
select FUN_GETNUM(substr(vchar, 1, 1)) * power(64, 5) +
FUN_GETNUM(substr(vchar, 2, 1)) * power(64, 4) +
FUN_GETNUM(substr(vchar, 3, 1)) * power(64, 3) +
FUN_GETNUM(substr(vchar, 4, 1)) * power(64, 2) +
FUN_GETNUM(substr(vchar, 5, 1)) * power(64, 1) +
FUN_GETNUM(substr(vchar, 6, 1)) * power(64, 0)
into vobjid
from dual;
select a.owner || '.' || a.object_name
into vobname
from dba_objects a
where a.object_id = vobjid;
select FUN_GETNUM(substr(vchar, 7, 1)) * power(64, 2) +
FUN_GETNUM(substr(vchar, 8, 1)) * power(64, 1) +
FUN_GETNUM(substr(vchar, 9, 1)) * power(64, 0)
into vfileid
from dual;
select FUN_GETNUM(substr(vchar, 10, 1)) * power(64, 5) +
FUN_GETNUM(substr(vchar, 11, 1)) * power(64, 4) +
FUN_GETNUM(substr(vchar, 12, 1)) * power(64, 3) +
FUN_GETNUM(substr(vchar, 13, 1)) * power(64, 2) +
FUN_GETNUM(substr(vchar, 14, 1)) * power(64, 1) +
FUN_GETNUM(substr(vchar, 15, 1)) * power(64, 0)
into vblockid
from dual;
select FUN_GETNUM(substr(vchar, 16, 1)) * power(64, 2) +
FUN_GETNUM(substr(vchar, 17, 1)) * power(64, 1) +
FUN_GETNUM(substr(vchar, 18, 1)) * power(64, 0)
into vblknum
from dual;
result := 'object_id:' || vobjid || ',' || 'object_name:' || vobname || ',' ||
'file#:' || vfileid || ',' || 'block_id:' || vblockid || ',' ||
'所在块第' || vblknum || '行';
return(result);
end;
当然,通过oracle提供的包也可以实现这个功能。
具体示例如下:
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25618347/viewspace-714692/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25618347/viewspace-714692/