MySQL 儲存過程/遊標/事務

將會用到的幾個表

mysql> DESC products;

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| prod_id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| vend_id    | int(11)      | YES  |     | NULL    |                |
| prod_name  | varchar(100) | YES  |     | NULL    |                |
| prod_price | int(11)      | YES  |     | NULL    |                |
| prod_desc  | varchar(300) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

 

mysql> DESC orders;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| order_num  | int(11)     | NO   | PRI | NULL    | auto_increment |
| order_date | date        | YES  |     | NULL    |                |
| cust_id    | varchar(20) | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

 

mysql> DESC orderitems;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| order_num  | int(11)     | NO   | PRI | NULL    | auto_increment |
| order_item | varchar(20) | YES  |     | NULL    |                |
| prod_id    | varchar(20) | YES  |     | NULL    |                |
| quantity   | int(11)     | YES  |     | NULL    |                |
| item_price | int(11)     | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

 

建立儲存過程：引數需要指定 OUT / IN / INOUT

 

CREATE PROCEDURE productpricing(

     OUT pl DECIMAL(8,2),

     OUT ph DECIMAL(8,2),

     OUT pa DECIMAL(8,2)

)

BEGIN

     SELECT Min( prod_price)

     INTO pl

     FROM products;

     SELECT Max( prod_price)

     INTO ph

     FROM products;

     SELECT Avg( prod_price)

     INTO pa

     FROM products;

END;

 

呼叫儲存過程：

CALL productpricing( @pricelow, @pricehigh, @priceaverage);

 

選擇返回的值：

SELECT @pricelow;

SELECT @pricelow,@pricehigh,@priceaverage  --選擇多個

 

刪除儲存過程：

DROP PROCEDURE productpricing;

 

-------------------------------------------------

 

CREATE PROCEDURE ordertotal(

     INT onumber INT,

     OUT ototal DECIMAL(8,2)

)

BEGIN

     SELECT sum(item_price * quantity)

     FROM orderitems

     WHERE order_num = onumber

     INTO ototal;

END;

 

呼叫：

CALL ordertotal(20005, @total);

 

SELECT @total;

 

儲存過程實際場景：需要獲得以前一樣的訂單合計，但需要對合計增加營業稅，不過只針對某些顧客，那麼需要做：

1. 獲得合計

2. 把營業稅有田間的新增到合計

3. 返回合計（帶或不帶稅）

 

CREATE PROCEDURE ordertotal（

IN onumber INT,

IN taxable BOOLEAN,

OUT octoal DECIMAL(8,2)

)

BEGIN

     -- 註釋 Declare variable for total

     DECLARE total DECIMAL(8,2);

     DECLARE taxrate INT DEFAULT 6;

 

     -- Get the order total

     SELECT Sum( item_price * quantity)

     FROM orderitems

     WHERE order_num = onumber

     INTO total;

 

     -- Is this taxable ?

     IF taxable THEN

          SELECT total + (tatal / 100 *taxrate) INTO total;

     END IF;

     

     SELECT total INTO ototal;

END;

     

CALL ordertotal(2005, 0, @total);

SELECT @total;

 

檢查儲存過程：

SHOW CREATE PROCEDURE ordertoal;

 

--------------------------------------------------

--------------------------------------------------

 

     SELECT 返回的是一個結果集，可能含有多行資料，有時候需要在檢索出來的行中前進或後退一行或多行。這就是使用遊標的原因。遊標（CURSOR） 是一個儲存在MySQL伺服器上的資料庫查詢，它不是一條SELECT語句，而是被語句檢索出來的結果集。在儲存了遊標之後應用程式可以根據需要滾動或瀏覽其中的資料。

     遊標主要用於互動式應用，其中使用者需要滾動螢幕上的資料，並對資料進行瀏覽或作出更改。

 

     MySQL遊標只能用於儲存過程。

使用遊標的步驟：

1. 定義遊標（針對某個SELECT語句）

2. 開啟遊標

3. 對填有資料的遊標，根據需要取出各行

4. 關閉遊標

 

簡單示例：

CREATE PROCEDURE processorders()

BEGIN

     DECLARE ordernumbers CURSOR

     FOR

     SELECT order_num FROM orders;

 

     OPEN ordernumbers;

 

     CLOSE ordernumbers;

END;

---------------- 使用遊標資料

 

CREATE PROCEDURE processorders()

BEGIN

 

     DECLARE o INT;

     DECLARE ordernumbers CURSOR

     FOR

     SELECT order_num FROM orders;

 

     OPEN ordernumbers;

 

     FETCH ordernumbers INTO o;

 

     CLOSE ordernumbers;

END;

----------------迴圈檢索資料

 

CREATE PROCEDURE processorders()

BEGIN

 

     DECLARE o INT;

     DECLARE done BOOLEAN DEFAULT 0;

 

     DECLARE ordernumbers CURSOR

     FOR

     SELECT order_num FROM orders;

 

     -- Declare continue handler

     DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

     -- SQLSTATE '02000' 是一個未找到條件，當沒有更多行可讀的時候設定 done = 1 然後退出

 

     OPEN ordernumbers;

     REPEAT

 

          FETCH ordernumbers INTO o;

 

     UNTIL done END REPEAT;

 

     CLOSE ordernumbers;

END;

-----------------------------------------------------

-----------------------------------------------------

------使用table 記錄CURSOR FETCH 出來的值

CREATE PROCEDURE processorders()

BEGIN

 

     DECLARE o INT;

     DECLARE done BOOLEAN DEFAULT 0;

     DECLARE t DECIMAL(8,2);

 

     DECLARE ordernumbers CURSOR

     FOR

     SELECT order_num FROM orders;

 

     -- Declare continue handler

     DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

     -- SQLSTATE '02000' 是一個未找到條件，當沒有更多行可讀的時候設定 done = 1 然後退出

 

     -- 建立table

     CREATE TABLE IF NOT EXISTS ordertotals(

          order_num INT, total DECIAML(8,2)

     );

 

     OPEN ordernumbers;

     REPEAT

 

          FETCH ordernumbers INTO o;

          CALL ordertotal(o,1,t); -- 呼叫過程

 

          -- 插入table

          INSERT INTO ordertotals(order_num, total)

          VALUES(o,t);

 

     UNTIL done END REPEAT;

 

     CLOSE ordernumbers;

END;

-----------------------------------------------------

-----------------------------------------------------

觸發器：在事件發生的時候自動執行

建立觸發器時，需要給出4條資訊：

1.唯一的觸發器名

2.觸發器關聯的表

3.觸發器應該響應的活動（DELETE/ INSERT / UPDATE）

4.觸發器何時執行

--------------------

CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added';

 

--該例子觸發器在每次插入之後顯示 Product added 訊息

 

---刪除觸發器

 

DROP TRIGGER newproduct;

 

--------------------------------------------------

--------------------------------------------------

事務處理（ transaction processing) 可以用來維護資料庫的完整性，它保證成批的MySQL操作要麼完全執行，要麼不執行。

幾個術語：

事務：transaction 指一組SQL語句

回退：rollback 指撤銷指定SQL語句過程

提交：commit 指將為儲存的SQL語句結果寫入資料庫表

保留點：savepoint 指事務處理中設定的臨時佔位符，你可以對它釋出退回

-------------

SELECT * FROM ordertotals;

START TRANSACTION;

DELETE FROM ordertotals; --刪除表

SELECT * FROM ordertotals; -- 確認刪除

ROLLBACK; -- 回滾

SELECT * FROM ordertotal; -- 再次顯示

 

--------------commit

一般的MySQL語句都是直接針對資料庫表進行操作，進行隱含的提交，即提交操作是自動執行的。

在 事務處理中，提交不會隱含執行，需要使用COMMIT語句。

START TRANSACTION;

DELETE FROM orderitems WHERE order_num = 20010;

DELETE FROM orders WHERE order_num = 20010;

COMMIT;