MySQL 存储过程动态执行sql语句

之前经常在程序中拼接sql语句，其实我们也可以在存储过程中拼接sql 语句，动态的执行~~

之前经常在程序中拼接sql语句，其实我们也可以在存储过程中拼接sql 语句，动态的执行~~

代码如下：

DROP PROCEDURE IF EXISTS SearchByDoctor;
CREATE PROCEDURE SearchBy本文来源gaodai#ma#com搞*!代#%^码网%Doctor(
IN DoctorId VARCHAR(50),
IN deptId VARCHAR(50),
IN beginDate VARCHAR(20),
IN endDate VARCHAR(20),
IN StandDeptId VARCHAR(50),
IN OperationFlag VARCHAR(50),
IN SsczflId VARCHAR(50),
OUT OperNum INT,
OUT AvgDangerIndex DOUBLE,
OUT OperGrCase INT
)
BEGIN
DECLARE cal1 VARCHAR(800);
DECLARE cal2 VARCHAR(800);

SET cal1=”SELECT COUNT(1),AVG(DANGER_INDEX) INTO @para1,@para2 FROM yw_ssxxb WHERE 1=1 “;
SET cal2=”SELECT COUNT(1) INTO @para3 FROM gr_grbw WHERE OPE_RELID IN (SELECT RELID FROM yw_ssxxb WHERE 1=1″;

#拼接医生id
SET cal1=CONCAT(cal1,” “,”AND OPEDOC_ID = “,”‘”,DoctorId,”‘”);
SET cal2=CONCAT(cal2,” “,”AND OPEDOC_ID = “,”‘”,DoctorId,”‘”);

#拼接科室id
IF deptId ” THEN
SET cal1=CONCAT(cal1,”AND DEPT_ID =”,”‘”,deptId,”‘”);
END IF;

#拼接开始结束日期
IF beginDate” AND endDate ” THEN
SET cal1=CONCAT(cal1,” “,”AND OPER_AT BETWEEN “,”‘”,beginDate,”‘”,” AND “,”‘”,endDate,”‘”);
END IF;

#拼接标准科室
IF StandDeptId” THEN
SET cal1=CONCAT(cal1,” “,”AND DEPT_ID IN ( SELECT ID FROM department WHERE STAND_DEPT_ID = “,”‘”,StandDeptId,”‘”,”)”);
END IF;

#拼接数据来源
IF OperationFlag ” THEN
SET cal1=CONCAT(cal1,” “,”AND OPEPARTKINDID IN (SELECT ID FROM zh_ssczfl WHERE FLAG= “,”‘”,OperationFlag,”‘”,”)”);
END IF;

#拼接手术操作类别
IF SsczflId ” THEN
SET cal1=CONCAT(cal1,” “,”AND OPEPARTKINDID = “,”‘”,SsczflId,”‘”);
END IF;

SET cal2 =CONCAT(cal2,”)”);

SET @sql1=cal1;
SET @sql2=cal2;

PREPARE stmt1 FROM @sql1;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1 ;

PREPARE stmt2 FROM @sql2;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2 ;

SET OperNum=@para1;
SET AvgDangerIndex=@para2;
SET OperGrCase=@para3;

END;

涉及到的具体的业务逻辑就不介绍了，语法方面还是可以参照下的。

喜欢 (0)赏

[搞代码]

分享 (0)