Oracle几个常用的伪列(Pseudo-column)使用

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

上一篇: Web开发电子期刊2009年第7期(总第35期)
下一篇: Oracle EBS 基本业务流程
文章来自: 本站原创
引用通告: 查看所有引用 | 我要引用此文章
Tags:
相关日志: