使用dbms_rowid包获得rowid的详细信息

使用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 varchar2isls_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/tigerSQL*Plus: Release 9.2.0.4.0 - Production on Sun Nov 7 12:30:19 2004Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to:Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning optionJServer Release 9.2.0.4.0 - ProductionSQL> set echo onSQL> @f_get_rowidSQL> 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 BOSTONSQL> select rowid,a.* from dept a;ROWID DEPTNO DNAME LOC------------------ ---------- -------------- -------------AAABiPAABAAAFRSAAA 10 ACCOUNTING NEW YORKAAABiPAABAAAFRSAAB 20 RESEARCH DALLASAAABiPAABAAAFRSAAC 30 SALES CHICAGOAAABiPAABAAAFRSAAD 40 OPERATIONS BOSTONSQL> col row_id for a60SQL> select get_rowid(@#AAABiPAABAAAFRSAAA@#) row_id from dual;ROW_ID------------------------------------------------------------Object# is :6287Relative_fno is :1Block number is :21586Row number is :0SQL> select get_rowid(@#AAABiPAABAAAFRSAAB@#) row_id from dual;ROW_ID------------------------------------------------------------Object# is :6287Relative_fno is :1Block number is :21586Row number is :1SQL>

 

 

 

本文作者:
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

 

原文转自：http://www.ltesting.net