SQL语法基础-其他函数V

一、数据类型转换函数

这类函数用于将数据从一种类型转换为另一种类型，非常有用于数据清洗和准备阶段。

TO_CHAR: 将数字或日期数据转换成字符串

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS formatted_date FROM dual;
-- 2024-04-21 15:50:45

SELECT TO_CHAR(123456789, 'FM$9,999,999,999') AS formatted_number FROM dual;
-- $123,456,789

TO_NUMBER: 将字符串转换成数字

SELECT TO_NUMBER('12345', '99999') AS tonumber FROM dual;
-- 12345

TO_DATE: 将字符串转换成日期

SELECT TO_DATE('2024-04-21', 'YYYY-MM-DD') AS todate FROM dual;
-- 2024-04-21 00:00:00

TO_TIMESTAMP: 将字符串转换为时间戳

SELECT TO_TIMESTAMP('2024-04-21 16:00:00', 'YYYY-MM-DD HH24:MI:SS') AS timestamp FROM dual;
-- 2024-04-21 16:00:00.000000000

CAST: 将表达式转换为指定数据类型

SELECT CAST('123' AS INTEGER) AS tonumber FROM dual;
-- 123

二、系统信息函数

这些函数提供关于数据库系统本身的信息，如用户信息、环境变量等。

USER: 返回当前数据库用户的用户名

SELECT USER FROM dual;
-- SYSTEM

UID: 返回当前用户的ID

SELECT UID FROM dual;
-- 5

SYS_CONTEXT(‘namespace’, ‘parameter’): 获取当前会话的特定环境参数

SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM dual;
-- SYSTEM

DBTIMEZONE: 返回数据库时区

SELECT DBTIMEZONE FROM dual;
-- +00:00

SESSIONTIMEZONE: 返回当前会话的时区

SELECT SESSIONTIMEZONE FROM dual;
-- +08:00

三、条件表达式函数

这些函数根据不同的条件来选择不同的输出，非常适合处理多条件查询。

DECODE: 简化IF-THEN-ELSE逻辑，根据expression的值返回对应的result

SELECT DECODE(1, 1, 'Match', 'No Match') FROM dual;
-- Match

CASE WHEN condition THEN result [ELSE result] END: 提供IF-THEN-ELSE逻辑的另一种实现方式，更易于读写

SELECT CASE WHEN 1=1 THEN 'True' ELSE 'False' END FROM dual;
-- True

NVL(expr1, expr2): 如果expr1是NULL，则返回expr2

SELECT NVL(NULL, 'Default Value') FROM dual;
-- Default Value

COALESCE(expr1, expr2, …, exprN): 返回参数列表中第一个非NULL的值

SELECT COALESCE(NULL, NULL, 'First Non-Null Value') FROM dual;
-- First Non-Null Value

四、XML相关函数

这些函数用于处理XML格式的数据，包括生成和提取XML数据。

XMLAGG: 聚合多行数据到一个XML类型的结果

SELECT XMLAGG(XMLELEMENT(E "item", column_name)).getClobVal() AS xml_output FROM table_name;

XMLELEMENT: 创建一个XML元素

SELECT XMLELEMENT("Employee", 'John Doe') FROM dual;

XMLFOREST: 创建一系列的XML元素

SELECT XMLFOREST(column_name AS "Column", another_column AS "Another") FROM table_name;

XMLPARSE: 解析字符数据为XML格式

SELECT XMLPARSE(DOCUMENT '<root><child>content</child></root>') FROM dual;

五、JSON函数（从Oracle 12c开始支持）

用于处理JSON数据，这些函数可以解析JSON文本、生成JSON文本和查询JSON数据。

JSON_VALUE: 提取JSON文档中的标量值

SELECT JSON_VALUE('{"name":"John", "age":30}', '$.name') FROM dual;

JSON_OBJECT: 生成JSON对象

SELECT JSON_OBJECT('name' VALUE 'John', 'age' VALUE 30) FROM dual;

JSON_ARRAY: 生成JSON数组

SELECT JSON_ARRAY(1, 2, 'three', NULL) FROM dual;

JSON_QUERY: 提取JSON文档中的复杂元素

SELECT JSON_QUERY('{"employees": [{"name":"John", "age":30}]}', '$.employees[*]') FROM dual;