PL/SQL游标LOOP循环中如何匹配预加载关联游标对应记录

你最初设想的"预加载后游标间匹配"逻辑本身有先天缺陷：普通游标是单向只读的流式结果集，不支持随机定位记录，没法在主游标循环中直接按客户号快速匹配另一个游标的数据，强行实现只能每次从头遍历禁入客户游标，性能比循环内逐行查表还差。

以下两种是生产环境通用的高性能实现，完全规避循环内逐行查表的性能问题：

方案1：内存集合预加载（适合禁入客户表数据量较小、可全量载入内存的场景） #

用PL/SQL关联数组（索引表）存预加载的禁入客户数据，以客户号作为数组键，匹配时直接通过数组下标做哈希查找，时间复杂度O(1)，全程无重复IO、无引擎上下文切换。

1. 首先在包定义部分声明集合类型与全局存储变量：

-- 定义禁入客户记录结构，字段类型直接引用表字段类型，避免类型不匹配
TYPE t_forbidden_cust_rec IS RECORD (
    customeradress forbidden_customers.customeradress%TYPE
);
-- 定义以客户号为索引的关联数组类型，索引类型与客户号字段类型一致
TYPE t_forbidden_cust_map IS TABLE OF t_forbidden_cust_rec 
    INDEX BY forbidden_customers.customernumber%TYPE;
-- 声明全局集合变量，存预加载的禁入客户映射表
g_forbidden_cust_map t_forbidden_cust_map;

2. 在包体的初始化块中一次性加载全量禁入客户数据，该块只会在包第一次被调用时执行一次：

-- 包体末尾的初始化块
BEGIN
    FOR rec IN c_forbidden_customers LOOP
        g_forbidden_cust_map(rec.customernumber).customeradress := rec.customeradress;
    END LOOP;
END package_name; -- 替换成你的实际包名

3. 发票游标循环中直接做内存判断即可：

OPEN c_invoices(v_year);
LOOP
    FETCH c_invoices INTO invoices_cursor;
    EXIT WHEN c_invoices%NOTFOUND;

    -- 内存哈希判断，无IO开销
    IF g_forbidden_cust_map.EXISTS(invoices_cursor.customernumber) THEN
        -- 取客户地址直接读：g_forbidden_cust_map(invoices_cursor.customernumber).customeradress
        -- 编写对应业务逻辑即可
    END IF;
END LOOP;
CLOSE c_invoices;

方案2：主游标直接关联查询（90%以上场景的最优选择，无数据量限制） #

不需要手动预加载任何数据，直接在发票游标定义时用LEFT JOIN关联禁入客户表，让SQL优化器自动选择哈希连接、排序合并连接等最优关联算法，性能远高于手动在PL/SQL层写循环匹配，代码也更简洁易维护。
改写后的游标定义如下：

CURSOR c_invoices(p_year IN INTEGER) IS
SELECT     
    ai.invoicenumber,   
    ai.invoicedate,
    ai.customernumber,
    fc.customeradress -- 非禁入客户该字段返回NULL
FROM all_invoices ai
LEFT JOIN forbidden_customers fc 
    ON ai.customernumber = fc.customernumber
WHERE ai.year = p_year;

遍历循环时直接判断customeradress是否非空即可，不需要额外写查询或匹配逻辑：

OPEN c_invoices(v_year);
LOOP
    FETCH c_invoices INTO invoices_cursor;
    EXIT WHEN c_invoices%NOTFOUND;

    IF invoices_cursor.customeradress IS NOT NULL THEN
        -- 直接用invoices_cursor里带出来的客户地址写业务逻辑即可
    END IF;
END LOOP;
CLOSE c_invoices;

不同实现方式性能对比 #

• 最差：循环内执行SELECT COUNT(*)查表，每次匹配都会触发PL/SQL与SQL引擎的上下文切换+表随机读IO，数据量超过1万条就会出现明显性能问题
• 较差：双循环嵌套遍历两个游标做匹配，时间复杂度O(N*M)，仅适合百级以内的极小数据量场景
• 良好：关联数组预加载方案，时间复杂度O(N+M)，全内存操作，适合禁入客户表规模在10万条以内的场景
• 最优：游标直接关联方案，由数据库优化器生成最优执行计划，无内存溢出风险，不管数据量大小都能稳定跑在最高性能

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