常用oracle 函数

1.oracle的 substr函数的用法：

 取得字符串中指定起始位置和长度的字符串   substr( string, start_position, [ length ] )。

例：substr('This is a test',  6,  2)     would  return 'is'

     substr('This is a test',  6)     would  return 'is a test'

      substr('TechOnTheNet',  - 3,  3)     would  return 'Net'

2.将Oracle中同一列的多行记录拼接成一个字符串

用 wm_concat函数:

select wm_concat(space_full_name) from ZD_TOBACCO_STORAGE_SPACE where TOBACCO_ID =' ' group by TOBACCO_ID

需要 排序时：

select ssd.tobacco_id,max(r) from

(select ssd0.space_code,ssd0.tobacco_id, wm_concat(ssd0.space_name)

over (partition by ssd0.tobacco_id order by to_number(ssd0.space_code)) r

from zd_tobacco_storage_space_detai ssd0)ssd

group by ssd.tobacco_id

3.查询不包含汉字的（ like '%\%' 是包含 ，not like 不包含）

where asciistr(t.tobacco_code) not like '%\%'

4.查询每条数据当天最后一条

select wh.unit_id,

wh.client_id,

wh.client_name,

wh.large_address_id,

wh.large_address_name,

wh.tobacco_type,

wh.tobacco_state,

wh.tobacco_number,wh.tobacco_weight,wh.change_time,

row_number() over( partition by wh.tobacco_id order by wh.change_time desc) rn

根据wh.tobacco_id group by再根据wh.change_time时间倒序查，该分组rn从1开始

from view_TobaccoReceive_Record wh

5.四舍五入

round(to_number(sum(b.AFWEI))/to_number(decode(sum(b.BFWEI),0,1,sum(b.BFWEI))),5)

6.类似于三目运算

decode(sum(b.BFWEI),0,1,sum(b.BFWEI))

当sum(b.BFWEI)=0时，取值为1，当sum(b.BFWEI)！=0时，取值sum(b.BFWEI)