DELIMITER //
Create procedure sort_orders()
BeginDeclare
o_cursor cursor for select * from orders,customers,salesreps
where cust = cust_num;
open o_cursor;
label1:loop
if(o_cursor.amount < 1000)
then insert into sallorders values(o_cursor.name,o_cursor.amount);
else if (o_cursor.amount>1000)
then insert into bigorders values(o_cursor.name,o_cursor.amount);
end if;
leave label1;
end loop label1;
close o_cursor;
end;//写了个关于游标的练习 “close o_cursor;” 附近出错- - 求大神帮忙看看 还有请教怎么在村春过程中建立临时表?? 头痛 休息会 马上回来!!!
Create procedure sort_orders()
BeginDeclare
o_cursor cursor for select * from orders,customers,salesreps
where cust = cust_num;
open o_cursor;
label1:loop
if(o_cursor.amount < 1000)
then insert into sallorders values(o_cursor.name,o_cursor.amount);
else if (o_cursor.amount>1000)
then insert into bigorders values(o_cursor.name,o_cursor.amount);
end if;
leave label1;
end loop label1;
close o_cursor;
end;//写了个关于游标的练习 “close o_cursor;” 附近出错- - 求大神帮忙看看 还有请教怎么在村春过程中建立临时表?? 头痛 休息会 马上回来!!!
CREATE PROCEDURE sort_orders()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE zz INT DEFAULT 0;
DECLARE zz1 CHAR(100);
DECLARE o_cursor CURSOR FOR SELECT amount,NAME FROM orders,customers,salesreps WHERE cust = cust_num;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN o_cursor;
FETCH o_cursor INTO zz,zz1;
WHILE done=0 DO
IF zz < 1000 THEN
INSERT INTO sallorders VALUES(zz1,zz);
END IF;
IF (zz>1000) THEN
INSERT INTO bigorders VALUES(zz1,zz);
END IF;
FETCH o_cursor INTO zz,zz1;
END WHILE;
CLOSE o_cursor;
END;//
DELIMITER ;
FETCH o_cursor INTO zz,zz1; 为什么最后还要写一次 不是光标开这么?
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
判断是否循环,你的代码要大改