请求协助修改PL/SQL函数：解决多行返回错误并实现批量收集

问题背景 #

编写的PL/SQL函数始终返回too many rows错误，需修改为使用BULK COLLECT存入嵌套表，实现接收产品名称参数，返回订购该产品的所有支付方式。

涉及表结构 #

CREATE TABLE "METODA_PLATA" 
(
    "ID_PLATA" NUMBER(*,0), 
    "NUME_METODA_PLATA" VARCHAR2(25 BYTE), 
    PRIMARY KEY ("ID_PLATA")
);

CREATE TABLE "COMANDA" 
(
    "ID_COMANDA" NUMBER(*,0), 
    "ID_CLIENT" NUMBER(*,0), 
    "ID_LIVRARE" NUMBER(*,0), 
    "ID_PLATA" NUMBER(*,0), 
    PRIMARY KEY ("ID_COMANDA")
);

CREATE TABLE "COMANDA_PRODUS" 
(
    "ID_COMANDA" NUMBER(*,0), 
    "ID_PRODUS" NUMBER(6,0), 
    FOREIGN KEY ("ID_COMANDA") REFERENCES "COMANDA" ("ID_COMANDA") ENABLE, 
    FOREIGN KEY ("ID_PRODUS") REFERENCES "PRODUS" ("ID_PRODUS") ENABLE
);

原函数代码 #

create or replace function subprogram_ex8
(v_nume_produs produs.nume_produs%type)
return varchar2 is
v_metoda_plata metoda_plata.nume_metoda_plata%type;
begin
SELECT nume_metoda_plata INTO v_metoda_plata
FROM metoda_plata mp JOIN comanda c on mp.id_plata = c.id_plata
             JOIN comanda_produs cp on cp.id_comanda = c.id_comanda
             JOIN produs p on p.id_produs = cp.id_produs
WHERE upper(p.nume_produs)=upper(v_nume_produs);

RETURN v_metoda_plata;

exception
when too_many_rows
then
return 'eroare too many rows';
when
no_data_found
then
return 'nu s-au gasit date';

END subprogram_ex8;  

修改方案 #

1. 创建自定义嵌套表类型 #

首先定义嵌套表类型，用于存储多个支付方式：

CREATE OR REPLACE TYPE tip_lista_metode_plata IS TABLE OF VARCHAR2(25);
/

2. 修改函数使用BULK COLLECT #

调整函数返回类型为嵌套表，通过BULK COLLECT批量获取所有匹配结果：

CREATE OR REPLACE FUNCTION subprogram_ex8(
    v_nume_produs produs.nume_produs%TYPE
) RETURN tip_lista_metode_plata IS
    lista_metode tip_lista_metode_plata;
BEGIN
    -- 用DISTINCT过滤重复的支付方式
    SELECT DISTINCT mp.nume_metoda_plata
    BULK COLLECT INTO lista_metode
    FROM metoda_plata mp
    JOIN comanda c ON mp.id_plata = c.id_plata
    JOIN comanda_produs cp ON cp.id_comanda = c.id_comanda
    JOIN produs p ON p.id_produs = cp.id_produs
    WHERE UPPER(p.nume_produs) = UPPER(v_nume_produs);

    -- 无数据时返回空表
    IF lista_metode IS NULL OR lista_metode.COUNT = 0 THEN
        RETURN tip_lista_metode_plata();
    END IF;

    RETURN lista_metode;

EXCEPTION
    WHEN OTHERS THEN
        RAISE; -- 可按需自定义异常处理逻辑
END subprogram_ex8;
/

3. 调用函数获取表格形式结果 #

在SQL中直接查询即可得到表格格式的支付方式列表：

SELECT COLUMN_VALUE AS 支付方式
FROM TABLE(subprogram_ex8('你的产品名称')); -- 替换为实际产品名称

也可通过PL/SQL块输出结果：

DECLARE
    rezultat tip_lista_metode_plata;
BEGIN
    rezultat := subprogram_ex8('你的产品名称');
    DBMS_OUTPUT.PUT_LINE('支付方式列表：');
    DBMS_OUTPUT.PUT_LINE('---------------------');
    FOR i IN rezultat.FIRST .. rezultat.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(rezultat(i));
    END LOOP;
END;
/

修改说明 #

• 将单值返回类型替换为嵌套表，支持返回多个支付方式
• 使用BULK COLLECT INTO批量获取结果，彻底避免too many rows错误
• 添加DISTINCT关键字过滤重复支付方式
• 调整无数据处理逻辑，返回空表而非字符串提示，符合数据返回的合理性

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