MySql存储过程学习笔记(1)

当信息不存在时, 新增顾客送货信息

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;