oracle如何拆分clob类型（相关搜索：数据库）

Question

各位技术人员,有一个关于oracle拆分clob列类型的问题,有没有清楚具体怎么做的,从oracle数据库通过接口获取到一段员工合规考试分数的xml,保存在oracle的一个clob类型的列内

内容如上图,同时在oracle数据库内创建了一张表对应各个不同的标签
请问如何将对应的grade_uid/login_name 等不同的标签放到对应的列内形成一张数据表?

Answer 1

Wzx0905 上午好☀️☀️☀️️
本答案参考通义千问

在Oracle中，CLOB（Character Large Object）类型用于存储大文本数据，包括XML文档。要从CLOB列中拆分XML并将其映射到多个表列，你可以使用DBMS_XML解析XML数据。以下是一个步骤指南：

1. 创建表结构: 首先，确保你已经有了一个表，例如EMP_EXAMS，包含你想要拆分的CLOB列XML_SCORES和对应标签的其他列（如GRADE_UID, LOGIN_NAME等）。

CREATE TABLE EMP_EXAMS (
    XML_SCORES CLOB,
    GRADE_UID VARCHAR2(50),
    LOGIN_NAME VARCHAR2(50)
);

1. 创建一个存储过程来解析XML: 使用DBMS_XML函数来解析XML。下面是一个示例存储过程，它会从CLOB中提取信息并填充表中的列：

CREATE OR REPLACE PROCEDURE parse_xml_scores (
    p_xml_scores IN CLOB,
    p_grade_uid OUT NUMBER,
    p_login_name OUT VARCHAR2
) IS
    xml_doc XMLType := XMLType(p_xml_scores);
BEGIN
    -- 解析XML文档
    FOR xml_data IN xml_doc.xml_children() LOOP
        IF xml_data.tag_name = 'GRADE_UID' THEN
            p_grade_uid := xml_data.get_textval();
        ELSIF xml_data.tag_name = 'LOGIN_NAME' THEN
            p_login_name := xml_data.get_textval();
            EXIT; -- 一旦找到一个匹配的，就退出循环
        END IF;
    END LOOP;
END parse_xml_scores;
/

1. 调用存储过程: 在实际应用中，你可以通过PL/SQL块或者包装程序来调用这个存储过程，将CLOB数据传递给它：

DECLARE
    clob_data CLOB := '...'; -- 从数据库中获取的实际XML数据
    grade_uid NUMBER;
    login_name VARCHAR2(50);
BEGIN
    parse_xml_scores(clob_data, grade_uid, login_name);
    -- 将结果插入到EMP_EXAMS表中
    INSERT INTO EMP_EXAMS (XML_SCORES, GRADE_UID, LOGIN_NAME)
    VALUES (clob_data, grade_uid, login_name);
END;
/

1. 处理异常: 如果XML格式不正确或没有找到预期的标签，可能需要添加错误处理代码来处理这种情况。

请注意，这个示例假设XML结构是固定的，并且每个<GRADE_UID>和<LOGIN_NAME>标签后面都有相应的值。如果XML结构复杂，可能需要调整解析逻辑以适应实际情况。