MySQL 游標輸出資料實戰範例：逐行擷取員工 ID 列表

建立測試資料表與資料

CREATE TABLE Employee(
    id INT,
    first_name VARCHAR(15),
    last_name VARCHAR(15),
    start_date DATE,
    end_date DATE,
    salary FLOAT(8,2),
    city VARCHAR(10),
    description VARCHAR(15)
);

INSERT INTO Employee VALUES
(1,'Jason','Martin','19960725','20060725',1234.56,'Toronto','Programmer'),
(2,'Alison','Mathews','19760321','19860221',6661.78,'Vancouver','Tester'),
(3,'James','Smith','19781212','19900315',6544.78,'Vancouver','Tester'),
(4,'Celia','Rice','19821024','19990421',2344.78,'Vancouver','Manager'),
(5,'Robert','Black','19840115','19980808',2334.78,'Vancouver','Tester'),
(6,'Linda','Green','19870730','19960104',4322.78,'New York','Tester'),
(7,'David','Larry','19901231','19980212',7897.78,'New York','Manager'),
(8,'James','Cat','19960917','20020415',1232.78,'Vancouver','Tester');

游標預存程序：逐行輸出 ID

DELIMITER $$
CREATE PROCEDURE myProc()
READS SQL DATA
BEGIN
    DECLARE l_last_row INT DEFAULT 0;
    DECLARE l_dept_id INT;
    
    DECLARE c_dept CURSOR FOR 
        SELECT id FROM employee;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row = 1;
    
    OPEN c_dept;
    
    dept_cursor: LOOP
        FETCH c_dept INTO l_dept_id;
        IF (l_last_row = 1) THEN
            LEAVE dept_cursor;
        END IF;
        SELECT l_dept_id;  -- 每行輸出一個結果集
    END LOOP dept_cursor;
    
    CLOSE c_dept;
END$$
DELIMITER ;

執行結果

mysql> CALL myProc();
+-----------+
| l_dept_id |
+-----------+
| 1         |
+-----------+
1 row in set (0.00 sec)

+-----------+
| l_dept_id |
+-----------+
| 2         |
+-----------+
1 row in set (0.16 sec)
... (持續至 ID 8)

清理

DROP PROCEDURE myProc;
DROP TABLE Employee;

關鍵觀察

• 迴圈中每次 SELECT l_dept_id 產生獨立結果集，客戶端會收到 8 個單行結果集。
• 若需彙總輸出，可改用變數累積（如 CONCAT）或暫存表。
• READS SQL DATA 特性宣告告知伺服器此程序僅讀取資料，有助優化器決策。