MySQL 預存程序中動態 SQL 獲取返回值的完整實現模式

通用分頁預存程序範例

CREATE PROCEDURE sp_page(
  IN  p_columns   VARCHAR(500),
  IN  p_tables    VARCHAR(100),
  IN  p_where     VARCHAR(4000),
  IN  p_order     VARCHAR(100),
  IN  p_pageindex INT,
  IN  p_pagesize  INT,
  OUT p_recordcount INT,
  OUT p_pagecount   INT
)
BEGIN
  DECLARE v_sqlcounts  VARCHAR(4000);
  DECLARE v_sqlselect  VARCHAR(4000);

  -- 1. 動態查總筆數
  SET v_sqlcounts = CONCAT('SELECT COUNT(*) INTO @recordcount FROM ', p_tables, p_where);
  SET @sqlcounts = v_sqlcounts;
  PREPARE stmt FROM @sqlcounts;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

  -- 2. 取回用戶變數到 OUT 參數
  SET p_recordcount = @recordcount;

  -- 3. 計算總頁數
  SET p_pagecount = CEILING((p_recordcount + 0.0) / p_pagesize);

  -- 4. 邊界修正
  IF p_pageindex < 1 THEN SET p_pageindex = 1;
  ELSEIF p_pageindex > p_pagecount AND p_pagecount <> 0 THEN SET p_pageindex = p_pagecount;
  END IF;

  -- 5. 動態分頁查詢
  SET v_sqlselect = CONCAT('SELECT ', p_columns, ' FROM ', p_tables, p_where,
                           IFNULL(p_order, ''), ' LIMIT ', (p_pageindex-1)*p_pagesize, ', ', p_pagesize);
  SET @sqlselect = v_sqlselect;
  PREPARE stmtselect FROM @sqlselect;
  EXECUTE stmtselect;
  DEALLOCATE PREPARE stmtselect;
END;

關鍵技巧總結

1. 用戶變數（@var）作為橋樑：SELECT ... INTO @var 在動態 SQL 中寫入，再用 SET out_param = @var 取出。
2. PREPARE/EXECUTE/DEALLOCATE 三步驟不可省略，避免洩漏 prepared statement 句柄。
3. 帶參數執行：SET @p1 = value; EXECUTE stmt USING @p1; 防止 SQL 注入。
4. IF/ELSEIF/END IF：MySQL 流程控制需以分號結尾，注意縮排可讀性。

帶參數動態 SQL 範例

SET v_sql = CONCAT('SELECT COUNT(*) INTO @cnt FROM ', v_tables, v_where);
SET @sql = v_sql;
PREPARE stmt FROM @sql;
SET @s1 = categoryid;   -- 綁定參數
EXECUTE stmt USING @s1;
DEALLOCATE PREPARE stmt;
SET recordcount = @cnt;