Oracle几个常用的伪列(Pseudo-column)使用 博客分类： DB-Oracle oracle伪列
https://blog.csdn.net/u012047933/article/details/39233657
Oracle的Pseudo-column,常被翻译成伪列,也有人翻译成虚拟字段。常常有我们的数据操作中带来一些意想不到的效果.
常用到的主要有
sysdate, systimestamp,RowNum ,RowID, CURRVAL, NEXTVAL,UID, USER,Level,ORA_ROWSCN
1.SYSDATE, SYSTIMESTAMP,
SYSDATE 返回当前的系统时间。SYSTIMESTAMP 以TIMESTAMP WITH TIMEZONE 返回当前的日期和时间 sysdate是取机器的时间的, 而systimestamp是显示数据库的时区所在的时间
Select SysDate from dual
获取当前服务器时间
结果: 2009-07-14 8:39:28
select sysdate,sysdate - interval '1' MINUTE from dual
获取当前服务器减去1分钟时间
结果: 1 2009-07-14 8:40:28 2009-07-14 8:39:28
select sysdate,sysdate - interval '1' hour from dual
获取当前服务器当前时间减去1小时的时间
结果: 1 2009-07-14 8:40:53 2009-07-14 7:40:53
select sysdate,sysdate - interval '1' day from dual
获取当前服务器时间减去1天的时间
结果: 1 2009-07-14 8:41:19 2009-07-13 8:41:19
select sysdate,sysdate - interval '1' month from dual
获取当前服务器时间减去1月的时间
结果: 1 2009-07-14 8:41:38 2009-06-14 8:41:38
select sysdate,sysdate - interval '1' year from dual
当前服务器时间减去7年的时间
结果: 1 2009-07-14 8:41:50 2008-07-14 8:41:50
select sysdate,sysdate - 7*interval '1' hour from dual
根据时间间隔乘以一个数字
结果: 1 2009-07-14 8:42:11 2009-07-14 1:42:11 (间隔7小时)
上面的结果也可以通过日期的四则运算获取对应的时间
Select sysdate,sysdate-1 from dual;
系统默认减一为当前服务器减去一天的时间
结果: 1 2009-07-14 8:41:19 2009-07-13 8:41:19
Select sysdate,sysdate-1/24 from dual;
系统默认减1/24为当前服务器减去一小时的时间
结果: 1 2009-07-14 8:40:53 2009-07-14 7:40:53
Select sysdate,sysdate-1/24/60 from dual;
系统默认减1/24/60为当前服务器减去一分钟的时间
结果: 1 2009-07-14 8:40:28 2009-07-14 8:39:28
Select sysdate-1/24/60/60 from dual;
系统默认减1/24/6/600为当前服务器减去一秒的时间
结果: 1 2009-07-14 8:41:19 2009-07-13 8:40:19
其它日期格式化不再叙述
(2)SYSTIMESTAMP是一个函数,返回当前系统的日期
select systimestamp from dual;
获取当前数据库时间
结果: 1 14-7月 -09 08.38.29.406000 上午 +08:00
select to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS') from dual;
根据格式显示当前日期
结果:1 2009-07-14 08:52:45
select to_char(systimestamp, 'SSSS.FF') from dual;
转化成对应字符类型获取当前时间的毫秒
结果: 1 3939.125000
Select
EXTRACT(year FROM systimestamp) EY,
EXTRACT(month FROM systimestamp) EM,
EXTRACT(day FROM systimestamp) ED,
EXTRACT(hour FROM systimestamp) EH,
EXTRACT(minute FROM systimestamp) EM,
EXTRACT(second FROM systimestamp) ES,
EXTRACT(timezone_hour FROM systimestamp) TH,
EXTRACT(timezone_minute FROM systimestamp) TM,
EXTRACT(timezone_region FROM systimestamp) TR,
EXTRACT(timezone_abbr FROM systimestamp) TA
FROM dual;
结果: 1 2009 7 14 0 47 38.546 8 0 UNKNOWN UNK
2.RowNum ,RowID
ROWNUM是一个序列，是oracle数据库从数据文件或缓冲区中读取数据的顺序。它取得第一条记录则rownum值为1，第二条为2，依次类推。如果你用>,>=,=,between...and这些条件，因为从缓冲区或数据文件中得到的第一条记录的rownum为1，则被删除，接着取下条，可是它的rownum还是1，又被删除，依次类推，便没有了数据. rownum 是对符合条件结果的序列号。它总是从1开始排起的。所以你选出的结果不可能没有1，而有其他大于1的值
rowid 可以说是物理存在的，表示记录在表空间中的唯一位置ID，在DB中唯一。只要记录没被搬动过，rowid是不变的。rowid 相对于表来说又像表中的一般列，所以以 rowid 为条件就不会有 rownum那些情况发生。
获取前10条记录
select rowid,rownum,grade from ubs_grade where rownum <=10;
获取表中3-5的记录:
select * from (
select rownum r,grade from ubs_grade
where rownum <= 5
order by grade )
where r > 2
结果:
1 3 b
2 4 b
3 5 c
select rowid,rownum,grade from ubs_grade where rownum between 1 and 10;
结果:
1 AAANCoAAFAAAADYAAA 1 a
2 AAANCoAAFAAAADYAAB 2 a
3 AAANCoAAFAAAADYAAC 3 b
4 AAANCoAAFAAAADYAAD 4 b
5 AAANCoAAFAAAADYAAE 5 c
3 CURRVAL, NEXTVAL 要与Sequence一起使用.
必须用以 sequence.NEXTVAL 或 sequence.CURRVAL 格式驻留在同一个数据库中的序列名称（或同义词）来限定 NEXTVAL 或 CURRVAL。第一次NEXTVAL返回的是初始值；随后的NEXTVAL会自动增加你定义的INCREMENT BY值，然后返回增加后的值。CURRVAL 总是返回当前SEQUENCE的值，但是在第一次NEXTVAL初始化之后才能使用CURRVAL，否则会出错。一次NEXTVAL会增加一次SEQUENCE的值，所以如果你在同一个语句里面使用多个NEXTVAL，其值就是不一样的.
先创建序列号
Create SEQUENCE ubs_sequence
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXVALUE -- 不设置最大值
NOCYCLE -- 一直累加，不循环
CACHE 10;
一旦定义了ubs_sequence，你就可以用CURRVAL，NEXTVAL
CURRVAL=返回 sequence的当前值
NEXTVAL=增加sequence的值，然后返回 sequence 值
比如：
ubs_sequence.CURRVAL
ubs_sequence.NEXTVAL
3.UID, USER,
返回数据库的用户的用户的ID,没有多大的实用性
Select uid, user FROM dual;
结果:1 55 DEZAI
4.Level
level仅仅用于在对表执行层次树遍历的select语句中
5. orA_ROWSCN
orACLE会在表中记录每条记录的最后的变化的SCN，在执行锁定(select … for update)，或者增量数据抽取的时候有用.默认情况下，每行记录的ORA_ROWSCN是基于Block的，除非在建表的时候执行开启行级跟踪(create table … rowdependencies)。
默认的情况下，每个块中所有的记录的ORA_ROWSCN都是相同的，当块中任意一条记录发生改变的情况下，块中所有记录的ORA_ROWSCN都会变化为最新值，ORA_ROWSCN的最小粒度是块。
查看与每行关联的SCN
select username,ora_rowscn from ubs_user;
结果:
1 nicky 1874974
2 ddddd 1341863
3 Dezai 1341863
4 Eastjazz 1341863
5 Susu 1341863
6 ChinaBoy 1341863
查看每行最新的事务时间
select username,scn_to_timestamp(ora_rowscn) from ubs_user;
其它
1.SQL> select sys_guid() from dual;
结果:
SYS_GUID()
--------------------------------
45690EA57A5F47FABE6F77C17980ABC0
2. 取得本机IP和服务器IP：
SQL> select sys_context('userenv','ip_address'), utl_inaddr.get_host_address from dual;
结果:
SYS_CONTEXT('USERENV','IP_ADDR GET_HOST_ADDRESS
----------------------------------------------------------------------
172.28.2.33
3..随机数的生成：（100-200之间）
select round(dbms_random.value(100,200)) from duals
4.NLSSORT()，用来进行语言排序
拼音
Select * FROM TEAM orDER BY NLSSORT(排序字段名,'NLS_SORT = SCHINESE_PINYIN_M')
笔划
Select * FROM TEAM orDER BY NLSSORT(排序字段名,'NLS_SORT = SCHINESE_STROKE_M')
部首
Select * FROM TEAM orDER BY NLSSORT(排序字段名,'NLS_SORT = SCHINESE_RADICAL_M')
5.查询性能较差的语句
Select ADDRESS, SQL_TEXT, buffer_gets, executions, buffer_gets/executions AVG
FROM v$sqlarea
Where executions>0 AND buffer_gets > 100000;
6. 查询当前数据库的名称装备和版本
select instance_name,status,version from v$instance;
总结一下，与大家分享，多多指教.
本文参考了以下相关文章,感谢作者:
http://bbs.linuxpk.com/thread-10595-1-1.html
http://topic.csdn.net/u/20080924/15/8070e357-2aca-4ba2-924a-ceeb1937e1ab.html