MySQL預存程序遊標使用執行個體詳解

例1，一個簡單預存程序遊標執行個體

代碼如下

複製代碼

DELIMITER $$ DROP PROCEDURE IF EXISTS getUserInfo $$ CREATE PROCEDURE getUserInfo(in date_day datetime) -- -- 執行個體 -- 預存程序名為：getUserInfo -- 參數為：date_day日期格式:2008-03-08 --     BEGIN declare _userName varchar(12); -- 使用者名稱 declare _chinese int ; -- 語文 declare _math int ;    -- 數學 declare done int; -- 定義遊標 DECLARE rs_cursor CURSOR FOR SELECT username,chinese,math from userInfo where datediff(createDate, date_day)=0; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; -- 擷取昨天的日期 if date_day is null then    set date_day = date_add(now(),interval -1 day); end if; open rs_cursor; cursor_loop:loop    FETCH rs_cursor into _userName, _chinese, _math; -- 取資料       if done=1 then     leave cursor_loop;    end if;    -- 更新表    update infoSum set total=_chinese+_math where UserName=_userName; end loop cursor_loop; close rs_cursor;     END$$ DELIMITER ;

例2，預存程序遊標迴圈跳出現

在MySQL的預存程序中,遊標操作時,需要執行一個conitnue的操作.眾所周知,MySQL中的遊標迴圈操作常用的有三種,LOOP,REPEAT,WHILE.三種迴圈,方式大同小異.以前從沒用過,所以記下來,方便以後查閱.

1.REPEAT

代碼如下	複製代碼
REPEAT     Statements;   UNTIL expression END REPEAT demo DECLARE num INT; DECLARE my_string  VARCHAR(255); REPEAT SET  my_string =CONCAT(my_string,num,','); SET  num = num +1;   UNTIL num <5 END REPEAT;

2.WHILE 

代碼如下	複製代碼
WHILE expression DO     Statements; END WHILE demo DECLARE num INT; DECLARE my_string  VARCHAR(255); SET num =1; SET str ='';   WHILE num  < span>10DO SET  my_string =CONCAT(my_string,num,','); SET  num = num +1; END WHILE;

3.LOOP(這裡面有非常重要的ITERATE,LEAVE)

代碼如下	複製代碼
DECLARE num  INT; DECLARE str  VARCHAR(255); SET num =1; SET my_string ='';   loop_label:  LOOP IF  num <10THEN       LEAVE  loop_label; ENDIF; SET  num = num +1; IF(num mod3)THEN       ITERATE  loop_label; ELSE SET  my_string =CONCAT(my_string,num,','); ENDIF; END LOOP;

PS:可以這樣理解ITERATE就是我們程式中常用的contiune,而ITERATE就是break.當然在MySQL預存程序,需要迴圈結構有個名稱,其他都是一樣的.

例3,mysql 預存程序中使用多遊標

先建立一張表，插入一些測試資料：

代碼如下

複製代碼

DROP TABLE IF EXISTS netingcn_proc_test; CREATE TABLE `netingcn_proc_test` (   `id` INTEGER(11) NOT NULL AUTO_INCREMENT,   `name` VARCHAR(20),   `password` VARCHAR(20),   PRIMARY KEY (`id`) )ENGINE=InnoDB; insert into netingcn_proc_test(name, password) values ('procedure1', 'pass1'), ('procedure2', 'pass2'), ('procedure3', 'pass3'), ('procedure4', 'pass4');下面就是一個簡單預存程序的例子： drop procedure IF EXISTS test_proc; delimiter // create procedure test_proc() begin  -- 聲明一個標誌done， 用來判斷遊標是否遍曆完成  DECLARE done INT DEFAULT 0;  -- 聲明一個變數，用來存放從遊標中提取的資料  -- 特別注意這裡的名字不能與由遊標中使用的列明相同，否則得到的資料都是NULL  DECLARE tname varchar(50) DEFAULT NULL;  DECLARE tpass varchar(50) DEFAULT NULL;  -- 聲明遊標對應的 SQL 陳述式  DECLARE cur CURSOR FOR   select name, password from netingcn_proc_test;  -- 在遊標迴圈到最後會將 done 設定為 1  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  -- 執行查詢  open cur;  -- 遍曆遊標每一行  REPEAT   -- 把一行的資訊存放在對應的變數中   FETCH cur INTO tname, tpass;   if not done then    -- 這裡就可以使用 tname， tpass 對應的資訊了    select tname, tpass;   end if;   UNTIL done END REPEAT;  CLOSE cur; end // delimiter ; -- 執行預存程序 call test_proc();

需要注意的是變數的聲明、遊標的聲明和HANDLER聲明的順序不能搞錯，必須是先聲明變數，再申明遊標，最後聲明HANDLER。上述預存程序的例子中只使用了一個遊標，那麼如果要使用兩個或者更多遊標怎麼辦，其實很簡單，可以這麼說，一個怎麼用兩個就是怎麼用的。例子如下：

代碼如下

複製代碼

drop procedure IF EXISTS test_proc_1; delimiter // create procedure test_proc_1() begin  DECLARE done INT DEFAULT 0;  DECLARE tid int(11) DEFAULT 0;  DECLARE tname varchar(50) DEFAULT NULL;  DECLARE tpass varchar(50) DEFAULT NULL;  DECLARE cur_1 CURSOR FOR   select name, password from netingcn_proc_test;  DECLARE cur_2 CURSOR FOR   select id, name from netingcn_proc_test;  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  open cur_1;  REPEAT   FETCH cur_1 INTO tname, tpass;   if not done then    select tname, tpass;   end if;   UNTIL done END REPEAT;  CLOSE cur_1;  -- 注意這裡，一定要重設done的值為 0  set done = 0;  open cur_2;  REPEAT   FETCH cur_2 INTO tid, tname;   if not done then    select tid, tname;   end if;   UNTIL done END REPEAT;  CLOSE cur_2; end // delimiter ; call test_proc_1();

上述代碼和第一個例子中基本一樣，就是多了一個遊標聲明和遍曆遊標。這裡需要注意的是，在遍曆第二個遊標前使用了set done = 0，因為當第一個遊標遍曆玩後其值被handler設定為1了，如果不用set把它設定為 0 ，那麼第二個遊標就不會遍曆了。當然好習慣是在每個開啟遊標的操作前都用該語句，確保遊標能真正遍曆。當然還可以使用begin語句塊嵌套的方式來處理多個遊標,例如：

代碼如下

複製代碼

drop procedure IF EXISTS test_proc_2; delimiter // create procedure test_proc_2() begin  DECLARE done INT DEFAULT 0;  DECLARE tname varchar(50) DEFAULT NULL;  DECLARE tpass varchar(50) DEFAULT NULL;  DECLARE cur_1 CURSOR FOR   select name, password from netingcn_proc_test;  DECLARE cur_2 CURSOR FOR   select id, name from netingcn_proc_test;  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  open cur_1;  REPEAT   FETCH cur_1 INTO tname, tpass;   if not done then    select tname, tpass;   end if;   UNTIL done END REPEAT;  CLOSE cur_1;  begin   DECLARE done INT DEFAULT 0;   DECLARE tid int(11) DEFAULT 0;   DECLARE tname varchar(50) DEFAULT NULL;   DECLARE cur_2 CURSOR FOR    select id, name from netingcn_proc_test;   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;   open cur_2;   REPEAT    FETCH cur_2 INTO tid, tname;    if not done then     select tid, tname;    end if;    UNTIL done END REPEAT;   CLOSE cur_2;  end; end // delimiter ; call test_proc_2();