二十二、mysql存储过程

mysql存储过程
什么存储过程：
数据库中保存的一系列 sql 命令的集合；编写存储过程时,可以使用变量、条件判断、流程控制等；存储过程,就是 MySQL 中的脚本
存储过程优点： 提高性能、可减轻网络负担、可以防止对表的直接访问、避免重复的 sql 操作

2.1 基本使用：创建 查看 调用 删除
创建存储过程
delimiter // 修改命令结束符为//
create procedure 名称 ()
begin
功能代码
.....
.....
end //结束存储过程的功能代码
// 结束存储过程
delimiter ; 修改回命令结束符为；

查看存储过程
• 方法 1 ： mysql> show procedure status;
• 方法 2 ： mysql> select db,name,type from mysql.proc where name=“ 存储过程名 ";
mysql> select db,name,type from mysql.proc where name="say";
mysql.proc ：记录存储过程文件 db 库名 name名称 type 类型（FUNCTION 函数 ，PROCEDURE 存储过程） body具体代码

调用存储过程
call 存储过程名 (); //存储过程没有参数时, () 可以省略有参数时,在调用存储过程时,必须传参。
mysql> call say();

删除存储过程
drop procedure 存储过程名;
mysql> drop procedure say;

2.2 存储过程参数类型： in out inout
Create procedure 名称 (类型 参数名 数据类型 ,类型 参数名 数据类型) 默认类型是 in
in 输入参数 作用是给存储过程传值,必须在调用存储过程时赋值,在存储过程中该参数的值一般不修改，如果变化，值只在存储过程中有效;
delimiter //
create procedure p1(in nu char(20))
begin
select name,shell from db9.user where name=nu;
end//
delimiter ;

out 输出参数,不能用于传入参数值,该值可在存储过程内部被改变\返回;调用存储过程时，OUT参数也需要知道，但必须是变量，不能是常量。
delimiter //
create procedure p3( out num int(2) )
begin
select num; //空
set num=7;
select num; //7
select count(name) into num from db9.user where shell="/bin/bash";
select num; //2
end
//
delimiter ;
call p3(@num) //该值会重新赋予到外面的用户变量里，即select @num；会为2，不会为前面赋予的值

inout 输入/输出参数 调用时可传入值，在调用过程中，可修改其值，同时也可返回值,INOUT调用时传入的是变量，而不是常量

注意:此三中类型的变量在存储过程中调用时不需要加 @ 符号 !!!

2.3 mysql 变量类型：系统变量（会话变量 全局变量） 用户变量 局部变量
系统变量 会话变量和全局变量叫系统变量 使用 set 命令定义;全局变量的修改会影响到整个服务器,但是对会话变量的修改,只会影响到当前 的会话。
mysql> show global variables; // 查看全局变量
Mysql> show global variables like “% 关键字 %”; // 查看全局变量
mysql> show session variables; // 查看会话变量
mysql> set session sort_buffer_size = 40000; // 设置会话变量
mysql> show session variables like “sort_buffer_size”; // 查看会话变量

用户变量 在客户端连接到数据库服务的整个过程中都是有效的。当当前连接断开后所有用户变量失效。
定义 set @ 变量名 = 值;输出 select @ 变量名;
mysql> set @y = 3; // 用户自定义变量,直接赋值
mysql> select max(uid) into @y from user; // 使用 sql 命令查询结果赋值
mysql> select name from user where uid=0 into @y; //使用 sql 命令查询结果赋值

局部变量 存储过程中的 begin/end 。其有效范围仅限于该语句块中,语句块执行完毕后,变量失效。declare 专门用来定义局部变量。
mysql> delimiter //
mysql> create procedure say48()
-> begin
-> declare x int default 9; // 局部变量 x
-> declare y char(10); // 局部变量 y
-> set y = "jim";
-> select x;
-> select y;
-> end
-> //
mysql> delimiter ;

注意:局部变量 和 参数变量 调用时 变量名前不需要加 @

2.4 mysql运算符号 : + - * / DIV %
+ 加法运算 SET @var1=2+2; 4
- 减法运算 SET @var2=3-2; 1
* 乘法运算 SET @var3=3*2 ; 6
/ 除法运算 SET @var4=10/3; 3.333333333
DIV 整除运算 SET @var5=10 DIV 3; 3
% 取模 SET @var6=10%3 ; 1

delimiter //
create procedure p5()
begin
declare x int;declare y int;
declare z int;
select count(shell) into x from db9.user where shell=”/bin/bash”;
select count(shell) into y from db9.user where shell=”/sbin/nologin”;set z=x+y;select z;
end
//
delimiter ;

2.5 条件判断符号：
> >= < <= = != or and ! like regexp is null is not null in not in between....and....

2.6 流程控制：
if 顺序结构
1) if 条件判断 then
代码
.....
end if ;

delimiter //
create procedure p1( in num int(2) )
begin
if num <=10 then
select * from db9.user where id <= num;
end if;
end
//
delimiter ;

2) if 条件判断 then
代码
.....
else
代码
.....
end if;

delimiter //
create procedure p2( in num int(2) )
begin
if num <=10 then
select * from db9.user where id <= num;
else
select * from db9.user where id >=num;
end if;
end
//
delimiter ;

循环结构
while 条件判断 do
循环体
.......
end while ;

delimiter //
create procedure p24()
begin
declare i int;
set i=1;
while i <= 5 do
select i;
set i=i+1;
end while;
end
//
delimiter ;

loop //死循环
循环体
......
end loop ;

delimiter //
create procedure p25()
begin
declare i int;
set i=1;
loop
select i;
set i=i+1;
end loop;
end
//
delimiter ;

mysql> call say(); #不按ctrl+c 结束 会一直执行

repeat
循环体
until 条件判断 //until 条件判断,成立时结束循环 ,不需要加分号；
end repeat ;

delimiter //
create procedure say3( )
begin
declare i int;
set i=1;
repeat
select i;
set i=i+1; //i必须有初值（初值不为空），否则无法做运算
until i=6
end repeat;
end
//
mysql> delimiter ;

循环控制参数
LEAVE 标签名 // 跳出循环，结束循环的执行 ITERATE 标签名 / 放弃本次循环,执行下一次循环

create procedure say()
begin
declare i int;
set i=1;
loab1:loop // 定义标签名为 loab1
set i=i+1;
if i=3 then #i 值是 3 时结束本次循环
iterate loab1;
end if;
if i=7 then #i 值是 7 时 结束循环
leave loab1;
end if;
select i;
end loop;
end