如何让存储过程配合XML文件完成双表数据插入操作？

云上老周

2026-5-19

刚好我之前做过类似的需求，给你拆解两个方案的具体实现步骤，帮你理清变量、游标怎么定义，还有Java调用的推进方式：

方案一：存储过程直接解析XML文件插入两张表

首先得明确：存储过程处理XML的核心是先把XML内容加载到变量，再用XML解析方法把节点转成关系型数据，最后用游标或循环遍历插入表。我以Oracle和SQL Server为例，给你写具体的实现代码，你可以对应自己的数据库调整。

1. 变量与游标定义思路

首先需要定义XML类型变量存储整个XML内容，用CLOB临时存储文件读取的文本；
对应两张表的字段变量，用来暂存每条记录的值；
游标用来遍历XML解析后的关系数据（本质是把XML节点映射成虚拟表，用游标遍历这个虚拟表）。

Oracle示例代码

CREATE OR REPLACE PROCEDURE insert_from_xml(
  p_file_dir VARCHAR2,  -- XML文件所在的数据库目录（需先创建并授权）
  p_file_name VARCHAR2
)
IS
  -- 变量定义
  v_file_handle UTL_FILE.FILE_TYPE;  -- 文件句柄
  v_clob CLOB;                       -- 存储文件文本内容
  v_xml XMLTYPE;                     -- 存储XML对象
  -- 表1字段变量
  v_t1_id NUMBER;
  v_t1_name VARCHAR2(100);
  -- 表2字段变量
  v_t2_detail VARCHAR2(500);
  v_t2_t1_fk NUMBER;

  -- 游标：遍历XML中的表1记录（用XMLTable把XML节点转成关系数据）
  CURSOR c_t1_records IS
    SELECT x.id, x.name
    FROM XMLTABLE('/Root/Table1/Record'
                  PASSING v_xml
                  COLUMNS id NUMBER PATH 'ID',
                          name VARCHAR2(100) PATH 'Name') x;
BEGIN
  -- 步骤1：读取XML文件到CLOB
  v_file_handle := UTL_FILE.FOPEN(p_file_dir, p_file_name, 'R');
  UTL_FILE.GET_LOB(v_file_handle, v_clob);
  UTL_FILE.FCLOSE(v_file_handle);

  -- 步骤2：将CLOB转为XMLTYPE
  v_xml := XMLTYPE(v_clob);

  -- 步骤3：遍历表1数据，插入表1，同时处理关联的表2数据
  FOR t1_rec IN c_t1_records LOOP
    v_t1_id := t1_rec.id;
    v_t1_name := t1_rec.name;

    -- 插入表1
    INSERT INTO table1(id, name) VALUES(v_t1_id, v_t1_name);

    -- 步骤4：遍历当前表1记录对应的表2子节点（用XMLTable过滤关联外键）
    FOR t2_rec IN (
      SELECT x.detail, x.t1_fk
      FROM XMLTABLE('/Root/Table2/Record[T1_FK=sql:variable("v_t1_id")]'
                    PASSING v_xml
                    COLUMNS detail VARCHAR2(500) PATH 'Detail',
                            t1_fk NUMBER PATH 'T1_FK') x
    ) LOOP
      v_t2_detail := t2_rec.detail;
      v_t2_t1_fk := t2_rec.t1_fk;

      -- 插入表2
      INSERT INTO table2(detail, t1_fk) VALUES(v_t2_detail, v_t2_t1_fk);
    END LOOP;
  END LOOP;

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;  -- 抛出异常方便排查
END;
/

注意：Oracle需要先创建数据库目录并给用户授权：CREATE DIRECTORY xml_dir AS '/path/to/xml'; GRANT READ ON DIRECTORY xml_dir TO your_user;

SQL Server示例代码

CREATE PROCEDURE insert_from_xml
  @file_path NVARCHAR(255)  -- XML文件的绝对路径
AS
BEGIN
  SET NOCOUNT ON;
  
  -- 变量定义
  DECLARE @xml XML;
  DECLARE @file_content NVARCHAR(MAX);
  -- 表1字段变量
  DECLARE @t1_id INT, @t1_name NVARCHAR(100);

  -- 步骤1：读取XML文件内容
  SELECT @file_content = BulkColumn
  FROM OPENROWSET(BULK @file_path, SINGLE_CLOB) AS file_data;

  -- 步骤2：转为XML类型
  SET @xml = CAST(@file_content AS XML);

  -- 步骤3：定义游标遍历表1记录
  DECLARE c_t1_records CURSOR FOR
    SELECT
      T.c.value('(ID)[1]', 'INT') AS id,
      T.c.value('(Name)[1]', 'NVARCHAR(100)') AS name
    FROM @xml.nodes('/Root/Table1/Record') T(c);

  -- 打开游标并遍历
  OPEN c_t1_records;
  FETCH NEXT FROM c_t1_records INTO @t1_id, @t1_name;

  WHILE @@FETCH_STATUS = 0
  BEGIN
    -- 插入表1
    INSERT INTO table1(id, name) VALUES(@t1_id, @t1_name);

    -- 插入关联的表2数据
    INSERT INTO table2(detail, t1_fk)
    SELECT
      T.c.value('(Detail)[1]', 'NVARCHAR(500)') AS detail,
      T.c.value('(T1_FK)[1]', 'INT') AS t1_fk
    FROM @xml.nodes('/Root/Table2/Record[T1_FK=sql:variable("@t1_id")]') T(c);

    FETCH NEXT FROM c_t1_records INTO @t1_id, @t1_name;
  END;

  -- 关闭并释放游标
  CLOSE c_t1_records;
  DEALLOCATE c_t1_records;

  COMMIT;
END;

注意：SQL Server需要启用Ad Hoc Distributed Queries，且数据库服务账号要有文件路径的读取权限

方案二：Java调用存储过程传参插入数据

这个方案更简单，把XML解析的工作交给Java（毕竟Java处理XML更灵活），存储过程只负责接收结构化参数插入表。推进步骤如下：

1. 定义带参数的存储过程

核心是用**表值参数（SQL Server）或自定义集合类型（Oracle）**来批量传递表2的数据，避免多次调用存储过程。

Oracle示例（自定义集合）

-- 先定义表2的记录类型
CREATE OR REPLACE TYPE t_table2_record AS OBJECT (
  detail VARCHAR2(500),
  t1_fk NUMBER
);

-- 定义表2的集合类型
CREATE OR REPLACE TYPE t_table2_list AS TABLE OF t_table2_record;

-- 存储过程
CREATE OR REPLACE PROCEDURE insert_with_params(
  p_t1_id NUMBER,
  p_t1_name VARCHAR2(100),
  p_t2_data t_table2_list
)
IS
BEGIN
  -- 插入表1
  INSERT INTO table1(id, name) VALUES(p_t1_id, p_t1_name);

  -- 批量插入表2（用FORALL提升效率）
  FORALL i IN 1..p_t2_data.COUNT
    INSERT INTO table2(detail, t1_fk)
    VALUES(p_t2_data(i).detail, p_t2_data(i).t1_fk);

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
/

SQL Server示例（表值参数）

-- 先创建表值类型
CREATE TYPE tvp_table2 AS TABLE (
  detail NVARCHAR(500),
  t1_fk INT
);

-- 存储过程
CREATE PROCEDURE insert_with_params
  @t1_id INT,
  @t1_name NVARCHAR(100),
  @t2_data tvp_table2 READONLY
AS
BEGIN
  SET NOCOUNT ON;
  
  INSERT INTO table1(id, name) VALUES(@t1_id, @t1_name);
  
  INSERT INTO table2(detail, t1_fk)
  SELECT detail, t1_fk FROM @t2_data;

  COMMIT;
END;

2. Java端调用逻辑

Java解析XML后，把表1的单个参数和表2的列表参数传给存储过程。这里用JDBC示例：

Oracle Java调用

import oracle.jdbc.OracleConnection;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.List;

// 自定义对应表2的Java类
class Table2Record {
    private String detail;
    private int t1Fk;
    // 构造器、getter、setter省略
    public Table2Record(String detail, int t1Fk) {
        this.detail = detail;
        this.t1Fk = t1Fk;
    }
    public String getDetail() { return detail; }
    public int getT1Fk() { return t1Fk; }
}

public class PegaXmlInsert {
    public static void main(String[] args) {
        String url = "jdbc:oracle:thin:@//your-db-host:1521/your-sid";
        String user = "your_user";
        String pwd = "your_pwd";

        // 模拟解析XML得到的数据
        int t1Id = 1001;
        String t1Name = "测试记录";
        List<Table2Record> t2List = List.of(
                new Table2Record("详情1", 1001),
                new Table2Record("详情2", 1001)
        );

        try (Connection conn = DriverManager.getConnection(url, user, pwd)) {
            String sql = "{call insert_with_params(?, ?, ?)}";
            try (CallableStatement cs = conn.prepareCall(sql)) {
                // 设置表1参数
                cs.setInt(1, t1Id);
                cs.setString(2, t1Name);

                // 转换表2列表为Oracle数组
                OracleConnection oraConn = conn.unwrap(OracleConnection.class);
                ArrayDescriptor desc = ArrayDescriptor.createDescriptor("T_TABLE2_LIST", oraConn);
                STRUCT[] structs = new STRUCT[t2List.size()];
                for (int i = 0; i < t2List.size(); i++) {
                    Table2Record rec = t2List.get(i);
                    Object[] attrs = new Object[]{rec.getDetail(), rec.getT1Fk()};
                    structs[i] = new STRUCT(desc, oraConn, attrs);
                }
                ARRAY array = new ARRAY(desc, oraConn, structs);
                cs.setArray(3, array);

                // 执行存储过程
                cs.execute();
                System.out.println("数据插入成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
            // 处理异常，比如回滚等
        }
    }
}

SQL Server Java调用

import com.microsoft.sqlserver.jdbc.SQLServerDataTable;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.List;

class Table2Record {
    private String detail;
    private int t1Fk;
    // 构造器、getter、setter省略
    public Table2Record(String detail, int t1Fk) {
        this.detail = detail;
        this.t1Fk = t1Fk;
    }
    public String getDetail() { return detail; }
    public int getT1Fk() { return t1Fk; }
}

public class PegaXmlInsert {
    public static void main(String[] args) {
        String url = "jdbc:sqlserver://your-db-host:1433;databaseName=your-db;encrypt=true;trustServerCertificate=true";
        String user = "your_user";
        String pwd = "your_pwd";

        int t1Id = 1001;
        String t1Name = "测试记录";
        List<Table2Record> t2List = List.of(
                new Table2Record("详情1", 1001),
                new Table2Record("详情2", 1001)
        );

        try (Connection conn = DriverManager.getConnection(url, user, pwd)) {
            String sql = "{call insert_with_params(?, ?, ?)}";
            try (CallableStatement cs = conn.prepareCall(sql)) {
                cs.setInt(1, t1Id);
                cs.setString(2, t1Name);

                // 创建SQLServerDataTable对应表值参数
                SQLServerDataTable tvp = new SQLServerDataTable();
                tvp.addColumnMetadata("detail", java.sql.Types.NVARCHAR);
                tvp.addColumnMetadata("t1_fk", java.sql.Types.INTEGER);
                for (Table2Record rec : t2List) {
                    tvp.addRow(rec.getDetail(), rec.getT1Fk());
                }

                cs.setObject(3, tvp);
                cs.execute();
                System.out.println("数据插入成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

方案选择建议

如果XML文件在数据库服务器可访问的路径下，且希望减少Java端逻辑，选方案一；
如果XML文件在应用服务器或客户端，或者Java已经有解析XML的逻辑，选方案二（更灵活，存储过程复杂度低，维护更简单）。

内容的提问来源于stack exchange，提问作者icerabbit

如何让存储过程配合XML文件完成双表数据插入操作？

1. 变量与游标定义思路 #

Oracle示例代码 #

SQL Server示例代码 #

1. 定义带参数的存储过程 #

Oracle示例（自定义集合） #

SQL Server示例（表值参数） #

2. Java端调用逻辑 #

Oracle Java调用 #

SQL Server Java调用 #