下面是整个存储过程,我自己查了数量一致类型一致,就是不知道它为什么报这个错误,还是说我写的这个游标有问题?
DELIMITER $$
DROP PROCEDURE IF EXISTS `PRODUCTINVENTORY_SYNC` $$
CREATE PROCEDURE PRODUCTINVENTORY_SYNC()
BEGIN
DECLARE tmpId INT(11);
DECLARE storeCode VARCHAR(50); -- 仓库编码
DECLARE storeName VARCHAR(80); -- 仓库名称
DECLARE productCode VARCHAR(60); -- 商品编码
DECLARE productSubno VARCHAR(40); -- 商品条码
DECLARE productName VARCHAR(80); -- 商品名称
DECLARE supplierCode VARCHAR(60); -- 供应商编码
DECLARE supplierName VARCHAR(80); -- 供应商名称
DECLARE productTypeCode VARCHAR(40); -- 商品类型编码
DECLARE productTypeName VARCHAR(80); -- 商品类型名称
DECLARE productBrandCode VARCHAR(60); -- 商品品牌编码
DECLARE productBrandName VARCHAR(80); -- 商品品牌名称
DECLARE purchasePrice DECIMAL(10,2); -- 进货价
DECLARE retailPrice DECIMAL(10,2); -- 零售价
DECLARE memberPrice DECIMAL(10,2); -- 会员价
DECLARE productStock INT(11); -- 商品库存
DECLARE c_count INT;
-- 遍历数据结束标志
DECLARE done INT DEFAULT FALSE;
-- 设置游标
DECLARE cur_productInventorys CURSOR FOR
SELECT tmp_id
store_code,
store_name,
product_code,
product_subno,
product_name,
supplier_code,
supplier_name,
product_type_code,
product_type_name,
product_brand_code,
product_brand_name,
purchase_price,
retail_price,
member_price,
product_stock FROM product_inventory_tmp_t t WHERE t.processingStatus = 2;
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DELETE it FROM product_inventory_t it WHERE NOT EXISTS (SELECT 1 FROM product_inventory_tmp_t pt WHERE it.product_code = pt.product_code AND it.product_subno = pt.product_subno and it.store_code = pt.store_code AND pt.processingStatus = 2);
-- 打开游标
OPEN cur_productInventorys;
-- 遍历
read_loop: LOOP
-- 设置给对应字段
FETCH cur_productInventorys INTO tmpId,storeCode,storeName,productCode,productSubno,productName,supplierCode,supplierName,productTypeCode,productTypeName,productBrandCode,productBrandName,purchasePrice,retailPrice,memberPrice,productStock;
IF done THEN -- 判断是否继续循环
LEAVE read_loop; -- 结束循环
END IF;
SELECT count(1) INTO c_count FROM product_inventory_t pit WHERE pit.product_code = productCode AND pit.product_subno = productSubno AND pit.store_code = storeCode;
IF c_count = 0 THEN
INSERT INTO product_inventory_t
(
`store_code`,
`store_name`,
`product_code`,
`product_subno`,
`product_name`,
`supplier_code`,
`supplier_name`,
`product_type_code`,
`product_type_name`,
`product_brand_code`,
`product_brand_name`,
`purchase_price`,
`retail_price`,
`member_price`,
`product_stock`,
`create_time`,
`update_time`
)
VALUES
(
storeCode,
storeName,
productCode,
productSubno,
productName,
supplierCode,
supplierName,
productTypeCode,
productTypeName,
productBrandCode,
productBrandName,
purchasePrice,
retailPrice,
memberPrice,
productStock,
`new()`,
`new()`);
ELSE
UPDATE product_inventory_t SET
`store_code`= storeCode,
`store_name` = storeName,
`product_code` = productCode,
`product_subno` = productSubno,
`product_name` = productName,
`supplier_code` = supplierCode,
`supplier_name` = supplierName,
`product_type_code` = productTypeCode,
`product_type_name` = productTypeName,
`product_brand_code` = productBrandCode,
`product_brand_name` = productBrandName,
`purchase_price` = purchasePrice,
`retail_price` = retailPrice,
`member_price` = memberPrice,
`product_stock` = productStock,
`update_time` = now()
WHERE product_code = productCode AND product_subno = productSubno AND store_code = storeCode;
END IF;
UPDATE product_inventory_tmp_t SET `processingStatus` = 4 where tmp_id = tmpId;
END LOOP read_loop;
-- 关闭游标
CLOSE cur_productInventorys;
DELETE FROM product_inventory_tmp_t WHERE `processingStatus` = 4;
END $$
DELIMITER