526互联

Oracle 常用函数

发布时间 2024-01-04 23:13:13作者: 伊文小哥

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;

 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;

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

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

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;