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)