dbms_rowid包的一个用法

关注

原创最新推荐文章于 2026-04-04 09:29:19 发布 1.1k 阅读 AI 写同款· GEO 优化 ›

Oracle数据库DBMS_ROWID包及标准库函数应用详解 Oracle数据库是全球范围内应用最广泛的关系型数据库管理系统之一。它由Oracle公司开发和维护，支持庞大的数据量和并发用户，为各个行业提供高效的数据库解决方案。Oracle数据库的设计旨在保证数据的完整性、安全性和可靠性，同时提供强大的数据集成、分布式处理和多平台支持。Oracle数据库支持多种编程语言的接口，如SQL, PL/SQL等，为数据库管理和应用开发提供了灵活的手段。阅读详情

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.youkuaiyun.com/eygle/archive/2004/11/09/174061.aspx