0 results found
DeCo
Oracle常用内置函数
2019/11/11 数据库 oracle
oracle

持续集成。。。

  1. nvl(value1, value2)

    函数作用:从两个表达式中返回一个非null值

    -- 如果value1的值非空,优先取value2的值;结果为 1
    SELECT "NVL"(1, NULL) FROM dual;
    -- 如果value1的值空且value2的值非空,则取expression2的值;结果为 2
    SELECT "NVL"(NULL, 2) FROM dual;
    -- 如果value1和value2均为空,则结果为NULL
    SELECT "NVL"(NULL, NULL) FROM dual;

    注意: value1, value2要保持字段类型相同!但是 NVL”(1, ‘1’) 不会报错

    [SQL] SELECT "NVL"(1, '4d') FROM dual
    [Err] ORA-01722: invalid number
  2. nvl(value1, value2, value3)

    函数作用:如果value1的值为null,函数返回value3,否则函数返回value2.也就是说函数永远不会返回value1

    -- c
    SELECT "NVL2"(NULL, 'b', 'c') FROM dual;
    -- b
    SELECT "NVL2"(1, 'b', 'c') FROM dual;

    注意: value2, value3要保持字段类型相同!

    [SQL] SELECT "NVL2"(NULL, 1, 'a') FROM dual
    [Err] ORA-01722: invalid number
  3. decode(field_name, value1, new_value1, value2, new_value2, default_value)

    函数作用:类似if…else…语句块,针对某个字段,如果它的值为value1,则转换为newValue1,如果值为value2,则转换为newValue2,其他情况显示默认值

    -- A
    select decode('1','1','A','2','B','默认值') from dual;
    -- B
    select decode('2','1','A','2','B','默认值') from dual;
    -- 默认值
    select decode('3','1','A','2','B','默认值') from dual;

    注意:decode(field_name, value1, new_value1, value2, new_value2, default_value)其中的value1,newValue1等可以是一个表达式

  4. row_number(order by field_name)

    select row_number() over(order by name) nid,id,name from a;
    NID NAME
    1 李四
    2 王五
    3 张三
  5. to_date(source_string, formater_string)

    函数作用:将字符串转换为日期类型

    select to_date('20191113','yyyyMMdd') from dual;
    select to_date('2019-07-09 23:59:59', 'yyyy-mm-dd hh24:mi:ss') from dual;
  6. to_char()

    函数作用:将其他类型转换为字符串类型

    select to_char(sysdate, 'yyyymmdd') from dual;
    select to_char(99, 'fm999.00') from dual;
  7. wm_concat

    函数作用:行转列,将多行查询结果聚合到一行的某一列中,用’,’拼接

    -- 结果: 李四,王五,张三
    select wm_concat(distinct name) from USER;

    注意:高版本oracle可能会去掉

  8. listagg() within group(order by field_name) over(partition by field_name)

    函数作用:同wm_concat,但是listagg可以自定义拼接符号,测试发现性能要比wm_concat好,建议使用listagg

    select listagg(name, '-') within group(order by name) from USER;
  9. concat(value1, value2)

    函数作用:字符串拼接函数

    select concat('left', 'right') from dual;
    -- 也可以用 ||
    select 'left' || 'right' from dual;
  10. sys_guid()

    函数作用:产生并返回一个全球唯一的标识符(原始值)由16个字节组成,32个字符

    select sys_guid() from dual;
    --解决乱码,rawtohex(),将raw串转换为十六进制
    select rawtohex(sys_guid()) from dual;
    --转小写
    select lower(rawtohex(sys_guid())) from dual;
  11. trunc

    函数作用:是截取日期或数字,根据规则返回指定的值

    select trunc(sysdate, 'yyyy') from dual;
    规则 含义
    mm 返回当月第一天
    yy 返回当年第一天
    dd 返回当前年月日
    yyyy 返回当年第一天
    d 返回当前星期的第一天
select trunc(126.56, 0) from dual;
规则 含义
0 默认值,取下取整
正数a 保留小数点后面a位小数,其他的抹掉,如果a比小数点后的位数多,则保留原值
负数b 小数点后面的全部去掉,小数点向左移动abs(b)位,用0代替被抹掉的数字,如果所有数字都被抹掉,则返回0
  1. substr(source, start [,length])

    函数作用:截取字符串

    -- bcd
    select substr('abcde', 2, 3) from dual;

    注意:oracle字符串索引从1开始

  2. replace(field_name, sub_str, replace_str)

    函数作用:将指定的字符串替换为指定的字符串

    -- world world
    select replace('hello world', 'hello', 'world') from dual;

    注意:也可以用在update语句set部分

  3. trim

    函数作用:去掉左右两端的空白字符

    -- dsf
    select trim(' dsf ') from dual;

    注意:仅去掉左边空白字符用ltrim,仅去掉右边空白字符用rtrim

  4. sign

    函数作用:取数字n的符号,大于0返回1, 小于0返回-1, 等于0返回0

    -- 1	-1	0
    select sign(50),sign(-100),sign(0) from dual;
  5. round(number[,decimal])

    函数作用:对数字n进行四舍五入处理,保留decimal位小数

    -- 123	124	123.6	120
    select round(123.34),round(123.51),round(123.56,1),round(123.34,-1) from dual;
  6. coalesce(expression1,expression2…)

    函数作用:返回表达式中第一个不为空的值,如果全为空则返回空值

    -- 8
    select coalesce(null,3+5,4+6) value from dual;
    -- 10
    select coalesce(null,null,4+6) value from dual;
    -- NULL
    select coalesce(null,null,null) value from dual;
请杯咖啡呗~
支付宝
微信
本文作者:DeCo
版权声明:本文首发于DeCo的博客,转载请注明出处!