mysql 存储过程 游标 事务,MySql存储过程（事务、游标嵌套）

DROP PROCEDURE IF EXISTS in_inventory_profit;

DELIMITER //

CREATE PROCEDURE in_inventory_profit() BEGIN

/* Part1:定义变量 */

DECLARE inTransferId2nd bigint(20);

-- in_transfer表

DECLARE inTransferId bigint(20);

DECLARE inTransferStutas int(11);

DECLARE inTransferOrgId int(11);

DECLARE inTransferStoreId int(11);

DECLARE inTransferTotal int(11);

DECLARE inTransferAuditUser varchar(50);

DECLARE inTransferAuditTime datetime;

DECLARE inTransferTotalCbjPrice decimal(18,2);

DECLARE inTransferSourceId bigint(20);

-- 公共字段

DECLARE c_remark varchar(255);

DECLARE c_create_time datetime;

DECLARE c_create_user varchar(50);

DECLARE c_create_name varchar(50);

DECLARE c_update_time datetime;

DECLARE c_update_user varchar(50);

DECLARE c_update_name varchar(50);

DECLARE c_sys_version int(11);

DECLARE c_is_delete int(11);

/* Part2:控制变量 */

-- 定义循环标识，默认值为 FALSE

DECLARE main_done INT DEFAULT FALSE;

DECLARE detail_done INT DEFAULT FALSE;

-- 定义游标(主单游标，第一层循环)

DECLARE inTransfer_Cursor

CURSOR FOR (SELECT id,status,org_id,store_id,total,audit_user,audit_time,total_cbj_price,source_id,remark,create_time,create_user,create_name,update_time,update_user,update_name,sys_version,is_delete

FROM rd_wms_intl_1st.in_transfer AS inxfer WHERE inxfer.type=5 OR inxfer.type=10);

-- 将结束标志绑定到游标，若没有数据返回,程序继续,并将变量main_done设为TRUE

DECLARE CONTINUE HANDLER FOR NOT FOUND SET main_done = TRUE;

-- 捕获执行过程中异常

DECLARE continue HANDLER FOR SQLEXCEPTION

GET DIAGNOSTICS CONDITION 1

@errno = MYSQL_ERRNO, @errmsg = MESSAGE_TEXT;

/* Part3:业务开始 */

-- 开启事务

START TRANSACTION;

-- 打开主单游标

OPEN inTransfer_Cursor;

inTransferLoop : LOOP

-- 主单：循环游标中的数据，并赋值到变量中

FETCH inTransfer_Cursor INTO inTransferId,inTransferStutas,inTransferOrgId,inTransferStoreId,inTransferTotal,inTransferAuditUser,inTransferAuditTime,inTransferTotalCbjPrice,inTransferSourceId,

c_remark,c_create_time,c_create_user,c_create_name,c_update_time,c_update_user,c_update_name,c_sys_version,c_is_delete;

IF main_done THEN

LEAVE inTransferLoop;

ELSE

CASE inTransferStutas

WHEN 200 THEN

SET inUpStatus = 100;

WHEN 300 THEN

SET inUpStatus = 200;

ELSE

SET inUpStatus = -10000;

END CASE;

-- 插入到in_transfer表(2nd)

INSERT INTO rd_wms_intl_miagrate.in_transfer (id,org_id,store_id,quantity,deliver_id,in_type,remark,create_time,create_user,create_name,update_time,update_user,update_name,sys_version,is_delete)

VALUES (inTransferId,inTransferOrgId,inTransferStoreId,inTransferTotal,-1,5,c_remark,c_create_time,c_create_user,c_create_name,c_update_time,c_update_user,c_update_name,c_sys_version,c_is_delete);

-- 获取刚插入记录的主键id

-- SET inTransferId2nd = (SELECT LAST_INSERT_ID());

END IF;

BEGIN

-- 声明 明细表变量

DECLARE detailPartCode varchar(50);

DECLARE detailWareId varchar(50);

DECLARE detailWareName varchar(255);

DECLARE detailSn varchar(50);

DECLARE detailPartPacking int(11);

DECLARE detailSymbol int(11);

DECLARE detailBrandId varchar(50);

DECLARE detailBrandName varchar(255);

DECLARE detailSupplierCode varchar(100);

DECLARE detailCbjPrice decimal(18,2);

DECLARE detailAttachment varchar(50);

DECLARE detailJdFlag int(11);

-- 声明明细表游标(第二层)

DECLARE inTransferDetail_Cursor

CURSOR FOR (SELECT part_code,ware_id,ware_name,sn,part_packing,symbol,brand_id,brand_name,supplier_code,cbj_price,attachment,jd_flag,

remark,create_time,create_user,create_name,update_time,update_user,update_name,sys_version,is_delete

FROM rd_wms_intl_1st.common_in_transfer_detail AS detail WHERE detail.main_id=inTransferId);

DECLARE CONTINUE HANDLER FOR NOT FOUND SET detail_done = TRUE;

-- 打开明细游标

OPEN inTransferDetail_Cursor;

inTransferDetailLoop : LOOP

-- 循环detail游标中的数据，并赋值到变量中

FETCH inTransferDetail_Cursor INTO detailPartCode,detailWareId,detailWareName,detailSn,detailPartPacking,detailSymbol,

detailBrandId,detailBrandName,detailSupplierCode,detailCbjPrice,detailAttachment,detailJdFlag,

c_remark,c_create_time,c_create_user,c_create_name,c_update_time,c_update_user,c_update_name,c_sys_version,c_is_delete;

IF detail_done THEN

LEAVE inTransferDetailLoop;

ELSE

-- 插入到in_transfer_detail表(2nd)

INSERT INTO rd_wms_intl_miagrate.in_transfer_detail (main_id,part_code,ware_id,ware_name,remark,create_time,create_user,create_name,update_time,update_user,update_name,sys_version,is_delete)

VALUES (inTransferId,detailPartCode,detailWareId,detailWareName,c_remark,c_create_time,c_create_user,c_create_name,c_update_time,c_update_user,c_update_name,c_sys_version,c_is_delete);

END IF;

END LOOP inTransferDetailLoop;

CLOSE inTransferDetail_Cursor;

SET detail_done = FALSE;

END;

END IF;

END LOOP inTransferLoop;

CLOSE inTransfer_Cursor;

-- 结束事务

IF @errno>0 THEN

SELECT @errno,@errmsg;

ROLLBACK;-- 事务回滚

ELSE

SELECT 'success' as result;

COMMIT;-- 事务提交

END IF;

END;

//

DELIMITER;-- 调用

CALL in_inventory_profit();