mysql数据库第十二次作业——存储过程的应用

create database cunchu;

create table stu(id int,name varchar(50) not null,class varchar(50) not null);

insert into stu values("1","Lucy","class1"),("2","Tom","class1"),("3","Rose","class2");

desc stu;

 

select * from stu;

 

mysql> delimiter $
mysql> CREATE PROCEDURE addcount(out count INT)
    -> BEGIN
    -> DECLARE itmp INT;
    -> DECLARE cur_id CURSOR FOR SELECT id FROM stu;
    -> DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_id;
    -> SELECT count(*) INTO count FROM stu;
    -> SET @sum= 0;
    -> OPEN cur_id;
    -> REPEAT
    -> FETCH cur_id INTO itmp;
    -> IF itmp<10
    -> THEN SET @sum=@sum+itmp;
    -> END IF;
    -> UNTIL 0 END REPEAT;
    -> CLOSE cur_id;
    -> END;$

 

 

CALL addcount(@count)$

 

 

SELECT @count,@sum$