mysql存储过程中游标问题

Question

    这是存储过程代码,功能是审核单据,如果可以审核,就把单据上的数量循环写入库存表,我仔细确认了表名,表字段都是完全正确的,游标查询也是有数据的,而且前面的判断条件都没问题,可是就是无法写入数据,返回是rollback; set come = 2;
    而且连带问题是,这个存储过程执行一次后,MYSQL数据库就再无法insert数据,非要重启才能insert
我知道肯定是这个存储过程有问题,但我找不出问题在哪里,请大家帮帮忙,,谢谢
CREATE DEFINER = 'root'@'%' PROCEDURE `VerifyPurchase`(
        IN ids INTEGER(11),
        IN UserID INTEGER(11),
        OUT come INTEGER(11)
    )
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    DECLARE err INTEGER DEFAULT 0;
    DECLARE b INTEGER default 0;
    DECLARE Warehouse_ID INTEGER;
    DECLARE Material_ID INTEGER;
    DECLARE Unit_ID INTEGER;
    DECLARE Qty1 CHAR;
    DECLARE TheoreticalWeight1 CHAR;
DECLARE ActualWeight1 CHAR;
DECLARE Money1 CHAR;
    DECLARE cur_1 CURSOR FOR select MaterialID,WarehouseID,UnitID,Qty,Money,TheoreticalWeight,ActualWeight from purchase_view where id=ids;         #定义游标
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err=1;
    START TRANSACTION;
    select count(*) INTO @IsExists from purchase where id=ids;  #检查单据是否存在
    set come = @IsExists;
    if @IsExists <= 0 then
       set come = 8601; # 单据不存在
    else
       select Verify INTO @Verify from purchase where id=ids;   #检查审核状态
       if @Verify > 0 then
          set come = 8602; #单据已审核
       else
          select Date INTO @Date from purchase where id=ids;    #检查期间
          if CompareCurrDate(@Date) = FALSE then
             set come = 8603;       #单据日期小于当前期间
          else
             # 写入审核人及日期
             update purchases set verify = UserID , VerifyDate = now() where id=ids;
             # 写入即时库存
             OPEN cur_1;
             FETCH cur_1 INTO Material_ID, Warehouse_ID, Unit_ID,Qty1,Money1,TheoreticalWeight1,ActualWeight1; #获取第一条记录
             while b<>1 do
                   insert into stock (MaterialID,WarehouseID,UnitID,Qty,Money,TheoreticalWeight,ActualWeight) values (Material_ID, Warehouse_ID, Unit_ID,Qty1,Money1,TheoreticalWeight1,ActualWeight1);
                   FETCH cur_1 INTO Material_ID, Warehouse_ID, Unit_ID,Qty1,Money1,TheoreticalWeight1,ActualWeight1; #取下一条记录
             end while;
             close cur_1;
             set come = 1;
          end if;
       end if;
    end if;    if err = 0 then
       commit;
    else
       rollback;
        set come = 2;
    end if;
END

Answer 1

在每个WHERE后加
SET b = 1

Answer 2

加上之后,存储过程无法编译,而且我也不明白加上set b=1的意思是什么?谢谢你的回答

Answer 3

在每个WHERE后加
SET b = 0;你的代码，在WHERE没有满足条件的记录时，会影响b的值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;

Answer 4

恩..明白了.我改成这样,还是无法写入:BEGIN
    DECLARE err INTEGER DEFAULT 0;
    DECLARE b INTEGER default 0;
    DECLARE Warehouse_ID INTEGER;
    DECLARE Material_ID INTEGER;
    DECLARE Unit_ID INTEGER;
    DECLARE Qty1 CHAR;
    DECLARE TheoreticalWeight1 CHAR;
DECLARE ActualWeight1 CHAR;
DECLARE Money1 CHAR;
    DECLARE cur_1 CURSOR FOR select MaterialID,WarehouseID,UnitID,Qty,Money,TheoreticalWeight,ActualWeight from purchase_view where id=ids;         #定义游标
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err=1;
    START TRANSACTION;
    select count(*) INTO @IsExists from purchase where id=ids;  #检查单据是否存在
    SET b = 0;
    set come = @IsExists;
    if @IsExists <= 0 then
       set come = 8601; # 单据不存在
    else
       select Verify INTO @Verify from purchase where id=ids;   #检查审核状态
       SET b = 0;
       if @Verify > 0 then
          set come = 8602; #单据已审核
       else
          select Date INTO @Date from purchase where id=ids;    #检查期间
          SET b = 0;
          if CompareCurrDate(@Date) = FALSE then
             set come = 8603;       #单据日期小于当前期间
          else
             # 写入审核人及日期
             update purchases set verify = UserID , VerifyDate = now() where id=ids;
             SET b = 0;
             # 写入即时库存
             OPEN cur_1;
             FETCH cur_1 INTO Material_ID, Warehouse_ID, Unit_ID,Qty1,Money1,TheoreticalWeight1,ActualWeight1; #获取第一条记录
             while b<>1 do
                   insert into stock (MaterialID,WarehouseID,UnitID,Qty,Money,TheoreticalWeight,ActualWeight) values (Material_ID, Warehouse_ID, Unit_ID,Qty1,Money1,TheoreticalWeight1,ActualWeight1);
                   FETCH cur_1 INTO Material_ID, Warehouse_ID, Unit_ID,Qty1,Money1,TheoreticalWeight1,ActualWeight1; #取下一条记录
             end while;
             close cur_1;
             set come = 1;
          end if;
       end if;
    end if;    if err = 0 then
       commit;
    else
       rollback;
        set come = 2;
    end if;
END而且这个存储过程执行一次后,数据库就再无法insert数据,不管插入哪个表都一样,提示是插入成功了,而且自动增量值也加了1,可是就是没有数据写入,奇怪了

Answer 5

给出你的create table 语句和 insert into 语句，这样别人可以直接在自己的机器创建相应的环境进行测试。

Answer 6

创建表
CREATE TABLE `table1` (
  `id` INTEGER(11) DEFAULT NULL,
  `BillNo` INTEGER(11) DEFAULT NULL
) ENGINE=InnoDB;insert
insert into table1 (BillNo) values (1111);存储过程
CREATE DEFINER = 'root'@'%' PROCEDURE `VerifyPurchase`(
        IN ids INTEGER(11),
        IN UserID INTEGER(11),
        OUT come INTEGER(11)
    )
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    DECLARE err INTEGER DEFAULT 0;
    DECLARE b INTEGER default 0;
    DECLARE Warehouse_ID INTEGER;
    DECLARE Material_ID INTEGER;
    DECLARE Unit_ID INTEGER;
    DECLARE Qty1 CHAR;
    DECLARE TheoreticalWeight1 CHAR;
DECLARE ActualWeight1 CHAR;
DECLARE Money1 CHAR;
    DECLARE cur_1 CURSOR FOR select MaterialID,WarehouseID,UnitID,Qty,Money,TheoreticalWeight,ActualWeight from purchase_view where id=ids;         #定义游标
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err=1;
    START TRANSACTION;
    select count(*) INTO @IsExists from purchase where id=ids;  #检查单据是否存在
    SET b = 0;
    set come = @IsExists;
    if @IsExists <= 0 then
       set come = 8601; # 单据不存在
    else
       select Verify INTO @Verify from purchase where id=ids;   #检查审核状态
       SET b = 0;
       if @Verify > 0 then
          set come = 8602; #单据已审核
       else
          select Date INTO @Date from purchase where id=ids;    #检查期间
          SET b = 0;
          if CompareCurrDate(@Date) = FALSE then
             set come = 8603;       #单据日期小于当前期间
          else
             # 写入审核人及日期
             update purchases set verify = UserID , VerifyDate = now() where id=ids;
             SET b = 0;
             # 写入即时库存
             OPEN cur_1;
             FETCH cur_1 INTO Material_ID, Warehouse_ID, Unit_ID,Qty1,Money1,TheoreticalWeight1,ActualWeight1; #获取第一条记录
             while b<>1 do
                   insert into stock (MaterialID,WarehouseID,UnitID,Qty,Money,TheoreticalWeight,ActualWeight) values (Material_ID, Warehouse_ID, Unit_ID,Qty1,Money1,TheoreticalWeight1,ActualWeight1);
                   FETCH cur_1 INTO Material_ID, Warehouse_ID, Unit_ID,Qty1,Money1,TheoreticalWeight1,ActualWeight1; #取下一条记录
             end while;
             close cur_1;
             set come = 1;
          end if;
       end if;
    end if;    if err = 0 then
       commit;
    else
       rollback;
        set come = 2;
    end if;
END;搞了一下午了,,还没找到原因..