Oracle SELECT子句中自定义类型的作用及SQL Server转换疑问

原Oracle代码的作用 #

这段Oracle代码核心逻辑如下：

• 调用自定义对象类型EXAMPLE_TYPE的构造函数，把Inventory_Offline表中符合条件的column1/column2/column3值封装成EXAMPLE_TYPE对象实例
• 通过BULK COLLECT INTO将这些对象实例批量存入嵌套表类型变量ExampleTypeTable
• DISTINCT基于对象的所有属性去重（仅当两个EXAMPLE_TYPE对象的三个列值完全相同时，才会被判定为重复）
• 通过对象实例的column3属性对结果集排序

SQL Server转换方法 #

SQL Server没有Oracle风格的对象类型嵌套表语法，需用表值类型替代，具体步骤如下：

1. 创建对应表值类型 #

先创建匹配EXAMPLE_TYPE结构的表值类型（对应Oracle的EXAMPLE_TYPE_TABLE）：

CREATE TYPE dbo.EXAMPLE_TYPE_TABLE AS TABLE (
    column1 VARCHAR(13),
    column2 VARCHAR(50),
    column3 VARCHAR(14)
);

2. 替换存储过程中的查询逻辑 #

在SQL Server存储过程中，直接查询列并插入表值类型变量，替代Oracle的BULK COLLECT INTO逻辑：

-- 声明表值类型变量
DECLARE @ExampleTypeTable dbo.EXAMPLE_TYPE_TABLE;

-- 插入去重并排序后的结果
INSERT INTO @ExampleTypeTable (column1, column2, column3)
SELECT DISTINCT io.column1, io.column2, io.column3
FROM Inventory_Offline io
WHERE io.column1 = @pTest -- SQL Server参数需加@前缀
ORDER BY io.column3;

注意事项 #

• 若原Oracle代码用到了EXAMPLE_TYPE的match排序成员函数，需在SQL Server中编写自定义标量函数实现对应比较逻辑，再用于排序或筛选
• SQL Server的DISTINCT直接作用于列组合，和Oracle中基于对象属性的去重效果完全一致
• 表值类型变量可像普通表一样执行SELECT/UPDATE等操作，支持复杂业务逻辑处理

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