【Oracle篇】伪列之Hierarchical Query、COLUMN_VALUE、OBJECT_ID、OBJECT_VALUE、XMLDATA（第六篇，总共六篇）

💫《博主主页》：
　　　🔎 CSDN主页__奈斯DB
　　　🔎 IF Club社区主页__奈斯、
🔥《擅长领域》：擅长阿里云AnalyticDB for MySQL(分布式数据仓库)、Oracle、MySQL、Linux、prometheus监控；并对SQLserver、NoSQL(Redis)有了解
💖如果觉得文章对你有所帮助，欢迎点赞收藏加关注💖

    作为「伪列系列」的收官之作，总算可以完结撒花了！🎉此前的5篇文章分别用独立的篇幅详细讲解了 5个 常用的伪列，那么这篇文章作为「伪列系列」的最后一篇，将用一篇文章高效梳理剩下的 5个 伪列，这些伪列平时存在感可能不高，但用对场景可以完美解决实际问题！🚀那么话不多说，先简单对剩下需要介绍的伪列进行总结👇：

• 🌳Hierarchical Query Pseudocolumns（层次查询伪列）： 用于分析和展示树形结构数据的层级关系信息
• 🧩COLUMN_VALUE Pseudocolumns（COLUMN_VALUE伪列）： 用于访问嵌套表或VARRAY中的单个元素值
• 🆔OBJECT_ID Pseudocolumns（OBJECT_ID伪列）： 为对象表中的每个行对象提供唯一标识符
• 📦OBJECT_VALUE Pseudocolumns（OBJECT_VALUE伪列）： 用于在对象表中访问和操作完整的行对象实例内容
• 📜XMLDATA Pseudocolumns（XMLDATA伪列）： 用于直接访问和操作XMLType表中的原始XML数据内容

    在Oracle 12c官方文档中可查的的伪列总共有 10个 ，对于网上的博客，以及其他学习资料对伪列的介绍都非常少，几乎没有大佬去讲关于伪列的内容，那么博主将用一个系列去认真介绍一下这 10个 伪列。先介绍一下伪列，伪列也叫虚拟列、特殊列、隐式列，不管怎么称呼，都泛指伪列。伪列是Oracle中一种特殊的列，它不像普通列那样存储在表中，但可像普通列一样在查询中进行引用，在查询时由数据库动态生成，主要用于获取行标识、序列值、数据操作相关元数据等特殊信息。需要注意的是伪列只能进行查询，不能插入、更新或删除它们的值。伪列类似于无参数的函数，但无参数函数通常对结果集中的每一行返回相同值，而伪列通常为每一行返回不同的值。对于比较常用的伪列将用独立的一篇文章介绍，对于不常用的伪列将用一篇文章简单介绍清楚其用途即可，因为在实际工作中有几个伪列使用的很频繁，真的非常有用处，关于伪列的系列文章如下：

• 第一篇：伪列之ORA_ROWSCN：精准查看行数据被修改的最后时间
• 第二篇：伪列之Version Query：全链路追踪行数据变更的所有记录(除记录行数据的最后修改时间外，还可追溯其修改前后的内容及对应的修改时间)
• 第三篇：伪列之Sequence：利用CURRVAL和NEXTVAL实现主键自增(含 12c 新特性通过 Identity 列实现主键自增)
• 第四篇：伪列之ROWID：行数据的物理地址(基于物理地址对行数据最快速度的查询、更新、删除)
• 第五篇：伪列之ROWNUM：实现Top-N查询和传统分页(含通过 ROW_NUMBER()函数 的实现方式)
• 第六篇：伪列之Hierarchical Query、COLUMN_VALUE、OBJECT_ID、OBJECT_VALUE、XMLDATA （当前篇）

特别说明💥：本篇文章部分理论性知识点均来源于版权归 Oracle 所有的官方公开文档手册，并结合了我个人的解读和案例演示。若需要调整，请联系，会尽快处理😄
   

官方文档对于伪列的介绍（Oracle 12c）：
Pseudocolumns

一、Hierarchical Query Pseudocolumns（层次查询伪列，用于分析和展示树形结构数据的层级关系信息）

  Oracle 数据库中的 层次查询伪列（Hierarchical Query Pseudocolumns） 在处理具有树形或层次结构关系的数据时非常有用，能轻松地分析和展示数据之间的层级关系，比如组织架构、文件系统或产品分类。
  这些伪列专用于CONNECT BY语法的层次查询中，提供关于层次结构的元信息，并且无法直接修改它们。需要注意一下 层次查询伪列与B树索引无关 。它们是两个完全不同的概念：

1. 层次查询伪列（如 LEVEL, CONNECT_BY_ISLEAF ）用于查询层次结构数据（树形结构），在运行时生成层级信息
2. B树索引是一种物理存储结构，用于加速数据检索，通过平衡树结构组织数据
      

  下表展示了主要层次查询伪列如何共同描述树形结构中的数据关系：

---

  想象一家公司的汇报关系是一棵树：

• 根节点 (Root): CEO
• 分支节点 (Branches): 各部门总监、经理
• 叶节点 (Leaves): 普通员工，没有下属

伪列名称	它回答的问题	在树结构中的含义	示例值 (基于上图)
LEVEL	"这个节点在树的第几层？"	根节点为1，根节点的直接子节点为2，依此类推。	1 (CEO) 2 (总监) 3 (经理) 4 (员工)
CONNECT_BY_ISLEAF	"这个节点是叶节点（尽头）吗？"	1 (是叶节点，没有下属) 0 (是分支节点，有下属)	0 (CEO、总监、经理) 1 (所有员工)
CONNECT_BY_ISCYCLE	"这条路径出现循环了吗？" (如：下属管理上司)	1 (出现循环，且需配合NOCYCLE使用) 0 (无循环，正常路径)	通常为 0 ，除非数据逻辑错误
CONNECT_BY_ROOT	"这个节点所在分支的根节点是谁？"	返回当前行所在分支最顶端的根节点的某个字段值。	对于任何节点，返回的都是 CEO 的姓名或ID
SYS_CONNECT_BY_PATH	"从根节点到这个节点的完整路径是什么？"	返回从根到当前节点的路径上，指定列的值用分隔符连接起来的字符串。	/CEO/技术总监/后端经理/张三

       

层次查询执行流程简述：
  Oracle 处理层次查询时，大致遵循以下步骤：

1. 执行 FROM 和 JOIN：获取初始数据集合。
2. 根据 START WITH 和 CONNECT BY 生成层次：
   • 首先，根据 START WITH 子句确定一个或多个根节点。
   • 然后，根据 CONNECT BY 子句中指定的条件（使用 PRIOR 运算符），为每个根节点查找其所有子节点。
   • 这个过程会递归地重复，直到找不到新的子节点为止。
3. 应用剩余的查询子句：对层次查询结果应用 WHERE、GROUP BY、HAVING 和 ORDER BY 等。
      
      

层次查询伪列的作用：

1. LEVEL：
   • 非常适用于格式化输出（例如使用 LPAD 函数进行缩进），使得层次结构一目了然。
   • 可以用于按层级过滤数据（例如 WHERE LEVEL = 2）或统计不同层级的节点数量。
2. CONNECT_BY_ISLEAF：
   • 常用于识别树结构中的末端节点。
   • 在报表中，可以用它来特别标记叶节点，或者只对叶节点数据进行聚合运算。
3. CONNECT_BY_ISCYCLE：
   • 主要用于检测数据中的逻辑错误，比如不合理的循环引用（例如一个员工误被设置为自己或下属的管理者）。使用它时必须配合 CONNECT BY NOCYCLE ，否则遇到循环会报错。
4. CONNECT_BY_ROOT：
   • 让你能够访问当前行所在分支的根节点数据。
   • 非常适合计算从根节点到当前行的汇总信息，例如计算每个管理者及其所有下属的工资总额或平均值。
5. SYS_CONNECT_BY_PATH：
   · 用于展示从根节点到当前节点的完整路径，这在需要清晰看到节点在结构中完整位置时非常方便，例如显示文件的完整路径或组织的完整汇报链。

案例：创建新表并使用层次查询伪列

创建示例表employee_relations，这个表模拟了一个简单的组织架构

SQL>
CREATE TABLE employee_relations (
   employee_id NUMBER(6) PRIMARY KEY,
   employee_name VARCHAR2(50) NOT NULL,
   job_title VARCHAR2(50),
   manager_id NUMBER(6),
   salary NUMBER(10, 2),
   CONSTRAINT fk_manager FOREIGN KEY (manager_id) REFERENCES employee_relations(employee_id)
);

-- 插入示例数据
INSERT INTO employee_relations (employee_id, employee_name, job_title, manager_id, salary) VALUES (1, 'Alice Wang', 'CEO', NULL, 100000);
INSERT INTO employee_relations (employee_id, employee_name, job_title, manager_id, salary) VALUES (2, 'Bob Li', 'CTO', 1, 80000);
INSERT INTO employee_relations (employee_id, employee_name, job_title, manager_id, salary) VALUES (3, 'Charlie Zhang', 'CFO', 1, 78000);
INSERT INTO employee_relations (employee_id, employee_name, job_title, manager_id, salary) VALUES (4, 'David Chen', 'Development Manager', 2, 65000);
INSERT INTO employee_relations (employee_id, employee_name, job_title, manager_id, salary) VALUES (5, 'Eva Liu', 'QA Manager', 2, 62000);
INSERT INTO employee_relations (employee_id, employee_name, job_title, manager_id, salary) VALUES (6, 'Frank Zhao', 'Senior Developer', 4, 55000);
INSERT INTO employee_relations (employee_id, employee_name, job_title, manager_id, salary) VALUES (7, 'Grace Yang', 'Developer', 4, 50000);
INSERT INTO employee_relations (employee_id, employee_name, job_title, manager_id, salary) VALUES (8, 'Henry Wu', 'QA Engineer', 5, 48000);
INSERT INTO employee_relations (employee_id, employee_name, job_title, manager_id, salary) VALUES (9, 'Ivy Lin', 'Financial Analyst', 3, 52000);

commit;

   

一、显示完整组织架构树，并格式化输出

SQL>
col INDENTED_NAME for a40 
col JOB_TITLE for a30 
SELECT
   LEVEL,
   LPAD(' ', (LEVEL - 1) * 4) || employee_name AS indented_name,
   job_title,
   manager_id,
   salary,
   CONNECT_BY_ISLEAF AS is_leaf,
   SYS_CONNECT_BY_PATH(employee_name, ' -> ') AS reporting_chain
FROM
   employee_relations
START WITH
   manager_id IS NULL
CONNECT BY
   PRIOR employee_id = manager_id
ORDER SIBLINGS BY employee_id;

说明： 此查询展示了整个汇报结构。LEVEL显示层级，LPAD实现缩进，CONNECT_BY_ISLEAF标识叶节点，SYS_CONNECT_BY_PATH显示从 CEO 到当前员工的完整汇报路径。

   

二、查找每个经理的团队薪资总额（包括经理自己的薪资）

SQL>
col INDENTED_NAME for a40 
col JOB_TITLE for a30 
SELECT
   CONNECT_BY_ROOT employee_name AS top_manager,
   employee_name,
   job_title,
   SUM(salary) OVER (PARTITION BY CONNECT_BY_ROOT employee_id) AS team_total_salary
FROM
   employee_relations
WHERE
   CONNECT_BY_ISLEAF = 1 -- 可选：仅从叶节点开始计算（这里可能不是最佳实践，仅作示例）
START WITH
   manager_id IS NULL
CONNECT BY
PRIOR employee_id = manager_id;

说明： CONNECT_BY_ROOT用于获取当前分支的根节点（经理），然后使用窗口函数SUM(salary) OVER (PARTITION BY ...)计算以该经理为根的分支（团队）的薪资总额。注意，在层次查询中结合分析函数和CONNECT_BY_ROOT功能很强大。

二、COLUMN_VALUE Pseudocolumns（COLUMN_VALUE伪列，用于访问嵌套表或VARRAY中的单个元素值）

    COLUMN_VALUE伪列专门用于从 TABLE()函数 返回的表中查询 嵌套表（Nested Table） 或 可变数组（VARRAY） 中的单个元素值。
    当使用 TABLE()函数 将集合类型（嵌套表或VARRAY）转换为可以像普通表一样查询的结果集时，COLUMN_VALUE就代表了该集合中的每个元素值。
   

语法：

SELECT t.COLUMN_VALUE
FROM TABLE(collection_expression) t;

   

主要用途：

1. 访问嵌套表中的元素
2. 访问VARRAY中的元素
3. 将集合数据转换为关系型数据格式
4. 在SQL中直接操作PL/SQL集合数据

案例：创建示例表和使用COLUMN_VALUE

创建标量数据类型的嵌套表

-- 创建简单数据类型的嵌套表
CREATE TYPE number_list AS TABLE OF NUMBER;
/

CREATE TYPE string_list AS TABLE OF VARCHAR2(100);
/

-- 创建使用标量类型嵌套表的表
CREATE TABLE student_grades (
   student_id NUMBER PRIMARY KEY,
   student_name VARCHAR2(50),
   math_scores number_list,
   course_list string_list
) NESTED TABLE math_scores STORE AS math_scores_nt
 NESTED TABLE course_list STORE AS course_list_nt;

-- 插入学生数据
INSERT INTO student_grades VALUES (
   1, '张三', 
   number_list(85, 92, 78, 95),
   string_list('数学', '物理', '化学')
);

INSERT INTO student_grades VALUES (
   2, '李四', 
   number_list(90, 88, 95, 87, 91),
   string_list('英语', '历史', '地理', '生物')
);

INSERT INTO student_grades VALUES (
   3, '王五', 
   number_list(76, 82, 79),
   string_list('美术', '音乐', '体育')
);

COMMIT;

   

一、查询所有数学成绩

SQL>
SELECT s.student_name, t.COLUMN_VALUE AS math_score
FROM student_grades s, TABLE(s.math_scores) t
ORDER BY s.student_name, t.COLUMN_VALUE;

   

二、查询所有课程

SQL>
SELECT s.student_name, c.COLUMN_VALUE AS course_name
FROM student_grades s, TABLE(s.course_list) c
ORDER BY s.student_name, c.COLUMN_VALUE;

   

三、统计每个学生的平均分

SQL>
SELECT s.student_name,
      COUNT(t.COLUMN_VALUE) AS score_count,
      ROUND(AVG(t.COLUMN_VALUE), 2) AS avg_score,
      MAX(t.COLUMN_VALUE) AS max_score,
      MIN(t.COLUMN_VALUE) AS min_score
FROM student_grades s, TABLE(s.math_scores) t
GROUP BY s.student_name
ORDER BY avg_score DESC;

   

四、查找高分成绩（大于90分）

SQL>
SELECT s.student_name, t.COLUMN_VALUE AS high_score
FROM student_grades s, TABLE(s.math_scores) t
WHERE t.COLUMN_VALUE > 90
ORDER BY t.COLUMN_VALUE DESC;

三、OBJECT_ID Pseudocolumns（OBJECT_ID伪列，为对象表中的每个行对象提供唯一标识符）

    OBJECT_ID伪列用于在对象表（Object Table）或对象视图（Object View）中返回每个行对象的唯一对象标识符（OID）。
   
   

核心概念：

1. 对象标识符（OID）： 每个存储在对象表中的行对象都有一个唯一的系统生成的标识符
2. 对象表： 基于对象类型创建的表，存储对象实例
3. 用途： 用于唯一标识对象实例，支持对象引用和导航
      
      

主要用途：

1. 唯一对象标识： 为每个对象实例提供全局唯一标识符
2. 对象引用： 在对象关系模型中建立对象之间的关联
3. 高效查询： 通过OBJECT_ID快速定位特定对象实例
4. 对象导航： 支持在对象网络中进行导航查询
5. 数据完整性： 确保对象引用的准确性和一致性
      
      

OBJECT_ID 与 ROWID 的区别：

特性	OBJECT_ID	ROWID
用途	对象标识	行物理地址
适用范围	对象表	所有表
稳定性	永久性标识	可能改变（行移动时）
内容	系统生成OID	物理存储信息
可见性	逻辑标识	物理地址

       

语法：

SELECT o.OBJECT_ID, o.其他列
FROM 对象表 o;

案例：创建示例表和使用 OBJECT_ID

创建对象类型

-- 创建地址对象类型
CREATE TYPE address_type AS OBJECT (
   street    VARCHAR2(100),
   city      VARCHAR2(50),
   province  VARCHAR2(50),
   zip_code  VARCHAR2(10)
);
/

-- 创建人员对象类型
CREATE TYPE person_type AS OBJECT (
   person_id   NUMBER,
   first_name  VARCHAR2(50),
   last_name   VARCHAR2(50),
   birth_date  DATE,
   address     address_type  -- 嵌套对象类型
);
/

   

创建对象表

-- 创建基于person_type的对象表
CREATE TABLE people OF person_type (
   person_id PRIMARY KEY
);

-- 或者使用另一种语法创建对象表
CREATE TABLE employees OF person_type (
   CONSTRAINT emp_pk PRIMARY KEY (person_id)
);

   

插入示例数据

-- 向对象表插入数据
INSERT INTO people VALUES (
   person_type(
       1, 
       '张', 
       '三', 
       TO_DATE('1990-01-15', 'YYYY-MM-DD'),
       address_type('长安街100号', '北京市', '北京', '100000')
   )
);

INSERT INTO people VALUES (
   person_type(
       2, 
       '李', 
       '四', 
       TO_DATE('1985-06-20', 'YYYY-MM-DD'),
       address_type('南京路200号', '上海市', '上海', '200000')
   )
);

INSERT INTO people VALUES (
   person_type(
       3, 
       '王', 
       '五', 
       TO_DATE('1992-12-05', 'YYYY-MM-DD'),
       address_type('天河路300号', '广州市', '广东', '510000')
   )
);

COMMIT;

   

一、查询所有人员及其OBJECT_ID

SQL>
SELECT o.OBJECT_ID, 
      o.person_id,
      o.first_name || ' ' || o.last_name AS full_name,
      o.birth_date,
      o.address.city || ' ' || o.address.street AS location
FROM people o;

   

二、通过OBJECT_ID查找特定对象

SQL>
-- 首先获取某个对象的OBJECT_ID
VAR obj_id VARCHAR2(100);

BEGIN
   SELECT OBJECT_ID INTO :obj_id 
   FROM people 
   WHERE person_id = 1;
END;
/

-- 使用OBJECT_ID查询特定对象
SELECT o.person_id,
      o.first_name || ' ' || o.last_name AS full_name,
      o.address.city
FROM people o
WHERE o.OBJECT_ID = :obj_id;

   

三、查看OBJECT_ID的格式和内容

SQL>
SELECT o.OBJECT_ID,
      LENGTH(o.OBJECT_ID) AS id_length,
      DUMP(o.OBJECT_ID) AS id_format,
      o.first_name || ' ' || o.last_name AS name
FROM people o;

四、OBJECT_VALUE Pseudocolumns（OBJECT_VALUE伪列，用于在对象表中访问和操作完整的行对象实例内容）

    OBJECT_VALUE伪列用于在对象表（Object Table）中返回当前行的实际对象实例。它代表了整个行对象的内容，可以用于查询、插入、更新等操作。
   
   

核心概念：

1. 对象实例访问： OBJECT_VALUE 返回行对象的完整内容
2. 类型安全： 返回的对象类型与表定义的对象类型一致
3. 多功能用途： 可用于查询、DML操作、函数参数传递等
      
      

主要用途：

1. 完整对象访问： 获取行对象的完整内容
2. 对象操作： 在PL/SQL中操作整个对象实例
3. 数据迁移： 在对象表之间复制数据
4. 函数参数： 将整个对象作为参数传递给函数
5. 序列化： 获取对象的序列化表示
      
      

语法：

SELECT o.OBJECT_VALUE FROM 对象表 o;

   
   

OBJECT_VALUE 与其他伪列的区别：

特性	OBJECT_VALUE	OBJECT_ID	ROWID
返回内容	完整对象实例	对象标识符	行物理地址
数据类型	对象类型	RAW(16)	ROWID
主要用途	对象操作	对象标识	行定位
适用范围	对象表	对象表	所有表

案例：创建示例表和使用 OBJECT_VALUE

创建对象类型

-- 创建地址对象类型
CREATE TYPE address_typ AS OBJECT (
   street    VARCHAR2(100),
   city      VARCHAR2(50),
   state     VARCHAR2(50),
   zip_code  VARCHAR2(10)
);
/

-- 创建联系人对象类型
CREATE TYPE contact_typ AS OBJECT (
   phone VARCHAR2(20),
   email VARCHAR2(100)
);
/

-- 创建人员对象类型
CREATE TYPE person_typ AS OBJECT (
   person_id   NUMBER,
   first_name  VARCHAR2(50),
   last_name   VARCHAR2(50),
   birth_date  DATE,
   salary      NUMBER(10,2),
   address     address_typ,
   contact     contact_typ
);
/

   

创建对象表

-- 创建基于person_typ的对象表
CREATE TABLE person_tab OF person_typ (
   person_id PRIMARY KEY
);

-- 创建另一个对象表用于演示
CREATE TABLE employee_tab OF person_typ (
   CONSTRAINT emp_pk1 PRIMARY KEY (person_id)
);

   

插入示例数据

-- 向对象表插入数据
INSERT INTO person_tab VALUES (
   person_typ(
       1, 
       '张', 
       '三', 
       TO_DATE('1990-01-15', 'YYYY-MM-DD'),
       50000,
       address_typ('长安街100号', '北京市', '北京', '100000'),
       contact_typ('138-1234-5678', 'zhangsan@email.com')
   )
);

INSERT INTO person_tab VALUES (
   person_typ(
       2, 
       '李', 
       '四', 
       TO_DATE('1985-06-20', 'YYYY-MM-DD'),
       75000,
       address_typ('南京路200号', '上海市', '上海', '200000'),
       contact_typ('139-8765-4321', 'lisi@email.com')
   )
);

INSERT INTO person_tab VALUES (
   person_typ(
       3, 
       '王', 
       '五', 
       TO_DATE('1992-12-05', 'YYYY-MM-DD'),
       60000,
       address_typ('天河路300号', '广州市', '广东', '510000'),
       contact_typ('137-1111-2222', 'wangwu@email.com')
   )
);

COMMIT;

   

一、查询完整的对象实例

SQL>
-- 查询所有对象的完整内容
SELECT o.OBJECT_VALUE FROM person_tab o;

   

二、提取对象属性

SQL>
-- 从OBJECT_VALUE中提取特定属性
SELECT o.OBJECT_VALUE.person_id AS id,
      o.OBJECT_VALUE.first_name || ' ' || o.OBJECT_VALUE.last_name AS full_name,
      o.OBJECT_VALUE.salary,
      o.OBJECT_VALUE.address.city || ', ' || o.OBJECT_VALUE.address.street AS address
FROM person_tab o;

   

三、在WHERE子句中使用OBJECT_VALUE

SQL>
-- 使用OBJECT_VALUE进行条件过滤
SELECT o.OBJECT_VALUE.person_id,
      o.OBJECT_VALUE.first_name,
      o.OBJECT_VALUE.salary
FROM person_tab o
WHERE o.OBJECT_VALUE.salary > 55000;

五、XMLDATA Pseudocolumns（XMLDATA伪列，用于直接访问和操作XMLType表中的原始XML数据内容）

    XMLDATA伪列用于访问与 XMLType 表或视图中的每一行相关联的底层 XMLType 数据。它提供了对存储在 XMLType 列中的原始 XML 内容的直接访问。
   
   

核心概念：

1. XMLType 数据访问： XMLDATA 返回存储在 XMLType 列中的原始 XML 数据
2. XMLType 表专用： 主要用于基于 XMLType 的表和视图
3. XML 操作： 支持对 XML 内容进行各种操作和转换
      
      

主要用途：

1. 原始 XML 访问： 直接访问存储在 XMLType 列中的原始 XML 数据
2. XML 操作： 支持各种 XML 操作函数（EXTRACT, UPDATEXML, APPENDCHILDXML 等）
3. 数据转换： 将 XML 数据转换为关系格式
4. 查询优化： 通过 XML 索引提高查询性能
5. 数据验证： 验证 XML 数据的结构和内容
      
      

XMLDATA 与其他伪列的区别：

特性	XMLDATA	OBJECT_VALUE	OBJECT_ID
数据类型	XMLType	对象类型	RAW(16)
适用范围	XMLType 表	对象表	对象表
返回内容	XML 数据	对象实例	对象标识符
主要用途	XML 操作	对象操作	对象标识

       

语法：

SELECT x.XMLDATA FROM xmltype_table x;

案例：创建示例表和使用 XMLDATA

创建 XMLType 表

-- 创建基于 XMLType 的表
CREATE TABLE employee_xml_tab OF XMLTYPE;

-- 或者创建包含 XMLType 列的表
CREATE TABLE employees_with_xml (
   employee_id NUMBER PRIMARY KEY,
   employee_name VARCHAR2(100),
   employee_data XMLTYPE
);

   

插入示例数据

-- 向 XMLType 表插入数据
INSERT INTO employee_xml_tab VALUES (
   XMLTYPE(
       '<Employee>
           <EmployeeID>1001</EmployeeID>
           <FirstName>张</FirstName>
           <LastName>三</LastName>
           <Department>技术部</Department>
           <Salary>50000</Salary>
           <HireDate>2020-01-15</HireDate>
           <Address>
               <Street>长安街100号</Street>
               <City>北京市</City>
               <PostalCode>100000</PostalCode>
           </Address>
       </Employee>'
   )
);

INSERT INTO employee_xml_tab VALUES (
   XMLTYPE(
       '<Employee>
           <EmployeeID>1002</EmployeeID>
           <FirstName>李</FirstName>
           <LastName>四</LastName>
           <Department>销售部</Department>
           <Salary>75000</Salary>
           <HireDate>2018-06-20</HireDate>
           <Address>
               <Street>南京路200号</Street>
               <City>上海市</City>
               <PostalCode>200000</PostalCode>
           </Address>
       </Employee>'
   )
);

INSERT INTO employee_xml_tab VALUES (
   XMLTYPE(
       '<Employee>
           <EmployeeID>1003</EmployeeID>
           <FirstName>王</FirstName>
           <LastName>五</LastName>
           <Department>市场部</Department>
           <Salary>60000</Salary>
           <HireDate>2019-03-10</HireDate>
           <Address>
               <Street>天河路300号</Street>
               <City>广州市</City>
               <PostalCode>510000</PostalCode>
           </Address>
       </Employee>'
   )
);

-- 向包含 XMLType 列的表插入数据
INSERT INTO employees_with_xml VALUES (
   2001,
   '赵六',
   XMLTYPE(
       '<EmployeeDetails>
           <Department>人力资源部</Department>
           <Salary>55000</Salary>
           <HireDate>2021-05-15</HireDate>
           <Skills>
               <Skill>招聘</Skill>
               <Skill>培训</Skill>
               <Skill>绩效考核</Skill>
           </Skills>
       </EmployeeDetails>'
   )
);

COMMIT;

   

一、查询完整的 XML 内容

SQL>
-- 查询 XMLType 表中的完整 XML 数据
SELECT x.XMLDATA FROM employee_xml_tab x;

   

二、提取 XML 特定元素

SQL>
-- 使用 XMLTABLE 提取特定元素
SELECT e.employee_id,
      e.employee_name,
      x.*
FROM employees_with_xml e,
    XMLTABLE('/EmployeeDetails'
      PASSING e.employee_data
      COLUMNS
        department VARCHAR2(50) PATH 'Department',
        salary NUMBER PATH 'Salary',
        hire_date VARCHAR2(20) PATH 'HireDate'
    ) x;

    伪列系列正式完结！掌声送给坚持学完的自己👏 下次再见可能就是新坑了，O(∩_∩)O~