数据库第十二次作业-存储过程的应用【带源码】

目录

​​1.创建一个stu 表 ​​

​​2.添加stu表数据 ​​

​​3.创建一个存储过程 ​​

---

 

1.创建一个stu 表 

create table zjl_stu(
    -> id int(10) not null,
    -> name varchar(50) not null,
    -> class varchar(50) not null,
    -> primary key(id)
    ->
    -> ); 

 

2.添加stu表数据 

 

 

 insert into zjl_stu values
    -> (1,"Luck","calss1"),
    -> (2,"Tom","class1"),
    -> (3,"Rose","class2");

 

 

3.创建一个存储过程 

 

 

 create procedure addcount(out count int)
    -> begin
    -> declare itmp int;
    -> declare cur_id cursor for select id from zjl_stu;
    -> declare exit handler for not found close cur_id;
    -> select count(*) into count from zjl_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/