Oracle PL/SQL自定义函数中隐式游标为何陷入无限循环？

我编写了Oracle PL/SQL函数ListCustomerOrders，需求是接收客户编号作为参数，查询该客户的所有订单，返回用逗号分隔的订单号字符串；若客户无订单，则仅显示一次客户编号和姓名。但使用隐式游标时出现了逻辑问题，不仅订单号拼接错误，还无法处理无订单的场景，原代码如下：

create or replace function ListCustomerOrders (test NUMBER) return varchar2 is 
    prev_key CUSTOMER.C_CUSTKEY%type := 0;    
    emptyList varchar2(32767):='';
    popList varchar2(32767):='';     

begin
FOR qRow in (select C_CUSTKEY, C_NAME, O_ORDERKEY from ORDERS join CUSTOMER on O_CUSTKEY =   C_CUSTKEY where C_CUSTKEY = test)

loop
    if prev_key != qRow.C_CUSTKEY then
        emptyList := (to_char(qRow.C_CUSTKEY) || ' ' || to_char(qRow.C_NAME) ||' : ' );
    end if;


    if qRow.O_ORDERKEY is not null then
       popList := ((to_char(qRow.O_ORDERKEY)) || ', ');
    end if;

    emptyList := emptyList || popList;

    prev_Key := qRow.C_CUSTKEY;

    popList := '';

end loop;

return emptyList;

end ListCustomerOrders;
/
SHOW ERRORS

问题分析 #

1. 内连接导致无订单时无结果：原查询用ORDERS和CUSTOMER内连接，若客户无订单，整个查询返回空，函数会返回空字符串，不符合“显示客户编号和姓名”的需求
2. 订单号拼接逻辑错误：每次循环都会把popList清空，导致只能保留最后一个订单号，还会多一个尾部逗号
3. 冗余变量无意义：因为参数是单个客户编号，prev_key判断不同客户的逻辑完全多余

修复后的代码 #

create or replace function ListCustomerOrders (test NUMBER) return varchar2 is 
    v_cust_info varchar2(1000);
    v_order_list varchar2(32767) := '';
begin
    -- 先获取客户基础信息，确保无订单时也能返回
    select to_char(C_CUSTKEY) || ' ' || C_NAME into v_cust_info
    from CUSTOMER
    where C_CUSTKEY = test;

    -- 循环拼接订单号，避免尾部多余逗号
    for qRow in (select O_ORDERKEY from ORDERS where O_CUSTKEY = test) loop
        if v_order_list is not null then
            v_order_list := v_order_list || ', ';
        end if;
        v_order_list := v_order_list || to_char(qRow.O_ORDERKEY);
    end loop;

    -- 组合最终返回结果
    return v_cust_info || case when v_order_list is not null then ' : ' || v_order_list else '' end;

exception
    -- 处理客户编号不存在的情况
    when no_data_found then
        return '客户编号 ' || test || ' 不存在';
end ListCustomerOrders;
/
SHOW ERRORS

修复说明 #

• 拆分查询：单独查询客户信息，确保即使无订单也能获取客户编号和姓名
• 优化订单拼接：判断已有订单号时再添加逗号，避免尾部冗余符号
• 增加异常处理：对不存在的客户编号返回明确提示
• 移除冗余变量：简化逻辑，去掉不必要的prev_key和临时变量

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