dbms_rowid包的一个用法

 

Rowid中包含了记录的详细信息,不过这串数字一般都不太看得懂,和informix中的rowid是不一样的。但是在oracle中通过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;         
/
                     
 

 

我们看一下其用法:

 

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> select rowid,service_id from service_bean where service_id=155;

ROWID              SERVICE_ID
------------------ ----------
AAAhZRAAaAAAAaKAC7        155

SQL> select get_rowid(AAAhZRAAaAAAAaMADi) rowid from dual;
select get_rowid(AAAhZRAAaAAAAaMADi) rowid from dual
                                     *
ERROR 位于第 1 行:
ORA-00923: FROM keyword not found where expected


SQL> select get_rowid(AAAhZRAAaAAAAaMADi) row_id from dual;
select get_rowid(AAAhZRAAaAAAAaMADi) row_id from dual
                 *
ERROR 位于第 1 行:
ORA-00904: "AAAHZRAAAAAAAAMADI": invalid identifier


SQL> select get_rowid(AAAhZRAAaAAAAaMADi) from dual;
select get_rowid(AAAhZRAAaAAAAaMADi) from dual
                 *
ERROR 位于第 1 行:
ORA-00904: "AAAHZRAAAAAAAAMADI": invalid identifier


SQL> select get_rowid('AAAhZRAAaAAAAaMADi') rowid from dual;
select get_rowid('AAAhZRAAaAAAAaMADi') rowid from dual
                                       *
ERROR 位于第 1 行:
ORA-00923: FROM keyword not found where expected


SQL> select get_rowid('AAAhZRAAaAAAAaMADi') row_id from dual;

ROW_ID
----------------------------------------------------------------

Object# is      :136785
Relative_fno is :26
Block number is :1676
Row number is   :226

其中,Object#是

select object_id from all_objects where object_name='SERVICE_BEAN';

Relative_fno 是该表所在数据文件的编号


 

该知识点的原文见http://blog.csdn.net/eygle/archive/2004/11/09/174061.aspx

posted on 2005-04-02 22:25  葛宏宾  阅读(1126)  评论(0编辑  收藏  举报