Oracle 常用函数

DECODE 函数

DECODE函数的两个格式   

  1. DECODE(条件, 值1, 返回值1, 值2, 返回值2,……,值n, 返回值n, 缺省值)相当于MySQL的 IF ELSEIF语句
  2. DECODE(字段或字段的运算, 值1, 值2, 值3)当字段或字段的运算的值等于1时，该函数返回值2，否则返回值3
//结果：2。 1+1 运算的值为2，命中值2，即返回值2
select decode(1+1,1,1,2,2,0) from dual;

//结果：0。 字段运算的值为0，返回值3
select decode(0,1,1,0) from dual;

SIGN 函数

sign函数根据某个值是0、正数还是负数，分别返回0、1、-1

  • SIGN 函数格式 SIGN(n)
SELECT SIGN(10-5) FROM DUAL;//返回 1

SELECT SIGN(10-10) FROM DUAL;//返回 0

SELECT SIGN(10-15) FROM DUAL;//返回 -1

SELECT SIGN(10-5) FROM DUAL;//返回 1

//判断结束日期和开始日期是否相差一个月，是的话取结束日期，否则开始日期加一个月
select decode(sign(months_between(to_date('2024-08-02','YYYY-MM-dd'),to_date('2024-05-02','YYYY-MM-dd'))), 1,
    to_char(add_months(to_date('2024-05-02','YYYY-MM-dd'),1), 'YYYY-MM-dd'), '2024-08-02') as dateTime
from dual;

 SUBSTR 函数

SUBSTR 函数从 position字符位置开始返回字符的一部分，长度为 subring_length字符。SUBSTR 使用输入字符集定义的字符计算长度

  • SUBSTR 函数格式 SUBSTR(char, position, substring_length)
//返回CDEF
SELECT SUBSTR('ABCDEFG',3,4) "Substring" FROM DUAL; 
//返回CDEF
SELECT SUBSTR('ABCDEFG',-5,4) FROM DUAL;

LENGTH 函数

LENGTH() 是一个内置函数，它返回给定字符串的字符长度

  • LENGTH 函数格式 LENGTH(str)
    • 如果任意一个参数为 NULL， LENGTH() 将返回 NULL
SELECT LENGTH('Hello') FROM dual;//返回5

REPLACE 函数

REPLACE 是一个内置函数，它将字符串中出现的所有子字符串替换为新的子字符串

  • REPLACE 函数格式 REPLACE(char, search_string [, replacement_string ])
    • 函数执行区分大小写的搜索
    • 如果第一个参数为 NULL， REPLACE() 将返回 NULL
SELECT REPLACE('Hi ywc', 'Hi', 'Hello') Result FROM dual;//返回 Hello ywc

REGEXP_REPLACE 函数

REGEXP_REPLACE函数允许通过正则表达式将字符串中的部分重复项替换为指定的值，即子字符串去重

REGEXP_REPLACE函数格式REGEXP_REPLACE(source_char, pattern, replace_string, position, occurrence, match_param)

    • source_char必需的。从中搜索的字符串。它可以是 CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, 或 NCLOB 中的任何一种数据类型
    • pattern必需的。正则表达式。它可以是 CHAR, VARCHAR2, NCHAR, 或 NVARCHAR2 中的任何一种数据类型。它最多包含 512 字节
    • replace_string可选的。被替换成的新内容
    • occurrence 可选的。它是一个整数，指示要返回的是第几次出现。默认为 0，即全部替换。
    • match_param可选的。执行匹配采用的模式。它可以是 VARCHAR2 或 CHAR。您可以通过此参数改变默认的搜索行为
//返回 123-abc-456-def。表示将匹配到的空格改为-
SELECT REGEXP_REPLACE('123 abc 456 def', '\s+', '-') FROM dual;

//返回 123 abc-456 def。表示从第一个字符开始匹配空格，第二次出现空格改为-
SELECT REGEXP_REPLACE('123 abc 456 def', '\s+', '-', 1, 2)  FROM dual;

//返回 1;3;5;6。表示根据;号进行切割，将重复组中的元素去第一个，即子字符串去重。常搭配 LISTAGG 函数使用
SELECT REGEXP_REPLACE('1;1;3;5;5;6;6', '([^;]+)(;\1)*(;|$)', '\1\3' ) FROM dual;

INSTR 函数

INSTR 函数用于搜索字符串中的子串。搜索操作的定义是将子串参数与长度相同的字符串子串进行比较，直到找到匹配的子串或没有更多子串为止。每个连续比较的字符串子串从上一个比较的子串的第一个字符开始向右移动一个字符（向前搜索）或向左移动一个字符（向后搜索）。如果找到与子串相等的子串，函数将返回一个整数，表示该子串第一个字符的位置。如果没有找到这样的子串，则函数返回 0

  • INSTR 函数格式 INSTR(string, substring, position, occurrence)
    • 可选参数
      • position是一个非零整数，表示 Oracle 数据库开始搜索的字符串字符，即要与子串比较的第一个子串的第一个字符的位置。如果 position为负数，则 Oracle 从字符串末尾开始向后计数，然后从结果位置开始向后搜索
      • occurrence是一个整数，表示 Oracle 应搜索字符串中哪个子串的出现次数。出现次数的值必须是正数。如果出现次数大于 1，则数据库不会在第一次匹配时返回，而是继续比较字符串中的连续子串，如上所述，直到找到匹配编号出现次数为止
//返回14
SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) FROM DUAL; 
//返回2
SELECT INSTR('CORPORATE FLOOR','OR', -3, 2) FROM DUAL;

LISTAGG 函数

LISTAGG 函数用于数据分组后，将指定列的数据按特定的字符进行合并。LISTAGG既是分析函数又是聚合函数（对数据进行分组分组之后，聚合函数只会每组返回一条数据，而分析函数会针对每条记录都返回）

LISTAGG 函数格式

  • 分析函数： LISTAGG([ALL/DISTINCT] 字段, 连接符) WITH GROUP(ORDER BY 合并字段的排序) VOER(PARTITION BY 分组字段) 
    • SELECT 
    department_id "Dept", 
    hire_date "Date", 
    last_name "Name",
    LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date, last_name) OVER (PARTITION BY department_id) as "Emp_list"
FROM employees
WHERE hire_date < '01-SEP-2003'
ORDER BY "Dept", "Date", "Name";


Dept Date      Name            Emp_list
----- --------- --------------- ---------------------------------------------
   30 07-DEC-02 Raphaely        Raphaely; Khoo
   30 18-MAY-03 Khoo            Raphaely; Khoo
   40 07-JUN-02 Mavris          Mavris
   50 01-MAY-03 Kaufling        Kaufling; Ladwig
   50 14-JUL-03 Ladwig          Kaufling; Ladwig
   70 07-JUN-02 Baer            Baer
   90 13-JAN-01 De Haan         De Haan; King
   90 17-JUN-03 King            De Haan; King
  100 16-AUG-02 Faviet          Faviet; Greenberg
  100 17-AUG-02 Greenberg       Faviet; Greenberg
  110 07-JUN-02 Gietz           Gietz; Higgins
  110 07-JUN-02 Higgins         Gietz; Higgins
  • 聚合函数： LISTAGG([ALL/DISTINCT] 字段, 连接符) WITH GROUP(ORDER BY 合并字段的排序) 需要结合 GROUP BY 语句使用 
    • SELECT 
	department_id "Dept.",
    LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date) "Employees"
FROM employees
GROUP BY department_id
ORDER BY department_id;

Dept. Employees
------ ------------------------------------------------------------
  10     Whalen
  20     Hartstein; Fay
  30     Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares
  40     Mavris
  50     Kaufling; Ladwig; Rajs; Sarchand; Bell; Mallin; Weiss; Davies
  60     Austin; Hunold; Pataballa; Lorentz; Ernst
  70     Baer

WM_CONCAT 函数

WM_CONCAT 是一个连接函数，它将多个值连接成一个字符串，重复值可直接使用 DISTINCT 关键字去重，这个函数主要用于连接少量的数据

  • 默认是采用逗号隔开字符
//拼接订单表的ID
SELECT WM_CONCAT(DISTINCT ID) IDS FROM OMS_ORDER

CONCAT 函数

CONCAT 函数返回 char1 与 char2 的连接。char1 和 char2 可以是 CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB或NCLOB 中的任何数据类型。返回的字符串与 char1 的字符集相同。其数据类型取决于参数的数据类型

  • （该函数等同于连接运算符 (||)）
  • CONCAT 函数格式 CONCAT(char1, char2)
SELECT 
	CONCAT(CONCAT(last_name, '''s job category is '), job_id) "Job" 
FROM employees 
WHERE employee_id = 152;
 
Job
------------------------------------------------------
Hall's job category is SA_REP

CONNECT BY 字句

CONNECT BY 子句用于在表中建立层次结构关系，它基于指定的列之间的父子关系进行数据连接。CONNECT BY 子句可以与 SELECT 语句结合使用，从而可以在查询结果中包含跨越多层次的数据

  • CONNECT BY字句格式 select … from tablename start with 条件1 connect by 条件2 where 条件3
    • start with 条件1：设置层级起点，用来限制第一层的数据，或者叫根节点数据；以这部分数据为基础来查找第二层数据，然后以第二层数据查找第三层数据，以此类推。默认以全部行为起点
    • connect by 条件2 ：用来指明在查找数据时以怎样的一种关系去查找；比如说查找第二层的数据时用第一层数据某个字段进行匹配，如果这个条件成立那么查找出来的数据就是第二层数据，同理往下递归匹配
      • prior ： 表示上一层级的标识符。经常用来对下一层级的数据进行限制。不可以接伪列。PRIOR在等号前面和后面，查询的数据是不一样的
      • level ：伪列（关键字），代表树形结构中的层级编号（数字序列结果集）
    • connect_by_root() ：显示根节点列。经常用来分组
    • connect_by_isleaf ：1是叶子节点，0不是叶子节点。在制作树状表格时必用关键字
    • sys_connect_by_path() ：将递归过程中的列进行拼接

目前遇到的场景是构造连续的数字、日期、切割字符串

//返回1到3
select level from dual connect by level <= 3;

//返回当前时间的三天日期：2025-04-10 22:49:01、2025-04-11 22:49:01、2025-04-12 22:49:01
select sysdate+level from dual connect by level <= 3;

//字符串按照逗号拆分，返回1到3
SELECT REGEXP_SUBSTR('1,2,3', '[^,]+', 1, LEVEL) 
FROM DUAL 
CONNECT BY REGEXP_SUBSTR('1,2,3', '[^,]+', 1, LEVEL) IS NOT NULL

LPDA 函数

LPDA 函数返回 expr1，用 expr2 中的字符序列左填充为长度为 n 的字符（如果没有指定 expr2，则默认为一个空格。如果 expr1 长于 n，则该函数返回 expr1 中适合 n 的部分。）。该函数可用于格式化查询的输出结果

  • LPDA 函数格式 LPDA(expr1, n, expr2)
//返回： *.*.*.*.*Page 1
SELECT LPAD('Page 1',15,'*.') "LPAD example" FROM DUAL;

NVL 函数

NVL 函数可以在查询结果中用字符串替换 null（返回空白）。如果 expr1 为空，则 NVL 返回 expr2。如果 expr1 不是空值，则 NVL 返回 expr1

  • NVL 函数格式 NVL(expr1, expr2)
//返回 1
SELECT NVL(NULL, 1) FROM DUAL;

NVL2 函数

NVL2 函数可根据指定表达式为空或非空来确定查询返回的值。如果 expr1 不是空值，那么 NVL2 返回 expr2。如果 expr1 为空，则 NVL2 返回 expr3

  • NVL2 函数格式 NVL2(expr1, expr2, expr3)
// 返回 2
SELECT NVL2(NULL, 1, 2) FROM DUAL;

TRUNC 函数

TRUNC 函数按指定的格式截取输入的数据

TRUNC 函数格式

  • 处理日期 TRUNC(date[,fmt])该日期将按指定的日期格式截取；忽略日期格式则由最近的日期截取 
    • //返回当前时间 2024-01-04 00:00:00
select trunc(sysdate) from dual;

//返回当年第一天 2024-01-01 00:00:00
select trunc(sysdate,'yy') from dual;

//返回当月的第一天 2024-01-01 00:00:00
select trunc(sysdate,'mm') from dual;

//返回当前星期的第一天，即星期天 2023-12-31 00:00:00
select trunc(sysdate,'d') from dual;

//返回当前日期,今天是 2024-01-04 00:00:00
select trunc(sysdate,'dd') from dual;

//返回本小时的开始时间 2024-01-04 23:00:00
select trunc(sysdate ,'HH24') from dual;

//返回本分钟的开始时间 2024-01-04 23:05:00
select trunc(sysdate ,'MI') from dual;
  • 处理number型数字 TRUNC(number[,decimals])number 待做截取处理的数值，decimals 指明需保留小数点后面的位数(可选项)忽略它则截去所有的小数部分。 注意：截取时并不对数据进行四舍五入 
    • //将小数点右边指定位数后面的截去: 123.45
select trunc(123.4567,2) from dual;

//第二个参数可以为负数，表示将小数点左边指定位数后面的部分截去，即均以0记: 1200
select trunc(1234.4567,-2) from dual;

//默认截去小数点后面的部分: 123
select trunc(123.4567) from dual;

TO_CHAR 函数

TO_CHAR 函数将数值型或者日期型转化为字符型

  • TO_CHAR 格式 TO_CHAR(value, format)
//返回：123.990
SELECT TO_CHAR(123.99, '999.999') FROM DUAL; 

//返回： 2023-12-12 13:13:13
SELECT TO_CHAR(sysdate, 'yyyy-mm-dd HH24:mi:ss') FROM DUAL; 

//返回： 2023-12-12 12:13:13
SELECT TO_CHAR(sysdate, 'yyyy-mm-dd HH12:mi:ss') FROM DUAL; 

//当日零点零时零分
SELECT TO_CHAR(trunc(sysdate), 'yyyy-mm-dd HH24:mi:ss') FROM DUAL; 返回： 2023-12-12 00:00:00

//当日 23:59:59
SELECT TO_CHAR(trunc(sysdate)+1-1/86400, 'yyyy-mm-dd HH24:mi:ss') FROM DUAL; 返回： 2023-12-12 23:59:59

TO_DATE 函数

TO_DATE 将字符转换为 DATE 数据类型的值

  • TO_DATE 格式 TO_DATE(string, format)
//返回： 2023/12/12 12:13:00
SELECT TO_DATE('2023-12-12 12:13', 'yyyy/mm/dd HH24:mi:ss') FROM DUAL;

MONTHS_BETWEEN 函数

MONTHS_BETWEEN 是一个内置函数，它返回两个给定日期之间的月数

  • MONTHS_BETWEEN 格式 MONTHS_BETWEEN(date1, date2)
    • 如果 date1 晚于 date2，则结果为正
    • 如果 date1 早于 date2，则结果为负
    • 如果 date1 和 date2 是某个月的同一天或都是所在月的最后一天，则结果始终为整数。否则 Oracle 数据库会根据有 31 天的月份计算结果的小数部分，并考虑时间分量 date1 和 date2 的差异
    • 如果任意一个参数为 NULL， MONTHS_BETWEEN() 将返回 NULL
SELECT MONTHS_BETWEEN(DATE '2024-05-20', DATE '2024-02-10') FROM dual;//返回 3.32258064516129032258064516129032258065

ADD_MONTHS 函数

ADD_MONTHS 是一个内置函数，它用于在给定的日期上增加或减少指定数量的月份

  • ADD_MONTHS 格式 ADD_MONTHS(date, months)
    • 如果任意一个参数为 NULL， ADD_MONTHS() 将返回 NULL
SELECT ADD_MONTHS(date '2023-02-11', 5) FROM DUAL;//返回 2023-07-11 00:00:00

SELECT ADD_MONTHS(date '2023-02-11', -1) FROM DUAL;//返回 2023-01-11 00:00:00

 

posted @ 2024-01-04 23:13  伊文小哥  阅读(312)  评论(0)    收藏  举报