Mysql變數、遊標及預存程序的應用

Mysql自5.0起就支援預存程序，預存程序通俗的講就是在一段封裝過的SQL，但不只SQL那麼簡單，通常還會有變數、條件判斷、循環體，遊標等。

預存程序的作用

在許多場景中，需要將多個表的資料處理，來產生新的我們需要的數據。這些多個表的資料並不能透過連接等查詢方式給出，只能透過判斷和循環才能產生。這個時候，就可以利用預存程序來實現。

此外，預存程序還有一些好處，例如效能比較高，還有能減少網路請求。如果不用預存程序來實現的話，使用php來實作就需要呼叫多次mysql,產生多次請求。

當然，預存程序也不是沒有缺點的，它比較哪調式，另外不支援叢集。

建立預存程序

建立預存程序語法如下：

CREATE PROCEDURE 过程名(参数) 
BEGIN
 过程体
END

關於參數，設定參數語法為

[IN|OUT|INOUT] 參數名稱類型

• IN 表示該變數只能在過程體內使用

• OUT 表示該變數只能在過程體外使用

• INOUT 表示在過程體內和體外都能使用

下面，我們來創建一個最簡單的儲存過程。

CREATE PROCEDURE p1(IN x INT) 
BEGIN
  SELECT x;
END;

變數

在MySQL中變數分為全域變數和局部變數。

全域變數以@開頭，無需聲明，直接使用即可,如

SET @name='gwx';

局部變數需要先聲明，局部變數的初始化方法如下：

DECLARE x int DEFAULT 0;

下面我們來完成一個預存程序：根據路程計算車費,3公里內以6遠計算，超過的距離以每公里1.2元計算.

-- distance 路程
CREATE PROCEDURE p1(in distance FLOAT)
BEGIN
  DECLARE d_money FLOAT DEFAULT 0; 
    IF distance>3 THEN 
       SET d_money=6+(distance-3)*1.2;
    ELSE
      SET d_money=6;
    END IF;
    SELECT d_money;
END;

遊標

#拿php做比較，遊標有點想foreach，每次迴圈取得一筆記錄。

定義一個遊標：

declare 游标名 CURSOR FOR SELECT 语句

開啟關閉遊標:

• #open 遊標名稱

• close 遊標名

取遊標資料:

FETCH 游标名 INTO 变量名

可以這麼簡單的介紹，大家會有疑惑，不清楚該如何去使用。下面，來看一個實例，從實例中學習如何使用遊標。

用遊標完成一個非常簡單的功能，將test_cursor表中數字全部累加起來。

CREATE TABLE IF NOT EXISTS test_cursor(
  num1 INT(10) UNSIGNED NOT NULL DEFAULT 0,
  num2 INT(10) UNSIGNED NOT NULL DEFAULT 0
);    
INSERT INTO test_cursor(num1,num2) VALUES(1,1),(2,2),(3,3);
 
CREATE PROCEDURE `test_cursor`()
BEGIN
    DECLARE sum INT(10) DEFAULT 0;
    DECLARE n1,n2 INT(10);
    DECLARE done INT DEFAULT 0;
    
    DECLARE cur CURSOR FOR SELECT num1,num2 FROM test_cursor; 
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; 
    
    OPEN cur; -- 打开游标
    WHILE done=0 DO      
        FETCH cur INTO n1,n2;
        
        IF done=0 THEN -- 注意这里为什么加IF条件，不加的话，最后一个值会被多加一遍         
      SET sum=sum+n1+n2;
        END IF;
    END WHILE;
    CLOSE cur;  -- 关闭游标
    
    SELECT sum;
END

這裡有幾點要注意，首先局部變數的定義必須要在宣告遊標前宣告。

另外，這裡DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; 表示遊標結束後將done設為1，以結束迴圈。

以上是Mysql變數、遊標及預存程序的應用的詳細內容。更多資訊請關注PHP中文網其他相關文章！