使用dbms_rowid包获得rowid的详细信息
Last Updated: Sunday, 2004-11-07 12:46 Eygle
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>
本文作者:
eygle,Oracle技术关注者,来自中国最大的Oracle技术论坛itpub.
www.eygle.com是作者的个人站点.你可通过Guoqiang.Gai@gmail.com来联系作者.欢迎技术探讨交流以及链接交换.
原文出处:
http://www.eygle.com/faq/Use.dbms_rowid.Package.Get.Detail.Of.Rowid.htm
