Drop PROCEDURE if exists OrderChangeLog;
/*==============================================================*/
/* 订单更新与删除以及日志的记录 INTYPE状态码意义 */
/* INTYPE=2:更新订单状态,3:删除订单 */
/* INTYPE=4:更新快递公司名称,INTTYP=5更新运单号 */
/* INTYPE=6:更新管理员注释,INTYPE=7更新价格数值 */
/*==============================================================*/
DELIMITER //
CREATE PROCEDURE OrderChangeLog(IN INOrderID char(20),IN INLoginName varchar(40),IN INAfterOrderValues text(512),IN INNowIP varchar(128),IN INViewVersion text(512),IN INType INT,OUT OUTOrderCOUNT INT)
BEGIN
DECLARE OUTOrderStatus INT DEFAULT 0;
DECLARE OUTCourierCompanies VARCHAR(64) DEFAULT '0';
DECLARE OUTWaybill VARCHAR(64) DEFAULT '0';
DECLARE OUTExplain TEXT DEFAULT '0';
DECLARE OUTProPrice decimal(10,2) DEFAULT '0';
SELECT COUNT(OrderID),OrderStatus,CourierCompanies,Waybill,`Explain`,ProPrice INTO OUTOrderCOUNT,OUTOrderStatus,OUTCourierCompanies,OUTWaybill,OUTExplain,OUTProPrice FROM OrderList WHERE OrderID=INOrderID LIMIT 0,1;
IF OUTOrderCOUNT THEN
IF INType=2 THEN
UPDATE OrderList SET OrderStatus=INAfterOrderValues,ChangeTime=NOW() WHERE OrderID=INOrderID AND (OUTOrderStatus!=INAfterOrderValues);
IF ROW_COUNT()>0 THEN
INSERT INTO OrderChangeLog(OrderID,LoginName,BeforeOrderValues,AfterOrderValues,ChangeTime,NowIP,ViewVersion,LogType) VALUES (INOrderID,INLoginName,OUTOrderStatus,INAfterOrderValues,NOW(),INNowIP,INViewVersion,'2');
ELSE
SET OUTOrderCOUNT=3;
END IF;
END IF;
IF INType=3 THEN
INSERT INTO DeleteOrderList SELECT * FROM OrderList WHERE OrderID=INOrderID;
IF ROW_COUNT()>0 THEN
DELETE FROM OrderList WHERE OrderID=INOrderID;
END IF;
IF ROW_COUNT()>0 THEN
INSERT INTO OrderChangeLog(OrderID,LoginName,BeforeOrderValues,AfterOrderValues,ChangeTime,NowIP,ViewVersion,LogType) VALUES (INOrderID,INLoginName,OUTOrderStatus,INAfterOrderValues,NOW(),INNowIP,INViewVersion,'2');
END IF;
END IF;
IF INType=4 THEN
UPDATE OrderList SET CourierCompanies=INAfterOrderValues,ChangeTime=NOW() WHERE OrderID=INOrderID AND (OUTCourierCompanies!=INAfterOrderValues);
IF ROW_COUNT()>0 THEN
INSERT INTO OrderChangeLog(OrderID,LoginName,BeforeOrderValues,AfterOrderValues,ChangeTime,NowIP,ViewVersion,LogType) VALUES (INOrderID,INLoginName,OUTOrderStatus,INAfterOrderValues,NOW(),INNowIP,INViewVersion,'4');
ELSE
SET OUTOrderCOUNT=3;
END IF;
END IF;
IF INType=5 THEN
UPDATE OrderList SET Waybill=INAfterOrderValues,ChangeTime=NOW() WHERE OrderID=INOrderID AND (OUTWaybill!=INAfterOrderValues);
IF ROW_COUNT()>0 THEN
INSERT INTO OrderChangeLog(OrderID,LoginName,BeforeOrderValues,AfterOrderValues,ChangeTime,NowIP,ViewVersion,LogType) VALUES (INOrderID,INLoginName,OUTOrderStatus,INAfterOrderValues,NOW(),INNowIP,INViewVersion,'5');
ELSE
SET OUTOrderCOUNT=3;
END IF;
END IF;
IF INType=6 THEN
UPDATE OrderList SET `Explain`=INAfterOrderValues,ChangeTime=NOW() WHERE OrderID=INOrderID AND (OUTExplain!=INAfterOrderValues);
IF ROW_COUNT()>0 THEN
INSERT INTO OrderChangeLog(OrderID,LoginName,BeforeOrderValues,AfterOrderValues,ChangeTime,NowIP,ViewVersion,LogType) VALUES (INOrderID,INLoginName,OUTOrderStatus,INAfterOrderValues,NOW(),INNowIP,INViewVersion,'6');
ELSE
SET OUTOrderCOUNT=3;
END IF;
END IF;
IF INType=7 THEN
UPDATE OrderList SET ProPrice=INAfterOrderValues,ChangeTime=NOW() WHERE OrderID=INOrderID AND (OUTProPrice!=INAfterOrderValues);
IF ROW_COUNT()>0 THEN
INSERT INTO OrderChangeLog(OrderID,LoginName,BeforeOrderValues,AfterOrderValues,ChangeTime,NowIP,ViewVersion,LogType) VALUES (INOrderID,INLoginName,OUTOrderStatus,INAfterOrderValues,NOW(),INNowIP,INViewVersion,'7');
ELSE
SET OUTOrderCOUNT=3;
END IF;
END IF;
END IF;
END//
DELIMITER ;调用:
CALL OrderChangeLog('20120616183424876074','admin','点击这里输入信息……','127.0.0.1','Mozilla/5.0 (Windows NT 5.1; rv:12.0) Gecko/20100101 Firefox/12.0','6',@OUTOrderCOUNT)
传出值@OUTOrderCOUNT 始终等于3OrderList表的Explain列初始状态是NULL的
为什么下面这个语句不能够被执行?
UPDATE OrderList SET `Explain`=INAfterOrderValues,ChangeTime=NOW() WHERE OrderID=INOrderID AND (OUTExplain!=INAfterOrderValues);
难道 NULL!=点击这里输入信息…… 这个条件的结果会是false吗
是不是mysql存储过程不支持字符串比较运算UPDATE OrderList SET OrderStatus=INAfterOrderValues,ChangeTime=NOW() WHERE OrderID=INOrderID AND (OUTOrderStatus!=INAfterOrderValues这句又可以正常了
OUTOrderStatus的初始值不为NULL,且为int类型
当我将订单ID为20120616183424876074的Explain列插入值后,运行上面的存储过程还是不行请大家不吝赐教
/*==============================================================*/
/* 订单更新与删除以及日志的记录 INTYPE状态码意义 */
/* INTYPE=2:更新订单状态,3:删除订单 */
/* INTYPE=4:更新快递公司名称,INTTYP=5更新运单号 */
/* INTYPE=6:更新管理员注释,INTYPE=7更新价格数值 */
/*==============================================================*/
DELIMITER //
CREATE PROCEDURE OrderChangeLog(IN INOrderID char(20),IN INLoginName varchar(40),IN INAfterOrderValues text(512),IN INNowIP varchar(128),IN INViewVersion text(512),IN INType INT,OUT OUTOrderCOUNT INT)
BEGIN
DECLARE OUTOrderStatus INT DEFAULT 0;
DECLARE OUTCourierCompanies VARCHAR(64) DEFAULT '0';
DECLARE OUTWaybill VARCHAR(64) DEFAULT '0';
DECLARE OUTExplain TEXT DEFAULT '0';
DECLARE OUTProPrice decimal(10,2) DEFAULT '0';
SELECT COUNT(OrderID),OrderStatus,CourierCompanies,Waybill,`Explain`,ProPrice INTO OUTOrderCOUNT,OUTOrderStatus,OUTCourierCompanies,OUTWaybill,OUTExplain,OUTProPrice FROM OrderList WHERE OrderID=INOrderID LIMIT 0,1;
IF OUTOrderCOUNT THEN
IF INType=2 THEN
UPDATE OrderList SET OrderStatus=INAfterOrderValues,ChangeTime=NOW() WHERE OrderID=INOrderID AND (OUTOrderStatus!=INAfterOrderValues);
IF ROW_COUNT()>0 THEN
INSERT INTO OrderChangeLog(OrderID,LoginName,BeforeOrderValues,AfterOrderValues,ChangeTime,NowIP,ViewVersion,LogType) VALUES (INOrderID,INLoginName,OUTOrderStatus,INAfterOrderValues,NOW(),INNowIP,INViewVersion,'2');
ELSE
SET OUTOrderCOUNT=3;
END IF;
END IF;
IF INType=3 THEN
INSERT INTO DeleteOrderList SELECT * FROM OrderList WHERE OrderID=INOrderID;
IF ROW_COUNT()>0 THEN
DELETE FROM OrderList WHERE OrderID=INOrderID;
END IF;
IF ROW_COUNT()>0 THEN
INSERT INTO OrderChangeLog(OrderID,LoginName,BeforeOrderValues,AfterOrderValues,ChangeTime,NowIP,ViewVersion,LogType) VALUES (INOrderID,INLoginName,OUTOrderStatus,INAfterOrderValues,NOW(),INNowIP,INViewVersion,'2');
END IF;
END IF;
IF INType=4 THEN
UPDATE OrderList SET CourierCompanies=INAfterOrderValues,ChangeTime=NOW() WHERE OrderID=INOrderID AND (OUTCourierCompanies!=INAfterOrderValues);
IF ROW_COUNT()>0 THEN
INSERT INTO OrderChangeLog(OrderID,LoginName,BeforeOrderValues,AfterOrderValues,ChangeTime,NowIP,ViewVersion,LogType) VALUES (INOrderID,INLoginName,OUTOrderStatus,INAfterOrderValues,NOW(),INNowIP,INViewVersion,'4');
ELSE
SET OUTOrderCOUNT=3;
END IF;
END IF;
IF INType=5 THEN
UPDATE OrderList SET Waybill=INAfterOrderValues,ChangeTime=NOW() WHERE OrderID=INOrderID AND (OUTWaybill!=INAfterOrderValues);
IF ROW_COUNT()>0 THEN
INSERT INTO OrderChangeLog(OrderID,LoginName,BeforeOrderValues,AfterOrderValues,ChangeTime,NowIP,ViewVersion,LogType) VALUES (INOrderID,INLoginName,OUTOrderStatus,INAfterOrderValues,NOW(),INNowIP,INViewVersion,'5');
ELSE
SET OUTOrderCOUNT=3;
END IF;
END IF;
IF INType=6 THEN
UPDATE OrderList SET `Explain`=INAfterOrderValues,ChangeTime=NOW() WHERE OrderID=INOrderID AND (OUTExplain!=INAfterOrderValues);
IF ROW_COUNT()>0 THEN
INSERT INTO OrderChangeLog(OrderID,LoginName,BeforeOrderValues,AfterOrderValues,ChangeTime,NowIP,ViewVersion,LogType) VALUES (INOrderID,INLoginName,OUTOrderStatus,INAfterOrderValues,NOW(),INNowIP,INViewVersion,'6');
ELSE
SET OUTOrderCOUNT=3;
END IF;
END IF;
IF INType=7 THEN
UPDATE OrderList SET ProPrice=INAfterOrderValues,ChangeTime=NOW() WHERE OrderID=INOrderID AND (OUTProPrice!=INAfterOrderValues);
IF ROW_COUNT()>0 THEN
INSERT INTO OrderChangeLog(OrderID,LoginName,BeforeOrderValues,AfterOrderValues,ChangeTime,NowIP,ViewVersion,LogType) VALUES (INOrderID,INLoginName,OUTOrderStatus,INAfterOrderValues,NOW(),INNowIP,INViewVersion,'7');
ELSE
SET OUTOrderCOUNT=3;
END IF;
END IF;
END IF;
END//
DELIMITER ;调用:
CALL OrderChangeLog('20120616183424876074','admin','点击这里输入信息……','127.0.0.1','Mozilla/5.0 (Windows NT 5.1; rv:12.0) Gecko/20100101 Firefox/12.0','6',@OUTOrderCOUNT)
传出值@OUTOrderCOUNT 始终等于3OrderList表的Explain列初始状态是NULL的
为什么下面这个语句不能够被执行?
UPDATE OrderList SET `Explain`=INAfterOrderValues,ChangeTime=NOW() WHERE OrderID=INOrderID AND (OUTExplain!=INAfterOrderValues);
难道 NULL!=点击这里输入信息…… 这个条件的结果会是false吗
是不是mysql存储过程不支持字符串比较运算UPDATE OrderList SET OrderStatus=INAfterOrderValues,ChangeTime=NOW() WHERE OrderID=INOrderID AND (OUTOrderStatus!=INAfterOrderValues这句又可以正常了
OUTOrderStatus的初始值不为NULL,且为int类型
当我将订单ID为20120616183424876074的Explain列插入值后,运行上面的存储过程还是不行请大家不吝赐教
解决方案 »
- 打开一个php网页, 怎么监测打开这个网页都执行了哪些sql语句以及所用的时间?
- Mysql 效率问题后篇---远程数据访问---特慢!!
- MYSQL 更新问题
- MSSQL存储过程转换为Mysql存储过程
- 几个constraints 的写法
- 第一次装MySql,客户给了我一个.MYI,一个.MYD的文件,请问能不把它还原(或附加)
- 在redhat 5.1下如何安装mysql?
- [主从复制] 这周碰到一个很神奇的现象
- .sql中grant设置动态创建的表权限给指定用户
- zabbix web,报表的前百大触发器,数据在哪里。
- PostgreSQL的全局变量问题
- MYSQL存储过程字符集 一个非常纠结的问题
SELECT 出来是NULL
没法select
UPDATE OrderList SET `Explain`=INAfterOrderValues,ChangeTime=NOW() WHERE OrderID=INOrderID AND (OUTExplain!=INAfterOrderValues);
IF ROW_COUNT()>0 THEN
INSERT INTO OrderChangeLog(OrderID,LoginName,BeforeOrderValues,AfterOrderValues,ChangeTime,NowIP,ViewVersion,LogType) VALUES (INOrderID,INLoginName,OUTOrderStatus,INAfterOrderValues,NOW(),INNowIP,INViewVersion,'6');
ELSE
SET OUTOrderCOUNT=3;
END IF;
END IF;
我将判断语句 (OUTExplain!=INAfterOrderValues);去掉之后
发现update语句执行了
insert语句执行成功了,操作日志正常记录IF ROW_COUNT()>0
才执行insert语句的
但是最后发现并没有更新成功Explain,里面的值还是之前的值,吐血