参考 http://yangdi1984.blog.hexun.com/20238141_d.html

http://www.cnblogs.com/kafony/archive/2011/08/25/2153675.html

?

数学函数
1，取整函数（ceil 向上取整,floor 向下取整）
?? select ceil(66.6) N1,floor(66.6) N2 from dual;

2, 取幂(power) 和 求平方根(sqrt)
?? select power(3,2) N1,sqrt(9) N2 from dual;

3,求余
?? select mod(9,5) from dual;

4,返回固定小数位数 (round:四舍五入，trunc:直接截断)
?? select round(66.667,2) N1,trunc(66.667,2) N2 from dual;

5,返回值的符号(正数返回为1,负数为-1)
?? select sign(-32),sign(293) from dual;

?

字符函数(可用于字面字符或数据库列)

1，字符串截取
?? select substr('abcdef',1,3) from dual

2,查找子串位置
?? select instr('abcfdgfdhd','fd') from dual

3,字符串连接
?? select 'HELLO'||'hello world' from dual;

4, 1)去掉字符串中的空格
??? select ltrim(' abc') s1,
??? rtrim('zhang ') s2,
??? trim(' zhang ') s3 from dual
?? 2)去掉前导和后缀
??? select trim(leading 9 from 9998767999) s1,
??? trim(trailing 9 from 9998767999) s2,
??? trim(9 from 9998767999) s3 from dual;
??
5,返回字符串首字母的Ascii值
?? select ascii('a') from dual

6,返回ascii值对应的字母
?? select chr(97) from dual

7,计算字符串长度
?? select length('abcdef') from dual

8,initcap（首字母变大写） ,lower(变小写),upper(变大写)
?? select lower('ABC') s1,
?????? upper('def') s2,
?????? initcap('efg') s3
?? from dual;

9,Replace
?? select replace('abc','b','xy') from dual;

10,translate
?? select translate('abc','b','xx') from dual; -- x是1位

11,lpad [左添充] rpad [右填充](用于控制输出格式)
?? select lpad('func',15,'=') s1, rpad('func',15,'-') s2 from dual;
?? select lpad(dname,14,'=') from dept;

12, decode[实现if ..then 逻辑]?? 注:第一个是表达式,最后一个是不满足任何一个条件的值
?? select deptno,decode(deptno,10,'1',20,'2',30,'3','其他') from dept;
?? 例:
?? select seed,account_name,decode(seed,111,1000,200,2000,0) from t_userInfo//如果seed为111,则取1000;为200,取2000;其它取0
?? select seed,account_name,decode(sign(seed-111),1,'big seed',-1,'little seed','equal seed') from t_userInfo//如果seed>111,则显示大;为200,则显示小;其它则显

示相等

13 case[实现switch ..case 逻辑]
??? SELECT CASE X-FIELD
???????? WHEN X-FIELD < 40 THEN 'X-FIELD 小于 40'
???????? WHEN X-FIELD < 50 THEN 'X-FIELD 小于 50'
???????? WHEN X-FIELD < 60 THEN 'X-FIELD 小于 60'
???????? ELSE 'UNBEKNOWN'
??????? END
?? FROM DUAL
??
?? 注:CASE语句在处理类似问题就显得非常灵活。当只是需要匹配少量数值时，用Decode更为简洁。

如：select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值

Decode函数的语法结构如下:
decode (expression, search_1, result_1)
decode (expression, search_1, result_1, search_2, result_2)
decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n)

decode (expression, search_1, result_1, default)
decode (expression, search_1, result_1, search_2, r