(一)oracle获取多个值拼接
WMSYS.wm_concat(u.user_name) AS SERVERNAMES
eg:select t.d_no,WMSYS.wm_concat(t.REQUEST) REQUEST from aisuser.OPR_REQUEST_DO t where t.request_stage=:requestStage group by t.d_no
(二)oracle 条件
--这个相当于不加条件(有时候某列会有空值)
select * from opr_kpi_count_report t where nvl(t.operate_type,'1')=nvl(null,nvl(t.operate_type,'1'))
--这个相当于查询为空的集合,相当于is null
and nvl(t.operate_type,'1')=nvl(operateType,'1')
(三)oracle Long and date
select to_char((to_date('1970-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')+ 1324431300000/1000/24/60/60),'yyyy-MM-dd HH24:mi:ss')
当前时间转成long :
select sysdate,
(sysdate- to_date('1970-01-01','yyyy-mm-dd'))* 24*60*60*1000
current_milli from dual;
--获取当前时区
select TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3)) from dual;
(四)oracle 货物数字和字符
--获取字符
select trim(translate(translate(upper('1dd231232小明1321asdf'),'M',' '),'1234567890',' ')) from dual;
--获取数字
trim(translate(translate(upper(ConText),'M',' '),translate(translate(upper(ConText),'M',' '),'1234567890',' '),' '));