MySQL存储过程

1、定义

　　存储过程是数据库中的一个重要对象，可以把它理解成是一个“函数‘存在数据库中，如果需要调用存储过程，就可以直接输入函数名称来调用，和python里面的方式差不多。

　　函数内部就是完成特定功能的SQL语句集，比如生成几百条测试数据等，当我们需要用到几百条数据的时候，就直接调用这个存储过程的函数名来运行，直接生成几百条数据，而不需要重复的去写很多SQL语句。

 

2、特点

　　用来完成较复杂业务，比较灵活，易修改，好编写，可编程性强，编写好的存储过程可以重复使用。

 

3、存储过程优缺点

　　优点： 效率高，可以被重复使用，安全

　　存储过程在创建的时候直接编译，sql语句每次使用都要编译，效率高。

　　存储过程可以被重复使用。

　　存储过程只连接一次数据库，sql语句在访问多张表时，连接多次数据库。

　　存储的程序是安全的。存储过程的应用程序授予适当的权限。

　　

　　缺点：移植性差，维护麻烦，很复杂的业务，存储过程也不行。

　　在哪里创建的存储过程， 就只能在哪里使用，可移植性差。

　　开发存储过程时，标准不定好的话，后期维护麻烦。

　　没有具体的编辑器，开发和调试都不方便。

　　太复杂的业务逻辑，存储过程也解决不了。

 

4、创建最简单的存储过程

　　

create procedure  过程名()
begin
    .....这里写任意的sql语句
end;

　案例： 查看员工与部门表的全信息

create procedure dept_emp()
begin
    select * from dept;
    select * from emp;
end;




mysql> call dept_emp();

　4.1 存储过程变量

格式：

　　declare  变量名 变量类型 default 默认值; #声明变量

　　set 变量名 = 值; #变量赋值

　　select  字段名 into 变量名 from  数据库表;  #查询表中字段，完成变量赋值

　　select  变量名; #显示变量

create procedure emp_name()
begin 
declare ename varchar(20) default ";
select name into ename from emp where id = 1;
select ename;
end;


mysql> call emp_name();

5、存储过程传入参数

格式：

create procedure 过程名([IN] OUT [INOUT] 参数名 参数数据类型)

begin

....

end;

注意： in： 传入参数     out：传出参数   inout：可以传入也可以传出

核心是传入，对于测试而言，只需要搞懂这个就够了

in表示该参数的值必须在调用存储过程时指定，如果不显示指定为in ，那么默认就是in类型。

案例： 根据传入的id查看员工的姓名。

create procedure emp_id(eid int)

begin

declare ename varchar(20) default ";

select name into ename from emp where id = eid;

select ename;

end;


mysql> call emp_id(1);

　6、存储过程条件判断

格式：

if()

then

...

else

...

end if;

 

案例：输入一个id，判断它是否是偶数，偶数打印对应的姓名，奇数打印id。

create procedure emp_if_id(eid int)
begin
declare ename varchar(20) default ";
if(eid % 2 = 0)
then
select name into ename from emp where id = eid;
select ename;
    else
    select eid;
    end if;
end;




mysql>call emp_if_id(1);


mysql>call emp_if_id(2);

 

7、存储过程查看、删除

查看存储过程格式：

show procedure status;

 

删除存储过程格式：

drop procedure 存储过程名;

 

8、存储过程的循环语句（重点）

循环语句在任意一个编程语言里面都存在，在MySQL里面主要循环语句分为三类：while循环（重点）、repeat循环、loop循环。

while循环也是最常用的循环语句，它会持续执行循环体内的语句，直到指定的条件不再为真。

语法：

create procedure LoopExample()

begin 

　　declare i int default 0; ----定义并初始化一个变量i

　　while i < 5 do

　　　　set i = i + 1;

　　　　---这里执行你需要的操作，例如插入数据到表中

　　　　--insert into some_table(column) values(i);

　　end while;

end;

 

repeat 循环（了解）

repeat循环会先执行循环体内的语句，然后检查条件，如果条件为假，则继续执行循环体内的语句；如果条件为真，则退出循环。

create procedure RepeatExample()

begin

　　declare i int default 0;

　　repeat

　　　　set i = i + 1;

　　　　select concat('Loop', i);

　　until i > 5 end repeat;

end;

Loop循环提供了一种更灵活的方式来控制循环的执行。你可以为循环体命名，并使用leave 和iterate 标签来控制循环的流程。

create procedure RepeatLoopExample()

begin

　　declare i int default 0;

　　my_loop:LOOP

　　　　SET i =  i + 1;

　　　　select concat('Loop', i);

　　　　

　　　　if i > = 5 THEN

　　　　　　LEAVE my_loop; ---当条件满足时退出循环

　　　　else　　

　　　　　　iterate  my_loop;  --继续下一次迭代

　　　　end if;

　　end LOOP my_loop;

end;

 

 

9、面试题

　　你使用过存储过程吗？使用存储过程来实现什么逻辑？[重点]

　　　　主要是通过存储过程中的循环语句 + insert into 来插入数据，达到造数据的目的。

例如：创建一个用户表，id自增，在用户表中插入1000条记录。

create PROCEDURE test05()

begin 

　　declare i int;　　

　　set i = 1;

　　while i <= 1000 do

　　　　insert into `user` (username,password,age,status) 

　　　　values(concat('jack',i)"e10adc3949ba59abbe56e057f20f883e",20,0);

　　　　set i = i+1;

　　　　end while;

end;

 

call test_05();