mysql - 如何在存储过程中使用mysql中的if

Question

我正在做一个存储过程，我正在验证当 id_plan 大于零时，也就是说，它会在考虑 id_plan 的情况下进行查询，否则忽略它并通过跳过 id_plan 进行查询

这就是我进行查询的方式

SET @id_convenio = 64; 
SET @id_plan = 20;
SET @fecha_ini = "03/01/2019";
SET @fecha_fin = "05/08/2019 23:59:59";

SELECT PD.id_detalle_precio, P.id_admision, P.id_paciente, HC.id_hc, 0 AS ind_revisado, 0 AS ind_borrado, NULL AS num_factura, P.id_convenio,
P.id_plan, NULL AS cod_prestador, TD.codigo_detalle AS tipo_documento, PA.numero_documento, 
DATE_FORMAT(IFNULL(HC.fecha_hora_hc, P.fecha_pago), '%d/%m/%Y') AS fecha_consulta_t, IFNULL(PD.num_autorizacion, A.num_autorizacion) AS num_autorizacion, PD.cod_procedimiento, 
NULL AS fin_consulta, NULL AS causa_ext, NULL AS cod_ciex_prin, NULL AS cod_ciex_rel1, NULL AS cod_ciex_rel2, NULL AS cod_ciex_rel3, NULL AS tipo_diag_prin, 
0 AS valor_consulta, 0 AS valor_cuota, 0 AS valor_neto, NULL AS observaciones, 1 AS id_usuario_crea, NOW() AS fecha_crea

FROM pagos P 

INNER JOIN pagos_detalle PD ON P.id_pago=PD.id_pago 
INNER JOIN planes PL ON P.id_plan=PL.id_plan 
INNER JOIN maestro_procedimientos MP ON PD.cod_procedimiento=MP.cod_procedimiento 
INNER JOIN pacientes PA ON P.id_paciente=PA.id_paciente 
LEFT JOIN listas_detalle TD ON PA.id_tipo_documento=TD.id_detalle 
INNER JOIN admisiones A ON P.id_admision=A.id_admision 
INNER JOIN tipos_citas_det CD ON A.id_tipo_cita=CD.id_tipo_cita 
INNER JOIN historia_clinica HC ON P.id_admision=HC.id_admision 
AND CD.id_tipo_reg=HC.id_tipo_reg 
WHERE P.id_convenio=@id_convenio 
/**AND P.id_plan=@id_plan**/
AND IF @id_plan>0 THEN  P.id_plan=@id_plan
END IF
AND P.estado_pago=2 
AND HC.fecha_hora_hc BETWEEN STR_TO_DATE(@fecha_ini, '%d/%m/%Y') AND STR_TO_DATE(@fecha_fin, '%d/%m/%Y %H:%i:%s') 
AND MP.tipo_procedimiento='C' 
AND NOT EXISTS ( SELECT RC.id_detalle_precio 
FROM rips_consultas RC 
WHERE RC.id_convenio=@id_convenio AND RC.id_plan=@id_plan AND PD.id_detalle_precio=RC.id_detalle_precio);

这是向我展示的错误

执行 5 个查询，4 个成功，1 个错误，0 个警告

查询：SELECT PD.id_detalle_precio, P.id_admision, P.id_paciente, HC.id_hc, 0 AS ind_revisado, 0 AS ind_borrado, NULL AS num_factura, P...

错误代码：1064 您的 SQL 语法有错误；检查与您的 MariaDB 服务器版本相对应的手册，以在第 18 行的“@id_plan THEN set P.id_plan=@id_plan END IF AND P.estado_pago=2 AND HC.fecha_ho”附近使用正确的语法

执行时间：0 秒传输时间：0 秒总时间：0 秒

标签： mysqlsql

Answer 1

这部分

WHERE P.id_convenio=@id_convenio 
AND IF @id_plan>0 THEN  P.id_plan=@id_plan
END IF
AND P.estado_pago=2
...

可以用多种方式表达。

使用 IF() 函数：

WHERE P.id_convenio=@id_convenio 
AND IF(@id_plan>0, P.id_plan=@id_plan, TRUE)
AND P.estado_pago=2
...

使用 CASE 语句

WHERE P.id_convenio=@id_convenio 
AND CASE WHEN @id_plan>0 THEN  P.id_plan=@id_plan ELSE TRUE END
AND P.estado_pago=2
...

使用 OR 条件：

WHERE P.id_convenio=@id_convenio 
AND (NOT (@id_plan>0) OR  P.id_plan=@id_plan)
AND P.estado_pago=2
...

这相当于

WHERE P.id_convenio=@id_convenio 
AND (@id_plan<=0 OR  P.id_plan=@id_plan)
AND P.estado_pago=2
...

如果@id_plan可以NULL，那么您可能需要

WHERE P.id_convenio=@id_convenio 
AND (@id_plan<=0 OR @id_plan is NULL OR P.id_plan=@id_plan)
AND P.estado_pago=2
...

如果@id_plan既不能是也不能是NULL负数，那么你只需要

WHERE P.id_convenio=@id_convenio 
AND (@id_plan=0 OR P.id_plan=@id_plan)
AND P.estado_pago=2
...