在預存程序中，可透過 SELECT 語句，得到想要的查詢結果。但如果我們想要的不是查看結果，而是像一般程式語言的陣列那樣，能夠逐一取出裡面的資料作處理，此時就需要用到「游標」（cursor）了。

本文會介紹游標的使用方式，並搭配「處理器」（handler）進行錯誤處理。

一、測試資料

以下是本文的測試資料。

員工表，包含員工編號與名字，共兩個欄位。

CREATE TABLE `employee` (
    `emp_id` INT PRIMARY KEY,
    `name` VARCHAR(20)
);

INSERT INTO `employee` (`emp_id`, `name`)
VALUES
(206, "小黃"),
(207, "小綠"),
(208, "小黑");

銷售紀錄表，包含員工編號、銷售日期與金額，共三個欄位。

CREATE TABLE `sales` (
    `emp_id` INT,
    `date` DATE,
    `total_sales` INT
);

INSERT INTO `sales` (`emp_id`, `date`, `total_sales`)
VALUES
(206, "2023-01-23", 70000),
(206, "2023-01-21", 21000),
(207, "2023-02-09", 34000),
(207, "2023-02-18", 87940),
(208, "2023-03-11", 19800),
(208, "2023-03-04", 43000);

根據以上的測試資料，以下的查詢語法，是統計 2023 年每位員工的銷售金額。

SELECT `employee`.`emp_id`, SUM(`total_sales`) AS `sum_sales`
FROM `sales`
JOIN `employee` ON `sales`.`emp_id` = `employee`.`emp_id`
WHERE YEAR(`date`) = 2023
GROUP BY `employee`.`emp_id`;

結果為：

讀者可以將查詢結果想像成「陣列」。在預存程序中，當我們想從查詢結果，逐一處理每一筆資料（就像處理陣列中的每一個元素），就需要用到「游標」（cursor）了。

二、游標（cursor）

以下的預存程序，是將上述查詢指定年度總銷售額的結果，儲存到叫做「employee_yearly_sales_snapshot」的獨立資料表中。

CREATE PROCEDURE `create_employee_yearly_sales_snapshot`(
    IN `targetYear` INT
)
BEGIN
    -- 宣告變數供 cursor 使用
    DECLARE c_emp_id INT;
    DECLARE c_sales INT;

    -- 宣告 cursor（需在變數之後）
    DECLARE cursor_sales CURSOR FOR
        SELECT `employee`.`emp_id`, SUM(`total_sales`) AS `sum_sales`
        FROM `sales`
        JOIN `employee` ON `sales`.`emp_id` = `employee`.`emp_id`
        WHERE YEAR(`date`) = `targetYear`
        GROUP BY `employee`.`emp_id`;

    -- 建立獨立資料表
    CREATE TABLE IF NOT EXISTS `employee_yearly_sales_snapshot`(
        `year` INT,
        `emp_id` INT,
        `sales` INT,
        PRIMARY KEY(`year`, `emp_id`)
    );

    -- 刪除該年度的舊資料
    DELETE FROM `employee_yearly_sales_snapshot`
    WHERE `year` = `targetYear`;

    -- 開啟 cursor
    OPEN cursor_sales;
    
    WHILE true DO
        -- 取出下一筆資料
        FETCH cursor_sales INTO c_emp_id, c_sales;

        -- 插入到資料表
        INSERT INTO `employee_yearly_sales_snapshot`(`year`, `emp_id`, `sales`)
        VALUES(targetYear, c_emp_id, c_sales);
    END WHILE;

    -- 關閉 cursor
    CLOSE cursor_sales;
END

在上面的程式中，宣告了一個叫做「cursor_sales」的游標。語法為 DECLARE 名稱 CURSOR FOR 查詢語法;。

游標有三個操作方式。

• OPEN 游標名稱：開啟游標，將查詢結果放進快取。
• FETCH 游標名稱 INTO 變數名稱...：取出下一筆資料的各個欄位值。
• CLOSE 游標名稱：關閉游標，從快取清除資料。

Cursor 宛如一個「指針」，會在記憶體中移動到下一筆資料的位置，我們並無法從中得知總共有幾筆資料。於是在範例程式中，筆者暫時寫出 WHILE true DO 這樣的無窮迴圈，來不斷取出資料。到了第三節，我們再來做調整。

使用 FETCH 能從 cursor 取出下一筆資料，並透過 INTO，將欄位值賦予給各個變數。要注意的是，變數名稱的順序，在撰寫上必須與查詢結果的欄位相同。

三、錯誤處理

若讀者執行上面完成的預存程序，會發現資料確實有插入到新的資料表中。然而出現了以下的錯誤訊息：

Error Code: 1329. No data - zero rows fetched, selected, or processed

會出現此訊息，是因為 cursor 的 FETCH 操作已經走訪完所有資料，已經找不到下一筆了。讀者可在官方文件中搜尋「1329」這個錯誤碼（Error Code），會找到「02000」的狀態碼（SQLSTATE）。

為了因應這項錯誤，本節將介紹「處理器」（handler）來進行錯誤處理。處理器的宣告語法如下：

DECLARE 動作 HANDLER FOR 狀態
BEGIN
    處理流程
END;

宣告時有三個需要自定義的地方。

• 動作（action）：可選擇 EXIT 或 CONTINUE。前者代表要停止執行當前的程式，後者則繼續執行。
• 狀態（state）：可提供的參數包含明確的狀態碼（如上述的 02000）、SQLWARNING（所有 01 開頭的狀態碼）、 NOT FOUND（所有 02 開頭的狀態碼），以及 SQLEXCEPTION（其他錯誤）。
• 處理流程：發生錯誤時要執行的程式，例如寫 log。

那麼，讓我們透過處理器來調整上一節的預存程序。以下會省略部份程式碼，讓讀者專注於使用方式。

CREATE PROCEDURE `create_employee_yearly_sales_snapshot`(
    IN `targetYear` INT
)
BEGIN
    -- 略

    DECLARE is_cursor_sales_done TINYINT DEFAULT 0;

    -- 宣告 cursor（需在變數之後）

    -- 宣告 handler（需在 cursor 之後）
    DECLARE CONTINUE HANDLER FOR SQLSTATE "02000"
    BEGIN
        SET is_cursor_sales_done = 1;
    END;

    -- 略

    OPEN cursor_sales;

    -- 取出第一筆資料，若有資料才進入迴圈
    FETCH cursor_sales INTO c_emp_id, c_sales;

    WHILE is_cursor_sales_done != 1 DO        
        -- 插入到資料表
        INSERT INTO `employee_yearly_sales_snapshot`(`year`, `emp_id`, `sales`)
        VALUES(targetYear, c_emp_id, c_sales);

        -- 取出下一筆資料
        FETCH cursor_sales INTO c_emp_id, c_sales;
    END WHILE;

    CLOSE cursor_sales;
END

此處用來取出 cursor 資料的迴圈，是透過名為「is_cursor_sales_done」的變數，來控制是否要循環。若試圖取出資料時發生「02000」狀態的錯誤，則處理器將該變數值設為 1，藉此離開迴圈。隨後繼續執行後面的程式碼。

在宣告處理器時，除了直接定義要處理的狀態碼，亦能考慮使用 NOT FOUND 關鍵字來囊括多個狀態碼，也能增加可讀性。

DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
    SET is_cursor_sales_done = 1;
END

今日文章到此結束！
最後宣傳一下自己的部落格，我是「新手工程師的程式教室」的作者，主要發表後端相關文章，請多指教