月光软件站 - 编程文档 - 数据库 - 使用dbms

数据库

本类阅读TOP10

·SQL语句导入导出大全
·Power Designer杂记
·SQL Server日期计算
·常用的oracle函数使用说明(一)
·sqlserver2000数据库置疑的解决方法
·MS SQLServer OLEDB分布式事务无法启动的一般解决方案
·SQL to Excel 的应用
·SQL语句导入导出大全
·Error:ORA-01033:ORACLE initialization or shutdown in progress错误解决
·Oracle中password file的作用及说明

→ 分类导航

VC语言	Delphi
VB语言	ASP
Perl	Java
Script	数据库
其他语言	游戏开发
文件格式	网站制作
软件工程	.NET开发

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

作者：未知来源：月光软件站 加入时间：2005-2-28　月光软件站

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

相关文章：

相关软件：