oracle常用函数(持续更新)

1.把返回结果集合,以表的形式返回

点击查看代码

  select * from table(p_split('1,2,3'),',');

  create or replace type type_split is table of varchar2(4000);

  create or replace function p_split(p_string in varchar2, p_delimiter in varchar2)
    return type_split
    pipelined
  as
    v_length number:=length(p_string);
    v_start number:=1;
    v_index number;
  begin
    while(v_start<=v_length)
    loop
      v_index:=instr(p_string,p_delimiter,v_start);
      if v_index = 0
      then
        pipe row(substr(p_string,v_start));
        v_start:=v_length + 1;
      else
        pipe row(substr(p_string, v_start, v_index - v_start));
        v_start:=v_index + 1;
      end if;
    end loop;
    return;
  end p_split;

2.to_char() 按照指定格式输出字符串
to_char(sysdate,'YYYYMMDD')
// fm 除去空格和0
// 9999999.0099：允许小数点左边最大正数为7位，小数点右边最少2位，最多4位，且在第5位进行四舍五入
Select TO_CHAR(123.0233,'FM9999999.0099') FROM DUAL

3.to_date() 按照指定格式输出日期
to_date('20230324','YYYYMMDD')
to_date(20230324,'YYYYMMDD')

4.to_number() 将字符串按指定格式输出为数值

TO_NUMBER('$123,456.78','$999,999.99')
TO_NUMBER('123.45')

5.trunc(date, format) 按照指定格式截断日期, 该format参数是可选的,默认DD

6.NVL(expr1,expr2) 含义是：如果oracle第一个参数为null那么显示第二个参数的值，如果第一个参数的值不为null，则显示第一个参数本来的值。
NVL2(expr1,expr2, expr3) 含义是：如果该函数的第一个参数为null那么显示第三个参数的值，如果第一个参数的值不为null，则显示第二个参数的值。

7.将结果集拼接成一个字段

点击查看代码

select listagg(字段名,',') within group (order by 字段名) from 表名 where 条件

8.case when函数

点击查看代码

case when 条件1 then 结果1
     when 条件2 then 结果2
     else 结果3
end;

9.高效率批量插入更新merge into

点击查看代码

merge into 表
using (表|视图|子查询等)
on (条件)
when match then
 update set 字段=值  
when no match then
 insert (字段...) values (值...)