mysql 開發基礎系列18 存儲過程和函數(下)

1. 游標的使用（游標）

　　在存儲過程和函數中可以使用游標對結果集進行迴圈的處理，游標使用包括游標的聲明，open ,fetch,close。
　　下麵在存儲過程中使用一個游標， 這個舉例中游標里的邏輯不重要， 重點在於如何使用游標：

SELECT city_id FROM test.city

DELIMITER $$
CREATE PROCEDURE proc_demo_cursor()
BEGIN
    -- 定義變數，獲取游標中的數據
        DECLARE city_id_staff SMALLINT DEFAULT 0;
        
    -- 聲明游標
    DECLARE cur_city CURSOR FOR SELECT city_id FROM test.city;
        
    -- 定義條件  沒有找到數據時退出游標 NOT FOUND CLOSE cur_city
    DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_city;    
    
    SET  @result=0;
    
    -- 打開游標
    OPEN cur_city;
    REPEAT
    FETCH  cur_city INTO city_id_staff;
         IF city_id_staff < 10 THEN 
         SET @result=@result+1;
         ELSE 
         SET @result=@result+2;
         END IF;
    UNTIL 0 END REPEAT;
    
    -- 關閉游標
    CLOSE cur_city;
END $$
DELIMITER ;

-- 調用
CALL proc_demo_cursor;
SELECT @result;

2. 流程式控制制

　　包括 if ,case, loop, leave, iterate, repeat, while 語句進行流程式控制制

2.1  IF 語句

--  語法如下
IF search_condition THEN statement_list
[ELSE IF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF
-- 舉例
IF city_id_staff < 10 THEN 
    SET @result=@result+1;
ELSE 
    SET @result=@result+2;
END IF;

2.2 CASE 語句

--  舉例二種用法
CASE
WHEN i_staff_id = 2 THEN
    SET @x1 = @x1 + d_amount;
ELSE
    SET @x2 = @x2 + d_amount;
END CASE;
-- 或者：
CASE i_staff_id
WHEN 2 THEN
    SET @x1 = @x1 + d_amount;
ELSE
    SET @x2 = @x2 + d_amount;
END CASE;

2.3  loop 語句

 　　loop 實現簡單的迴圈，退出迴圈條件可以使用leave語句。

DELIMITER $$
CREATE PROCEDURE proc_demo_loop()
BEGIN
    DECLARE increase INT DEFAULT 1;
    SET @x=0;
    ins: LOOP
      SET increase=increase+1;  
      IF increase =100 THEN
        SET @x=increase;
        -- 退出
        LEAVE ins;
      END IF;
     
     END LOOP ins;    
END $$
DELIMITER ;

-- 調用
CALL proc_demo_loop;
SELECT @x;

2.4  ITERATE語句

　　ITERATE該語句必須用在迴圈中，作用相當於for 中的continue, 跳過當前迴圈剩下的語句，直接進入下一輪迴圈。

DELIMITER $$
CREATE PROCEDURE proc_demo_iterate()
BEGIN
    DECLARE increase INT DEFAULT 1;
    SET @x=0;
    ins: LOOP
      SET increase=increase+1;  
      IF increase =100 THEN
        -- 退出
        LEAVE ins;
      ELSEIF MOD(increase,2)=0 THEN
        -- 返回
        ITERATE ins;
      END IF;
      SET @x=@x+1;
     END LOOP ins;    
END $$
DELIMITER ;
-- 調用
CALL proc_demo_iterate;
SELECT @x;

2.5 repeat 語句

　　有條件的迴圈控制語句，當滿足條件的時候退出迴圈,在上面的游標也是一種迴圈,使用repeat來退出游標，使用close來關閉游標。

-- 語法
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]

--  舉例（UNTIL 0 END REPEAT）退出 當search_condition為0時，使用END REPEAT退出
    REPEAT
    FETCH  cur_city INTO city_id_staff;
         IF city_id_staff < 10 THEN 
         SET @result=@result+1;
         ELSE 
         SET @result=@result+2;
         END IF;
    UNTIL 0 END REPEAT;

2.6 while 語句

 　　也是有條件的迴圈控制語句，當滿足條件的時候退出迴圈。WHILE 迴圈和REPEAT 迴圈的區別在於：WHILE 是滿足條件才執行迴圈，REPEAT 是滿足條件退出迴圈；WHILE 在首次迴圈執行之前就判斷條件，所以迴圈最少執行0 次，而REPEAT 是在首次執行迴圈之後才判斷條件，所以迴圈最少執行1 次。 相當於開發語言的while(bool) 和do while(bool)。

DELIMITER $$
CREATE PROCEDURE proc_demo_while()
BEGIN
     DECLARE increase INT DEFAULT 1;
     SET @x=0;
     WHILE increase<=10 DO
     SET increase=increase+1;
     SET  @x=@x+increase;
     END WHILE;
END $$
DELIMITER ;

-- 調用
CALL proc_demo_while;
SELECT @x;