MySQL數據庫高級（九）——游標

MySQL數據庫高級（九）——游標

一、游標簡介

1、游標簡介

游標的設計是一種數據緩沖區的思想，用來存放SQL語句執行的結果。游標是一種能從包括多條數據記錄的結果集中每次提取一條記錄的機制。
盡管游標能遍歷結果中的所有行，但一次只指向一行。
游標的作用就是用于對查詢數據庫所返回的記錄進行遍歷，以便進行相應的操作。

2、游標的特性

游標具有三個屬性：
A、不敏感（Asensitive）：數據庫可以選擇不復制結果集
B、只讀（Read only）
C、不滾動（Nonscrollable）：游標只能向一個方向前進，并且不可以跳過任何一行數據。

3、游標的優點

游標是針對行操作的，對從數據庫中SELECT查詢得到的結果集的每一行可以進行分開的獨立的相同或不同的操作，是一種分離的思想。游標是面向集合與面向行的設計思想之間的一種橋梁。

4、游標的缺點

游標的主要缺點是性能不高。
游標的開銷與游標中進行的操作相關，如果在游標中進行復雜的操作，開銷會非常高。如果采用面向集合的SQL語句，掃描成本為O(N)；但如果采用面向集合的SQL語句的掃描成本為O(N*N)，則使用游標有可能會帶來性能上的提升。
游標的缺點是只能一行一行操作。在數據量大的情況下，速度過慢。數據庫大部分是面對集合的，業務會比較復雜，而游標使用會有死鎖，影響其他的業務操作，不可取。 當數據量大時，使用游標會造成內存不足現象。

5、游標的適用場景

MySQL數據庫中，可以在存儲過程、函數、觸發器、事件中使用游標。

二、游標的操作

1、游標的定義

DECLARE?cursor_name?CURSOR?FOR?select_statement??

2、打開游標

OPEN?cursor_name;

3、取游標中的數據

FETCH?cursor_name?INTO?var_name?[,?var_name]...

4、關閉游標

CLOSE cursor_name;

5、釋放游標

DEALLOCATE cursor_name;

三、游標實例

1、創建一張游標的測試表

CREATE TABLE cursor_table
(
id INT ,
name VARCHAR(10),
age INT
)ENGINE=innoDB DEFAULT CHARSET=utf8;
insert into cursor_table values(1, '孫悟空', 500);
insert into cursor_table values(2, '豬八戒', 200);
insert into cursor_table values(3, '沙悟凈', 100);
insert into cursor_table values(4, '唐僧', 20);

使用三種方式使用游標創建一個存儲過程，統計年齡大于30的記錄的數量。

2、Loop循環

CREATE  PROCEDURE getTotal()
BEGIN  
    DECLARE total INT; 
    ##創建接收游標數據的變量  
    DECLARE sid INT;  
    DECLARE sname VARCHAR(10);  
    #創建總數變量  
    DECLARE sage INT;  
    #創建結束標志變量  
    DECLARE done INT DEFAULT false;  
    #創建游標  
    DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age>30;  
    #指定游標循環結束時的返回值  
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;  
    #設置初始值  
    SET sage = 0;  
    SET total=0;
    #打開游標  
    OPEN cur;  
    #開始循環游標里的數據  
    read_loop:loop  
    #根據游標當前指向的一條數據  
    FETCH cur INTO sid,sname,sage;  
    #判斷游標的循環是否結束  
    IF done THEN  
        LEAVE read_loop;    #跳出游標循環  
    END IF;  
    #獲取一條數據時，將count值進行累加操作，這里可以做任意你想做的操作，  
    SET total = total + 1;  
    #結束游標循環  
    END LOOP;  
    #關閉游標  
    CLOSE cur;  

    #輸出結果  
    SELECT total;  
END

#調用存儲過程  
call getTotal();  

3、While循環

CREATE  PROCEDURE getTotal()
BEGIN  
    DECLARE total INT; 
    ##創建接收游標數據的變量  
    DECLARE sid INT;  
    DECLARE sname VARCHAR(10);  
    #創建總數變量  
    DECLARE sage INT;  
    #創建結束標志變量  
    DECLARE done INT DEFAULT false;  
    #創建游標  
    DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age>30;  
    #指定游標循環結束時的返回值  
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;   
    SET total = 0;  
    OPEN cur;  
    FETCH cur INTO sid, sname, sage;  
    WHILE(NOT done) 
    DO  
        SET total = total + 1;  
        FETCH cur INTO sid, sname, sage;  
    END WHILE;  

    CLOSE cur;  
    SELECT total;  
END

4、Repeat循環

CREATE getTotal()
BEGIN  
    DECLARE total INT; 
    ##創建接收游標數據的變量  
    DECLARE sid INT;  
    DECLARE sname VARCHAR(10);  
    #創建總數變量  
    DECLARE sage INT;  
    #創建結束標志變量  
    DECLARE done INT DEFAULT false;  
    #創建游標  
    DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age > 30;  
    #指定游標循環結束時的返回值  
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;   
    SET total = 0;  
    OPEN cur;  
    REPEAT  
    FETCH cur INTO sid, sname, sage;   
    IF NOT done THEN  
        SET total = total + 1;  
    END IF;  
    UNTIL done END REPEAT;  
    CLOSE cur;  
    SELECT total;  
END