Oracle迁移Azure SQL时如何处理嵌套用户自定义表类型的存储过程传参

SQL Server 原生不支持嵌套的用户自定义表类型（UDTT），无需 Java 侧拍平嵌套结构的前提下，可采用以下两种成熟方案实现需求：

方案1：多表类型参数+显式关联ID（适配原有技术思路） #

该方案直接解决父子表引用关系处理问题，实现逻辑如下：

• 分别定义父、子两级用户自定义表类型，额外新增关联标识字段：父类型 ManagerUDTT 新增 ManagerUID 字段（可采用 UUID 或者业务唯一ID，由 Java 侧生成），子类型 EmployeeUDTT 新增 ParentManagerUID 字段对应关联父表的唯一标识。
• Java 侧无需改动原有嵌套业务对象结构，仅需在参数序列化阶段，将每个 Manager 关联的 Employee 集合的所有记录，打上对应 Manager 的 ManagerUID 到 ParentManagerUID 字段即可，无需拍平嵌套结构。
• 存储过程同时接收两个表值参数：@Managers dbo.ManagerUDTT READONLY、@Employees dbo.EmployeeUDTT READONLY。
• 存储过程内处理逻辑时，直接通过 ManagerUID = ParentManagerUID 条件做 JOIN 即可维护父子关联关系；如果需要使用数据库生成的自增主键，可先插入父表拿到自增ID，和传入的 ManagerUID 生成映射关系，再关联子表数据做插入即可。

该方案性能最优，和 SQL Server 表值参数（TVP）兼容性最高，适合单次传入数据量较大的场景。

方案2：JSON 单参数传递（代码改动量最小） #

该方案无需定义多组自定义表类型，完全保留Java侧原有嵌套结构：

• Java 侧直接将嵌套的 Manager 业务对象序列化为 JSON 字符串，作为 NVARCHAR(MAX) 类型参数传入存储过程即可，无需做任何结构调整。
• 存储过程侧使用 SQL Server 原生支持的 OPENJSON 函数解析嵌套结构，Azure SQL 全版本、SQL Server 2016及以上版本均支持该语法，示例实现如下：

CREATE PROCEDURE dbo.BatchInsertManagersWithEmp
    @ManagerJson NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    -- 解析父级Manager数据，同时保留嵌套的Employee子集合JSON
    WITH ParsedManagers AS (
        SELECT 
            ManagerUID, 
            ManagerName, 
            Department,
            EmployeesJson = Employees
        FROM OPENJSON(@ManagerJson)
        WITH (
            ManagerUID UNIQUEIDENTIFIER,
            ManagerName NVARCHAR(100),
            Department NVARCHAR(50),
            Employees NVARCHAR(MAX) AS JSON
        )
    ),
    -- 解析子级Employee数据，自动关联对应父级Manager的唯一标识
    ParsedEmployees AS (
        SELECT
            pm.ManagerUID,
            emp.EmployeeName,
            emp.JobTitle,
            emp.Salary
        FROM ParsedManagers pm
        CROSS APPLY OPENJSON(pm.EmployeesJson)
        WITH (
            EmployeeName NVARCHAR(100),
            JobTitle NVARCHAR(50),
            Salary DECIMAL(18,2)
        ) emp
    )

    -- 后续业务逻辑直接用两个CTE关联处理即可，示例插入逻辑：
    -- 插入父表
    INSERT INTO dbo.Managers(ManagerName, Department)
    SELECT ManagerName, Department FROM ParsedManagers;

    -- 插入子表，通过ManagerUID关联拿到父表的自增ID
    INSERT INTO dbo.Employees(ManagerID, EmployeeName, JobTitle, Salary)
    SELECT m.ManagerID, pe.EmployeeName, pe.JobTitle, pe.Salary
    FROM ParsedEmployees pe
    JOIN dbo.Managers m ON pe.ManagerUID = m.ManagerUID;
END

该方案适配成本最低，适合嵌套层级多、单次传入数据量中等（单JSON建议不超过100MB）的场景。

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