oracle添加伪列,Oracle伪列jifengtang的个人空间OSCHINA中文开源技术交流社区...

在oracle10g和下&＃xff0c;伪列包括如下内容&＃xff1a;

lHierarchical Query Pseudocolumns

分级查询是oracle提供的递归查询语法&＃xff0c;在这里不做展开。只有在分级查询下&＃xff0c;才可以使用以下伪列&＃xff1a;

1.CONNECT_BY_ISCYCLE Pseudocolumn

2.CONNECT_BY_ISLEAF Pseudocolumn

3.LEVEL Pseudocolumn

lSequence Pseudocolumns

Sequence有以下2个伪列&＃xff1a;

1.CURRVAL&＃xff1a;返回当前sequence值

2.NEXTVAL&＃xff1a;增加sequence并返回下一个值

一般用法&＃xff1a;

sequence.CURRVAL

sequence.NEXTVAL

lVersion Query Pseudocolumns

Version Query伪列只有在Flashback Version Query时才有效&＃xff0c;内容如下&＃xff1a;

1.VERSIONS_STARTSCN and VERSIONS_STARTTIME

2.VERSIONS_ENDSCN and VERSIONS_ENDTIME

3.VERSIONS_XID

4.VERSIONS_OPERATION

lCOLUMN_VALUE Pseudocolumn

lOBJECT_ID Pseudocolumn

lOBJECT_VALUE Pseudocolumn

lORA_ROWSCN Pseudocolumn

lROWID Pseudocolumn

lROWNUM Pseudocolumn

lXMLDATA Pseudocolumn

在对普通表做查询时&＃xff0c;比较常用的伪列有&＃xff1a;ORA_ROWSCN、ROWID、ROWNUM。

ORA_ROWSCN

虽然叫ORA_ROWSCN&＃xff0c;不过默认情况下&＃xff0c;查询出的该值是从数据文件块头获取的&＃xff0c;也就是说&＃xff0c;查询出的是block的最近事务的scn&＃xff0c;而不是精确到row的scn。

在创建表时&＃xff0c;可以指定ROWDEPENDENCIES来使ora_rowscn真正记录行一级的scn。

看一下ROWDEPENDENCIES的定义&＃xff1a;Specify ROWDEPENDENCIES if you want to enable row-level dependency tracking. This setting is useful primarily to allow for parallel propagation in replication environments. It increases the size of each row by 6 bytes.

从这里也可以看出一个SCN占用的空间(6 bytes)。SCN的最大值是0xffff.ffffffff&＃xff0c;共48位。包括2bytes的高位字节(SCN wrap)和4bytes的低位字节(SCN base)。

SQL>select ora_rowscn,username from t;

ORA_ROWSCN USERNAME

---------- ------------------------------

86516279 SYSTEM

86516279 SYS

86516279 OUTLN

86516279 LINCINQ

86516279 TEST

86516279 LINC

86516279 SPLEXUC

86516279 DIP

86516279 TSMSYS

86516279 WMSYS

86516279 DBSNMP

11 rows selected.

需要查询scn对于的具体时间&＃xff0c;可以用函数SCN_TO_TIMESTAMP

SQL>alter session set nls_timestamp_format&＃61;&＃39;yyyy-mm-dd hh24:mi:ss&＃39;;

Session altered.

SQL>select scn_to_timestamp(ora_rowscn),username from t;

SCN_TO_TIMESTAMP(ORA_ROWSCN)USERNAME

------------------------------ ------------------------------

2011-06-01 13:13:31SYSTEM

2011-06-01 13:13:31SYS

2011-06-01 13:13:31OUTLN

2011-06-01 13:13:31LINCINQ

2011-06-01 13:13:31TEST

2011-06-01 13:13:31LINC

2011-06-01 13:13:31SPLEXUC

2011-06-01 13:13:31DIP

2011-06-01 13:13:31TSMSYS

2011-06-01 13:13:31WMSYS

2011-06-01 13:13:31DBSNMP

11 rows selected.

ROWID

Rowid是基于64位编码的18个字符&＃xff0c;格式如下&＃xff1a;

data_object_idfile_idblock_numberrow_number

OOOOOOFFFBBBBBBRRR

SQL>select rowid,username from t;

ROWIDUSERNAME

------------------ ------------------------------

AAAE0LAAOAABQqMAAA SYSTEM

AAAE0LAAOAABQqMAAB SYS

AAAE0LAAOAABQqMAAC OUTLN

AAAE0LAAOAABQqMAAD LINCINQ

AAAE0LAAOAABQqMAAE TEST

AAAE0LAAOAABQqMAAF LINC

AAAE0LAAOAABQqMAAG SPLEXUC

AAAE0LAAOAABQqMAAH DIP

AAAE0LAAOAABQqMAAI TSMSYS

AAAE0LAAOAABQqMAAJ WMSYS

AAAE0LAAOAABQqMAAK DBSNMP

11 rows selected.

通过dbms_rowid包&＃xff0c;我们可以获得rowid对应的记录详细信息。

SQL>select dbms_rowid.rowid_object(&＃39;&1&＃39;) data_object_id#,

2dbms_rowid.rowid_relative_fno(&＃39;&1&＃39;) rfile#,

3dbms_rowid.rowid_block_number(&＃39;&1&＃39;) block#,

4dbms_rowid.rowid_row_number(&＃39;&1&＃39;) row# from dual;

Enter value for 1: AAAE0LAAOAABQqMAAA

old1: select dbms_rowid.rowid_object(&＃39;&1&＃39;) data_object_id#,

new1: select dbms_rowid.rowid_object(&＃39;AAAE0LAAOAABQqMAAA&＃39;) data_object_id#,

Enter value for 1: AAAE0LAAOAABQqMAAA

old2:dbms_rowid.rowid_relative_fno(&＃39;&1&＃39;) rfile#,

new2:dbms_rowid.rowid_relative_fno(&＃39;AAAE0LAAOAABQqMAAA&＃39;) rfile#,

Enter value for 1: AAAE0LAAOAABQqMAAA

old3:dbms_rowid.rowid_block_number(&＃39;&1&＃39;) block#,

new3:dbms_rowid.rowid_block_number(&＃39;AAAE0LAAOAABQqMAAA&＃39;) block#,

Enter value for 1: AAAE0LAAOAABQqMAAA

old4:dbms_rowid.rowid_row_number(&＃39;&1&＃39;) row# from dual

new4:dbms_rowid.rowid_row_number(&＃39;AAAE0LAAOAABQqMAAA&＃39;) row# from dual

DATA_OBJECT_ID#RFILE#BLOCK#ROW#

--------------- ---------- ---------- ----------

19723143303800

ROWNUM

在对表的查询中&＃xff0c;每返回一条记录&＃xff0c;rownum伪列就返回一个数字&＃xff0c;代表查询返回的行的编号。

SQL>select rownum,username from t;

ROWNUM USERNAME

---------- ------------------------------

1 SYSTEM

2 SYS

3 OUTLN

4 LINCINQ

5 TEST

6 LINC

7 SPLEXUC

8 DIP

9 TSMSYS

10 WMSYS

11 DBSNMP

11 rows selected.

从下面的例子可以看出&＃xff0c;rownum返回的是查询过程中返回记录的顺序&＃xff0c;并不是查询结果的序列号。

SQL>select rownum,username from t order by username;

ROWNUM USERNAME

---------- ------------------------------

11 DBSNMP

8 DIP

6 LINC

4 LINCINQ

3 OUTLN

7 SPLEXUC

2 SYS

1 SYSTEM

5 TEST

9 TSMSYS

10 WMSYS

11 rows selected.

SQL>select rownum,username from (select username from t order by username);

ROWNUM USERNAME

---------- ------------------------------

1 DBSNMP

2 DIP

3 LINC

4 LINCINQ

5 OUTLN

6 SPLEXUC

7 SYS

8 SYSTEM

9 TEST

10 TSMSYS

11 WMSYS

11 rows selected.