24. 使用MySQL之使用遊標

hisun9發表於2024-11-11
MySql

1. 遊標

由前幾章可知，MySQL檢索操作返回一組稱為結果集的行。這組返回的行都是與SQL語句相匹配的行（零行或多行）。

使用簡單的SELECT語句，例如，沒有辦法得到第一行、下一行或前10行，也不存在每次一行地處理所有行的簡單方法（相對於成批地處理它們）。

有時，需要在檢索出來的行中前進或後退一行或多行。這就是使用遊標的原因。

遊標（cursor）是一個儲存在MySQL伺服器上的資料庫查詢，它不是一條SELECT語句，而是被該語句檢索出來的結果集。在儲存了遊標之後，應用程式可以根據需要滾動或瀏覽其中的資料。遊標主要用於互動式應用，其中使用者需要滾動螢幕上的資料，並對資料進行瀏覽或做出更改。

注意：

  • 只能用於儲存過程：

    不像多數DBMS，MySQL遊標只能用於儲存過程（和函式）。

2. 使用遊標

使用遊標涉及幾個明確的步驟。

  • 在能夠使用遊標前，必須宣告（定義）它。這個過程實際上沒有檢索資料，它只是定義要使用的SELECT語句。

  • 一旦宣告後，必須開啟遊標以供使用。這個過程用前面定義的SELECT語句把資料實際檢索出來。

  • 對於填有資料的遊標，根據需要取出（檢索）各行。

  • 在結束遊標使用時，必須關閉遊標。

在宣告遊標後，可根據需要頻繁地開啟和關閉遊標。在遊標開啟後，可根據需要頻繁地執行取操作。

2.1 建立遊標

遊標用DECLARE語句建立（參見第23章）。

DECLARE命名遊標，並定義相應的SELECT語句，根據需要帶WHERE和其他子句。

例如，下面的語句定義了名為ordernumbers的遊標，使用了可以檢索所有訂單的SELECT語句。

delimiter //
create procedure processorders()
begin
	declare ordernumbers cursor 
    for
    select order_num from orders;
end //
delimiter ;

這個儲存過程並沒有做很多事情，DECLARE語句用來定義和命名遊標，這裡為ordernumbers。儲存過程處理完成後，遊標就消失（因為它侷限於儲存過程）。

在定義遊標之後，可以開啟它。

2.2 開啟和關閉遊標

遊標用OPEN CURSOR語句來開啟：

open ordernumbers;

在處理OPEN語句時執行查詢，儲存檢索出的資料以供瀏覽和滾動。

遊標處理完成後，應當使用如下語句關閉遊標：

close ordernumbers;

CLOSE釋放遊標使用的所有內部記憶體和資源，因此在每個遊標不再需要時都應該關閉。

在一個遊標關閉後，如果沒有重新開啟，則不能使用它。但是，使用宣告過的遊標不需要再次宣告，用OPEN語句開啟它就可以了。

補充：

  • 隱含關閉：

    如果你不明確關閉遊標，MySQL將會在到達END語句時自動關閉它。

下面是前面例子的修改版本：

delimiter //
create procedure processorders()
begin
	-- Declare the cursor
	declare ordernumbers cursor 
    for
    select order_num from orders;
    
    -- Open the cursor
    open ordernumbers;
    
    -- close the cursor
    close ordernumbers;
end //
delimiter ;

這個儲存過程宣告、開啟和關閉一個遊標。但對檢索出的資料什麼也沒做。

2.3 使用遊標資料

在一個遊標被開啟後，可以使用FETCH語句分別訪問它的每一行。

FETCH指定檢索什麼資料（所需的列），檢索出來的資料儲存在什麼地方。它還向前移動遊標中的內部行指標，使下一條FETCH語句檢索下一行（不重複讀取同一行）。

舉例說明

  • 第一個例子從遊標中檢索單個行（第一行）：

    delimiter //
create procedure processorders()
begin

    -- Declare local variables
    declare o int;
    
    -- Declare the cursor
    declare ordernumbers cursor 
    for
    select order_num from orders;
    
    -- Open the cursor
    open ordernumbers;
    
    -- Get order number
    fetch ordernumbers into o;
    
    -- close the cursor
    close ordernumbers;
end //
delimiter ;

    其中FETCH用來檢索當前行的order_num列（將自動從第一行開始）到一個名為o的區域性宣告的變數中。對檢索出的資料不做任何處理。

  • 在下一個例子中，迴圈檢索資料，從第一行到最後一行：

    delimiter //
create procedure processorders()
begin

    -- Declare local variables
    declare done boolean default 0;
    declare o int;
    
    -- Declare the cursor
    declare ordernumbers cursor 
    for
    select order_num from orders;
    
    -- Declare continue handler
    declare continue handler for sqlstate '02000' set done = 1;
    
    -- Open the cursor
    open ordernumbers;
    
    -- loop through all rows
    repeat
    
        -- Get order number
        fetch ordernumbers into o;
    
    -- End of loop
    until done end repeat;
    
    -- close the cursor
    close ordernumbers;
end //
delimiter ;

    與前一個例子一樣，這個例子使用FETCH檢索當前order_num到宣告的名為o的變數中。

    但與前一個例子不一樣的是，這個例子中的FETCH是在REPEAT內，因此它反覆執行直到done為真（由UNTIL done END REPEAT;規定）。為使它起作用，用一個DEFAULT 0（假，不結束）定義變數done。那麼，done怎樣才能在結束時被設定為真呢？答案是用以下語句：

    declare continue handler for sqlstate '02000' set done = 1;

    這條語句定義了一個CONTINUE HANDLER，它是在條件出現時被執行的程式碼。這裡，它指出當SQLSTATE '02000'出現時，SET done=1。SQLSTATE '02000'是一個未找到條件，當REPEAT由於沒有更多的行供迴圈而不能繼續時，出現這個條件。

    插句題外話

    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;是一種在 SQL 中定義異常處理器的語句，通常在儲存過程或儲存函式中使用。以下是其具體含義：

    • DECLARE CONTINUE HANDLER：宣告一個處理器，當指定的錯誤或狀態碼發生時繼續執行，而不會退出或中斷當前的 SQL 語句塊。

    • FOR SQLSTATE '02000'：表示處理 SQL 狀態碼 '02000'。這個狀態碼 '02000' 通常指“沒有更多的行”或“未找到資料”，例如在遊標操作中遍歷結束時會出現這個狀態碼。

    • SET done = 1：當狀態碼 '02000' 發生時，將變數 done 的值設定為 1。這個 done 變數通常用來判斷是否已經到達資料的末尾，以控制迴圈結構（例如 LOOP、WHILE 等）的結束條件。

    迴歸正題

    如果呼叫這個儲存過程，它將定義幾個變數和一個CONTINUE HANDLER，定義並開啟一個遊標，重複讀取所有行，然後關閉遊標。

    如果一切正常，你可以在迴圈內放入任意需要的處理（在FETCH語句之後，迴圈結束之前）。

    補充：

    • MySQL的錯誤程式碼：

      關於MySQL 5使用的MySQL錯誤程式碼列表，請參閱此連結。

    • DECLARE語句的次序：

      DECLARE語句的釋出存在特定的次序。用DECLARE語句定義的區域性變數必須在定義任意遊標或控制代碼之前定義，而控制代碼必須在遊標之後定義。不遵守此順序將產生錯誤訊息。

    • 重複或迴圈？：

      除這裡使用的REPEAT語句外，MySQL還支援迴圈語句，它可用來重複執行程式碼，直到使用LEAVE語句手動退出為止。通常REPEAT語句的語法使它更適合於對遊標進行迴圈。

  • 為了把這些內容組織起來，下面給出遊標儲存過程樣例的更進一步修改的版本，這次對取出的資料進行某種實際的處理：

delimiter //
create procedure processorders()
begin

	-- Declare local variables
    declare done boolean default 0;
    declare o int;
    declare t decimal(8,2);
    
	-- Declare the cursor
	declare ordernumbers cursor 
    for
    select order_num from orders;
    
    -- Declare continue handler
    declare continue handler for sqlstate '02000' set done = 1;
    
    -- Create a table to store the results
    create table if not exists ordertotals
		(order_num int, total decimal(8,2));
    
    -- Open the cursor
    open ordernumbers;
    
    -- loop through all rows
    repeat
    
		-- Get order number
		fetch ordernumbers into o;
        
        -- Get the total for this order
        call ordertotal(o, 1, t);
        
        -- Insert order and total into ordertotals
        insert into ordertotals(order_num, total)
        values(o, t);
        
    -- End of loop
    until done end repeat;
    
    -- close the cursor
    close ordernumbers;
end //
delimiter ;

插一句題外話

注意在建立了這個儲存過程後，一定要call（呼叫）它，才會生成表

即要執行這個

call processorders();

迴歸正題

在這個例子中，增加了另一個名為t的變數（儲存每個訂單的合計）。此儲存過程還在執行中建立了一個新表（如果它不存在的話），名為ordertotals。這個表將儲存儲存過程生成的結果。FETCH像以前一樣取每個order_num，然後用CALL執行另一個儲存過程（我們在前一章中建立）來計算每個訂單的帶稅的合計（結果儲存到t）。最後，用INSERT儲存每個訂單的訂單號和合計。

此儲存過程不返回資料，但它能夠建立和填充另一個表，可以用一條簡單的SELECT語句檢視該表：

select * 
from ordertotals;

輸出如下：

img

插句題外話

對於這個輸出結果，如果好奇為什麼最後兩行是重複的，以及想改正這一點，可以看看這篇部落格 《MySQL必知必會》第24章 使用遊標 中的bug：最後一行被重複INSERT

迴歸正題

這樣，我們就得到了儲存過程、遊標、逐行處理以及儲存過程呼叫其他儲存過程的一個完整的工作樣例。

相關文章