mysql 存储过程和触发器

mysql 存储过程和触发器

PROCEDURE

存储过程

调用存储过程：
调用时，所有MySQL变量都必须以@开始

CALL procedure_name([@param1, @param2, ...]) # 参数可选
# eg.
CALL ordertotal(20009, @total);

创建存储过程：

CREATE PROCEDURE procedure_name([@param1, @param2, ...]) # 参数可选
BEGIN
	code block
END;

使用mysql命令行实用程序时，需要注意存储过程中使用的分隔符，如果都用;，会报错。因此需要重定义一个分隔符。

DELIMITER //

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

删除存储过程：

DROP PROCEDURE [IF EXISTS] procedure_name;

存储过程 变量：

IN关键字：把参数传递给存储过程
OUT关键字：返回结果，可以指定多个返回。
INOUT关键字：既传入也传出
INTO关键字：将值保存到相应的变量
参数数据类型：和表中使用的数据类型一致。参数不能是记录集，只能是一个独立的值。

DEMO:

CREATE PROCEDURE productpricing(
	OUT p1 DECIMAL(8,2),
    IN p2 CHAR(3),
    INTO P3 DECIMAL(8,2)
)
BEGIN
	SELECT Min(prod_price)
	INTO p1
	FROM products;
	...
END;

检索变量值：

SELECT @var[, @var1, ...];

一个完整的例子：

-- 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 taxab1e THEN
        -- Yes, so add taxrate to the total
        SELECT total+(tota1/100*taxrate) INTO total;
    END IF;
    
    -- And finally，save to out variable
    SELECT total INTO ototal;
END;

--：表示注释
COMMENT关键字：它不是必需的，但如果给出，将在SHOW PROCEDURE STATUS的结果中显示。
DECLARE关键字：定义局部变量。
IF var THEN ... END IF;：表条件判断。

IF ... THEN
ELSEIF
ELSE
END IF;

检查存储过程：

SHOW CREATE PROCEDURE procedure_name; # 查看创建存储过程的sql语句
SHOW PROCEDURE STATUS; # 列出所有存储过程的状态，一个包括何时、由谁创建等详细信息的存储过程列表
SHOW PROCEDURE STATUS LIKE 'procedure_name'; # 过滤，只看某一个存储过程的状态

cursor

游标

mysql中，游标只用于存储过程。

步骤：
定义游标；DECALRE
打开游标；OPEN
使用游标；FETCH
关闭游标。CLOSE

FETCH关键字：索引一行，并索引移动到下一行。

DECLARE命名游标，并定义相应的SELECT语句，根据需要带WHERE和其他子句。

CREATE PROCEDURE procedure_name()
BEGIN
	# 定义
	# 存储过程处理完成后，游标就消失（因为它局限于存储过程）。
	DECLARE cursor_name CURSOR
	FOR
	SELECT ...;
	
	# 打开
    OPEN cursor_name;
    
    # 使用
    FETCH cursor_name INTO var;

    # 关闭(如果你不明确关闭游标，MySQL将会在到达END语句时自动关闭它)
    CLOSE cursor_name;
END;

...
	-- 定义变量done，默认为false
	DECLARE done BOOLEAN DEFAULT 0;
	-- 定义 CONTINUE HANDLER，
	-- 当SQLSTATE '02000'出现时，SET done=1。
	-- SQLSTATE '02000'是一个未找到条件，当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件。
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'SET done=1;
 	# 或者循环使用
 	REPEAT
 		FETCH ...;
 	UNTIL done END REPEAT;
...

TRIGGER

触发器

相应INSERT,UPDATE,DELETE语句而自动执行的一条SQL语句（或位于BEGIN和END语句之间的一组语句）。

MySQL触发器中不支持CALL语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。

创建触发器：
 唯一的触发器名；（CREATE TRIGGER trigger_name）
 触发器关联的表；(ON table_name)
 触发器应该响应的活动（DELETE、INSERT或UPDATE）；
 触发器何时执行（处理之前或之后）。(AFTER或BEFORE)

触发器名必须在每个表中唯一，但不是在每个数据库中唯一。(名以表为范围，唯一。最好在数据库范围内唯一命名。)
只有表才支持触发器，视图不支持（临时表也不支持）。

每个表最多支持6个触发器（每条INSERT、UPDATE和DELETE的之前和之后）。
单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。

CREATE TRIGGER trigger_name AFTER[|BEFORE] INSERT[|UPDATE|DELETE] ON table_name
FOR EACH ROW 
SELECT 'XXX';
# SELECT 'XXX' 用于触发器成功执行后的显示（'XXX'）

FOREACH ROW，代码对每个插入行执行。

---

删除触发器：

DROP TRIGGER trigger_name;

触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，然后再重新创建。

---

INSERT触发器

 在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；
 在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）；
 对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。

虚拟表NEW：

CREATE TRIGGER neworder AFTER INSERT ON orders 
FOR EACH ROw 
SELECT NEW.order_num;

触发器从NEW.order_num取得order_num的值并返回它。使用AFTER是因为插入后才会有新的订单号。

DELETE触发器

 在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行；
 OLD中的值全都是只读的，不能更新。

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INT0 archive_orders (order_num，order_date，cust_id)
    VALUES(OLD.order_num，OLD.order_date，OLD.cust_id) ;
END;
# 通过OLD这个虚拟表来获取删除的数据

UPDATE触发器

 在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问以前（UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新更新的值；
 在BEFORE UPDATE触发器中，NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）；
 OLD中的值全都是只读的，不能更新。

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