oracle入门知识总结二

1.oracle的dual表

dual作为一个系统表，其实是虚拟的，更多的是构建一个select语法结构。所有用户都可以使用，以下是常用查询：

select user from dual;--查询当前用户

select sysdate from dual;--查询当前时间

select dbms_random.random from dual;--获得一个随机数

select 序列名.nextval from dual--获取序列的下一个值

select SYS_CONTEXT('USERENV','language') from dual;--获得当前 locale

2.oracle的序列（Sequence）

序列是生成唯一连续等间的数值的一个对象。oracle中没有自增长的整型列，这一功能就是通常序列来实现的，但注意的是序列不是专属于某个表的，它是单独存在的，换句话说多个表同时使用一个序列那么对于单个表中列值并不是和自动增长列一样，这些不是连续的。如果想得到连续值你只能是一个表专用一个序列了。

创建序列语句格式：

create sequence 序列名

[increment by n]

[start with n]

[{maxvalue/minvalue n|nomaxvalue}]

[{cycle|nocycle}]

[cache n|nocache];

参数意义为：

increment by  定义序列步长，默认为1，可以是递减序列；

start with  序列初始值，默认为1；

maxvalue  产生的最大值，nomaxvalue表示没有最大值，些时最大值是10的27次方；

minvalue  产生的最小值，系统默认最小值是-10的26次方；

cycle  表示序列达到限制值后是否循环；

cache  定义存放序列的内存块大小 ，默认为20，对序列进行内存缓冲可改善性能；

获取序列当前值：序列名.currval，获取序列下一个值：序列名.nextval。

以下语句向userinfo表插入一条数据，id列值由序列seq_name产生：

insert into userinfo(id,username) values(seq_name.nextval,'charles');

3.oracle的日期时间

oracle date类型存储7字节，显示时一般显示年月日而不显示时分秒，缺省的时间格式数据的显示形式，与所使用的字符集有关。

oracle日期格式参数：
d: 一周中的星期几 
day: 天的名字，使用空格填充到9个字符 
dd:  月中的第几天 
ddd: 年中的第几天 
dy:    天的简写名 
iw:    ISO标准的年中的第几周 
iyyy:  ISO标准的四位年份 
yyyy: 四位年份 
yyy,yy,y: 年份的最后三位，两位，一位 
hh:        小时，按12小时计 
hh24:   小时，按24小时计 
mi:     分 
ss:     秒 
mm:  月 
mon: 月份的简写 
month: 月份的全名 
w:    该月的第几个星期 
ww: 年中的第几个星期

oracle常用时间函数：
sysdate 获取当前系统时间

to_date(varchar,[fmt]) 字符串转换为时间，如：to_date('2008-11-27 13:34:43', 'yyyy-mm-dd hh24:mi:ss') ，TO_DATE(null)表示时间为null；

to_char(date,[fmt]） 时间转换为对应格式的字符串，如：to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')；

next_day（date，day）     返回下个星期的日期,day为1-7或星期日-星期六,1表示星期日。如：返回当前开始下一个星期五next_day(sysdate,6)，后面的数字是从星期日开始算起；

last_day(date) 返回date所在月份的最后一天。如：返回本月的最后一天 Select last_day(sysdate) from dual；

add_months(date,n) 返回date加n个月的时期；

Months_between(f,s)  日期f和s间相差月数；

java中对oracle时间类型列的操作：

取时间，ResultSet的getDate()方法返回的是java.sql.Date类型，而java.sql.Date类型只有日期部分，时间部分都清零了，因此如果我们需要精确到时间时这个方法并不合适。

使用getString()和getTimestamp()时均可取出精确时间，但需要转换为你所需要的类型。

插入时间，同样需要插入精确时间时仍然不能使用setDate()，你可以将时间转换为java.sql.Timestamp后再使用setTimestamp()插入，或者是插入时间格式的字符串而在sql语句中使用oracle的to_date函数转换。

4.rownum和rowid

rowid伪列，用于唯一标识表中一行记录，是物理存在的，在插入数据时即生成。虽然在查询记录时它是最快的，但对于这样一个18个无规律字符实在没有多大实用。

rownum也是伪列，它是某次查询的结果集的一个序号列，是先查询出结果集然后才有的列。

它是按顺序标记，而且是逐次递加的，换句话说就是只有有rownum=1的记录，才可能有rownum=2的记录。当把rownum作为where条件时这点非常重要，看下面示例：

select xx from table where rownum > n （n>1）
这个查询结果是个空集，假设我们的查询条件为rownum = 3，那么在查询出的第一条记录的时候，Oracle标记此条记录rownum为1，结果发现和rownum=3的条件不符，于是结果集为空。再看：
select xx from table where rownum < n （n>1）
这个查询结果是正确的。道理跟前一条一样。

用rownum实现分页条件是最常用的方法，看下面的分页sql：

select s.* from (select u.*,rownum rn from userinfo u where u.id>10 and rownum<11) s where s.rn>4;

查询userinfo表中id>10在5-10间的数据，这里有点注意的是oracle中as只能用在列的别名上不能用在表的别名上