MySQL 預存程序中游標應用實戰：計算湖人隊連敗場次

什麼是游標？

游標是預存程式中處理結果集的核心機制，可視為 C/C++ 的指標或 PHP 的迭代器。它允許逐行遍歷查詢結果，並在伺服器端完成資料處理，減少網路傳輸與客戶端運算負擔。

實戰場景：統計湖人隊最長連敗/連勝

資料表結構：

部分 2013-14 賽季資料：

單一年份查詢預存程序

DELIMITER $$
CREATE PROCEDURE `streak`(
    IN cur_year INT,
    OUT longeststreak INT,
    OUT status CHAR(1)
)
BEGIN
    DECLARE current_win CHAR(1);
    DECLARE current_streak INT;
    DECLARE current_status CHAR(1);
    
    -- 宣告游標：按 ID 降序取得該年度勝負記錄
    DECLARE cur CURSOR FOR 
        SELECT winlose FROM lakers 
        WHERE year = cur_year AND winlose <> '' 
        ORDER BY id DESC;
    
    SET current_streak = 0;
    OPEN cur;
    FETCH cur INTO current_win;
    SET current_streak = current_streak + 1;
    
    start_loop: LOOP
        FETCH cur INTO current_status;
        IF current_status <> current_win THEN
            LEAVE start_loop;
        ELSE
            SET current_streak = current_streak + 1;
        END IF;
    END LOOP;
    
    CLOSE cur;
    SELECT current_streak INTO longeststreak;
    SELECT current_win INTO `status`;
END$$
DELIMITER ;

PHP 呼叫測試

<?php
$dsn = "mysql:host=localhost;dbname=sitepoint";
$cn = new PDO($dsn, 'root', 'your_pass_here');
$cn->exec('CALL streak(2013, @longeststreak, @status)');
$res = $cn->query('SELECT @longeststreak, @status')->fetchAll();
$win = $res[0]['@status'] == 'L' ? 'Loss' : 'Win';
$streak = $res[0]['@longeststreak'];
echo "Lakers is now $streak consecutive $win.\n";
?>

輸出結果：

回傳資料集：多年份統計表

利用暫存表彙總所有年份：

CREATE PROCEDURE `yearly_streak`()
BEGIN
    DECLARE cur_year, max_year, min_year INT;
    SELECT MAX(year), MIN(year) FROM lakers INTO max_year, min_year;
    
    DROP TEMPORARY TABLE IF EXISTS yearly_streak;
    CREATE TEMPORARY TABLE yearly_streak (
        season INT, streak INT, win CHAR(1)
    );
    
    SET cur_year = max_year;
    year_loop: LOOP
        IF cur_year < min_year THEN LEAVE year_loop; END IF;
        CALL streak(cur_year, @l, @s);
        INSERT INTO yearly_streak VALUES (cur_year, @l, @s);
        SET cur_year = cur_year - 1;
    END LOOP;
    
    SELECT * FROM yearly_streak;
    DROP TEMPORARY TABLE IF EXISTS yearly_streak;
END

PHP 直接查詢資料集

$res = $cn->query('CALL yearly_streak')->fetchAll();
foreach ($res as $r) {
    echo sprintf("In year %d, the longest W/L streaks is %d %s\n", 
        $r['season'], $r['streak'], $r['win']);
}

輸出結果：

關鍵差異

• 純輸出參數：需用 exec() 呼叫，再用 query() 取得 @variable。
• 回傳結果集：可直接用 query('CALL proc') 獲取。

結語

游標讓複雜邏輯下沉到資料庫層，配合暫存表可構建靈活的報表查詢。更多語法請參考 MySQL 官方文件。