Apex-Oracle中执行PL/SQL取数代码遭遇Internal Server Error问题排查

问题 #

尝试在PL/SQL命令行执行一段代码，意在从包含100条记录的customers表中获取信用额度超过70000的客户信息，但持续返回Internal Server Error错误。

执行的PL/SQL代码 #

Declare

l_customer_name CUSTOMERS.NAME%TYPE;
l_credit_limit CUSTOMERS.CREDIT_LIMIT%TYPE;
cntr CUSTOMERS.ID%TYPE := 1;
max_id_customer CUSTOMERS.ID%TYPE;


Begin

    SELECT MAX(ID) INTO max_id_customer FROM customers;
    LOOP
        IF cntr > max_id_customer THEN
            EXIT;
        END IF;
        SELECT name, credit_limit INTO l_customer_name, l_credit_limit FROM customers WHERE ID=cntr;
            
        IF l_credit_limit > 70000 THEN
        DBMS_OUTPUT.PUT_LINE(cntr || ' : ' || l_customer_name || ' : ' || l_credit_limit);

        END IF;
    END LOOP;    
End;

错误返回信息 #

{ "code": "InternalServerError", "message": "Internal Server Error", "type": "tag:oracle.com,2020:error/InternalServerError", "instance": "tag:oracle.com,2020:ecid/005w1djNZUxFw0KimTtlWJ00025A0009bx" }

分析与解决 #

你的代码存在两个核心问题，是触发错误的主要原因：

• 未处理异常：循环中通过ID=cntr查询单条记录，如果customers表的ID存在断号（比如有记录被删除过），SELECT ... INTO会抛出NO_DATA_FOUND异常。由于代码未捕获该异常，会导致未处理的错误，在部分PL/SQL执行环境中会触发Internal Server Error。
• 效率低下：原代码需要执行101次SQL查询（1次MAX查询+100次循环查询），属于完全没必要的低效逐行处理方式。

优化后的代码 #

直接通过一次SQL筛选出符合条件的记录，用游标循环输出即可，既避免异常问题，又大幅提升效率：

Begin
    FOR rec IN (
        SELECT id, name, credit_limit
        FROM customers
        WHERE credit_limit > 70000
        ORDER BY id
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.id || ' : ' || rec.name || ' : ' || rec.credit_limit);
    END LOOP;
End;
/

如果一定要保留原有循环逻辑（不推荐），需要添加异常处理块捕获NO_DATA_FOUND异常：

Declare
    l_customer_name CUSTOMERS.NAME%TYPE;
    l_credit_limit CUSTOMERS.CREDIT_LIMIT%TYPE;
    cntr CUSTOMERS.ID%TYPE := 1;
    max_id_customer CUSTOMERS.ID%TYPE;
Begin
    SELECT MAX(ID) INTO max_id_customer FROM customers;
    LOOP
        IF cntr > max_id_customer THEN
            EXIT;
        END IF;
        
        BEGIN
            SELECT name, credit_limit INTO l_customer_name, l_credit_limit 
            FROM customers WHERE ID=cntr;
            
            IF l_credit_limit > 70000 THEN
                DBMS_OUTPUT.PUT_LINE(cntr || ' : ' || l_customer_name || ' : ' || l_credit_limit);
            END IF;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                -- 跳过不存在的ID，也可添加日志记录逻辑
                NULL;
        END;
        
        cntr := cntr + 1;
    END LOOP;    
End;
/

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