根据rowid查找相关对象信息

日常工作中也经常会需要根据查询结果中的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提供的包也可以实现这个功能。
具体示例如下：

rowid.jpg

dbms_rowid.jpg

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25618347/viewspace-714692/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25618347/viewspace-714692/