mysql存储过程游标遍历表数据报错，1328 Incorrect number of FETCH variables

Question

下面是整个存储过程，我自己查了数量一致类型一致，就是不知道它为什么报这个错误，还是说我写的这个游标有问题？

  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

Answer 1

参考GPT和自己的思路：

根据代码分析，游标声明时 SELECT 语句的字段数为 15，然而在 FETCH 语句却只设置了 14 个变量，这是引起错误的原因。因此，你需要在 FETCH 语句中添加一个变量来与 SELECT 语句中的字段数对应，即：

FETCH cur_productInventorys INTO tmpId, storeCode, storeName, productCode, productSubno, productName, supplierCode, supplierName, productTypeCode, productTypeName, productBrandCode, productBrandName, purchasePrice, retailPrice, memberPrice, productStock;

修复这个问题后再次执行存储过程就不应该再出现 1328 Incorrect number of FETCH variables 的错误了。