MySQL 動態游標實戰：存儲過程中構建動態視圖實現參數化查詢

場景

存儲過程中需要根據輸入參數動態構建查詢條件，標準游標不支援動態 SQL。解決方案：動態建立視圖 + 靜態游標查詢視圖。

完整範例

1. 準備測試表與數據

CREATE TABLE webuser (username VARCHAR(10));
INSERT INTO webuser VALUES ('a1'),('a2'),('a3'),('b1'),('b2'),('b3');

2. 建立存儲過程

DROP PROCEDURE IF EXISTS dynamic_cursor;
DELIMITER //

CREATE PROCEDURE dynamic_cursor (IN p_name VARCHAR(10))
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE v_username VARCHAR(10);
  
  -- 1. 宣告游標指向固定視圖名
  DECLARE cur CURSOR FOR SELECT username FROM webuser_view;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  -- 2. 動態構建視圖 (若存在先刪除)
  DROP VIEW IF EXISTS webuser_view;
  
  SET @sqlstr = "CREATE VIEW webuser_view AS ";
  SET @sqlstr = CONCAT(@sqlstr, "SELECT username FROM webuser WHERE username LIKE '", p_name, "%'");
  
  PREPARE stmt FROM @sqlstr;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

  -- 3. 開啟游標遍歷視圖結果
  OPEN cur;
  f_loop: LOOP
    FETCH cur INTO v_username;
    IF done THEN LEAVE f_loop; END IF;
    SELECT v_username;  -- 這裡可替換為業務邏輯
  END LOOP f_loop;
  CLOSE cur;
END//

DELIMITER ;

3. 測試調用

CALL dynamic_cursor('a');
-- 輸出: a1, a2, a3

關鍵技術點解析

注意事項

• 視圖屬於 會話級 可見，並發調用時視圖名衝突風險 → 建議視圖名加入 CONNECTION_ID() 或隨機後綴
• 動態 SQL 注入風險：參數務必經過驗證/轉義，避免直接拼接不可信輸入
• MySQL 8.0+ 可考慮使用 CTE (WITH ...) 替代視圖，無命名衝突、作用域更清晰
• 游標僅支援 FORWARD ONLY、READ ONLY、NON-SCROLLABLE

進階：MySQL 8.0 CTE 寫法 (無需視圖)

CREATE PROCEDURE dynamic_cursor_cte (IN p_name VARCHAR(10))
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE v_username VARCHAR(10);
  DECLARE cur CURSOR FOR
    WITH webuser_cte AS (
      SELECT username FROM webuser WHERE username LIKE CONCAT(p_name, '%')
    )
    SELECT username FROM webuser_cte;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  
  OPEN cur;
  f_loop: LOOP
    FETCH cur INTO v_username;
    IF done THEN LEAVE f_loop; END IF;
    SELECT v_username;
  END LOOP f_loop;
  CLOSE cur;
END;

CTE 方案更簡潔、線程安全、無額外對象開銷，推薦 MySQL 8.0+ 環境使用。