常用内置函数

decode

--对某字段判断抄的条件判断的结果是1，那么decode返回a，以此类推
select decode(condition,1,a,2,b,3,c,d) from dual;

exists,not exists

--exists中能查询到数据就返回真，用法于in类似。
select h.bp_id
  from hls_bp_master h
 where exists (select 1 from con_contract c where c.bp_id_tenant = h.bp_id)

substr

-- L190941  (第二批)
select substr('L190941(第二批)', 1, instr('L190941(第二批)', '(') - 1),
       substr('L190941(第二批)', instr('L190941(第二批)', '('))
  from dual;
--11 22 33 44 55
select regexp_substr('11,22,33,44,55,', '[^,]+', 1, level) as name
  from dual
connect by regexp_substr('11,22,33,44,55,', '[^,]+', 1, level) is not null;

to_char

9代表如果存在数字则显示数字，不存在则显示空格。
0代表如果存在数字则显示数字，不存在则显示0，即占位符。

--9999.0956  09999.0956
select to_char(9999.09556, 'fm99999.0900'),
       to_char(9999.09556, 'fm00099.0900')
  from dual;

to_date

在使用Oracle的to_date函数来做日期转换时，使用'yyyy-MM-dd HH:mm:ss'的格式在Oracle中会引起错误：“ORA 01810 格式代码出现两次”,原因是SQL中不区分大小写，MM和mm被认为是相同的格式代码，所以Oracle的SQL采用了mi代替分钟'yyyy-MM-dd HH24:mi:ss'

select to_date('2023-03-06 10:11:35','yyyy-mm-dd HH24:MI:SS') from dual;

extract

--抽取函数 2013 11
select extract(year from date'2013-11-30') year,extract(month from date'2013-11-30') month from dual;

add_months，months_between

--add_months(date,number) 获取date的number月后的日期  
--2022/8/20  2022/8/24 11:09:10
select trunc(add_months(last_day(sysdate), -1) + 20),sysdate from dual;
-- 2022/8/31 11:11:57  2022/8/24 11:11:57
select last_day(sysdate),sysdate from dual;
--504 计算两个时间之间间隔的月份
select months_between(sysdate,to_date('1980-9-19', 'yyyy-mm-dd')) from dual

wm_concat，listagg，xmlagg

-- APP_DESC,APPTYPE,CODE,CREATIONTIME
select wmsys.wm_concat(column_name)
  from user_tab_columns s
 where s.TABLE_NAME = upper('sm_appregister');

greatest，least

greatest取参数最大值，least取最小值

--2022/8/26 14:50:36  4
select greatest(sysdate,sysdate+1,sysdate+2),least(5,6,4) from dual;

over

row_number()：排序，不会有重复的排序数值。对于相等的两个数字，排序序号不一致，123
dense_rank()：排序，可有重复值。对于相等的两个数字，排序序号一致，112
rank()：排序，可有重复值。对于相等的两个数字，排序序号一致，但是总数会减少，113

select * ,row_number() over (partition by 父表id order by 子表某个字段) from 子表;
--partition by用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组。
-eg.
select *
  from (select c.billno,
               a.pk_org,
               a.pk_psndoc tjr,
               b.pk_psndoc spr,
               row_number() over(PARTITION BY c.billno order by to_date(c.dealdate, 'YYYY/MM/DD HH24:MI:SS') desc) as rn
          from pub_workflownote c, sm_user a, sm_user b
         where c.senderman = a.cuserid
           and c.checkman = b.cuserid
           and c.billno = 'CZSQ2022092900000002')
 where rn = 1;

rawtohex,sys_guid

--返回32位全球唯一码 CB40A3586A204F90B1C76DA63EDB59F1
select rawtohex(sys_guid()) from dual;