肖SIR__数据库之存储过程 练习题__16.2

实验
一、

实验要求：

理解存储过程的概念
掌握存储过程的语法格式、使用方法
掌握存
储过程的创建、执行
二、实验前提：
– 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中的记录信息，并执行存储过程

结果：select  * from  student

建一个存储

查询所有信息
方法1：

delimiter //
create PROCEDURE cj()
BEGIN
SELECT * from student;
END
//
call cj()

　　

2:创建一个存储过程，根据给定的学生学号返回该学生的姓名
in  输入学号

out  返回
方法一：直接输入学号查询出来的方法

Delimiter //
CREATE PROCEDURE proc2( IN id int)
BEGIN

SELECT name from student s WHERE s.id=id;

END
//

Call proc2(1);

方法二：输入编号，在返回姓名

Delimiter //
CREATE PROCEDURE proc12( IN id int,out x varchar(255))
BEGIN

SELECT name into x from student s WHERE s.id=id;

END
//

Call proc12(1,@x);
select @x

3:创建一个存储过程，根据所给的班级的编号，统计该班人数，并将人数以输出变量返回给用户。
方法一：
delimiter //
create procedure cc(in b varchar(255) , out c int(10))
begin
select count(id) into c from student where Roomid=b;
END
//
call cc(8,@c)
select@c

方法二：
delimiter //
create procedure cc(in b varchar(255) , out c int(10))
begin
select count(id) into c from student where Roomid=b;
END
//
call cc(8,@c)
select@c

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

id

85 “考的好”
考的不好
方法一：
delimiter //
drop procedure if EXISTS mm;
create procedure mm(in x int(10),out y int(255))
BEGIN
select degree into y from student where id=x;
select if (y>85, ‘考得好’,‘考的不好’ );
end
//
call mm(6,@y)

方法二：
delimiter //
drop procedure if EXISTS mm;
create procedure mm(in x int (10))
BEGIN
declare i char(255) DEFAULT (select degree from student where id=x);
select if (i>85,‘考得好’,‘考的不好’);
end
//
call mm(6)
方法三：
delimiter//
DROP PROCEDURE IF EXISTS dd;
CREATE PROCEDURE dd(in x varchar(255))
BEGIN
declare i INT ;
set i=(SELECT cast((SELECT degree FROM student where Id=x) as signed)) ;
IF i >85 then
SELECT ‘考得好’ ;
ELSE
SELECT ‘考得不好’;
END IF;
END
//
call dd(1);
方法四：
drop procedure if exists pro;
delimiter//
create procedure pro(in x int)
BEGIN
if (select degree from student where id=x)>85 then
select ‘考得好’;
else
select ‘考得不好’;
end if;
END
//
call pro(2);

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

1，已建表，在插入数据

方法一：
delimiter //
drop procedure if exists dd;
create procedure dd(in x varchar(255),in y varchar(255),in z varchar(255),in m char(1),in n varchar(3))
BEGIN
declare i int default 0;
Insert into student values(x,y,z,m,n);
END
//
call dd(7,‘adv’,4,1,59);
select * from student;
方法二：
delimiter //
DROP PROCEDURE if EXISTS ee;
CREATE PROCEDURE ee()
BEGIN
DECLARE i int DEFAULT (SELECT count(*) from student);
INSERT into student (Id) VALUES(i+1);
INSERT into student (Id) VALUES(i+2);
SELECT * from student;
end
//
call ee();

方法三：
drop procedure if exists pro;
delimiter//
create procedure pro(in x int)
BEGIN
declare i int default 0;
select max(id) into i from student;
while i<x do
insert into student(id) values(i+1);
set i=i+1;
end while;
END
//delimiter;
call pro(3);

方法四：
drop procedure if exists pro;
delimiter//
create procedure pro(in x int)
BEGIN
declare i int default 0;
declare j int default 0;
select max(id) into i from student;
set j=i;
while i<j+x do
insert into student(id) values(i+1);
set i=i+1;
end while;
END
//delimiter;
call pro(3);
select * from student;

方法五：
delimiter //
drop procedure if exists ee;
create procedure ee()
begin
insert into student values(“7”,“aaa”,“6”,“0”,“88”),(“8”,“bbb”,“4”,“1”,“89”);
END
//
call ee();

6:请撰写一个存储过程，输出各班学生的平均成绩。
select avg(degree) from student group by roomid;
方法：
delimiter//
DROP PROCEDURE IF EXISTS ff;
CREATE PROCEDURE ff()
BEGIN
SELECT Roomid,avg(Degree) FROM student GROUP BY Roomid;
END
//
call ff();

7:请撰写一个存储过程,能根据用户输入的学号，输出学生的姓名、性别到两个参数变量中，以供其它程序使用。
方法一：
delimiter//
DROP PROCEDURE IF EXISTS gg;
CREATE PROCEDURE gg(in x VARCHAR(10),OUT y varchar(10),out z char(10) )
BEGIN
SELECT Name,Sex INTO y,z FROM student WHERE Id=x;
END
//
call gg(1,@y,@z);
SELECT @y,@z;
方法二：
delimiter //
drop procedure if exists ff;
create procedure ff(in x int,out y char(20),out z char)
BEGIN
select Name into y from student where Id=x;
select Sex into z from student where Id=x;
END
//
call ff(3,@y,@z);
select @y,@z;

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 gg;
create procedure gg(in x varchar(255))
begin
declare a varchar(255) default (select sex from student where id=x);
declare b varchar(255) default (select degree from student where id=x);
drop table if exists temp;
create table temp(Sex varchar(255),Degree varchar(255));
insert into temp values(a,b);
select *from temp;
end
//
call gg(“6”);

方法二：
delimiter //
drop procedure if exists gg;
drop table if exists temp;
create procedure gg(in x int)
BEGIN
declare m char(20) default 0;
declare n char default 0;
select Degree into m from student where Id=x;
select Sex into n from student where Id=x;
Create table temp(Sex varchar(255),Degree varchar(255));
insert into temp values(m,n);
END
//
call gg(2);
select * from temp;

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

（1）
方法一：（利用计算公式(i+1)*i/2;）
delimiter //
drop PROCEDURE if exists yy;
create procedure yy(in i int)
BEGIN
declare j int ;
set j=(i+1)*i/2;
select j;
end
//
方法二：
call mm(4);delimiter //
DROP PROCEDURE if EXISTS mm;
CREATE PROCEDURE mm(in x VARCHAR(255))
BEGIN
DECLARE a int DEFAULT 1;
DECLARE b int DEFAULT 0;
WHILE (a<x+1) DO
set b=b+a;
set a=a+1;
END WHILE;
SELECT b;
end
//
call mm(8);
call yy(6)
方法三：
delimiter //
drop PROCEDURE if exists yy;
create procedure yy(in i int,out y int)
BEGIN
declare j int default 0 ;
while (i>0) DO
set j=i+j;
set i=i-1;
end while;
set y=j;
end
//
call yy(3,@x)
select @x
方法四：
delimiter//
DROP PROCEDURE if EXISTS xx;
CREATE PROCEDURE xx(IN x int )
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE x1 int ;
set x1=x;
WHILE (i<x) DO
set x1=i+x1;
set i=i+1;
END WHILE ;
set x=x1;
SELECT x;
END
//
call xx(9);

(2)偶数和
call mm(4);delimiter //
DROP PROCEDURE if EXISTS mm;
CREATE PROCEDURE mm(in x VARCHAR(255))
BEGIN
DECLARE a int DEFAULT 2;
DECLARE b int DEFAULT 0;
WHILE (a<x+1) DO
set b=b+a;
set a=a+2;
END WHILE;
SELECT b;
end
//
（3）奇数和
call mm(4);delimiter //
DROP PROCEDURE if EXISTS mm;
CREATE PROCEDURE mm(in x VARCHAR(255))
BEGIN
DECLARE a int DEFAULT 2;
DECLARE b int DEFAULT 0;
WHILE (a<x+1) DO
set b=b+a;
set a=a+1;
END WHILE;
SELECT b;
end
//