MySQL 触发器、存储过程、游标
来源:互联网 发布:虚拟机装mac os 编辑:程序博客网 时间:2024/05/17 03:15
MySQL 触发器、函数
触发器有时也称作事件-条件-动作规则,或者ECA规则。
触发器语法
- DROP 如果触发器已存在,则删除
- delimiter 设置数据库语句的定界符(分隔符)
- CREATE TRIGGER创建触发器
- 指出触发事件并告诉触发器实在触发事件之前还是之后使用数据库状态的子句
- FOR EACH ROW 告诉触发器只对每个修改的行执行依次,还是对由SQL语句做的所有修改执行一次的子句
- BEGIN 触发器执行部分,OLD表示原来的数据,NEW表示新插入的数据,因此对于SELECT语句可以使用OLD值,对于DELECT使用OLD值,INSERT使用NEW值,UPDATE 使用NEW值和OLD值
DROP TRIGGER IF EXISTS tr_after_delete_distribution_goods;delimiter //CREATE TRIGGER tr_after_delete_distribution_goodsAFTER DELETE ON t_distribution_goods FOR EACH ROWtrig:BEGIN DECLARE var_warehouseCode SMALLINT;-- 定义变量 SELECT f_warehouseCode INTO var_warehouseCode FROM t_distribution WHERE f_distNumber=OLD.f_distNumber; UPDATE t_stock_total SET f_lockedQuantity=f_lockedQuantity-OLD.f_distQuantity WHERE f_goodsId=OLD.f_goodsId AND f_warehouseCode=var_warehouseCode;END;//delimiter ;
设置触发器条件,当f_flag为“已作废”时执行更新语句
DROP TRIGGER IF EXISTS tr_after_update_distribution;delimiter //CREATE TRIGGER tr_after_update_distributionAFTER UPDATE ON t_distribution FOR EACH ROWtrig:BEGIN DECLARE var_goodsId INT; DECLARE var_distQuantity INT; if (NEW.f_flag='INVALID') then SELECT f_goodsId,f_distQuantity INTO var_goodsId,var_distQuantity FROM t_distribution_goods WHERE f_distNumber=OLD.f_distNumber; UPDATE t_stock_total SET f_lockedQuantity=f_lockedQuantity-var_distQuantity WHERE f_goodsId=var_goodsId AND f_warehouseCode=OLD.f_warehouseCode; end if;END;//delimiter ;
插入 触发器
DROP TRIGGER IF EXISTS tr_after_insert_commodity_stock;delimiter //CREATE TRIGGER tr_after_insert_commodity_stockAFTER INSERT ON t_commodity FOR EACH ROWtrig:BEGIN DECLARE var_warehouseCode INT; SELECT f_warehouseCode INTO var_warehouseCode FROM t_stock WHERE f_purchaseNumber=NEW.f_purchaseNumber AND f_goodsId=NEW.f_goodsId; UPDATE t_stock SET f_inQuantity=f_inQuantity+1 WHERE f_purchaseNumber=NEW.f_purchaseNumber AND f_goodsId=NEW.f_goodsId; UPDATE t_stock_total SET f_quantity=f_quantity+1 WHERE f_goodsId=NEW.f_goodsId AND f_warehouseCode=var_warehouseCode;END;//delimiter ;
查看触发器
SELECT TRIGGER_NAME FROM information_schema.`TRIGGERS`;
触发器详细代码
SHOW CREATE TRIGGER tr_after_update_stock;
推荐参考链接:http://www.cnblogs.com/fengxw/p/6076150.html
MySQL 存储过程
-- 生成一个RFID-- 参数var_goodsId,var_quantityDROP PROCEDURE IF EXISTS p_next_sheet_rfid;delimiter //CREATE PROCEDURE p_next_sheet_rfid ( OUT var_return VARCHAR(30),-- 输出值 IN var_goodsId INT,-- 输入值 IN var_quantity INT )BEGIN DECLARE var_category SMALLINT;-- 临时变量 DECLARE var_brand INT; DECLARE var_goods INT; DECLARE var_maxRfid INT; DECLARE var_str VARCHAR(20) DEFAULT ''; -- 异常时回滚跳出 -- DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; SET var_return='0'; START TRANSACTION; -- 临时变量赋值 SELECT f_id,f_categoryCode,f_brandCode,f_lastRfidNumber INTO var_goods, var_category, var_brand, var_maxRfid FROM t_goods WHERE f_id=var_goodsId; SET var_maxRfid = var_maxRfid % 100000000; UPDATE t_goods SET f_lastRfidNumber=var_maxRfid+var_quantity WHERE f_id=var_goodsId; -- concat连接各参数,LPAD(str,len,padstr)用字符串 padstr对 str进行左边填补直至它的长度达到 len个字符长度,然后返回 str。如果 str的长度长于 len',那么它将被截除到 len个字符。RPAD(str,len,padstr)为右边 SELECT CONCAT(LPAD(var_category, 3, '0'), LPAD(var_brand, 4, '0'), LPAD(var_goods, 5, '0'), LPAD(var_maxRfid+1, 8, '0')) INTO var_str; SET var_return = RIGHT(var_str, 20); COMMIT;END;//delimetr;
存储过程,使用游标循环遍历数据表修改另一张表
-- 逐条从t_goods_temp导入货品到t_goods.DROP procedure IF EXISTS p_import_goods_from_temp;delimiter //CREATE PROCEDURE p_import_goods_from_temp ()BEGIN DECLARE var_categoryCode SMALLINT; DECLARE var_name VARCHAR(120); DECLARE var_brandCode SMALLINT; -- DECLARE var_brandName VARCHAR(18); DECLARE var_superCategoryName VARCHAR(18); DECLARE var_categoryName VARCHAR(18); DECLARE var_saleSpec VARCHAR(18); DECLARE var_unitCode SMALLINT; -- DECLARE var_unitName VARCHAR(12); DECLARE var_marketPrice DECIMAL(6, 2); DECLARE var_done INT DEFAULT 0; DECLARE var_warning INT DEFAULT 0; DECLARE cur_goods_temp CURSOR FOR -- 定义游标,设置结果集 SELECT f_name, f_brandCode, f_superCategoryName, f_categoryName, f_saleSpec, f_unitCode, f_marketPrice FROM v_goods_temp; -- 声明异常处理 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET var_done = 1; -- 若没有数据返回,程序继续,并将变量var_done设为1 类似于DECLARE CONTINUE HANDLER FOR NOT FOUNDSET var_done = 1; DECLARE CONTINUE HANDLER FOR SQLWARNING SET var_warning = 1; DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; START TRANSACTION;-- 开启事务 SET var_done = 0; OPEN cur_goods_temp; -- 打开游标 REPEAT-- 开始循环 FETCH cur_goods_temp INTO var_name, var_brandCode, var_superCategoryName, var_categoryName, var_saleSpec, var_unitCode, var_marketPrice; -- SELECT f_categoryCode, var_brandCode, var_unitCode, var_name, var_saleSpec, var_marketPrice, now(), var_done -- FROM v_category WHERE f_superCategoryName=var_superCategoryName AND f_categoryName=var_categoryName LIMIT 1; -- SELECT 'cur_goods_return_out var_done: ', var_done; IF NOT var_done THEN -- var_done=0,异常标识 INSERT INTO t_goods (f_categoryCode, f_brandCode, f_unitCode, f_name, f_saleSpec, f_marketPrice, f_creationTime) SELECT f_categoryCode, var_brandCode, var_unitCode, var_name, var_saleSpec, var_marketPrice, now() FROM v_category WHERE f_superCategoryName=var_superCategoryName AND f_categoryName=var_categoryName LIMIT 1; IF var_warning THEN SHOW warnings; CALL p_debug(CONCAT('warning for ', var_categoryName), false); END IF; END IF; UNTIL var_done END REPEAT; -- 出错时退出循环 CLOSE cur_goods_temp; -- 关闭游标 COMMIT; -- 提交数据END;//delimiter ;
添加判断条件的,在游标定义语句之前不能使用SELECT定义变量,if条件判断使用=,而不是==
DROP procedure IF EXISTS p_update_distribution_stock;delimiter //CREATE PROCEDURE p_update_distribution_stock( IN var_distNumber INT, IN var_flag VARCHAR(10))trig:BEGIN DECLARE var_goodsId INT; DECLARE var_distQuantity INT; DECLARE var_done INT DEFAULT 0; DECLARE var_warning INT DEFAULT 0; DECLARE var_warehouseCode INT; DECLARE cur_disribution CURSOR FOR SELECT f_goodsId,f_distQuantity FROM t_distribution_goods WHERE f_distNumber=var_distNumber; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET var_done = 1; DECLARE CONTINUE HANDLER FOR SQLWARNING SET var_warning = 1; DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; START TRANSACTION; SET var_done = 0; OPEN cur_disribution; REPEAT FETCH cur_disribution INTO var_goodsId,var_distQuantity; IF NOT var_done THEN IF (var_flag = 'INVALID') THEN SELECT f_warehouseCode INTO var_warehouseCode FROM t_distribution WHERE f_distNumber=var_distNumber; UPDATE t_stock_total SET f_lockedQuantity=f_lockedQuantity-var_distQuantity WHERE f_goodsId=var_goodsId AND f_warehouseCode=var_warehouseCode; ELSEIF (var_flag = 'CLEAR') THEN SELECT f_warehouseCode INTO var_warehouseCode FROM t_distribution WHERE f_distNumber=var_distNumber; UPDATE t_stock_total SET f_lockedQuantity=f_lockedQuantity+var_distQuantity WHERE f_goodsId=var_goodsId AND f_warehouseCode=var_warehouseCode; ELSEIF var_warning THEN SHOW warnings; CALL p_debug(CONCAT('warning for ', var_goodsId), false); END IF; END IF; UNTIL var_done END REPEAT; CLOSE cur_disribution; COMMIT;END;//delimiter ;
阅读全文
0 0
- Mysql -- 存储过程/触发器/游标
- 【MySQL】存储过程 游标 触发器
- MySQL存储过程+游标+触发器
- MySQL 触发器、存储过程、游标
- mysql函数、存储过程、触发器、游标
- MySQL必知必会 存储过程 游标 触发器
- MYSQL 存储过程,游标和触发器
- MySQL笔记 存储过程 游标 触发器
- 游标,存储过程,触发器
- MySQL 存储过程游标嵌套,触发器调用存储过程
- mysql 存储过程、触发器、视图、游标、分布式事务--之触发器
- 5.存储过程 游标 触发器
- 游标、触发器、存储过程实例
- 包 存储过程 触发器 游标
- 游标,存储过程,触发器,事务
- MySQL必知必会笔记(六)存储过程 游标 触发器
- Mysql常用命令、简单查询、游标、存储过程、触发器简单介绍
- MYSQL数据库的索引、视图、触发器、游标和存储过程
- Android使用OpenCV合成双目裸眼3D图片(推荐Native方法)
- ES6之箭头函数(Arrow Function)
- Hibernate和struts2的整合代码截图
- Maven工程的多模块项目、项目与项目之间的引用
- Niagara在国内为什么没有发展起来?
- MySQL 触发器、存储过程、游标
- Android 多线程----AsyncTask异步任务详解
- 蓝牙协议分析(7)_BLE连接有关的技术分析
- 新零售下,当大数据遇上了心理学
- 142. Linked List Cycle II
- Java中,既然double类型比float类型,表示的数值范围更大,大家都用double类型不就行了,Java还发明float类型干什么?因为占内存少于double,其他没有任何优势了
- PCA为什么使用协方差矩阵
- 如何调试Cocos2d-JS和Cocos2d-Lua工程
- STM32 boot跳转到APP的Jump_Address()分析