mysql存储过程

循环插入表数据
BEGIN
	#Routine body goes here...
	DECLARE l_age int DEFAULT 10;
	DECLARE a int DEFAULT 0;

	REPEAT
		SET a=a+1;
		INSERT INTO student (name,age) VALUES (CONCAT("user",a),l_age);
		SET l_age=l_age+1;
	UNTIL a=num END REPEAT;
END

#游标
BEGIN
	#Routine body goes here...
	DECLARE l_age int DEFAULT 0;
	DECLARE sum_age int DEFAULT 0;
	DECLARE done int DEFAULT 0;

	DECLARE mycursor CURSOR FOR SELECT age FROM student;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

	OPEN mycursor;
		REPEAT
			FETCH mycursor INTO l_age;
			SET sum_age=sum_age+l_age;
		UNTIL done end REPEAT;
	CLOSE mycursor;

	SELECT sum_age;
END
#跨表统计数据量
BEGIN
	#Routine body goes here...
	DECLARE sum int DEFAULT 0;
	DECLARE tb_name VARCHAR(100) DEFAULT '';
	DECLARE i int DEFAULT 0;

	REPEAT
	SET i=i+1;
	SET tb_name=CONCAT("student",i);
	SET @stmtp=CONCAT("SELECT COUNT(1) INTO @num FROM ",tb_name);
	PREPARE stmt FROM @stmtp;  
  EXECUTE stmt;
	SET sum=sum+@num;
	UNTIL i=3 END REPEAT;

	SELECT sum;
END