oracle 常见函数

1.decode

select decode (columnname, 值1，翻译1，值2，翻译2，。。。值n，翻译n,缺省值)

from talbename

where ...

2,nvl

nvl(string1,replace_with)

功能：如果string1 为null， 则nvl函数返回replace——with的值，否则返回string1 的值

注意事项：数据类型必须统一数据类型

3. greatest

greatest(expr1,expr2,.....expr_n)

找出最大数返回

例：select greatest(2,5,12,3,16,8) A from dual

A

16

4, coalesce 返回其参数中第一个非空表达式

coalesce(expression[,...n])

5，truncate 删除表数据--不可恢复

delete 删除表数据--可恢复

drop 删除表结构

6,translate 替换 replace

translate(expr, from_string, to_string)

7,merge

MERGE语句是Oracle9i新增的语法，用来合并UPDATE和INSERT语句,,也可以是删除语句

1---merge into fzq1 aa --fzq1表是需要更新的表

using（select fzq.id , fzq.chengji

from fzq join fzq2

on fzq.id = fzq2,id) bb --数据集也可以是单个表

on (aa.id = bb.id) -- 关联条件

when matched then --匹配关联条件，作更新处理

update set

aa.chengji=bb.chengji+1,

aa.name=bb.name --可以同时修改多个字段

when not matched then

insert values(bb.id, bb.name, bb.sex, bb.kecheng, bb.chengji);

2----merge into bonuses d

using ( select employee_id,salary, department_id

from hr.employees

where department_id = 80) s

on (d.employee_id = s.employee_id)

when matched then

update set d.bonus = d.bonus + s.salary * 0.01

where----只能出现一次，如果这里出现where ，delete后面where 无效

delete where （s.salary >8000)

when not matched then

insert (d.employee_id , d.bonus) values (s.employee_id, s.salary * 0.01)

where (s.salary <=8000)

8, instr

可以使用instr函数对某个字符串进行判断，判断其是否含有制定的字符。

在一个字符串中查找制定的字符，返回被查找到的制定的字符的位置。

instr（源字符串，目标字符串，开始位置（可选，默认1），第几次出现）

select instr（’abcdefgh','de') position from dual

select instr('avcdefghbc','bc',3) position from dual; --从第3个起开始往后查找bc

9, regexp_replace----多次 replayce

用正则表达式将字母与数字分开

select regexp_replace(data ,'[0-9]','') dname,

regexp_replace(data, '[^0-9],'') deptno

from dept

^--在方括号内代表否，否则代表开始

regexp_like ----模糊查询

regexp_like(data, '[abc]') 相当于 （like '%a%' or '%b%' or '%c%')

regexp_count

regexp_substr(v.emps,'[^,]', 1, level) ---把非，的分割----regexp ---可以使用正则表达式

10, with clause

with x as

(select rownum as seq, a.* from (select 编号，项目， 金额 from detail order by 编号） a）

-----with clause 以with 开头的查询，相当于创建一个临时表

select 编号，项目， 金额，

(case when seq = 1 then 金额 else -金额 end) as 转换后的值

from x

11, over （分析函数）

sum(sal) over (partition by deptno order by ename) new_alias

sum 就是函数名

（sal) 是分析函数的参数，每个函数有0~3个参数，参数看可以使表达式，例如： sum(sal+comm)

over 是一个关键字，可用于表示分析函数，否则查询分析器不能区别sum() 聚集函数 和 sum(0分析函数

partition by deptno 是可选的分区子句，如果不存在任何分区子句，则全部的结果集客看做一个单一的大区

order by ename 是可选的order by 子句

12， 与over（）函数常见的函数介绍(必须和over() 函数一起使用）

---1，row_number() 有两个第一只显示一个

select * from

(

select name , class, s , row_number() over(partition by class order by s desc) row_number from t2

)

where row_number=1

---2, rank() 和 dense_rank() 可以将所有的都查找出来

区别 rank() 是跳跃排序，有两个第二名时接下来就是第四名

dense_rank() 是连续排序，有两个第二名时仍然跟着第三名

---3， sum() over()

select name,class, sum(s) over (partition by class order by s desc) sum from t2

有两个第一时，第一个sum就是两个第一的和

----4 first_value() over() 和 last_value() over()

select opr_id, res_type,

first_value(res_type) over(partition by opr_id order by res_type) low,

last_value(res_type) over(partition by opr_id order by res_type) high

from rm_circuit_route

where opr_id in ('001',.......)

order by opr_ID

-----5 lag() over() 取出前n行的值

lead() over() 取出后n行的值

with a as

(select 1 id, 'a' name from dual

union

select 2 id, 'b' name from dual

union

select 3 id, 'c' name from dual

union

select 4 id, 'd' name from dual

union

select 5 id, 'e' name from dual

)

14, ntile 对数据分组

select ntile(3) over (order by empno) as 组,empno as 编码,ename as 姓名 from emp

where job in ('clerk' , ' manager');

组 编码 姓名

1 7369 aaa

1 3453 bbb

1 2311 ccc

2 4454 ddd

2 5555 eee

3

15 rollup 合计

select deptno , sum(sal) s_sal from emp group by rollup(deptno)

select deptno 部门编码， job 工作， sum(sal) 工资小计 from emp

group by rollup( (deptno,job))

16，ceil(n) 和floor(n)

ceil(n) 取大于等于数值n的最小整数

floor(n)取小于等于数值n的最大整数

特殊列子：

where (ceil((ta.repairTempDate-ta.repairDate))*24) >= 12