开发者

Unknown System Variable "no_more_data" at Mysql Stored Procedure

I'm new using mysql stored procedure. There is error that I can't fix this is my code

DELIMITER $$

CREATE PROCEDURE `bankdb`.`charge` () 
BEGIN
DECLARE idcust_val INT;
DECLARE balance_val FLOAT;
DECLARE productCd_val VARCHAR(10);

DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;

DECLARE col_cur CURSOR FOR
select c.cust_id, c.balance, c.product_cd
from开发者_如何学C account c;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;

OPEN col_cur;
select FOUND_ROWS() into num_rows;

read_loop: LOOP
FETCH col_cur INTO idcust_val, balance_val, productCd_val;

IF no_more_rows THEN
close col_cur;
leave read_loop;
END IF;

IF productCd_val == 'SAV' || productCd_val == 'CD' THEN
    IF balance_val == 2000 THEN
          balance_val = balance_val-10;
          UPDATE account SET avail_balance = balance_val
          WHERE account_id =idcust_val;
    END IF;
END IF;

IF productCd_val == 'CHK' || productCd_val == 'SAV' || productCd_val == 'MM'
|| productCd_val == 'CD' THEN
  balance_val = balance_val + (balance_val*0,05);
  UPDATE account SET avail_balance = balance_val
  WHERE account_id =idcust_val;
ELSE
  balance_val = balance_val - (balance_val*0,1);
  UPDATE account SET avail_balance = balance_val
  WHERE account_id =idcust_val;
END IF;
 SET loop_cntr = loop_cntr + 1;
END LOOP;
END $$

DELIMITER ;

when I execute it, mysql query browser show error like this:

Script line: 3 Unknown system variable 'no_more_rows'

please help me!!


  1. A variable no_more_rows must be delared, write next line at the begining of the procedure's body.

    DECLARE no_more_rows INT DEFAULT 0;

  2. Equal operator is '='

    IF productCd_val == 'SAV' and etc. should be IF productCd_val = 'SAV'

  3. Use SET command to assigns values, e.g. -

    SET balance_val = balance_val - 10;

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜