{CAOKEE}soone's blog

昨晚花了一个晚上时间hll的写了这个存储过程，刚好也复习下存储过程的语法。这个存储过程是为西瓜猫下配送订单的时候作的，实现扣卡金额，减少商品数量，生成配送单，插入配送单详情的一系列操作。

drop procedure if exists createOrder;

create procedure createOrder (IN uid int, IN goods varchar(800), IN orderNo varchar(25), IN orderType tinyint, IN rate smallint, IN cid int, OUT goMoney int)
soone_pro:BEGIN
DECLARE allPrice int DEFAULT 0;
DECLARE theFlag int DEFAULT 0;
DECLARE oRate int DEFAULT 0;
DECLARE leaInfo varchar(255);
DECLARE gInfo varchar(20);
DECLARE gid int;
DECLARE gNum int DEFAULT 0;
DECLARE gp int DEFAULT 0;
DECLARE gl int DEFAULT 0;
DECLARE gi tinyint;
DECLARE gn varchar(30);
DECLARE cls tinyint;
DECLARE cle datetime;
DECLARE cia int;

IF (orderType=1 OR orderType=2) AND cid IS NULL THEN
SET goMoney = 0;
LEAVE soone_pro;
END IF;

IF rate IS NOT NULL THEN
SET oRate = rate;
END IF;

SET theFlag = locate(‘|’, goods);
SET leaInfo = goods;
SET AUTOCOMMIT = 0;
soone_loop:WHILE(theFlag >= 0) do
SET gInfo = SUBSTRING_INDEX(leaInfo, ‘|’, 1);
SET gid = SUBSTRING_INDEX(gInfo, ‘,’, 1);
SET gNum = SUBSTRING_INDEX(gInfo, ‘,’, -1);

SELECT gl_mprice, gl_leaves, gl_isspec, gl_name INTO gp, gl, gi, gn FROM xgm_goodlib WHERE gl_id = gid FOR UPDATE;
IF gl < gNum THEN
SET goMoney = 1;
LEAVE soone_pro;
END IF;

IF gi = 1 THEN
SET allPrice = allPrice+(gp*gNum);
ELSE
SET allPrice = allPrice+(gp*gNum*(100-oRate));
END IF;

UPDATE xgm_goodlib SET gl_leaves = gl_leaves-gNum WHERE gl_id = gid;
INSERT INTO xgm_goinfo SET gl_id = gid, goi_nums = gNum, gl_name = gn, go_order = orderNo;

IF theFlag = 0 THEN
LEAVE soone_loop;
END IF;
SET leaInfo = SUBSTRING(leaInfo, theFlag+1);
SET theFlag = locate(‘|’, leaInfo);
SET gInfo = NULL;
SET gid = 0;
SET gNum = 0;
SET gp = NULL;
SET gl = NULL;
SET gi = NULL;
SET gn = NULL;
END WHILE soone_loop;

IF orderType=1 OR orderType=2 THEN
IF cid IS NULL THEN
SET goMoney = 0;
ROLLBACK;
LEAVE soone_pro;
END IF;

SELECT cl_state, cl_expire, ci_balance INTO cls, cle, cia FROM xgm_cardlib WHERE cl_id = cid FOR UPDATE;
IF cls = 0 OR cle < now() OR cia = 0 THEN
SET goMoney = 0;
ROLLBACK;
LEAVE soone_pro;
END IF;

IF orderType = 1 OR (orderType = 2 AND allPrice > cia) THEN
UPDATE xgm_cardlib SET ci_balance = 0 WHERE cl_id = cid;
ELSEIF orderType = 2 THEN
UPDATE xgm_cardlib SET ci_balance = ci_balance – allPrice WHERE cl_id = cid;
END IF;
ELSE
SET cid = 0;
END IF;

INSERT INTO xgm_goodorder SET ou_id = uid, go_order = orderNo, go_date = now(), go_status = 1, cl_id = cid, go_type = orderType, go_allprice = allPrice, go_rate = oRate;

SET goMoney = allPrice;
IF @@ERROR_COUNT > 0 OR allPrice <= 0  OR goMoney = 0 THEN
SET goMoney = 3;
ROLLBACK;
ELSE
COMMIT;
END IF;
END soone_pro;

调用方式
call createOrder(1, ’1,1′, ’3213213213213′, 2, 0,1,@g);select @g;