用dbms_rowid包获得rowid的详细信息


我们知道,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>

本文作者:



相关阅读:
对联广告 flash版
教你更改windows xp操作系统序列号
网页制作学习教程 CSS Position
dl、dt、dd 标记来改造163邮箱的广告条
使用伪元素来实现图片替换的两种方法
PHP 的几个配置文件函数
实用技巧:Linux系统下查CPU个数的方法
Windows7系统技巧:Win7便笺的快捷应用
How do I change MySQL timezone?
基于jquery实现的上传图片及图片大小验证、图片预览效果代码
JavaScript窗口功能指南之创建弹出窗口
把sql数据和access数据整合成一个数据集
详细介绍ASP.NETWeb相关服务
Windows7系统直接查看系统的启动时间
快速导航

Copyright © 2016 phpStudy |