数据库之 存储过程 (造数据)

存储过程

一、存储过程介绍

1、什么是存储过程？

存储过程是实现某个特定功能的sql语句的集合，编译后的存储过程会保存在数据库中，通过存储过程的名称反复的调用执行。

2、存储过程的优点？

（1）存储过程创建后，就可以反复的调用和使用，不需要重新写复杂的语句
（2）创建，修改存储过程不会对数据有任何的影响
（3）存储过程可以通过输入参数返回输出值
（4）通过存储过程中加入控制语句，可以加强sql语句的功能性和灵活性
（5）对于单个l语句增删改查，可以直接封装一个集合中，存储过程一旦创建就可以直接调用，且可以重复调用
（6）单个sql语句每一次执行都需要对数据进行编译，而存储过程被创建只需要编译一次，后续即可调用
（7）创建的存储过程，可以重复进行调用，可以减少数据库开发人员的工作量
（8）防止sql 注入
（9）造数据（重点）

3、Mysql 5.0版本后支持存储过程，存储过程=sql语句集合+控制语句

============================

格式：
delimiter // #分隔符 符号 //
create procedure 存储名( 参数名：in out inout)
begin #开始
sql语句 #执行语句
end #结束
// 分隔符号
call 存储名（参数） 调用一个存储过程

案例：

三、存储过程的基本语句:

(1) 删除存储过程

格式：drop procedure 存储名称

案例：drop procedure hz001

（2）查看单个存储过程的详情

格式：show create procedure 存储名称

案例：show create procedure hz001

（3）查看所有已经建好的存储过程详情

格式：show procedure status

（4）查询指定数据库里创建的存储过程

格式：show procedure status where db="数据库名"

案例：show procedure status where db="hz017"

四、创建存储

1、创建无参数存储过程

格式：
delimiter //
create PROCEDURE     存储名1() #无参数，是括号内无参数
BEGIN     #开始
sql语句    #执行语句
END       #结束
//  结束符号
call     存储名()      调用存储名称

案例：
delimiter //
create PROCEDURE hz1() #无参数，是括号内无参数
BEGIN
SELECT * from emp where dept2=101 ;
END
//
call hz1()

2、带有in参数的存储

in表示输入数据

格式：
delimiter //
create PROCEDURE   存储名(in x int ) #in参数，是括号
BEGIN
sql语句
END
//
call 存储过程(入参)

案例：
delimiter //
create PROCEDURE hz(in x int ) #无参数，是括号内无参数
BEGIN
SELECT * from emp where dept2=x ;
END
//
call hz(105)

3、带out 的参数（输出参数）

格式：
delimiter //
create PROCEDURE   存储名(out x int ) #in参数，是括号
BEGIN
sql语句
END
//
call 存储过程(入参)

 案例：
delimiter //
create PROCEDURE hz5(out y int ) #无参数，是括号内无参数
BEGIN
SELECT age into y from emp where dept2=105;
END
//
call hz5(@y)
select @y

---

带有 in 和 out 的存储过程

案例：
delimiter //
create PROCEDURE hz6(in x int ,out y int ) #无参数，是括号内无参数
BEGIN
SELECT age into y from emp where dept2=x;
END
//
call hz6(109,@y)
select @y

===========================================================================
inout参数 代表着输入和输出

案例1：
delimiter //
create PROCEDURE hz8(inout m int ) #无参数，是括号内无参数
BEGIN
set m=m+1;
END
//
set @m=2
call hz8(@m)
select @m

---

案例2：
delimiter //
create PROCEDURE hz9(inout x int ) #无参数，是括号内无参数
BEGIN
SELECT age into x from emp where dept2=x;
END
//

set @x=109
call hz8(@x)
select @x

备注：
CHARACTER set UTF8  解决？号

===========================================
用户变量：
定义语法L
set @ 变量名

赋值的方法：
（1）方式一
set @ 变量名：=值
或
set @变量名=值；
select @变量名：=值；

（2）方式二

通过查询结果为变量赋值：
select 字段 into 变量名 from 表名 where 条件

（3）declare 声明变量

例如：declare i int default 0 ；

============================================
循环语句：
三种：
（1）while ......do ......end while

while 循环语句：
格式：while 条件 do

sql语句

end while

(2) loop ......end loop

(3) repeat.......until ,,,,,end repeat

============================================
造数据场景：
1、通过存储过程在一个指定的空表中插入10条数据？

案例：
drop table ff ;
CREATE table ff(id int (10),age int(10));
select * from ff ;
delimiter //
drop procedure if exists hz10; #增强健壮性， 判断是否存在指定存储过程，如果存在就删除；
create procedure hz10()
BEGIN
DECLARE i int DEFAULT 0 ;
while (i<10) DO
INSERT into ff(id )VALUES (i);
set i=i+1;
end WHILE;
select * from ff ;
END
//
call hz10()

场景2：插入指定数据的行数

delimiter //
DROP table if EXISTS ff ;
drop procedure if exists hz10; #增强健壮性， 判断是否存在指定存储过程，如果存在就删除；
CREATE table ff(id int (10),age int(10));
create procedure hz10( in x int )
BEGIN
DECLARE i int DEFAULT 0 ;
while (i<x) DO
INSERT into ff(id )VALUES (i);
set i=i+1;
end WHILE;
select * from ff ;
END
//
call hz10(5)

场景三：指定插入的数据，并将已有的数据统计，在已有的数据基础上在添加

delimiter //
DROP table if EXISTS ff ;
drop procedure if exists hz10; #增强健壮性， 判断是否存在指定存储过程，如果存在就删除；
CREATE table ff(id int (10),age int(10));
create procedure hz10( in x int )
BEGIN
DECLARE i int DEFAULT (select Count(*) from ff ) ;
while (i<x) DO
INSERT into ff(id )VALUES (i+1);
set i=i+1;
end WHILE;
select * from ff ;
END
//
call hz10(100)

==============================================
面试题：

1、你会存储吗？

2、存储的结构？
delimiter//
create procedure 存储名
begin
sql语句
end
//
call 存储名

3、在工作中你们用存储做什么？ 造数据

4、你会写存储吗？

=============================================
if 语句
if 的单分支

if     条件  then
执行sql1
else 
执行sql2 
end  if  ；

案例：
delimiter //
create procedure hz11( in a int )

BEGIN
if a>10 THEN
SELECT * from emp ;
ELSE
SELECT * from dept;
end if;
END
//
call hz11(9)

一个if 对应一个end if

---

if 多分支

在if 判断语句汇总，有几个判断分支if，就有几个结束语 end if ；

if  条件  1   then
  执行sql1
else  if    条件2  then
  执行sql2
else  if   条件3  then
 执行sql3
end  if ；
end  if ；
end  if；
end

案例：
create procedure hz12( in a int )
BEGIN
if a>100 THEN
SELECT * from emp ;
ELSE if a<10 then
SELECT * from dept;
else if a=50 then
select * from student2 ;
ELSE
SELECT * from ff ;
end IF;
end IF;
end if;
END
//
call hz12(9)

=========================================

面试题：根据student学生表去写
1.当传入的参数（大于0）小于等于表里面数据的条数时，则根据分组显示班级的总成绩
2.当传入的参数大于表里面数据的条数时，则统计表里面的数据有多少条
3.当传入其他，则查询表里面的所有数据

 方法1：
delimiter //
create procedure hz09( in x int )
BEGIN
    DECLARE i int DEFAULT (select Count(*) from student2 );
    if x<=i and x>0 THEN
    SELECT sum(chinese+math+english) from student2 group by class;
    ELSE if x>i then
    SELECT count(*) from student2;
    else
    select * from student2;
    end if;
    end if;
END
//
call hz09(6)

方法2：
select * from xsb;
delimiter //
create procedure student1( in a int )
BEGIN 
if a>0 and a<=(select count(id) from xsb) THEN 
SELECT sum(english+chinese+math) from xsb GROUP BY class ;
ELSE if a>(select count(id) from xsb) then
SELECT count(id) from xsb;
ELSE
SELECT * from xsb ;
end IF;
end IF;
END
//