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!!
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;
Equal operator is '='
IF productCd_val == 'SAV' and etc. should be IF productCd_val = 'SAV'
Use SET command to assigns values, e.g. -
SET balance_val = balance_val - 10;
精彩评论