(Oracle数据库)函数

一. 字符串函数

(1) 求长

LENGTH(字符串)	求字符长度(几个字符)
LENGTHB(字符串)	求字节长度(几个Byte)

--length example
SELECT LENGTH('莱因哈特') FROM DUAL;
--lengthb example
SELECT LENGTHB('莱因哈特') FROM DUAL;

        使用LENGTH查询可得该字符串长度为4个字符。

        在ZHS16GBK下，每个字符占2byte长度,使用LENGTHB查询可得该字符串长度为8个byte。

DUAL函数

        在Oracle数据库中，dual是一个特殊的单行单列表。它基本上是一个虚拟表，只包含一行一列，并具有某些特殊功用。所有用户都可以使用dual名称来访问它。dual表的主要用途包括选择系统变量、计算常量表达式、执行伪列查询等。dual在Oracle中主要起到了一个符合语法和规范的作用，为用户提供了方便且灵活的方式来执行各种查询和操作。

(2) 查找

INSTR(字符串,要查找的子串,子串第一次位置, 子串出现次数)	返回子串在字符串中首次出现的位置
SUBSTR(字符串,起始位置,长度)	从字符串中提取子字符串

     使用INSTIR函数可以查询到"Wen"在字符串中起始位置是第六个字符。

     使用SUBSTR函数可以查询到，字符串第一个字符开始的四个字符是"Yang"。

     Oracle中，字符串下标是从1而不是从0开始的。 

(3) ASCII转换

ASCII(字符串)	返回字符串首字符的 ASCII 值
CHR(数字)	返回给定 ASCII 值的字符。

--CHR example
SELECT CHR(65) FROM DUAL;
--ASCII example
SELECT ASCII('A') FROM DUAL;

        字母"A"的ASCII编码为65，使用CHR和ASCII可以实现两者互换。

(4) 大小写转换

INITCAP(字符串)	将字符串的每个单词的首字母转换为大写，其余字母转换为小写
UPPER(字符串)	将字符串全改为大写
LOWER(字符串)	将字符串全改为小写

--initcap example
SELECT INITCAP('kircheis') FROM DUAL;
--upper example
SELECT UPPER('KIRCHEIS') FROM DUAL;
--lower example
SELECT LOWER('kircheis') FROM DUAL;

        使用这三个函数可以统一数据库的命名规范。

        要注意，虽然数据库对大小写不敏感，你写SeLeCt，FRom之类的并不会报错，但是为了提高程序可读性，规范代码，一般推荐关键词、变量、表名、列名等全大写。

(5) 替换

REPLACE(字符串,需替换的内容,替换内容)	在字符串中替换子字符串
TRANSLATE(列表,需替换的列表,替换列表)	在字符串中替换字符

--replace example
SELECT REPLACE('Wolfgang Mittermeyer',''Mittermeyer','Reuenthal') FROM DUAL;
--translate example
SELECT TRANSLATE('Wolfgang Mittermeyer',
                 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
                 'abcdefghijklmnopqrstuvwxyz') 
FROM DUAL;

       

        REPLACE作用为字符串替换，作用对象为字符串，可以对整个字符串进行替换；

        TRANSLATE作用为字符替换，作用对象为字符，可以对单个字符进行替换。

(6) 连接

CONCAT('连接字符串1','连接字符串2'...)

连接两个或多个字符串

--concat example
SELECT CONCAT('Arexandor ', 'Bucock') FROM DUAL;

(7) 填充

LPAD(字符串,长度,指定字符)	在字符串的左侧填充指定的字符，直到达到指定的长度
RPAD(字符串,长度,指定字符)	在字符串的右侧填充指定的字符，直到达到指定的长度

--lpad example
SELECT LPAD('Hilde',10, 'AB') FROM DUAL;
--rpad example
SELECT RPAD('Hilde',10, 'ABC') FROM DUAL;

        分别向字符串的左右两端插入一定的字符，补全字符串长度到指定值为止，如果字符长度不够的话就循环补全。

(8) 删除

TRIM([leading|trailing|both],[指定字符],字符串)	从字符串中删除空格或者指定的字符,leading|trailing|both 是可选字符,用于指定从前面/后面/两端删除空格或者指定字符 指定字符可选,不加默认为删除空格
LTRIM(字符串,[指定字符])	从字符串的左侧删除空格或者指定的字符
RTRIM(字符串,[指定字符])	从字符串的右侧删除空格或者指定的字符

--trim example
SELECT TRIM('   Lohengramm   '), FROM DUAL;
--ltrim example
SELECT LTRIM('Lohengramm','L'), FROM DUAL;
--rtrim example
SELECT RTRIM('Lohengramm','m') FROM DUAL;

         这三种TRIM一般是用于去除空格。

二. 聚合函数

        Oracle 数据库中的聚合函数用于执行一组值上的计算，并返回单个值。这些函数通常与 GROUP BY子句一起使用，以按一个或多个列对结果集进行分组，并对每个组执行聚合计算。

MAX()	求最大值
MIN()	求最小值
AVG()	计算平均值
SUM()	计算算术和
COUNT()	计算行数

        测试用表

DROP TABLE IF EXISTS TEST_TABLE;
CREATE TABLE TEST_TABLE(NAME VARCHAR2(10),VALUE INTEGER);
INSERT INTO TEST_TABLE(NAME,VALUE) VALUES('A','128');
INSERT INTO TEST_TABLE(NAME,VALUE) VALUES('BC','256');
INSERT INTO TEST_TABLE(NAME,VALUE) VALUES('DEF','384');
INSERT INTO TEST_TABLE(NAME,VALUE) VALUES('GHIJ','512');
INSERT INTO TEST_TABLE(NAME,VALUE) VALUES('KLMNO','640');
SELECT ROWNUM ID,NAME,VALUE FROM TEST_TABLE;
--假设创建一个用于测试的表TEST_TABLE
|      TEST_TABLE       |
|-----------------------|
|  ID   |  NAME | VALUE | 
|   1   | A     |  128  |
|   2   | BC    |  256  |
|   3   | DEF   |  384  |
|   4   | GHIJ  |  512  |
|   5   | KLMNO |  640  |
--ROWNUM是一个伪列,给表中每行元素都加上了序号,ID是自己设置的ROWNUM的别称

(1) 求最大值MAX

        MAX()函数用于返回特定列的最大值

--MAX example
SELECT MAX(VALUE) FROM TEST_TABLE;

 (2) 求最小值MIN

        MIN()函数用于返回特定列的最小值

--MAX example
SELECT MAX(VALUE) FROM TEST_TABLE;

(3) 求平均值AVG

        AVG()函数用于计算特定列的平均值

--MAX example
SELECT AVG(VALUE) FROM TEST_TABLE;

 (4) 求总和SUM

        SUM()函数用于计算特定列的平均值

--SUM example
SELECT SUM(VALUE) FROM TEST_TABLE;

 (5) 求行数COUNT

        COUNT()函数用于计算特定列中的行数

--MAX example
SELECT COUNT(VALUE) FROM TEST_TABLE;

        全部计算结果如下图所示:

注意事项

        ①聚合函数不能在WHERE子句中使用，因为WHERE子句在聚合之前过滤行。如果需要根据聚合结果来过滤数据，应该使用 HAVING 子句。

        ②聚合函数不能在 SELECT 列表的非聚合列中使用。如果 SELECT 语句中包含聚合函数，那么 SELECT 列表中的非聚合列必须出现在 GROUP BY 子句中。这是因为非聚合列的值对于每个组可能是不确定的，所以数据库需要知道如何根据GROUP BY子句来组织这些值。

        ③不能在 PL/SQL 控制结构中使用。在 PL/SQL中，不能直接在控制结构（如WHILE循环）中使用聚合函数来操作查询结果，因为聚合函数是在SQL查询执行期间计算的，而控制结构是在 PL/SQL 代码块执行期间评估的。可以在 PL/SQL 块中执行一个返回聚合结果的 SQL 查询，然后在控制结构中处理这个结果，但不能直接在控制结构中调用聚合函数。

        ④不能嵌套使用。大多数数据库系统不允许你在一个聚合函数内部嵌套另一个聚合函数,例如MAX(COUNT())。这是因为这样的嵌套可能会导致语义上的不明确性，并且数据库系统通常不支持这种复杂的计算。

三. 数学函数

ROUND	取小数位数
TRUNC	取小数位数
ABS	绝对值
SIGN	求正负
CEIL	向上取整
FLOOR	向下取整
MOD	取余
POWER	取幂

(1) 取小数位数ROUND

        ROUND函数用于将数字四舍五入到指定的小数位数。

--ROUND example
SELECT ROUND(2.7182818, 2) AS ROUNUM FROM DUAL;
--将e(2.7182818)四舍五入到两位小数

(2) 取小数位数TRUNC

        TRUNC函数用于用于将数字截断为指定的小数位数。

--TTRUNC example
SELECT TRUNC(2.7182818, 2) AS TRUNUM FROM DUAL;
--将e(2.7182818)截取到两位小数

        从运行结果可以看出这两个函数的区别，ROUND在取位数的时候会考虑四舍五入，第三位小数为8大于5，因而前一位进1；而TRUNC则只是单纯地将后面位数的小数全部去除。这两个函数无分优劣，各有各的用途。

(3) 绝对值ABS

        ABS用于计算数字的绝对值。

--ABS example
SELECT ABS(-16) AS ABSNUM1 FROM DUAL;
SELECT ABS(3.14) AS ABSNUM2 FROM DUAL;
--取-16的绝对值16,取3.14的绝对值3.14

(4) 正负判断SIGN

--SIGN example
SELECT SIGN(-16) AS SIGNBOOL1 FROM DUAL;
SELECT SIGN(3.14) AS SIGNBOOL2 FROM DUAL;
--取-16的正负为负数，取3.14的正负为正数

        根据输入值的正负返回相应的整数。如果值为正，返回1；如果为负，返回-1；如果为零，返回0。

        可以得出，使用ABS函数取-16的绝对值为16，使用SIGN函数取-16的符号位为-1；使用ABS函数取3.14的绝对值为3.14，使用SIGN函数取3.14的符号位为1。

        这里的SIGNBOOL取名不是很严谨,因为BOOL类型只有1或者0而没有-1。

(5) 向上取整CEIL

        CEIL返回大于或等于输入值的最小整数，即向上取整。

--CEIL example
SELECT CEIL(1.414) AS CEILNUM FROM DUAL;
--将1.414向上取整

(6) 向下取整FLOOR

        FLOOR返回小于或等于输入值的最大整数，即向下取整。

--FLOOR example
SELECT FLOOR(1.414) AS FLOORNUM FROM DUAL;
--将1.414向下取整

        从运行结果可以看出，CEIL函数对1.414进行了向上取整，结果为2；FLOOR函数对1.414进行了向下取整，结果为1。

(7) 取余MOD

        MOD用于求两个数值相除的余数。例如MOD(10,3)=1。

--MOD example
SELECT MOD(10,3) AS MODRESULT FROM DUAL;
--10除3等于3余1

(8) 取幂POWER

        POWER用于求两个数值相除的余数。例如POWER(2,10)=1024

--POWER example
SELECT POWER(2,10) AS POWERRESULT FROM DUAL;
--2的10次幂为1024

四. 转换函数

CAST	转换为目标函数类型
TO_NUMBER	转换为数字
TO_CHAR	转换为字符
TO_DATE	转换为时间

(1) CAST

        CAST将一个内置数据类型或集合类型转变为另一个内置数据类型或集合类型。

--CAST example
SELECT CAST(SYSDATE AS VARCHAR2(100)) FROM DUAL;
--提取系统时间并转换为可变字符串类型

(2) TO_CHAR

        TO_CHAR将日期或数字转换为字符串，并可以使用格式模型来指定输出的格式。

--TO_CHAR example
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
--提取系统时间并转换为可变字符串类型
SELECT TO_CHAR(12345.67, '99999D99') FROM DUAL;
--将数字12345.67转换为可变字符串类型

(3) TO_DATE

        TO_DATE将字符串转换为日期类型，通常与格式模型一起使用。

--TO_DATE example
SELECT TO_DATE('2024-04-12', 'YYYY-MM-DD') FROM DUAL;
--提取系统时间并转换为可变字符串类型

(3) TO_NUMBER

        TO_NUMBER将字符串转换为数字类型。

--TO_DATE example
SELECT TO_NUMBER('12345') FROM DUAL;
--将字符串'12345'转换为数字

(4) ROWID转换

CHARTOROWID	VARCHAR2转换为对应的ROWID
ROWIDTOCHAR	ROWID转换为对应的VARCHAR2

--ROWID example
SELECT CHARTOROWID('rowid_string') FROM DUAL;
--将字符串类型的'rowid_string'转换为ROWID
SELECT ROWIDTOCHAR(ROWID) FROM some_table WHERE some_condition;
--将some_table表中的some_condition情况下的ROWID转换为字符类型

(5) 字符集CONVERT

    CONVERT将字符串从一个字符集转变为另一个字符集。

--CONVERT example
SELECT CONVERT('伊谢尔伦', 'AL32UTF8', 'ZHS16GBK') FROM DUAL;
--将字符串'伊谢尔伦'由AL32UTF8转为ZHS16GBK

(6) 转换格式

--日期

    'YYYY-MM-DD'：
        YYYY：代表四位数的年份。
        MM：代表两位数的月份。
        DD：代表两位数的日期。
    'YYYY-MM-DD HH24:MI:SS'：
        HH24：代表24小时制的小时数。
        MI：代表分钟数。
        SS：代表秒数。

--数字
    '9999999999.99'：
        9：代表一个数字占位符。如果原始数字的对应位置有数字，则显示该数字；否则，不显示任何内容（不同于0，0会在没有数字时显示为0）。
        .：代表小数点。
    'FM9999999999.99'：
        FM：是一个前缀，用于删除结果字符串开头的任何空格。这在避免前导空格时特别有用。
    '0999999999.99'：
        0：代表一个零占位符。如果原始数字的对应位置没有数字，则显示为0。

--在所有这些格式中，数字的数量表示该部分的最大长度。例如，在'9999999999.99'中，整数部分最多可以有9位，小数部分最多有2位。

五. 日期函数

(1) 查询SYSDATE

        SYSDATE返回当前日期和时间，包括时、分、秒。这是Oracle中最常用的日期函数之一

--SYSDATE example
SELECT SYSDATE FROM DUAL;

(2) 查询CURRENT_DATE

        CURRENT_DATE返回当前会话的时区日期。

--SYSDATE example
SELECT CURRENT_DATE FROM DUAL;

        SYSDATE和CURRENT_DATE区别

        例如，某个数据库在东六区，在东十区的客户端上使用该数据库。

        SYSDATE将会返回数据库时区(东六区)的时间；

        CURRENT_DATE考虑到了数据库会话的时区设置，返回本地(东十区)的时间。

(3) ADD_MONTHS

        ADD_MONTHS在给定日期上增加或减去指定的月数。

--ADD_MONTHS example
SELECT ADD_MONTHS(SYSDATE, 3) FROM DUAL;
-- 查询当前数据库并加3个月  
SELECT ADD_MONTHS(SYSDATE, -1) FROM DUAL;
-- 查询当前数据库并减1个月 

(4) LAST_DAY

       LAST_DAY返回给定日期所在月份的最后一天的日期。

--LAST_DAY example
SELECT LAST_DAY(SYSDATE) FROM DUAL;

(5) NEXT_DAY

        NEXT_DAY返回指定日期后的下一个星期几的日期。

--NEXT_DAY example
SELECT NEXT_DAY(SYSDATE, '星期一') FROM DUAL;
--下一个周一的日期
--该日期设置需要根据数据库语言设置决定,如果使用英文环境,需要换成'MONDAY'

(6) EXTRACT

        从日期或时间值中提取指定的部分，如年、月、日等。

--EXTRACT example
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;
--提取年份

        所有日期函数例子结果如图所示

六. 条件函数

(1) CASE

        在Oracle中，CASE语句是一种条件表达式，用于根据一系列条件来返回不同的结果。它类似于其他编程语言中的switch或if-else。CASE语句的基本语法如下：

--CASE DEFINE
CASE 要评估表达式 
    WHEN 情况1 THEN 输出/结果1  
    WHEN 情况2 THEN 输出/结果2
    ...  
    ELSE 输出/结果N
END

        假设使用聚合函数的建表语句，建立TEST_TABLE表。使用CASE将VALUE值<=256的标记为LOW，将>256且<=512的值标记为MEDIUM，将>512的标记为HIGH,其他情况则为UNKNOWN。

--case example1
SELECT ROWNUM AS ID, NAME, VALUE,  
       CASE   
           WHEN VALUE <= 256 THEN 'LOW'  
           WHEN VALUE > 256 AND VALUE <= 512 THEN 'MEDIUM'  
           WHEN VALUE > 512 THEN 'HIGH'  
           ELSE 'UNKNOWN'  
       END AS category  
FROM TEST_TABLE;

        最终显示结果如图所示，将比较结果输出为CATEGORY列并显示。

(2) DECODE

        在Oracle数据库中，DECODE函数是一个条件表达式，它类似于CASE语句，用于根据输入值返回不同的结果。DECODE函数在语法上比CASE语句更简洁，但功能上有所限制。

        将上一个CASE例子改为用DECODE实现。

--DECODE example
SELECT ROWNUM AS ID, NAME, VALUE,  
       DECODE(  
           SIGN(VALUE - 256) + SIGN(512 - VALUE) + 2,  
           3, 'LOW',  
           2, 'MEDIUM',  
           1, 'HIGH',  
           'UNKNOWN'  
       ) AS category  
FROM TEST_TABLE;

        由于DECODE的限制，不能直接使用比较符号进行运算例如>，<等，只能逐条进行比较，需要先对数据进行处理。这里采用SIGN函数和数学运算来模拟CASE语句中的条件逻辑。SIGN函数返回一个数字的正负号：正数返回1，负数返回-1，0返回0。通过组合SIGN函数的返回值，可以构造一个唯一的整数，然后用DECODE函数来映射这个整数到相应的分类。

七. 判空函数

(1) COALESCE

用于返回其参数中的第一个非 NULL 值。如果所有参数都是 NULL，那么它将返回 NULL

--COALESCE example
SELECT NAME, COALESCE(VALUE, 0) AS IFNULL FROM TEST_TABLE;

        虽然 CASE 表达式也可以实现类似的功能，但 COALESCE 通常更简洁，特别是在处理多个参数时。

(2) NVL

        在Oracle数据库中，NVL函数用于将NULL值替换为另一个值。它的工作方式是检查第一个参数，如果它是NULL，则返回第二个参数的值；如果第一个参数不是NULL，则返回第一个参数的值。

-NVL example
SELECT NAME, NVL(VALUE, 0) AS IFNULL FROM TEST_TABLE;

        NVL 和 COALESCE 在功能上有些相似，但它们之间存在一些重要的区别：

        ①参数数量：NVL 只接受两个参数，而 COALESCE 可以接受任意数量的参数。
        ②NULL 处理：NVL 只检查第一个参数是否为 NULL，并据此决定是否返回替换值。而 COALESCE 会返回其参数列表中的第一个非 NULL 值。
        ③返回值：如果 NVL 的第一个参数不是 NULL，它将返回该参数的值（即使它是另一个 NULL 值）。而 COALESCE 会继续检查其后续参数，直到找到一个非 NULL 值为止。