Oracle数据库常见内置函数

Oracle数据库常见内置函数

zenos 17 2024-07-18

函数分类

1. 字符函数（Character Functions）：

- CHR: 返回与指定的ASCII码相对应的字符。

- CONCAT: 将两个字符串连接起来。

- INITCAP: 将字符串的每个单词的首字母转换为大写。

- INSTR: 返回子字符串在字符串中第一次出现的位置。

- LENGTH: 返回字符串的长度。

- LOWER: 将字符串转换为小写。

- LPAD: 在字符串的左侧填充指定的字符。

- RPAD: 在字符串的右侧填充指定的字符。

- LTRIM: 删除字符串左侧的空格或其他指定字符。

- RTRIM: 删除字符串右侧的空格或其他指定字符。

- REPLACE: 用新的子字符串替换字符串中的子字符串。

- SUBSTR: 返回字符串中的子字符串。

- UPPER: 将字符串转换为大写。

2. 数值函数（Number Functions）：

- ABS: 返回数值的绝对值。

- CEIL: 返回大于等于指定数值的最小整数。

- FLOOR: 返回小于等于指定数值的最大整数。

- MOD: 返回两个数值相除的余数。

- ROUND: 按照指定的小数位数对数值进行四舍五入。

- TRUNC: 按照指定的小数位数截断数值。

3. 日期函数（Date Functions）：

- SYSDATE: 返回当前系统日期和时间。

- CURRENT_DATE: 返回当前会话的日期和时间。

- ADD_MONTHS: 返回增加指定月数后的日期。

- MONTHS_BETWEEN: 返回两个日期之间的月数。

- NEXT_DAY: 返回指定日期之后的第一个指定星期几的日期。

- LAST_DAY: 返回指定日期所属月份的最后一天。

- TRUNC: 按照指定的日期部分截断日期。

- ROUND: 按照指定的日期部分对日期进行四舍五入。

4. 转换函数（Conversion Functions）：

- TO_CHAR: 将日期或数值转换为字符串。

- TO_DATE: 将字符串转换为日期。

- TO_NUMBER: 将字符串转换为数值。

- CAST: 将一种数据类型转换为另一种数据类型。

5. 聚合函数（Aggregate Functions）：

- AVG: 返回数值列的平均值。

- COUNT: 返回列中非空值的个数。

- MAX: 返回列中的最大值。

- MIN: 返回列中的最小值。

- SUM: 返回数值列的总和。

- STDDEV: 返回列值的标准偏差。

- VARIANCE: 返回列值的方差。

6. 分析函数（Analytical Functions）：

- RANK: 返回排序值的排名。

- DENSE_RANK: 返回没有间断排名的排名。

- ROW_NUMBER: 返回行的序号。

- LEAD: 返回当前行之后某个偏移量的行值。

- LAG: 返回当前行之前某个偏移量的行值。

7. 其他函数：

- NVL: 如果表达式1为空，则返回表达式2的值。

- NVL2: 如果表达式1不为空，则返回表达式2，否则返回表达式3。

- DECODE: 基于表达式的值返回不同的结果。

- COALESCE: 返回第一个非空表达式的值。

- NULLIF: 如果两个表达式相等，则返回NULL，否则返回第一个表达式的值。

具体使用方法

1. 字符函数（Character Functions）

-- CHR: 返回与指定的ASCII码相对应的字符
SELECT CHR(65) FROM DUAL;  -- 返回 'A'

-- CONCAT: 将两个字符串连接起来
SELECT CONCAT('Hello', ' World') FROM DUAL;  -- 返回 'Hello World'

-- INITCAP: 将字符串的每个单词的首字母转换为大写
SELECT INITCAP('hello world') FROM DUAL;  -- 返回 'Hello World'

-- INSTR: 返回子字符串在字符串中第一次出现的位置
SELECT INSTR('Hello World', 'o') FROM DUAL;  -- 返回 5

-- LENGTH: 返回字符串的长度
SELECT LENGTH('Hello World') FROM DUAL;  -- 返回 11

-- LOWER: 将字符串转换为小写
SELECT LOWER('HELLO WORLD') FROM DUAL;  -- 返回 'hello world'

-- LPAD: 在字符串的左侧填充指定的字符
SELECT LPAD('Hello', 10, '*') FROM DUAL;  -- 返回 '*****Hello'

-- RPAD: 在字符串的右侧填充指定的字符
SELECT RPAD('Hello', 10, '*') FROM DUAL;  -- 返回 'Hello*****'

-- LTRIM: 删除字符串左侧的空格或其他指定字符
SELECT LTRIM('  Hello', ' ') FROM DUAL;  -- 返回 'Hello'

-- RTRIM: 删除字符串右侧的空格或其他指定字符
SELECT RTRIM('Hello  ', ' ') FROM DUAL;  -- 返回 'Hello'

-- REPLACE: 用新的子字符串替换字符串中的子字符串
SELECT REPLACE('Hello World', 'World', 'Oracle') FROM DUAL;  -- 返回 'Hello Oracle'

-- SUBSTR: 返回字符串中的子字符串
SELECT SUBSTR('Hello World', 1, 5) FROM DUAL;  -- 返回 'Hello'

-- UPPER: 将字符串转换为大写
SELECT UPPER('hello world') FROM DUAL;  -- 返回 'HELLO WORLD'

2. 数值函数（Number Functions）

-- ABS: 返回数值的绝对值
SELECT ABS(-5) FROM DUAL;  -- 返回 5

-- CEIL: 返回大于等于指定数值的最小整数
SELECT CEIL(5.2) FROM DUAL;  -- 返回 6

-- FLOOR: 返回小于等于指定数值的最大整数
SELECT FLOOR(5.8) FROM DUAL;  -- 返回 5

-- MOD: 返回两个数值相除的余数
SELECT MOD(10, 3) FROM DUAL;  -- 返回 1

-- ROUND: 按照指定的小数位数对数值进行四舍五入
SELECT ROUND(5.567, 2) FROM DUAL;  -- 返回 5.57

-- TRUNC: 按照指定的小数位数截断数值
SELECT TRUNC(5.567, 2) FROM DUAL;  -- 返回 5.56

3. 日期函数（Date Functions）

-- SYSDATE: 返回当前系统日期和时间
SELECT SYSDATE FROM DUAL;  -- 返回当前日期和时间

-- CURRENT_DATE: 返回当前会话的日期和时间
SELECT CURRENT_DATE FROM DUAL;  -- 返回当前会话的日期和时间

-- ADD_MONTHS: 返回增加指定月数后的日期
SELECT ADD_MONTHS(SYSDATE, 3) FROM DUAL;  -- 返回当前日期后3个月的日期

-- MONTHS_BETWEEN: 返回两个日期之间的月数
SELECT MONTHS_BETWEEN('2024-12-31', '2024-01-01') FROM DUAL;  -- 返回 11

-- NEXT_DAY: 返回指定日期之后的第一个指定星期几的日期
SELECT NEXT_DAY(SYSDATE, 'MONDAY') FROM DUAL;  -- 返回下一个星期一的日期

-- LAST_DAY: 返回指定日期所属月份的最后一天
SELECT LAST_DAY(SYSDATE) FROM DUAL;  -- 返回当前月份的最后一天

-- TRUNC: 按照指定的日期部分截断日期
SELECT TRUNC(SYSDATE, 'MONTH') FROM DUAL;  -- 返回当前月份的第一天

-- ROUND: 按照指定的日期部分对日期进行四舍五入
SELECT ROUND(SYSDATE, 'MONTH') FROM DUAL;  -- 返回最近的月份的第一天或最后一天

4. 转换函数（Conversion Functions）

-- TO_CHAR: 将日期或数值转换为字符串
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL;  -- 返回当前日期的字符串格式

-- TO_DATE: 将字符串转换为日期
SELECT TO_DATE('2024-12-31', 'YYYY-MM-DD') FROM DUAL;  -- 返回日期格式

-- TO_NUMBER: 将字符串转换为数值
SELECT TO_NUMBER('12345') FROM DUAL;  -- 返回数值 12345

-- CAST: 将一种数据类型转换为另一种数据类型
SELECT CAST('12345' AS NUMBER) FROM DUAL;  -- 返回数值 12345

5. 聚合函数（Aggregate Functions）

-- AVG: 返回数值列的平均值
SELECT AVG(salary) FROM employees;

-- COUNT: 返回列中非空值的个数
SELECT COUNT(*) FROM employees;

-- MAX: 返回列中的最大值
SELECT MAX(salary) FROM employees;

-- MIN: 返回列中的最小值
SELECT MIN(salary) FROM employees;

-- SUM: 返回数值列的总和
SELECT SUM(salary) FROM employees;

-- STDDEV: 返回列值的标准偏差
SELECT STDDEV(salary) FROM employees;

-- VARIANCE: 返回列值的方差
SELECT VARIANCE(salary) FROM employees;

6. 分析函数（Analytical Functions）

-- RANK: 返回排序值的排名
SELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC) FROM employees;

-- DENSE_RANK: 返回没有间断排名的排名
SELECT employee_id, salary, DENSE_RANK() OVER (ORDER BY salary DESC) FROM employees;

-- ROW_NUMBER: 返回行的序号
SELECT employee_id, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) FROM employees;

-- LEAD: 返回当前行之后某个偏移量的行值
SELECT employee_id, salary, LEAD(salary, 1) OVER (ORDER BY salary DESC) FROM employees;

-- LAG: 返回当前行之前某个偏移量的行值
SELECT employee_id, salary, LAG(salary, 1) OVER (ORDER BY salary DESC) FROM employees;

7. 其他函数

-- NVL: 如果表达式1为空，则返回表达式2的值
SELECT NVL(commission_pct, 0) FROM employees;

-- NVL2: 如果表达式1不为空，则返回表达式2，否则返回表达式3
SELECT NVL2(commission_pct, 'Has Commission', 'No Commission') FROM employees;

-- DECODE: 基于表达式的值返回不同的结果
SELECT DECODE(department_id, 10, 'Administration', 20, 'Marketing', 'Other') FROM employees;

-- COALESCE: 返回第一个非空表达式的值
SELECT COALESCE(commission_pct, salary, 0) FROM employees;

-- NULLIF: 如果两个表达式相等，则返回NULL，否则返回第一个表达式的值
SELECT NULLIF(salary, 10000) FROM employees;

SQL