Oracle PL/SQL函数如何返回两种不同数据类型？

你遇到的核心问题是PL/SQL函数的返回类型必须单一，无法同时返回数值和字符串。以下是几种可行的解决思路：

1. 将返回类型改为VARCHAR2 #

把函数返回类型统一设为字符串，正常逻辑下将数值型价格转为字符串返回，异常时直接返回错误标识。调用方需要自行判断返回值是有效价格（可转为数字）还是错误码。

修改后的示例代码：

CREATE OR REPLACE FUNCTION get_product_price(p IN NUMBER, d IN DATE, curTo IN NUMBER)
RETURN VARCHAR2 -- 修改为VARCHAR2类型
IS
    prPrice NUMBER; -- 产品价格
    curFrom NUMBER; -- 汇率对应源币种
    pgcount NUMBER := 0; -- 产品库存计数
    noProductsOnDate EXCEPTION;
    wrongCurrency EXCEPTION;
BEGIN
    SELECT COUNT(*) INTO pgcount
    FROM Products pr, Outgoing outg, Incoming inc 
    WHERE pr.PROD_ID = outg.PROD_ID 
      AND pr.PROD_ID = inc.PROD_ID  
      AND inc.inc_date > d
    HAVING SUM(inc.quantity) > SUM(outg.quantity);

    IF pgcount = 0 OR pgcount IS NULL THEN
        RAISE noProductsOnDate;
    END IF;

    IF curTo > 2 OR curTo < 1 THEN
        RAISE wrongCurrency;
    END IF;

    curFrom := CASE curTo WHEN 1 THEN 2 WHEN 2 THEN 1 END;

    SELECT pric.Value * cour.value INTO prPrice 
    FROM Prices pric, Cources cour 
    WHERE p = pric.prod_id 
      AND pric.DAYFROM <= d 
      AND (pric.DAYTO >= d OR pric.DAYTO IS NULL) 
      AND cour.cur_idto = curTo 
      AND cour.cur_idfrom = curFrom;

    RETURN TO_CHAR(prPrice); -- 将数值转为字符串返回
EXCEPTION
    WHEN noProductsOnDate THEN 
        RETURN '1q'; -- 返回错误标识
    WHEN wrongCurrency THEN 
        RETURN '2q'; -- 返回错误标识
END;

2. 使用OUT参数传递错误信息 #

保持函数返回NUMBER类型（正常返回价格），新增一个OUT参数用于传递异常时的错误标识。正常执行时OUT参数设为NULL，异常时赋值错误码。

示例代码：

CREATE OR REPLACE FUNCTION get_product_price(p IN NUMBER, d IN DATE, curTo IN NUMBER, error_code OUT VARCHAR2)
RETURN NUMBER
IS
    prPrice NUMBER;
    curFrom NUMBER;
    pgcount NUMBER := 0;
    noProductsOnDate EXCEPTION;
    wrongCurrency EXCEPTION;
BEGIN
    error_code := NULL; -- 初始化错误码为NULL
    SELECT COUNT(*) INTO pgcount
    FROM Products pr, Outgoing outg, Incoming inc 
    WHERE pr.PROD_ID = outg.PROD_ID 
      AND pr.PROD_ID = inc.PROD_ID  
      AND inc.inc_date > d
    HAVING SUM(inc.quantity) > SUM(outg.quantity);

    IF pgcount = 0 OR pgcount IS NULL THEN
        RAISE noProductsOnDate;
    END IF;

    IF curTo > 2 OR curTo < 1 THEN
        RAISE wrongCurrency;
    END IF;

    curFrom := CASE curTo WHEN 1 THEN 2 WHEN 2 THEN 1 END;

    SELECT pric.Value * cour.value INTO prPrice 
    FROM Prices pric, Cources cour 
    WHERE p = pric.prod_id 
      AND pric.DAYFROM <= d 
      AND (pric.DAYTO >= d OR pric.DAYTO IS NULL) 
      AND cour.cur_idto = curTo 
      AND cour.cur_idfrom = curFrom;

    RETURN prPrice;
EXCEPTION
    WHEN noProductsOnDate THEN 
        error_code := '1q';
        RETURN NULL; -- 异常时返回NULL，通过OUT参数传递错误码
    WHEN wrongCurrency THEN 
        error_code := '2q';
        RETURN NULL;
END;

调用示例：

DECLARE
    v_price NUMBER;
    v_error VARCHAR2(10);
BEGIN
    v_price := get_product_price(123, SYSDATE, 1, v_error);
    IF v_error IS NOT NULL THEN
        DBMS_OUTPUT.PUT_LINE('错误：' || v_error);
    ELSE
        DBMS_OUTPUT.PUT_LINE('价格：' || v_price);
    END IF;
END;

3. 使用自定义记录类型 #

定义一个包含价格和错误码的记录类型，函数返回该类型。调用方通过判断记录中的错误码字段来区分正常/异常情况。

示例代码：

-- 定义自定义记录类型
CREATE OR REPLACE TYPE product_price_result IS RECORD(
    price NUMBER,
    error_code VARCHAR2(10)
);

CREATE OR REPLACE FUNCTION get_product_price(p IN NUMBER, d IN DATE, curTo IN NUMBER)
RETURN product_price_result
IS
    result product_price_result;
    prPrice NUMBER;
    curFrom NUMBER;
    pgcount NUMBER := 0;
    noProductsOnDate EXCEPTION;
    wrongCurrency EXCEPTION;
BEGIN
    result.error_code := NULL;
    SELECT COUNT(*) INTO pgcount
    FROM Products pr, Outgoing outg, Incoming inc 
    WHERE pr.PROD_ID = outg.PROD_ID 
      AND pr.PROD_ID = inc.PROD_ID  
      AND inc.inc_date > d
    HAVING SUM(inc.quantity) > SUM(outg.quantity);

    IF pgcount = 0 OR pgcount IS NULL THEN
        RAISE noProductsOnDate;
    END IF;

    IF curTo > 2 OR curTo < 1 THEN
        RAISE wrongCurrency;
    END IF;

    curFrom := CASE curTo WHEN 1 THEN 2 WHEN 2 THEN 1 END;

    SELECT pric.Value * cour.value INTO prPrice 
    FROM Prices pric, Cources cour 
    WHERE p = pric.prod_id 
      AND pric.DAYFROM <= d 
      AND (pric.DAYTO >= d OR pric.DAYTO IS NULL) 
      AND cour.cur_idto = curTo 
      AND cour.cur_idfrom = curFrom;

    result.price := prPrice;
    RETURN result;
EXCEPTION
    WHEN noProductsOnDate THEN 
        result.error_code := '1q';
        RETURN result;
    WHEN wrongCurrency THEN 
        result.error_code := '2q';
        RETURN result;
END;

调用示例：

DECLARE
    v_result product_price_result;
BEGIN
    v_result := get_product_price(123, SYSDATE, 1);
    IF v_result.error_code IS NOT NULL THEN
        DBMS_OUTPUT.PUT_LINE('错误：' || v_result.error_code);
    ELSE
        DBMS_OUTPUT.PUT_LINE('价格：' || v_result.price);
    END IF;
END;

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