Oracle综合技巧:灵活运用dbms_rowid包获得rowid地详细信息[库库中文网]-数据库类教程 QQGB.com

我们知道，Rowid中包含了记录的详细信息，通过dbms_rowid包可以很轻松地获得这些信息.本文将通过一个自定义函数详细介绍这个package的使用。

create or replace function get_rowid

(l_rowid in varchar2)

return varchar2

is

ls_my_rowid  varchar2(200);          

rowid_type  number;          

object_number  number;          

relative_fno  number;          

block_number  number;          

row_number  number;  

begin

dbms_rowid.rowid_info(l_rowid,rowid_type,

object_number,relative_fno, block_number, row_number);          

ls_my_rowid := 'Object# is      :'||to_char(object_number)||chr(10)||

'Relative_fno is :'||to_char(relative_fno)||chr(10)||

'Block number is :'||to_char(block_number)||chr(10)||

'Row number is   :'||to_char(row_number);

return ls_my_rowid ;

end;          

/

其它的用法:

[oracle@jumper tools]$ sqlplus scott/tiger



SQL*Plus: Release 9.2.0.4.0 - Production on Sun Nov 7 12:30:19 2004



Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.





Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the Partitioning option

JServer Release 9.2.0.4.0 - Production



SQL> set echo on

SQL> @f_get_rowid

SQL> create or replace function get_rowid

2  (l_rowid in varchar2)

3  return varchar2

4  is

5  ls_my_rowid        varchar2(200);

6  rowid_type number;

7  object_number      number;

8  relative_fno       number;

9  block_number       number;

10  row_number number;

11  begin

12   dbms_rowid.rowid_info(l_rowid,rowid_type,



object_number,relative_fno, block_number, row_number);



13   ls_my_rowid := 'Object# is :'||to_char(object_number)||chr(10)||



14   'Relative_fno is :'||to_char(relative_fno)||chr(10)||



15    'Block number is :'||to_char(block_number)||chr(10)||

16    'Row number is   :'||to_char(row_number);

17   return ls_my_rowid ;

18  end;

19  /



Function created.



SQL> 

SQL> select * from dept;



    DEPTNO DNAME          LOC

---------- -------------- -------------

10 ACCOUNTING     NEW YORK

20 RESEARCH       DALLAS

30 SALES          CHICAGO

40 OPERATIONS     BOSTON



SQL> select rowid,a.* from dept a;



ROWID                  DEPTNO DNAME          LOC

------------------ ---------- -------------- -------------

AAABiPAABAAAFRSAAA         10 ACCOUNTING     NEW YORK

AAABiPAABAAAFRSAAB         20 RESEARCH       DALLAS

AAABiPAABAAAFRSAAC         30 SALES          CHICAGO

AAABiPAABAAAFRSAAD         40 OPERATIONS     BOSTON





SQL> col row_id for a60

SQL> select get_rowid('AAABiPAABAAAFRSAAA') row_id from dual;



ROW_ID

------------------------------------------------------------

Object# is      :6287

Relative_fno is :1

Block number is :21586

Row number is   :0





SQL> select get_rowid('AAABiPAABAAAFRSAAB') row_id from dual;



ROW_ID

------------------------------------------------------------

Object# is      :6287

Relative_fno is :1

Block number is :21586

Row number is   :1

SQL>