Oracle PL/SQL学生记录查询存储过程逻辑实现求助

需求回顾 #

• 仅传入pis_email时，查询该邮箱对应的学生记录
• 仅传入pis_rollno/pis_dept/pi_class_id中任意一个参数时，执行对应条件的查询
• 所有参数均为null时，返回student表全部记录

补全后的完整存储过程 #

create or replace procedure get_student_records(
    pis_email in varchar2,
    pis_rollno in number,
    pis_dept in varchar,
    pi_class_id in number
)
IS
    cursor c_student is
        select s.s_id, s.s_rollno, s.s_name, s.s_dept, s.s_email, 
               c.class_name, d.dept_name
        from student s
        left join class c on s.class_id = c.class_id
        left join dept d on c.dept_id = d.dept_id
        where 
            -- 仅邮箱参数有效
            (pis_email is not null 
             and pis_rollno is null 
             and pis_dept is null 
             and pi_class_id is null
             and s.s_email = pis_email)
            -- 仅学号参数有效
            or (pis_rollno is not null 
                and pis_email is null 
                and pis_dept is null 
                and pi_class_id is null
                and s.s_rollno = pis_rollno)
            -- 仅部门参数有效
            or (pis_dept is not null 
                and pis_email is null 
                and pis_rollno is null 
                and pi_class_id is null
                and s.s_dept = pis_dept)
            -- 仅班级ID参数有效
            or (pi_class_id is not null 
                and pis_email is null 
                and pis_rollno is null 
                and pis_dept is null
                and s.class_id = pi_class_id)
            -- 所有参数为空，返回全部记录
            or (pis_email is null 
                and pis_rollno is null 
                and pis_dept is null 
                and pi_class_id is null);
    v_student c_student%rowtype;
begin
    -- 校验：禁止传入多个非空参数
    if (case when pis_email is not null then 1 else 0 end +
        case when pis_rollno is not null then 1 else 0 end +
        case when pis_dept is not null then 1 else 0 end +
        case when pi_class_id is not null then 1 else 0 end) > 1 then
        dbms_output.put_line('错误：仅允许传入单个参数或不传入任何参数');
        return;
    end if;

    -- 遍历并输出查询结果
    open c_student;
    loop
        fetch c_student into v_student;
        exit when c_student%notfound;
        
        dbms_output.put_line(
            '学生ID: ' || v_student.s_id || 
            ' | 学号: ' || v_student.s_rollno || 
            ' | 姓名: ' || v_student.s_name || 
            ' | 部门: ' || nvl(v_student.dept_name, '未分配') || 
            ' | 邮箱: ' || v_student.s_email || 
            ' | 班级: ' || nvl(v_student.class_name, '未分配')
        );
    end loop;
    close c_student;

    -- 提示无匹配记录
    if c_student%rowcount = 0 then
        dbms_output.put_line('未找到匹配的学生记录');
    end if;
exception
    when others then
        dbms_output.put_line('执行异常: ' || sqlerrm);
        raise;
end get_student_records;
/

关键逻辑说明 #

1. 严格条件过滤：通过where子句的多分支判断，确保只有单个参数非空时才执行对应条件的查询，完全符合需求场景；全参数为空时返回所有记录。
2. 非法输入校验：通过计数非空参数的数量，禁止传入多个非空参数的情况，避免逻辑混淆。
3. 关联表查询：关联class和dept表，返回包含班级名称、部门名称的完整学生信息，比仅查student表更实用。
4. 友好输出处理：用nvl()函数处理可能为空的字段（如未分配班级/部门的情况），同时增加无数据提示和异常捕获，提升用户体验。

可选调整建议 #

• 如果需要将结果返回给调用程序而非打印，可添加out sys_refcursor参数，将游标作为输出返回。
• 若pis_dept参数需要匹配部门名称而非student表中的dept代码，可修改查询条件为s.s_dept = d.dept_id and d.dept_name = pis_dept。

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