createPROCEDUREpro_test()BEGINDECLAREidINTEGER;DECLAREnamevarchar(20);DECLAREnum1INTEGER;DECLAREnum2INTEGER;DECLAREsum1INTEGER;DECLAREsum2INTEGER;DECLAREcount1BIGINT;DECL...
create PROCEDURE pro_test()
BEGIN
DECLARE id INTEGER;
DECLARE name varchar(20);
DECLARE num1 INTEGER;
DECLARE num2 INTEGER;
DECLARE sum1 INTEGER;
DECLARE sum2 INTEGER;
DECLARE count1 BIGINT;
DECLARE index1 BIGINT;
DECLARE c_test CURSOR FOR select * from test1;
SET sum1 = 0;
SET sum2 = 0;
set index1 = 0;
open c_test;
drop table IF EXISTS test2;
create TEMPORARY table test2(id INTEGER, name varchar(20), num1 INTEGER, num2 INTEGER);
select count(*) into count1 from `test1`;
LOOP
set index1 = index1 + 1;
FETCH c_test INTO id, name, num1, num2;
set sum1 = sum1 + num1;
set sum2 = sum2 + num2;
IF index1=count1 THEN
INSERT into test2 values(id, name, sum1,sum2);
select * from test2;
end if;
END LOOP;
select sum2; //就这里输不出东西,显示no data to fetch
CLOSE c_test;
END
展开