使用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
>
> SQLPlus: 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