存储过程
一、存储过程介绍
1、定义：存储过程是实现某个特点功能的sql语句的集合，编译后的存储过程会保存在数据中，通过存储过程的名称反复的调用执行
2、存储过程的优点
（1）存储创建以后就可以反复的调用，不需要写复杂的语句
（2）存储过程可以添加输入和输出参数实现变量 ；例如：in out inot
（3）存储过程可以加入控制语句，增加sql语句的功能性和灵活 ； if
（4）创建存储过程可以减少数据开发人员的工作量
（5）防止sql注入；
（6）造数据；（重点）
二、存储过程的格式
（1）创建存储过程
格式：
#分隔符
delimiter//
drop PROCEDURE if exists 存储名称; #判断是否存在存储，存在删除
create PROCEDURE 存储名称()
BEGIN
sql语句
END
//
call 存储名称（）
案例：
#分隔符
delimiter //
drop PROCEDURE if exists cc1;
CREATE PROCEDURE cc1()
BEGIN
select * from emp ;
select * from dept;
select * from student;
END
//
call cc1()
（2）查看已经建好存储过程
格式：
show create procedure 存储名称 ;
案例：
show create procedure cc1 ;
（3）查看所有的存储过程
格式：
show procedure status ;
案例：
show procedure status ;
（4）删除存储过程
格式：
drop procedure 存储名
案例：
drop procedure c1
（5）判断是否存在新建名的存储，存在则删除，这条语句增强健壮性
drop PROCEDURE if EXISTS 存储名;
案例：
drop PROCEDURE if EXISTS cc1;
三、存储过程的运用
（1）创建无参数存储过程
案例：
delimiter//
drop PROCEDURE if EXISTS cum;
create PROCEDURE cum()
BEGIN
select * from emp where dept2=101;
END
//
call cum()
（2）创建带有in的存储过程
案例2：
delimiter//
drop PROCEDURE if EXISTS cum;
create PROCEDURE cum(in x int) #in是传入参数
BEGIN
select * from emp where dept2=x;
END
//
call cum(102)
（3）创建带有out 的存储过程
delimiter//
drop PROCEDURE if EXISTS cum;
create PROCEDURE cum(out y int)
BEGIN
select age into y from emp where sid=1565;
END
//
call cum(@y)
select @y 显示变量值
注意点：
用户变量：
方式1：
set @ 变量名：=值
或者
set @变量名=值
select @变量名：=值
方法2：
通过查询结果为变量值
select 字段名 into 变量名 form 表名 where 条件
方法3：
declare 声明 变量
例如：
declare i int default 0 ；
（4）创建带有in ，out 的存储过程
案例：
delimiter//
drop PROCEDURE if EXISTS cum;
create PROCEDURE cum(in x int,out y int)
BEGIN
select age into y from emp where sid=x;
END
//
call cum(1776,@y)
select @y
（5）创建带有inout 的存储过程
案例：
delimiter//
drop PROCEDURE if EXISTS cum;
create PROCEDURE cum(inout m int)
BEGIN
set m=m+1;
END
//
set @m=2
call cum(@m)
select @m
四、存储造数据
1、循环语句while语句(重点讲)
格式：while 条件 do
sql语句
end while
（2）loop ....end loop
（3）repeat until ....end repeat
2、造数据
（1）新建一个表
例如：
create table s9(id int(10) PRIMARY key ,name VARCHAR(20)) ;
（2）插入固定的数据：（0-9）
select * from s9 ;
create table s10(id int(10) ,name VARCHAR(20)) ;
delimiter//
drop PROCEDURE if EXISTS zs;
create PROCEDURE zs()
BEGIN
DECLARE i int DEFAULT 0;
while (i<10) DO
INSERT into s10(id) VALUES(i);
set i=i+1;
END WHILE;
select *from s9;
END
call zs()
（2）插入指定的数量的数据（100或1000）
create table s10(id int(10) ,name VARCHAR(20)) ;
delimiter//
drop PROCEDURE if EXISTS zs;
create PROCEDURE zs(in x int)
BEGIN
DECLARE i int DEFAULT 0;
while (i<x) DO
INSERT into s10(id) VALUES(i);
set i=i+1;
END WHILE;
select *from s10;
END
call zs(1000)
(3)插入数据，将表中已经存在的数据进行统计，在已有的基础上添加数据
delimiter//
drop PROCEDURE if EXISTS zs;
create PROCEDURE zs(in x int)
BEGIN
DECLARE i int DEFAULT (select count(*) from s10);
while (i<x) DO
INSERT into s10(id) VALUES(i);
set i=i+1;
END WHILE;
select *from s10;
END
call zs(15)
（4）插入数据，每次会新建表，重新插入数据
delimiter//
drop PROCEDURE if EXISTS zs;
create PROCEDURE zs(in x int)
BEGIN
DECLARE i int DEFAULT 0;
drop table if EXISTS s10 ;
create table s10(id int(10),name VARCHAR(20)) ;
while (i<x) DO
INSERT into s10(id) VALUES(i);
set i=i+1;
END WHILE;
select *from s10;
END
call zs(3)
五、 if语句
（1）if单分
格式：
if 条件 THEN
sql语句1
ELSE
sql语句2
end IF;
注意点一个if对应一个end if
案例：
delimiter//
drop PROCEDURE if EXISTS zs;
create PROCEDURE zs(in x int)
BEGIN
if x=10 THEN
select * from emp;
ELSE
select * from dept;
end IF;
end
//
call zs(1)
（2）if多分支
if 条件1 then
sql1
else if 条件2 then
sql2
else if 条件3 then
sql3
else if 条件4 then
sql4
else
sql5
end if;
end if;
end if;
end if;
案例：
delimiter//
drop PROCEDURE if EXISTS zs;
create PROCEDURE zs(in x int)
BEGIN
if x=10 THEN
select * from emp;
else if x=1 THEN
select * from mm;
else if x=0 THEN
select * from nn;
else if x=100 THEN
select * from xx;
ELSE
select * from dept;
end IF;
end if;
end if;
end if;
end
//
call zs(99)
作业：
面试题：根据student学生表去写
1.当传入的参数（大于0）小于等于表里面数据的条数时，则根据分组显示班级的总成绩
2.当传入的参数大于表里面数据的条数时，则统计表里面的数据有多少条
3.当传入其他，则查询表里面的所有数据
delimiter//
drop PROCEDURE if EXISTS pd;
create PROCEDURE pd(in x int)
BEGIN
DECLARE i int DEFAULT(select count(*) from student2);
if x>0 and x<=i THEN
select sum(english+chinese+math) from student2 group by class ;
ELSE if x>i THEN
select count(*) from student2;
ELSE
select * from student2 ;
end if;
end IF;
END
//
call pd(-1)
