mysql存储过程中游标问题
这是存储过程代码,功能是审核单据,如果可以审核,就把单据上的数量循环写入库存表,我仔细确认了表名,表字段都是完全正确的,游标查询也是有数据的,而且前面的判断条件都没问题,可是就是无法写入数据,返回是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 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;
只有分步调试，SELECT 变量名，OR 将结果插入到临时表中保存，检查结果
分布调试一下了，没有其他的方法。
