1. SIGN 返回1,0,-1
SELECT SIGN(98-100) FROM dual return -1
SELECT SIGN(100-100) FROM dual return 0
SELECT SIGN(198-100) FROM dual return 1.
2. DECODE(value,if1,then1,if2,then2,if3,then3,...,else) 表示如果value 等于if1时,DECODE函数的结果返回then1,...,如果不等于任何一个if值,则返回else。
select decode(sign(100-100),0,1,2) from dual; return 1
select decode(sign(102-100),0,1,2,3,4) from dual return 4
3. INSTR(源字符串, 目标字符串, 起始位置, 匹配序号) 起始位置从1开始,默认查找顺序为从左到右。当起始位置为负数的时候,从右边开始查找。
SELECT INSTR('CORPORATE FLOOR', 'OR', -1, 3) FROM DUAL return 2
SELECT INSTR('CORPORATE FLOOR', 'OR', 1, 1) FROM DUAL return 2
SELECT INSTR('CORPORATE FLOOR', 'OR', 3, 1) FROM DUAL return 5
4. Trunc(for number) ,非四舍五入 *grammar:TRUNC(number[,decimals])
decimals 指明需保留小数点后面的位数。可选项,忽略它则截去所有的小数部分
TRUNC(89.985,2)=89.98
TRUNC(89.985)=89
TRUNC(89.985,-1)=80
注意:第二个参数可以为负数,表示为小数点左边指定位数后面的部分截去,即均以0记。
5. Round(for number) 四舍五入 *grammar:ROUND( number, decimal_places )
select round(123.456, 0) from dual; return 123
select round(123.456, 3) from dual; return 123.456
select round(123.456, 2) from dual return 123.46
6. to_timestamp and to_date
to_timestamp, ie: TO_TIMESTAMP('2008-3-24 13:09:59.776000','YYYY-fmMMfm-fmDDfm fmHH24fm:MI:SS.FF')
to_date, ie: TO_DATE('2011-08-25 15:37:50','yyyy-MM-dd hh24:mi:ss')
创建历史表&向历史表中插入数据
*执行语句:CREATE TABLE 新表名 AS SELECT * FROM 旧表名;
*插入数据:INSERT INTO WBTS_T2 SELECT * FROM WBTS_T1;
查询分页 , 2层或3层子查询
ie: select * from( select rownum r, p.* from players p where rownum<20) where r>=10; no order by
ie: select * from (select rownum rn, temp.* from (select e.ename, e.sal from emp e order by e.sal desc) temp where rownum <=10) where rn>5 ; order by
注意:检索出来再排序,还是将排序后的结果再检索出来
这里有大量oracle 参考实例: http://www.java2s.com/Code/Oracle/CatalogOracle.htm