使用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是作者的个人站点.你可通过[email protected]来联系作者.欢迎技术探讨交流以及链接交换.
原文出处:
http://www.eygle.com/faq/Use.dbms_rowid.Package.Get.Detail.Of.Rowid.htm
本文地址：http://com.8s8s.com/it/it20011.htm