MySQL 存儲過程動態構建並執行 SQL：使用 PREPARE、EXECUTE 實現條件化查詢

MySQL 存儲過程動態 SQL 實戰：PREPARE + EXECUTE

場景：根據輸入參數動態拼接 WHERE 條件，執行聚合查詢並返回多個結果集給調用者。

表結構假設

• yw_ssxxb：手術信息表，含 OPEDOC_ID（醫生）、DEPT_ID（科室）、OPER_AT（手術時間）、DANGER_INDEX（危險指數）、OPEPARTKINDID（手術分類）。
• gr_grbw：病人信息表，關聯 OPE_RELID。
• department、zh_ssczfl：字典表。

存儲過程完整代碼

DROP PROCEDURE IF EXISTS SearchByDoctor;
CREATE PROCEDURE SearchByDoctor(
  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);

  -- 基礎查詢 1：手術統計
  SET cal1 = "SELECT COUNT(1), AVG(DANGER_INDEX) INTO @para1, @para2 FROM yw_ssxxb WHERE 1=1 ";
  -- 基礎查詢 2：病人關聯統計
  SET cal2 = "SELECT COUNT(1) INTO @para3 FROM gr_grbw WHERE OPE_RELID IN (SELECT RELID FROM yw_ssxxb WHERE 1=1";

  -- 動態拼接條件
  SET cal1 = CONCAT(cal1, " AND OPEDOC_ID = '", DoctorId, "'");
  SET cal2 = CONCAT(cal2, " AND OPEDOC_ID = '", DoctorId, "'");

  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;

關鍵語法解析

• PREPARE stmt FROM @sql：將用戶變量中的 SQL 字串編譯為預處理語句。
• EXECUTE stmt：執行預處理語句。
• DEALLOCATE PREPARE stmt：釋放預處理語句資源。
• 用戶變量 @para1 等用於跨語句傳遞結果（INTO @var）。
• CONCAT() 拼接字串，注意單引號嵌套轉義。

優勢與注意

• 將複雜條件邏輯封裝在 DB 端，減少應用層往返交互。
• 動態 SQL 難以靜態分析，需嚴格防範注入（此處參數經應用層驗證）。
• MySQL 8.0+ 可考慮使用 EXECUTE IMMEDIATE（尚不支援），目前仍依賴 PREPARE/EXECUTE。