小7788 2018-10-10 16:51 采纳率: 0%
浏览 1482

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

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

  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

展开全部

  • 写回答

1条回答 默认 最新

  • lshen01 2023-03-15 11:01
    关注

    参考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 的错误了。

    评论
    编辑
    预览

    报告相同问题?

    悬赏问题

    • ¥15 宝塔面板一键迁移使用不了
    • ¥15 求一个按键录像存储到内存卡的ESP32CAM代码
    • ¥15 如何单独修改下列canvas推箱子代码target参数?,插入图片代替其形状,就是哪个绿色的圆圈每关用插入的图片替代
    • ¥20 四叉树的创建和输出问题
    • ¥15 javaweb连接数据库,jsp文件加载不出来
    • ¥15 matlab关于高斯赛德尔迭代的应用编撰。(相关搜索:matlab代码|迭代法)
    • ¥15 损失匹配问题,求解答
    • ¥15 3500常用汉字书法体检测数据集下载
    • ¥15 odoo17在制造模块或采购模块良品与次品如何分流和在质检模块下如何开发
    • ¥15 Qt音乐播放器的音乐文件相对路径怎么写
    手机看
    程序员都在用的中文IT技术交流社区

    程序员都在用的中文IT技术交流社区

    专业的中文 IT 技术社区,与千万技术人共成长

    专业的中文 IT 技术社区,与千万技术人共成长

    关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

    关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

    客服 返回
    顶部