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
