PL/SQL验证函数语法错误求助：WHEN子句相关脚本改写方案

看起来你在写这个匹配函数的时候踩了几个PL/SQL的常见坑，我来帮你梳理问题并给出改写方案：

原代码的主要问题： #

• DECLARE位置错误：在PL/SQL函数中，变量声明块DECLARE应该放在BEGIN之前，而不是嵌套在BEGIN内部。
• SELECT * INTO单个变量错误：SELECT *会返回表的所有列，但你试图把它存入单个字段类型的变量（比如V_BUSINESS_PROVIDER），这会导致类型不匹配。
• WHERE子句条件错误：你写的WHERE V_BUSINESS_PROVIDER = valueGet是用声明的变量和输入值比较，而应该用表的列名（比如business_provider = valueGet）。
• 重复的大小写判断：Upper(V_BUSINESS_PROVIDER) = Upper(valueGet)已经实现了忽略大小写匹配，不需要再写V_BUSINESS_PROVIDER = valueGet，这是冗余的。
• EXCEPTION的错误用法：不能在同一个BEGIN块里多次写独立的EXCEPTION，应该用嵌套块或者按顺序处理查询，捕获一次no_data_found后继续下一个查询。

改写后的函数代码： #

CREATE OR REPLACE FUNCTION BUSINESS_PROVIDER_GET(valueGet IN VARCHAR2) 
RETURN VARCHAR2 
IS
    v_billing_account_id business_provider_configuration.billing_account_id%TYPE;
BEGIN
    -- 先匹配business_provider（忽略大小写）
    SELECT billing_account_id
    INTO v_billing_account_id
    FROM BUSINESS_PROVIDER_CONFIGURATION
    WHERE UPPER(business_provider) = UPPER(valueGet);
    
    RETURN v_billing_account_id;
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- 匹配失败，尝试匹配trading_name
        BEGIN
            SELECT billing_account_id
            INTO v_billing_account_id
            FROM BUSINESS_PROVIDER_CONFIGURATION
            WHERE UPPER(trading_name) = UPPER(valueGet);
            
            RETURN v_billing_account_id;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                -- 再次失败，尝试匹配customer_id
                BEGIN
                    SELECT billing_account_id
                    INTO v_billing_account_id
                    FROM BUSINESS_PROVIDER_CONFIGURATION
                    WHERE UPPER(customer_id) = UPPER(valueGet);
                    
                    RETURN v_billing_account_id;
                EXCEPTION
                    WHEN NO_DATA_FOUND THEN
                        -- 所有匹配都失败，抛出自定义错误
                        RAISE_APPLICATION_ERROR(-20000, 'Not found');
                END;
        END;
END BUSINESS_PROVIDER_GET;
/

代码逻辑说明： #

1. 我们把变量声明移到了BEGIN之前，只声明需要返回的billing_account_id变量（因为你的需求是获取对应记录的这个字段）。
2. 用嵌套的BEGIN-EXCEPTION块依次处理三个字段的匹配：
   • 首先尝试匹配business_provider，成功则直接返回对应的billing_account_id。
   • 如果第一个查询抛出NO_DATA_FOUND，进入内层块尝试匹配trading_name。
   • 第二个查询也失败的话，进入最内层块尝试匹配customer_id。
   • 三个查询都失败时，抛出自定义错误-20000并提示"Not found"。
3. 所有匹配都使用UPPER()函数统一大小写，确保忽略大小写的匹配逻辑生效。

另外，如果你担心多个记录匹配的情况（比如同一个值对应多条记录），可以在每个SELECT语句后面加上AND ROWNUM = 1，或者提前确保表中这三个字段的唯一性，避免抛出TOO_MANY_ROWS异常。

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