1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
|
CREATE PROCEDURE p_sth_test2()
BEGIN
DECLARE a VARCHAR(50);
DECLARE b INT;
DECLARE c INT;
DECLARE no_more_record INT;
DECLARE cur1 CURSOR FOR SELECT value1, value2 FROM my_table;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_record=1;
SET no_more_record=0;
OPEN cur1;
CREATE TEMPORARY TABLE temp1(value1 varchar(50), intNbPoint INT);
record_loop:WHILE(no_more_record=0) DO
FETCH cur1 INTO a, b;
SELECT sum(intNbPoint) INTO c from mytable2 WHERE id = b;
INSERT INTO temp1(value1, intNbPoint) values(a, c);
END WHILE tag_record;
CLOSE cur1;
SELECT value1 as strXValue, intNbPoint as strYValue from temp1;
DROP TABLE temp1;
SET no_more_record=0;
END; |
Partager