当信息不存在时, 新增顾客送货信息
INSERT INTO product_received_addr
SELECT a.id, a.cuid, a.recer, a.phone, a.tel, a.addr from (
SELECT count(*) as len, REPLACE(UUID(),'-', '') as id, _customerId as cuid,
recer as recer, phone as phone,tel as tel, address as addr FROM product_received_addr
WHERE receiver_name = recer and phone = phone and tel = tel and addr = address
) a where a.len < 1;
游标的操作:
OPEN _tempProducts;
cursor_loop:loop
FETCH _tempProducts INTO _productId, _productName, _productNum;
if _stopFlag then ##
leave cursor_loop;
end if;
end loop cursor_loop;
##关闭游标
CLOSE _tempProducts;
end loop cursor_loop;
事务提交以及回滚:
if (_isRollbak != 0) then ##错误
ROLLBACK; ##事务回滚
select product_name, error_code from _temp_error_info order by sort ;
set state = 1;
else
##行级锁,对要操作的产品进行锁定
select stock
from product_info
where product_id
in (
select product_id from _temp_order_list_info
) for update;