Oracle BLOB 是一个二进制大对象数据类型，用于存储大量的二进制数据，如图像、音频、视频等。下面是与 BLOB 相关的一些函数、索引和操作。

BLOB 函数

DBMS_LOB: 提供了一组用于操作 BLOB、CLOB 和 NCLOB 数据类型的程序包。

主要函数包括：

• DBMS_LOB.OPEN: 打开 LOB
• DBMS_LOB.CLOSE: 关闭 LOB
• DBMS_LOB.READ: 读取 LOB 数据
• DBMS_LOB.WRITE: 写入 LOB 数据
• DBMS_LOB.GETLENGTH: 获取 LOB 长度
• DBMS_LOB.APPEND: 将数据追加到 LOB
• DBMS_LOB.COPY: 复制 LOB 数据
• DBMS_LOB.TRIM: 裁剪 LOB 数据
• DBMS_LOB.COMPARE: 比较两个 LOB 数据
• DBMS_LOB.INSTR: 搜索 LOB 中的模式
• DBMS_LOB.SUBSTR: 提取 LOB 的子串

UTL_RAW: 提供了一组用于操作 RAW 数据类型的程序包。

主要函数包括：

• UTL_RAW.CAST_TO_RAW: 将输入转换为 RAW 类型
• UTL_RAW.CAST_TO_VARCHAR2: 将 RAW 类型转换为 VARCHAR2 类型
• UTL_RAW.LENGTH: 返回 RAW 数据的长度
• UTL_RAW.BIT_COMPLEMENT: 返回 RAW 数据的位补
• UTL_RAW.BIT_AND: 返回两个 RAW 数据的位与运算结果
• UTL_RAW.BIT_OR: 返回两个 RAW 数据的位或运算结果
• UTL_RAW.BIT_XOR: 返回两个 RAW 数据的位异或运算结果
• UTL_RAW.CONCAT: 连接两个 RAW 数据
• UTL_RAW.SUBSTR: 从 RAW 数据中提取子串
• UTL_RAW.TRANSLATE: 根据给定的转换表进行 RAW 数据转换
• UTL_RAW.COMPARE: 比较两个 RAW 数据

索引

使用 BLOB 列作为索引的基础：
BLOB 列可以与 Oracle Text 一起使用，创建基于 BLOB 列内容的全文索引。
可以使用 DBMS_LOB.SUBSTR 函数和函数索引从 BLOB 列提取特定的子串，并在查询中使用它们。

示例：使用 emp 表中的 photo 列（BLOB 类型）作为示例。

使用 DBMS_LOB.READ 从 BLOB 中读取数据：

DECLARE
  v_buffer RAW(32767);
BEGIN
  SELECT photo INTO v_buffer FROM emp WHERE empno = 100;
  DBMS_LOB.READ(v_buffer, v_amount, v_offset, v_buffer);
END;
复制

使用 DBMS_LOB.WRITE 将数据写入 BLOB

DECLARE
  v_buffer RAW(32767);
BEGIN
  SELECT photo INTO v_buffer FROM emp WHERE empno = 100;
  DBMS_LOB.WRITE(v_buffer, v_amount, v_offset, v_buffer);
  UPDATE emp SET photo = v_buffer WHERE empno = 100;
END;
复制

获取 BLOB 的长度：

SELECT LENGTH(photo) FROM emp WHERE empno = 100;
使用 UTL_RAW.CAST_TO_VARCHAR2 将 BLOB 转换为 VARCHAR2
复制

DECLARE
  v_blob BLOB;
  v_varchar2 VARCHAR2(32767);
BEGIN
  SELECT photo INTO v_blob FROM emp WHERE empno = 100;
  v_varchar2 := UTL_RAW.CAST_TO_VARCHAR2(v_blob);
END;
复制

创建一个函数索引：

-- 创建一个提取员工照片中某个属性的函数
CREATE OR REPLACE FUNCTION get_photo_attribute(p_photo BLOB) RETURN NUMBER DETERMINISTIC IS
BEGIN
  -- 在此处实现提取属性的逻辑
  RETURN ...;
END;
/
复制

– 基于 get_photo_attribute 函数创建一个函数索引
CREATE INDEX emp_photo_attribute_idx ON emp(get_photo_attribute(photo));

json 函数

Oracle 19c提供了一系列的JSON函数，以方便地处理JSON数据。以下是一些常用的JSON函数，结合EMP表的示例说明：

JSON_VALUE: 提取JSON数据中的标量值

SELECT JSON_VALUE(emp_json, '$.name') AS emp_name
FROM (SELECT '{"name": "John", "age": 30, "city": "New York"}' AS emp_json FROM DUAL) emp;

复制

JSON_QUERY: 提取JSON数据中的对象或数组

SELECT JSON_QUERY(emp_json, '$.skills') AS emp_skills
FROM (SELECT '{"name": "John", "age": 30, "skills": ["Java", "SQL"]}' AS emp_json FROM DUAL) emp;
复制

JSON_TABLE: 将JSON数据转换为关系表

SELECT emp_name, emp_age, emp_city
FROM (SELECT '{"name": "John", "age": 30, "city": "New York"}' AS emp_json FROM DUAL) emp,
     JSON_TABLE(emp_json, '$'
                 COLUMNS (emp_name VARCHAR2(50) PATH '$.name',
                          emp_age NUMBER PATH '$.age',
                          emp_city VARCHAR2(50) PATH '$.city'));
复制

JSON_EXISTS: 检查JSON数据中是否存在指定的路径

SELECT CASE
         WHEN JSON_EXISTS(emp_json, '$.city') THEN '存在'
         ELSE '不存在'
       END AS city_exists
FROM (SELECT '{"name": "John", "age": 30, "city": "New York"}' AS emp_json FROM DUAL) emp;
复制

JSON_OBJECT: 根据键值对生成JSON对象

SELECT JSON_OBJECT('name' VALUE e.ename, 'job' VALUE e.job) AS emp_json
FROM emp e
WHERE e.empno = 7369;
复制

JSON_ARRAY: 根据查询结果生成JSON数组

SELECT JSON_ARRAYAGG(JSON_OBJECT('name' VALUE e.ename, 'job' VALUE e.job)) AS emp_json_array
FROM emp e
WHERE e.deptno = 10;
复制

JSON_OBJECTAGG: 聚合多个JSON对象

SELECT deptno, JSON_OBJECTAGG(ename, job) AS emp_data
FROM emp
GROUP BY deptno;
复制

JSON_ARRAYAGG: 聚合多个JSON数组

SELECT deptno, JSON_ARRAYAGG(JSON_OBJECT('name' VALUE ename, 'job' VALUE job)) AS emp_data
FROM emp
GROUP BY deptno;
复制

JSON_SERIALIZE: 将JSON数据转换为字符串或CLOB。

SELECT JSON_SERIALIZE(emp_json) AS emp_json_str
FROM (SELECT JSON_OBJECT('name' VALUE e.ename, 'job' VALUE e.job) AS emp_json FROM emp e WHERE e.empno = 7369) emp;
复制

JSON_MERGEPATCH: 合并两个JSON文档，根据RFC 7396定义的JSON Merge Patch算法

SELECT JSON_MERGEPATCH(base_json, patch_json) AS merged_json
FROM (SELECT '{"name": "John", "age": 30, "city": "New York"}' AS base_json,
             '{"age": 31, "city": null}' AS patch_json
      FROM DUAL) emp;
复制

JSON_EQUAL: 检查两个JSON文档是否相等

SELECT CASE
         WHEN JSON_EQUAL(json1, json2) THEN '相等'
         ELSE '不相等'
       END AS json_comparison
FROM (SELECT '{"name": "John", "age": 30, "city": "New York"}' AS json1,
             '{"city": "New York", "name": "John", "age": 30}' AS json2
      FROM DUAL) emp;
复制

JSON_TEXTCONTAINS2: 检查JSON数据中是否包含指定的文本

SELECT ename
FROM emp e
WHERE JSON_TEXTCONTAINS2(e.emp_json, '$.skills[*]', 'SQL');
复制

JSON_KEYS: 获取JSON对象的所有键。

SELECT JSON_KEYS(emp_json) AS emp_keys
FROM (SELECT '{"name": "John", "age": 30, "city": "New York"}' AS emp_json FROM DUAL) emp;
复制

JSON_LENGTH: 获取JSON对象或数组的长度

SELECT JSON_LENGTH(emp_json, '$.skills') AS skill_count
FROM (SELECT '{"name": "John", "age": 30, "skills": ["Java", "SQL"]}' AS emp_json FROM DUAL) emp;
复制

JSON_DATAGUIDE: 自动生成JSON数据指南，以描述JSON数据的结构

SELECT JSON_DATAGUIDE(emp_json) AS data_guide
FROM (SELECT '{"name": "John", "age": 30, "city": "New York"}' AS emp_json FROM DUAL) emp;
复制

JSON_REMOVE: 删除JSON数据中指定的属性

SELECT JSON_REMOVE(emp_json, '$.city') AS emp_without_city
FROM (SELECT '{"name": "John", "age": 30, "city": "New York"}' AS emp_json FROM DUAL) emp;
复制

JSON_INSERT: 在JSON数据中插入新的属性

SELECT JSON_INSERT(emp_json, '$.country', 'USA') AS emp_with_country
FROM (SELECT '{"name": "John", "age": 30, "city": "New York"}' AS emp_json FROM DUAL) emp;
复制

JSON_REPLACE: 替换JSON数据中的属性

SELECT JSON_REPLACE(emp_json, '$.city', 'San Francisco') AS emp_updated_city
FROM (SELECT '{"name": "John", "age": 30, "city": "New York"}' AS emp_json FROM DUAL) emp;
复制