如何判断存储过程输出游标是否为空并返回提示信息？

要实现游标为空时返回提示信息，你可以先判断查询结果是否存在数据，再根据情况返回对应内容。以下是修改后的存储过程：

CREATE OR REPLACE PROCEDURE sp_test(
    LISTA OUT SYS_REFCURSOR
)
IS
    v_count NUMBER;
BEGIN
    -- 统计table1的记录数
    SELECT COUNT(*) INTO v_count FROM table1;

    IF v_count = 0 THEN
        -- 无数据时返回提示，保持列数与原查询一致
        OPEN LISTA FOR
            SELECT 
                '未找到任何值' AS column1,
                '' AS column2,
                '' AS column3,
                '' AS column4,
                NULL AS column5,
                '' AS column6,
                '' AS column7,
                'NO_DATA' AS column8,
                '1' AS column9
            FROM DUAL;
    ELSE
        -- 有数据时返回原查询结果
        OPEN LISTA FOR
            SELECT 
                rte AS column1,
                r AS column2,
                cs AS column3,
                d AS column4,
                xy.latitud AS column5,
                x AS column6,
                r AS column7,
                'OK' AS column8,
                '0' AS column9
            FROM table1;
    END IF;
END sp_test;

关键说明 #

• 先通过COUNT(*)判断table1是否有数据，若数据量极大，也可以改用EXISTS提升性能
• 无数据时返回的结果保持与原查询相同的列数和字段类型，确保调用端无需修改处理逻辑
• 可通过column8和column9的标识值，让调用端快速判断是否为无数据提示

如果你希望通过抛出异常的方式返回提示（而非游标返回），可以改用以下方式：

CREATE OR REPLACE PROCEDURE sp_test(
    LISTA OUT SYS_REFCURSOR
)
IS
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM table1;

    IF v_count = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, '未找到任何值');
    ELSE
        OPEN LISTA FOR
            SELECT 
                rte AS column1,
                r AS column2,
                cs AS column3,
                d AS column4,
                xy.latitud AS column5,
                x AS column6,
                r AS column7,
                'OK' AS column8,
                '0' AS column9
            FROM table1;
    END IF;
END sp_test;

这种方式会直接抛出自定义异常，调用端需要捕获该异常来获取提示信息。

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