数据库_存储过程练习

一、建表

drop table if exists student;
create table student
(id varchar(255), #学号
name varchar(255), #姓名
roomid varchar(255), #班级
sex char(1), #性别
degree varchar(3));#成绩;
Insert into student values('1','amy','8','0','65');
Insert into student values('2','bob','4','1','80');
Insert into student values('3','candy','6','0','70');
Insert into student values('4','danny','8','1','90');
Insert into student values('5','edward','8','1','95');
Insert into student values('6','frank','7','1','100');

二、题目

1. 创建一个存储过程，实现查询表student中的所有信息，并执行存储过程

delimiter//
drop procedure if exists st1;
create procedure st1()
begin
	select * from student;
end//
call st1()

2. 创建一个存储过程，根据给定的学生学号显示学生的姓名

delimiter//
drop procedure if exists st2;
create procedure st2(in sid char(10))
begin
	select name from student where id=sid;
end//
call st2('4')

3. 创建一个存储过程，根据所给的班级的编号，统计该班人数，并将人数以输出变量返回给用户。

delimiter//
drop procedure if exists st3;
create procedure st3(in room char,out res int)
begin
	select count(id) into res from student where roomid=room;
end//
delimiter;
call st3('8',@res)
select @res;

4. 创建一个存储过程，根据所给的学号查询该学生的考试分数是否超过了85分， 若超过则输出“ 考得好”， 否则输出 “考得不好”。

delimiter//
drop procedure if exists st4;
create procedure st4(in sid char(10),out res char(20) charset utf8)
begin
	declare core int default 0;
	select degree into core from student where id=sid;
	if core > '85' then
		set res = '考的好';
	else 
		set res = '考的不好';
	end if;
end//
delimiter;
call st4('4',@res);
select @res;

5. 创建一个存储过程，对表student增加两条记录。

// 审题错误
delimiter//
drop procedure if exists st5;
create procedure st5(in i int(10))
begin
	declare summ int default 0;
	select count(*) into summ from student;
	while summ<i do
		insert into student values(summ+1,concat('aaa',summ+1),'4','1','98');
		set summ=summ+1;
	end while;
end//
call st5(8);
select * from student;

delimiter//
drop procedure if exists st5;
create procedure st5(in i int(10))
begin
	declare summ int default 0;
	select count(*) into summ from student;
	insert into student values(summ+1,concat('aaa',summ+1),'4','1','98');
    insert into student values(summ+1,concat('bbb',summ+2),'8','0','67');
end//
call st5();
select * from student;

6. 请撰写一个存储过程，输出各班学生的平均成绩。

delimiter//
drop procedure if exists st6;
create procedure st6()
begin
	select roomid,avg(degree) from student group by roomid;
end//
call st6();

7. 请撰写一个存储过程,能根据用户输入的学号，输出学生的姓名、性别到两个参数变量中，以供其它程序使用。

delimiter//
drop procedure if exists st7;
create procedure st7(in sid char(10),out sname char(10),out ssex char(10))
begin
	select name,sex into sname,ssex from student where id=sid;
end//
call st7('4',@sname,@ssex);
select @sname,@ssex;

8. 撰写一个存储过程，根据用户输入的学号，输出学生性别，成绩两个参数放到新表temp中Create table temp(Sex varchar(255),Degree varchar(255));

两张表的字段一致，插入数据：
方法一：insert into 目标表 select * from 来源表；#插入全部数据
方法二：insert into 目标表（字段 ）select 字段1,字段2 from 来源表。

delimiter//
drop procedure if exists st8;
create procedure st8(in sid char(10))
begin
	declare x char default 0;
	declare y char default 0;
	drop table if exists temp;
	create table temp(sex char(255),degree char(255));
	select sex,degree into x,y from student where id=sid;
	insert into temp values(x,y);
end//
call st8('4');
select * from temp;

delimiter//
drop procedure if exists st8;
create procedure st8(in sid char(10))
begin
	drop table if exists temp;
	create table temp(sex char(255),degree char(255));
	insert into temp select sex,degree from student where id=sid;
end//
call st8('4');
select * from temp;

9. 求值

• 请撰写一个存储过程，求1+2+3+…x的值。

delimiter //
drop procedure if exists st9;
create procedure st9(in x int,out y int)
begin
	declare i int default 1;
	declare sum int default 0;
	while i<=x do
		set sum = sum+i;
		set i=i+1;
	end while;
	set y = sum;
end//
call st9(6,@y);
select @y;

请撰写一个存储过程，求0+2+4+…x的值。

delimiter //
drop procedure if exists st9;
create procedure st9(in x int,out y int)
begin
	declare i int default 0;
	declare sum int default 0;
	while i<=x do
		set sum = sum+i;
		set i=i+2;
	end while;
	set y = sum;
end//
call st9(6,@y);
select @y;

请撰写一个存储过程，求1+3+5+…x的值。

delimiter //
drop procedure if exists st9;
create procedure st9(in x int,out y int)
begin
	declare i int default 1;
	declare sum int default 0;
	while i<=x do
		set sum = sum+i;
		set i=i+2;
	end while;
	set y = sum;
end//
call st9(7,@y);
select @y;

三、总结错误

语法类

1. 语句末尾忘记写;

• begin…end 内部每一条 SQL 结尾必须加分号，少写直接报错。

2. delimiter// 规范写法 delimiter //【这个不影响】
3. CHAR 不写长度：char → char(10)，直接 char 会告警。【虽然不写没事】
4. 输出结果是中文但显示???时，在创建存储过程的时候out输出值后面添加charset utf8

变量declare

1. DECLARE 必须放在 BEGIN 块的最开头，不能放在 drop table /select/insert 后面！
2. DECLARE ... DEFAULT 后面只能写常量，不能写 SELECT 子查询
3. 两种赋值方式的区分

• 给局部或out变量赋值：多条字段只写一次into，逗号分隔，顺序一一对应
• 简单赋值：set 变量=值

4. select ... into 只能接收 1 行结果，多行就报错：Result consisted of more than one row【GROUP BY 分组大概率返回多行，不能搭配 INTO，直接 select 输出表格。】

参数 in/out

1. OUT 参数定义了，代码里面必须 SET 赋值，不然调用后 @变量为空。
2. IN 是入参，可以传常量。

循环

1. WHILE 循环内部一定要更新循环变量，否则死循环。