Mysql存储过程和游标

创建MYSQL存储过程

CREATE PROCEDURE productpricing()
BEGIN
 SELECT Avg(prod_price) AS priceaverage
 FROM products;
END;

此存储过程名为productpricing ，用CREATE PROCEDURE productpricing() 语句定义。如果存储过程接受参数，它们将在() 中列举出来。此存储过程没有参数，但后跟的() 仍然需要。BEGIN 和END 语句用来限定存储过程体，过程体本身仅是一个简单的SELECT 语句

执行存储过程

CALL productpricing();

删除存储过程

DROP PROCEDURE productpricing;
DROP PROCEDURE IF EXISTS productpricing

创建带变量的存储过

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;

此存储过程接受3个参数：pl 存储产品最低价格，ph 存储产品最高价格，pa 存储产品平均价格。每个参数必须具有指定的类型，这里使用十进制值。关键字OUT 指出相应的参数用来从存储过程传出一个值（返回给调用者）。MySQL支持IN （传递给存储过程）、OUT （从存储过程传出，如这里所用）和INOUT （对存储过程传入和传出）类型的参数。存储过程的代码位于BEGIN 和END 语句内，如前所见，它们是一系列SELECT 语句，用来检索值，然后保存到相应的变量（通过指定INTO 关键字）

执行带参数的存储过程

CALL productpricing(@pricelow, @pricehigh, @priceaverage);
由于此存储过程要求3个参数，因此必须正好传递3个参数，不多也不少。所以，这条CALL 语句给出3个参数。它们是存储过程将保存结果的3个变量的名字。
变量名 所有MySQL变量都必须以@ 开始。
直接查询变量 即显示变量的返回值
SELECT @priceaverage;
SELECT @pricehigh, @pricelow, @priceaverage;

创建带输入参数和返回参数的存储过程

CREATE PROCEDURE ordertotal(
 IN onumber INT,
 OUT ototal DECIMAL(8,2)
)
BEGIN
 SELECT Sum(item_price*quantity)
 FROM orderitems
 WHERE order_num = onumber
 INTO ototal;
END;

onumber 定义为IN ，因为订单号被传入存储过程。ototal 定义为OUT ，因为要从存储过程返回合计。SELECT 语句使用这两个参数，WHERE 子句使用onumber 选择正确的行，INTO 使用ototal 存储计算出来的合计。
调用
CALL ordertotal(20005, @total);

创建复杂的存储过程

-- Name: ordertotal
-- Parameters: onumber = order number
-- taxable = 0 if not taxable, 1 if taxable
-- ototal = order total variable
CREATE PROCEDURE ordertotal(
 IN onumber INT,
 IN taxable BOOLEAN,
 OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
 -- Declare variable for total
 DECLARE total DECIMAL(8,2);
 -- Declare tax percentage
 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
 -- Yes, so add taxrate to the total
 SELECT total+(total/100*taxrate) INTO total;
 END IF;
 -- And finally, save to out variable
 SELECT total INTO ototal;
END;

taxable ，它是一个布尔值（如果要增加税则为真，否则为假）。在存储过程体中，用DECLARE 语句定义了两个局部变量。DECLARE 要求指定变量名和数据类型，它也支持可选的默认值（这个例子中的taxrate 的默认被设置为6% ）。SELECT 语句已经改变，因此其结果存储到total （局部变量）而不是ototal 。IF 语句检查taxable是否为真，如果为真，则用另一SELECT 语句增加营业税到局部变量total 。最后，用另一SELECT 语句将total （它增加或许不增加营业税）保存到ototal 。
执行
CALL ordertotal(20005, 0, @total);
SELECT @total;
IF 语句 这个例子给出了MySQL的IF 语句的基本用法。IF 语句还支持ELSEIF 和ELSE 子句（前者还使用THEN 子句，后者不使用）。
小知识
SHOW PROCEDURE STATUS
可以获得包括何时、由谁创建等详细信息的存储过程列表

游标

前言
游标只能用于存储过程
使用游标涉及几个明确的步骤

• 在能够使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的SELECT 语句
• 一旦声明后，必须打开游标以供使用。这个过程用前面定义的SELECT 语句把数据实际检索出来
• 对于填有数据的游标，根据需要取出（检索）各行
• 在结束游标使用时，必须关闭游标
  创建游标

CREATE PROCEDURE processorders()
BEGIN
 -- Declare the cursor
 DECLARE ordernumbers CURSOR
 FOR
 SELECT order_num FROM orders;
 -- Open the cursor
 OPEN ordernumbers;
 -- Close the cursor
 CLOSE ordernumbers;
END;

使用游标

CREATE PROCEDURE processorders()
BEGIN
 -- Declare local variables
 DECLARE o INT;
 -- Declare the cursor
 DECLARE ordernumbers CURSOR
 FOR
 SELECT order_num FROM orders;
 -- Open the cursor
 OPEN ordernumbers;
 -- Get order number
 FETCH ordernumbers INTO o;
 -- Close the cursor
 CLOSE ordernumbers;
END;

触发器

规则

• 唯一的触发器名
• 触发器关联的表；
• 触发器应该响应的活动（DELETE 、INSERT 或UPDATE ）；
• 触发器何时执行（处理之前或之后）
  创建触发器

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

CREATE TRIGGER 用来创建名为newproduct 的新触发器。触发器可在一个操作发生之前或之后执行，这里给出了AFTER INSERT ，所以此触发器将在INSERT 语句成功执行后执行。这个触发器还指定FOREACH ROW ，因此代码对每个插入行执行。在这个例子中，文本Product added 将对每个插入的行显示一次。
使用INSERT 语句添加一行或多行到products中，你将看到对每个成功的插入，显示Product added 消息
仅支持表 只有表才支持触发器，视图不支持（临时表也不支持）。

每个表最多支持6个触发器（每条INSERT 、UPDATE 和DELETE 的之前和之后）

器失败果BEFORE 触发器失败，则MySQL将不执行请求的操作。此外，如果BEFORE 触发器或语句本身失败，MySQL将不执行AFTER 触发器（如果有的话）。
删除触发器
DROP TRIGGER newproduct;
触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，然后再重新创建
使用触发器
INSERT 触发器在INSERT 语句执行之前或之后执行

• 在INSERT 触发器代码内，可引用一个名为NEW 的虚拟表，访问被插入的行
• 在BEFORE INSERT 触发器中，NEW 中的值也可以被更新（允许更改被插入的值）
• 对于AUTO_INCREMENT(自动生成列)NEW 在INSERT 执行之前包含0 ，在INSERT 执行之后包含新的自动生成值。
  举个栗子
  CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num;
  此代码创建一个名为neworder 的触发器，它按照AFTER INSERT ON orders 执行。在插入一个新订单到orders 表时，MySQL生成一个新订单号并保存到order_num 中。触发器从NEW.order_num 取得这个值并返回它。此触发器必须按照AFTER INSERT 执行，因为在BEFORE INSERT 语句执行之前，新order_num 还没有生成。对于orders 的每次插入使用这个触发器将总是返回新的订单号
  则往 orders 表中插入一行的时候，将会返回插入这一行的行号

--输入
INSERT INTO orders(order_date, cust_id)
VALUES(Now(), 10001);
--输出
+-----------+
| order_num |
+-----------+
| 20010 |
+-----------+

DELETE 触发器

• 在DELETE 触发器代码内，你可以引用一个名为OLD 的虚拟表，访问被删除的行；
• OLD 中的值全都是只读的，不能更新。
  演示使用OLD 保存将要被删除的行到一个存档表中：

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
 INSERT INTO archive_orders(order_num, order_date, cust_id)
 VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END;

在任意订单被删除前将执行此触发器。它使用一条INSERT 语句将OLD中的值（要被删除的订单）保存到一个名为archive_orders 的存档表中（为实际使用这个例子，你需要用与orders 相同的列创建一个名为archive_orders 的表）。
使用BEFORE DELETE 触发器的优点（相对于AFTER DELETE 触发器来说）为，如果由于某种原因，订单不能存档，DELETE 本身将被放弃。
UPDATE 触发器

• 在UPDATE 触发器代码中，你可以引用一个名为OLD 的虚拟表访问以前（UPDATE 语句前）的值，引用一个名为NEW 的虚拟表访问新更新的值
• 在BEFORE UPDATE 触发器中，NEW 中的值可能也被更新（允许更改将要用于UPDATE 语句中的值）
• OLD 中的值全都是只读的，不能更新
  下面的例子保证州名缩写总是大写（不管UPDATE 语句中给出的是大
  写还是小写）

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);

显然，任何数据净化都需要在UPDATE 语句之前进行，就像这个例子中一样。每次更新一个行时，NEW.vend_state 中的值（将用来更新表行的值）都用Upper(NEW.vend_state) 替换。