mysql - 多变量 If 语句

Question

我最近能够生成一个过程，如果未设置变量，我可以将其设置为 null。现在我现在希望有多个变量，但如果尚未为该变量设置值，则它会返回所有行。

BEGIN

DECLARE ps_Project_Leader VARCHAR(15);
DECLARE ps_RD_Plan VARCHAR (15);
DECLARE ps_Approval_Status VARCHAR (15);
DECLARE ps_Design_Plan VARCHAR (15);

        SET ps_Project_Leader = ifnull(Project_Leader,null);
        SET ps_RD_Plan = ifnull(RD_Plan,null);
        SET ps_Approval_Status = ifnull(Approval_Status,null);
        SET ps_Design_Plan = ifnull(Design_Plan,null);

 SELECT pp.pid,
    pp.description,
    pp.approval_status,
    pp.design_plan,
    pp.rd_plan,
    pp.estimated_completion,
    pp.project_leader,
    pp.actual_completion
 FROM project_register pp

WHERE pp.project_leader =Project_Leader
        OR Project_Leader is null

and pp.rd_plan =RD_Plan
        OR RD_Plan is null

and pp.approval_status = Approval_Status
        OR Approval_Status is null

and pp.design_plan = Design_Plan
        OR Design_Plan is null

    and 
    PP.actual_completion is null;

end

例如，如果我设置了 2 个变量而不是另外 2 个，我不希望它搜索尚未设置的变量。

提前非常感谢，如果我没有完全理解(我对此很陌生，所以我表示歉意)，我很乐意澄清问题。

Answer 1

您需要正确地将 WHERE 表达式括起来:

WHERE (pp.project_leader = ps_Project_Leader
        OR ps_Project_Leader is null)
and (pp.rd_plan = ps_RD_Plan
        OR ps_RD_Plan is null)
and (pp.approval_status = ps_Approval_Status
        OR ps_Approval_Status is null)
and (pp.design_plan = ps_Design_Plan
        OR ps_Design_Plan is null)
and PP.actual_completion is null;

因为 AND 的优先级高于 OR。