/*定义光标*/ DECLARE productCursor CURSOR FOR SELECT `id` FROM `product` WHERE `id` NOT IN (SELECT `product_id` FROM `product_discount`);
/*错误定义,标记循环结束*/ DECLARE CONTINUE HANDLER FOR NOT FOUND SET isDone = 1; ....
好吧,,这样可以我试过把 DECLARE isDone integer; 放在 DECLARE CONTINUE HANDLER FOR NOT FOUND SET isDone = 1; 这句的前面,但是提示出错,就是没再试了
DELIMITER //
CREATE PROCEDURE insert_product_discount() BEGIN /*定义变量*/ DECLARE pid INTEGER DEFAULT 0; DECLARE isDone INTEGER; /*定义光标*/ DECLARE productCursor CURSOR FOR SELECT `id` FROM `product` WHERE `id` NOT IN (SELECT `product_id` FROM `product_discount`);
/*错误定义,标记循环结束*/ DECLARE CONTINUE HANDLER FOR NOT FOUND SET isDone = 1;
/*打开光标*/ OPEN productCursor;
REPEAT /*获取产品ID*/ FETCH productCursor INTO pid; IF NOT isDone THEN /*循环未结束则执行insert*/ INSERT INTO `product_discount` (`product_id`, `price`, `type`) VALUES (pid, '10.00', 'amount'); END IF; UNTIL isDone END REPEAT;
DECLARE pid integer DEFAULT 0;
DECLARE isDone integer;
/*定义光标*/
DECLARE productCursor CURSOR FOR SELECT `id` FROM `product` WHERE `id` NOT IN (SELECT `product_id` FROM `product_discount`);
/*错误定义,标记循环结束*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET isDone = 1;
....
好吧,,这样可以我试过把 DECLARE isDone integer; 放在
DECLARE CONTINUE HANDLER FOR NOT FOUND SET isDone = 1;
这句的前面,但是提示出错,就是没再试了
CREATE PROCEDURE insert_product_discount()
BEGIN
/*定义变量*/
DECLARE pid INTEGER DEFAULT 0;
DECLARE isDone INTEGER;
/*定义光标*/
DECLARE productCursor CURSOR FOR SELECT `id` FROM `product` WHERE `id` NOT IN (SELECT `product_id` FROM `product_discount`);
/*错误定义,标记循环结束*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET isDone = 1;
/*打开光标*/
OPEN productCursor;
REPEAT
/*获取产品ID*/
FETCH productCursor INTO pid;
IF NOT isDone THEN
/*循环未结束则执行insert*/
INSERT INTO `product_discount` (`product_id`, `price`, `type`) VALUES (pid, '10.00', 'amount');
END IF;
UNTIL isDone END REPEAT;
/*关闭光标*/
CLOSE productCursor;
END
//
DELIMITER ;在5.5下测试通过