使用存储过程求解平均年龄
delimiter &
create procedure cursor_test_3(out avg_age int)
begin
declare tmp int;/*临时变量*/
declare sum_age int;/*总年龄*/
declare num int ;/*总人数*/
declare cursor_stu cursor for select age from student;
declare exit handler for not found close cursor_stu;
open cursor_stu;
set avg_age=0;/*平均年龄*/
set num=0;
set sum_age=0;
repeat
fetch cursor_stu into tmp;
set sum_age=sum_age+tmp;
set num=num+1;
until 0 end repeat;
close cursor_stu;
set avg_age=sum_age/num;/*这里avg_age赋值不产生作用*/
select * from student; /*这里使用select语句也没有起作用*/
end &
delimiter ;mysql存储cursor
delimiter &
create procedure cursor_test_3(out avg_age int)
begin
declare tmp int;/*临时变量*/
declare sum_age int;/*总年龄*/
declare num int ;/*总人数*/
declare cursor_stu cursor for select age from student;
declare exit handler for not found close cursor_stu;
open cursor_stu;
set avg_age=0;/*平均年龄*/
set num=0;
set sum_age=0;
repeat
fetch cursor_stu into tmp;
set sum_age=sum_age+tmp;
set num=num+1;
until 0 end repeat;
close cursor_stu;
set avg_age=sum_age/num;/*这里avg_age赋值不产生作用*/
select * from student; /*这里使用select语句也没有起作用*/
end &
delimiter ;mysql存储cursor
解决方案 »
- mysql建立触发器问题
- mysql中source *.sql的方法导入数据太慢了
- 合并记录的时候如何记录下每条记录ID
- 用ODBC数据源建立MySQL数据连接的问题
- 数据库的难题?
- 如何在命令行中将连接时的字符集设置成utf-8,即实现jdbc:mysql://localhost/<database>?useUnicode=true&characterEncoding=utf-8
- asp中怎么读取mysql?100分。
- 不知是不是Mysql“死锁”
- 请问用触发器同步一个数据库两个表,语句怎么写?
- mysql中 两个master上的不同的库,能否都用一个slave上
- 这个Mysql是出什么问题了
- MYSQL数据库,一般应用于什么地方?
看看select 出来的是什么东西
select sum_age from dual 加在 set sum_age=sum_age+tmp
后边 ,你这个循环没有停止条件吧
| sum_age |
+---------+
| 20 |
+---------+
1 row in set (0.00 sec)+---------+
| sum_age |
+---------+
| 41 |
+---------+
1 row in set (0.01 sec)+---------+
| sum_age |
+---------+
| 63 |
+---------+
1 row in set (0.01 sec)+---------+
| sum_age |
+---------+
| 86 |
+---------+
1 row in set (0.02 sec)+---------+
| sum_age |
+---------+
| 110 |
+---------+
1 row in set (0.03 sec)+---------+
| sum_age |
+---------+
| 135 |
+---------+
在表中存储学生的年龄依次是:20,21,22,23,24,25
我想问一下:
until 0 end repeat;是repeat循环终止条件吧?
后面加select avg_age from dual 是什么东西
select avg_age from dual 就是测试临时表的数据
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]mysql> delimiter //mysql> CREATE PROCEDURE dorepeat(p1 INT)
-> BEGIN
-> SET @x = 0;
-> REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)mysql> CALL dorepeat(1000)//
Query OK, 0 rows affected (0.00 sec)mysql> SELECT @x//
+------+
| @x |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)
until 0 意味着永远不会变成真,死循环。
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a CHAR(16);
DECLARE b,c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur1;
OPEN cur2; REPEAT
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF NOT done THEN
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END IF;
UNTIL done END REPEAT; CLOSE cur1;
CLOSE cur2;
END