Oracle单行函数和多行函数以及谓词和case表达式

单行函数
单行函数分为五种类型：字符函数、数值函数、日期函数、转换函数、通用函数

1字符函数:

(1）INSTR(x, str [,start] [,n) 在 x 中查找 str，可以指定从 start 开始，也可以指定从第 n 次开始。

instr(title,'oracle')>0 相当于like
instr(title,'oracle')=0 相当于not like

(2)REPLACE(x,old,new)  在 x 中查找 old，并替换为 new。

(3)SUBSTR(x,start[,length]) 
返回x的字串，从 staart 处开始，截取 length 个字符，缺省 length，默认到结尾
SUBSTR(string,start,count)取子字符串,从start开始,取count个 select substr(13088888888,3,8) from dual


2数值函数
ROUND(x[,y])  x 在第 y 位四舍五入 ROUND(3.456,2)=3.46
TRUNC(x[,y])  x 在第 y 位截断 TRUNC(3.456,2)=3.45

ROUND(X[,Y])，四舍五入。
在缺省 y 时，默认 y=0；比如：ROUND(3.56)=4。

y 是正整数，就是四舍五入到小数点后 y 位。ROUND(5.654,2)=5.65

y 是负整数，四舍五入到小数点左边|y|位。ROUND(351.654,-2)=400

TRUNC(x[,y])，直接截取，不四舍五入。
在缺省 y 时，默认 y=0；比如：TRUNC (3.56)=3。

y 是正整数，就是四舍五入到小数点后 y 位。TRUNC (5.654,2=5.65

y 是负整数，四舍五入到小数点左边|y|位。TRUNC (351.654,-2=300

3日期函数
日期函数对日期进行运算。常用的日期函数有1.ADD_MONTHS(d,n)， 在某一个日期 d 上， 加上指定的月数 n， 返回计算后的新日期。

LAST_DAY(d)，返回指定日期当月的最后一天。

ROUND(d[,fmt])，返回一个以 fmt 为格式的四舍五入日期值，d 是日期，fmt 是格式模型。默认 fmt 为 DDD，即月中的某一天。
如果 fmt 为“YEAR”则舍入到某年的 1 月 1 日，即前半年舍去，后半年作为下一年
如果 fmt 为“MONTH”则舍入到某月的 1 日，即前月舍去，后半月作为下一月。
默认为“DDD” ，即月中的某一天，最靠近的天，前半天舍去，后半天作为第二天。
如果 fmt 为“DAY”则舍入到最近的周的周日，即上半周舍去，下半周作为下一周周日

select sysdate ,round(sysdate),round(sysdate,'day'),

round(sysdate，'month'）,round(sysdate,'year')

from dual

sysdate
2009/8/11  21:53:00
2009-8-12 2009-8-9 2009-8-1 2010-1-1

select trunc(sysdate) from dual; --2014/3/5

select trunc(sysdate, 'mm') from dual; --2014/3/1 返回当月第一天.

select trunc(sysdate, 'yy') from dual; --2014/1/1 返回当年第一天

select trunc(sysdate, 'dd') from dual; --2014/3/5 返回当前年月日

select trunc(sysdate, 'yyyy') from dual; --2014/1/1 返回当年第一天

select trunc(sysdate, 'd') from dual;--2014/3/2 (星期天)返回当前星期的第一天

select trunc(sysdate, 'hh') from dual; --2014/3/5 10:00:00 当前时间为14:41

select trunc(sysdate, 'mi') from dual; --2014/3/5 10:57:00

EXTRACT(fmt FROM d)，提取日期中的特定部分。
select sysdate"date"
extract(year from sysdate) "year"  2009
extract(month from sysdate) "month" 8
extract(day from sysdate) "day" 11
extract(hour from sysdate) "hour"
extract(minute from sysdate) "minute" 35
extract(second from sysdate) "second" 23...
from dual
date  
2009-8-11 11:35:23


4转换函数
转换函数将值从一种数据类型转换为另外一种数据类型
1.TO_CHAR(d|n[,fmt]) 把日期和数字转换为制定格式的字符串。TO_CHAR 函数能够把数字和日期转换符串格式。
2.TO_DATE(x [,fmt]) 把一个字符串以 fmt 格式转换为一个日期类型。TO_DATE 函数能够把转换为日期类型。
3.TO_NUMBER(x[,fmt]) 把一个字符串以 fmt 格式转换为一个数字


5通用函数或其他单行函数

NVL(x,value) 如果 x 为空，返回 value，否则返回 x。

NVL2(x,value1,value2) 如果 x 非空，返回 value1，否则返回 value2。 

Oracle中的多行函数
1组函数是处理多行返回一行，组函数不能计算空值 
avg count max min  sum
2数据分组（GROUP BY)


分析函数
row_number() over(partition by ... order by ...)
rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)
count() over(partition by ... order by ...)
max() over(partition by ... order by ...)
min() over(partition by ... order by ...)
sum() over(partition by ... order by ...)
avg() over(partition by ... order by ...)
first_value() over(partition by ... order by ...)
last_value() over(partition by ... order by ...)
lag() over(partition by ... order by ...)
lead() over(partition by ... order by ...)

sum(...) over( partition by... )，同组内所行求和

sum(...) over( partition by... order by ... )，同第1点中的排序求和原理，只是范围限制在组内

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)

row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开时排序）．

rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）．

dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的

谓词
谓词就是返回为真值的函数
谓词：LIKE BETWEEN IS NULL IS NOT NULL    IN  EXISTS




CASE 表达式
搜素case表达式
case when <判断表达式>then <表达式>
     when <判断表达式>then <表达式>
     when <判断表达式>then <表达式>
     .
     .
     else<表达式>
END


简单case表达式
case<表达式>
     when<表达式>then<表达式>
     when<表达式>then<表达式>
     .
     .
     else<表达式>
END