MYSQL存储过程 NULL与传入值 的比较运算结果有问题～
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′);
MYSQL存储过程 NULL与传入值 的比较运算结果有有关问题
