过程和函数
1、 创建存储过程,查询电气2班的学生人数。
CREATE PROCEDURE `studentS1`()
BEGIN
select count(*) '人数'from student where sclass ='电气2班';
END
2、 创建存储过程,查询每个班级的学生人数。
CREATE PROCEDURE `student22`()
BEGIN
select sclass , count(*) from student group by sclass;
END
3、 创建存储过程,查询女学生的人数,要求输出人数
CREATE PROCEDURE `student24`()
BEGIN
select avg(grade) from sc where sno='1001';
END
4、 创建存储过程,查询1001学号的平均成绩,然后调用该存储过程
CREATE PROCEDURE `student24`()
BEGIN
select avg(grade) from sc where sno='1001';
END
5、 创建存储过程,查看80-90成绩人数,并统计成绩和。
CREATE PROCEDURE `student25`()
BEGIN
select '人数',count(*) from sc where grade in(80,90);
select sum(grade) from sc where grade in(80,90);
END
6、调用2存储过程,然后删除
call blili.student22;
drop procedure if exists student22;
7、 创建自定义函数,实现查询某个学号的姓名。
CREATE FUNCTION `Name12`(sno VARCHAR(10)) RETURNS varchar(20) CHARSET utf8
BEGIN
DECLARE student_name VARCHAR(20) CHARACTER SET utf8;
SELECT sname INTO student_name FROM student WHERE sno = sno LIMIT 1;
RETURN student_name;
END
8、 创建可以通过自定义函数来实现查看70-80分的学生人数
CREATE FUNCTION `getcount`() RETURNS int(11)
BEGIN
declare num int(4);
select count(*) into num from sc where grade in (70,80);
RETURN num;
END
9、在studentinfo数据库创建一个名称为cursor_proc的存储过程,在该存储过程中,创建一个名称为s_cursor的游标,对应的结果集为学生表student中学生学号sno和姓名sname,然后利用游标逐一从结果集中出出每一条记录,并显示各字段的值。
CREATE PROCEDURE `cursor_pro`()
begin
declare temp char(20)CHARSET utf8;
declare temp1 char(20);
declare done int default false;
declare s_cur Cursor for select sname,sno from student;
declare continue handler for not found set done=true;
create table res(sno int ,sname char(10) charset utf8);
open s_cur;
read_loop:loop
fetch s_cur into temp,temp1;
if done then
leave read_loop;
end if;
insert into res(sno,sname) values(temp1,temp);
end loop;
close s_cur;
end
10、结合游标和循环,按学号统计指定学生的总成绩(studentinfo.SC表)
CREATE PROCEDURE `cursor_pro2`(in id int,out socre int)
begin
declare temp int;
declare done int default false;
declare s_cur Cursor for select grade from sc where sno = id;
declare continue handler for not found set done=1;
open s_cur;
set socre = 0;
read_loop:loop
fetch s_cur into temp;
if done=1 then
leave read_loop;
else
set socre = socre + temp;
end if;
end loop;
close s_cur;
end