MySQL數(shù)據(jù)庫高級（九）——游標

MySQL數(shù)據(jù)庫高級（九）——游標

一、游標簡介

1、游標簡介

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

2、游標的特性

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

3、游標的優(yōu)點

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

4、游標的缺點

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

5、游標的適用場景

MySQL數(shù)據(jù)庫中，可以在存儲過程、函數(shù)、觸發(fā)器、事件中使用游標。

二、游標的操作

1、游標的定義

DECLARE?cursor_name?CURSOR?FOR?select_statement??

2、打開游標

OPEN?cursor_name;

3、取游標中的數(shù)據(jù)

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

4、關(guān)閉游標

CLOSE cursor_name;

5、釋放游標

DEALLOCATE cursor_name;

三、游標實例

1、創(chuàng)建一張游標的測試表

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);

使用三種方式使用游標創(chuàng)建一個存儲過程，統(tǒng)計年齡大于30的記錄的數(shù)量。

2、Loop循環(huán)

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

    #輸出結(jié)果  
    SELECT total;  
END

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

3、While循環(huán)

CREATE  PROCEDURE getTotal()
BEGIN  
    DECLARE total INT; 
    ##創(chuàng)建接收游標數(shù)據(jù)的變量  
    DECLARE sid INT;  
    DECLARE sname VARCHAR(10);  
    #創(chuàng)建總數(shù)變量  
    DECLARE sage INT;  
    #創(chuàng)建結(jié)束標志變量  
    DECLARE done INT DEFAULT false;  
    #創(chuàng)建游標  
    DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age>30;  
    #指定游標循環(huán)結(jié)束時的返回值  
    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循環(huán)

CREATE getTotal()
BEGIN  
    DECLARE total INT; 
    ##創(chuàng)建接收游標數(shù)據(jù)的變量  
    DECLARE sid INT;  
    DECLARE sname VARCHAR(10);  
    #創(chuàng)建總數(shù)變量  
    DECLARE sage INT;  
    #創(chuàng)建結(jié)束標志變量  
    DECLARE done INT DEFAULT false;  
    #創(chuàng)建游標  
    DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age > 30;  
    #指定游標循環(huán)結(jié)束時的返回值  
    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