標籤：
來源：http://www.cnblogs.com/zhuyp1015/p/3575823.html 將會用到的幾個表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;
MySQL 預存程序/遊標/觸發器/事務
