肖sir__mysql之存储练习题__013

实验
一、

实验要求：

1. 理解存储过程的概念
2. 掌握存储过程的语法格式、使用方法
3. 掌握存

储过程的创建、执行
二、实验前提：
– 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 value

s(‘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中的记录信息，并执行存储过程
建一个存储
结果：查询表student中的记录信息，
方法1：
delimiter //
drop procedure IF EXISTS xx;
CREATE PROCEDURE xx ()
BEGIN
SELECT * FROM student;
END//

call xx;

2:创建一个存储过程，根据给定的学生学号返回该学生的姓名
结果：学生姓名
条件：
语句：
select name from student wehre id =x
select name into y from student wehre id =x
方法：
delimiter //
create procedure student3(in x int(10),out y varchar(10))
BEGIN
select Name into y from student where id=x;
END //

call student3(1,@y)
select @y

3:创建一个存储过程，根据所给的班级的编号，统计该班人数，并将人数以输出变量返回给用户。
结果：
条件：roomid =x
select count（name） into y from roomid= x
方法：
delimiter //
create PROCEDURE student10(in z varchar(225),out x varchar(225))
begin
select count(id)into x from student where roomid =z;
END
//

call student10(“8”,@x)
select @x

方法2：
delimiter //
create PROCEDURE p3 (in x varchar(255) , out v int(10))
begin

SELECT count(student.Id) into v from student where student.Roomid=x ;

SELECT @v;

END//

call p3(‘8’,@v);
4：创建一个存储过程，根据所给的学号查询该学生的考试分数是否超过了85分， 若超过则输出“ 考得好”， 否则输出 “考得不好”。

结果：

85 “考的好”
考的不好

条件：select degree from student where id =x
判断if

方法1：
delimiter //
DROP PROCEDURE IF EXISTS stu4;
CREATE PROCEDURE stu4(IN x VARCHAR(225))
BEGIN
IF (SELECT Degree FROM student s WHERE x=s.Id)>85 THEN
SELECT “考得好”;
ELSE
SELECT “考得不好”;
END IF;
END
//
CALL stu4(5)

方法2：
delimiter //
create procedure student5(in x varchar(255), out y varchar(255))
BEGIN
select level into y from (select *, case when Degree >85 then “考得好” when Degree <=85 then “考得不好” end level from student) a where Id=x;
END
//

call student5(4,@y)
select @y

方法3：
drop procedure studentresult;
delimiter//
create procedure studentresult(in stuid varchar(20),out sturesult varchar(20))
begin
select degree into sturesult from student where id = stuid;
if(sturesult > 85) then
select ‘考得好’ ;
elseif(sturesult < 85) then
select ‘考得不好’ ;
end if;
end;
//
call studentresult(4,@sturesult);
方法4：
delimiter//
create procedure p6(in x int(10) )
BEGIN
DECLARE exam_score int DEFAULT (SELECT Degree FROM student WHERE Id = x);

IF exam_score > 85 THEN
    SELECT '考得好' ;
ELSE
    SELECT '考得不好' ;
END IF;
1
2
3
4
5

END//

call p6(4)

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

1，已建表，在插入数据
方法1：
drop procedure studentinsert;
delimiter//
create procedure studentinsert()
begin
insert into student values(7,‘mary’,7,1,75);
insert into student values(8,‘smith’,6,0,92);
end
//
call studentinsert();
select * from student;
方法2：
delimiter //
CREATE procedure cc()
BEGIN
declare i int DEFAULT (select count(id) from student) ;
insert into student(id) VALUES(i+1) ;
insert into student(id) VALUES(i+2) ;
select * from student ;
END
//

call cc()

2、不存在表，在如数据
a、方法1：

delimiter//
create procedure p7(in a int(10),in b VARCHAR(255),in a1 int(10),in b1 VARCHAR(255))
BEGIN
drop table if EXISTS s1;
create table s1(sid int(10) PRIMARY key,sname VARCHAR(255) );
insert into s1 VALUES (a,b);
insert into s1 VALUES (a1,b1);
SELECT * from s1;
end//
call p7(9,‘aa’,10,‘bb’)

6:请撰写一个存储过程，输出各班学生的平均成绩。
结果：avg(degree)
分组： roomid
select avg(degree) from student group by roomid;
方法：
drop procedure studentavg;
delimiter//
create procedure studentavg()
begin
select avg(degree) from student group by roomid;
end
//
call studentavg();

7:请撰写一个存储过程,能根据用户输入的学号，输出学生的姓名、性别到两个参数变量中，以供其它程序使用。
结果：姓名、性别
条件：
方法1select name，sex into m，n from id=x

drop procedure if exists studentnamesex ;
delimiter//
create procedure studentnamesex(in stuid varchar(20),out stuname varchar(20),out stusex char(10))
begin
select name,sex into stuname ,stusex from student where id = stuid;
end
//
call studentnamesex(1,@stuname,@stusex);
select @stuname,@stusex;

方法2：
select name into m from id=x
select sex into n from id=x

drop procedure if exists studentnamesex ;
delimiter//
create procedure studentnamesex(in stuid varchar(20),out stuname varchar(20),out stusex char(10))
begin
select name into stuname from student where id = stuid;
select sex into stusex from student where id = stuid;
end
//
call studentnamesex(1,@stuname,@stusex);
select @stuname,@stusex;

方法3：
drop procedure if exists studentnamesex ;
delimiter//
create procedure studentnamesex(in stuid varchar(20))
begin
set@y=(select name from student where id = stuid);
set@z=(select sex from student where id = stuid);
select @y,@z ;
end
//
call studentnamesex(1);

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

结果：性别，成绩
条件：输入的学号 ，建表

方法1：
delimiter //
drop procedure if EXISTS student2 ;
create procedure student2(in x int(10))
BEGIN
drop table if exists temp;
create table temp(Sex1 char(1) , Degree1 varchar(3));
insert into temp(Sex1,Degree1) select Sex,Degree from student where x=Id;
select * from temp;
end
//

call student2(2)

方法2：

delimiter //
drop PROCEDURE if EXISTS p9 ;
create PROCEDURE p9( in I int(10),out s VARCHAR(255),out d VARCHAR(255))
BEGIN

SELECT Sex,Degree into s,d from student where Id = I ;
SELECT @s,@d;
drop table if EXISTS temp;
Create table temp(Sex varchar(255),Degree varchar(255));
insert into temp VALUES(@s,@d);
SELECT * from temp;
END

call p9(2,@s,@d)
方法3：

delimiter //
DROP PROCEDURE if exists s4;
CREATE PROCEDURE s4(in x int, out y char(1), out z varchar(3) )
BEGIN

SELECT sex,degree into y,z from student where id=x;
Create table if not EXISTS temp(Sex varchar(255),Degree varchar(255));
insert into temp VALUES(y,z);
END
//
CALL s4(6,@y,@z);
select @y,@z;
select * from temp;

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

9:请撰写一个存储过程，求1+2+3+…x的值。
方法1：
delimiter //
drop procedure if exists he;
create procedure he(inout x int(10))
BEGIN
set x=x*(x+1)/2;
END

set @x=10
call he(@x)
select @x

方法2：
drop procedure if exists studentadd1;
delimiter//
create procedure studentadd1(in x varchar(20))
begin
declare result varchar(255) default 0;
while x > 0 do
set result = result + x;
set x = x - 1;
end while;
select result;
end;
//
call studentadd1(10);

方法3：

Delimiter //
Create procedure stu9(in x VARCHAR(225))
Begin
Declare i int default 1;
Declare num int default 0;
While i<=x do
Set num=num+i;
Set i=i+1;
End while;
Select num;
End
//
call stu9(10)

请撰写一个存储过程，求0+2+4+…x的值。
方法1：
delimiter //
drop PROCEDURE if EXISTS p11;
create PROCEDURE p11(in x int)
BEGIN

DECLARE i int DEFAULT 0;
DECLARE s int DEFAULT 0;
WHILE i<=x do

set s=i+s;
set i=i+2;
end WHILE ;
select s;
END//

call p11(10)
方法2：
drop procedure if exists studentadd1;
delimiter//
create procedure studentadd1(in x varchar(20))
begin
declare result varchar(255) default 0;
while x > 0 do
set result = result + x;
set x = x - 2;
end while;
select result;
end;
//
call studentadd1(10);

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

方法：
delimiter //
drop PROCEDURE if EXISTS p11;
create PROCEDURE p11(in x int)
BEGIN

DECLARE i int DEFAULT 1;
DECLARE s int DEFAULT 0;
WHILE i<=x do

set s=i+s;
set i=i+2;
end WHILE ;
select s;
END//

call p11(10)

方法2：
drop procedure if exists studentadd1;
delimiter//
create procedure studentadd1(in x varchar(20))
begin
declare result varchar(255) default 0 ;
while x > 0 do
set result = result + x;
set x = x - 2;
end while;
select result;
end;
//
call studentadd1(9);