Oracle存储过程编写报PLS-00103错误 露营者收费校验逻辑排查

一、编译报错根因 #

你触发PLS-00103报错的直接原因是分支语法不闭合：代码中一共定义了4个IF判断分支，仅写了3个END IF收尾，最后一个预算校验的IF分支缺失对应的END IF语句，Oracle语法解析到最后一个END的时候找不到匹配的IF，就会报该错误。

除此之外你的代码还存在大量逻辑和语法问题，完全不符合需求要求：

• 输入参数类型全错：category、chargedate、amount三个参数全部错写为charges.camperid%type，类型完全不匹配
• 变量名不一致：声明的错误标识变量是p_error，实际代码里用的是v_error，运行会报变量未定义
• 校验逻辑完全错误：从charges表查指定camperid的记录来校验camperid是否有效，逻辑倒置，应该查存储露营者基础信息的campers表；分类校验逻辑也不对，不能用对应camper的消费记录来判断分类是否合法
• 无异常处理：SELECT INTO语句如果查询不到数据会直接触发NO_DATA_FOUND异常，没有捕获逻辑会直接中断运行
• 逻辑覆盖问题：前序校验的结果会被后续校验覆盖，无法正确返回第一个触发的错误
• 预算校验逻辑错误：直接写campers.spent < campers.budget没有加camperid过滤，会返回多行报错，而且没有把本次传入的金额算入总消费判断是否超预算
• 金额范围校验错误：需求要求金额不超过40，你写的是<40，应该是<=40

二、修正后符合需求的存储过程代码 #

create or replace procedure P_STORED
(
    c_camperid   in  charges.camperid%type,
    c_category   in  charges.category%type,
    c_chargedate in  charges.chargedate%type,
    c_amt        in  charges.amt%type,
    c_message    out varchar2
)
as
    v_camper_count number;
    v_cat_count    number;
    v_spent        campers.spent%type;
    v_budget       campers.budget%type;
begin
    -- 校验1：camperid是否有效，查露营者基础表
    select count(*) into v_camper_count 
    from campers 
    where camperid = c_camperid;
    if v_camper_count = 0 then
        c_message := 'ERROR: No such camper ID.';
        return;
    end if;

    -- 校验2：分类是否有效，此处假设合法分类存在于charges表的category字段，如有单独字典表可替换为查字典表
    select count(*) into v_cat_count 
    from (select distinct category from charges) 
    where category = c_category;
    if v_cat_count = 0 then
        c_message := 'ERROR: No such charge category.';
        return;
    end if;

    -- 校验3：金额范围是否合法
    if c_amt <=0 or c_amt > 40 then
        c_message := 'ERROR: amount must be >0 and no more than $ 40.';
        return;
    end if;

    -- 校验4：加本次金额后总消费是否超预算
    select spent, budget into v_spent, v_budget
    from campers
    where camperid = c_camperid;
    if v_spent + c_amt > v_budget then
        c_message := 'ERROR: insufficient funds.';
        return;
    end if;

    -- 所有校验通过返回成功提示
    c_message := 'SUCCESS: charge record is valid.';
exception
    when others then
        c_message := 'ERROR: system exception, ' || sqlerrm;
end;
/

三、补充说明 #

• 代码新增了提前return逻辑，只要某一个校验不通过就直接返回错误，不会执行后续校验，符合常规校验逻辑
• 新增了异常捕获逻辑，避免运行时异常直接中断存储过程
• 如果你的合法分类是存在单独的category字典表，把第二个校验的查询语句替换为查对应字典表即可
• 所有变量和参数类型都匹配对应表字段类型，避免隐式类型转换报错

内容的提问来源于stack exchange，提问作者spread-peace Lord