存储过程进阶

• MySQL 存储过程,共有三种参数类型 IN,OUT,INOUT
Create procedure 名称 (类型 参数名 数据类型 ,类型 参数名 数据类型
关键字 名称
in 输入参数  作用是给存储过程传值,必须在调用存储过程时赋值,在存储过程中该参数的值不允许修改;默认类型是 in，
out 输出参数  该值可在存储过程内部被改变,并可返回。
inout 输入 / 输出参数  调用时指定,并且可被改变和返回。
注意:此三中类型的变量在存储过程中调用时不需要加 @ 符号 !!!

举例说明：
mysql> delimiter //
mysql> create procedure say(in username char(10))           # 定义 in 类型的参数变量 username
-> begin
-> select username;
-> select * from user where name=username;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call say(“root”);  # 调用存储过程时给值。

 

变量类型
• 变量的种类 : 全局变量 \ 会话变量 \ 用户变量 \ 局部变量
会话变量 会话变量和全局变量叫系统变量 使用 set 命令定义;
全局变量的修改会影响到整个服务器,但是对会话变量的修改,只会影响到当前的会话。
用户变量 ：在客户端连接到数据库服务的整个过程中都是有效的。当前连接断开后所有用户变量失效。
定义 set
@ 变量名 = 值;
输出 select @ 变量名;
局部变量 存储过程中的 begin/end 。其有效范围仅限于该语句块中,语句块执行完毕后,变量失效。declare 专门用来定义局部变量。
注意:局部变量 和 参数变量 调用时 变量名前不需要加 @

举例说明：mysql> show global variables; // 查看全局变量
mysql> show session variables; // 查看会话变量
mysql> set session sort_buffer_size = 40000; // 设置会话变量
mysql> show session variables like “sort_buffer_size”; // 查看会话变量
mysql> show global variables like “% 关键字 %”; // 查看全局变量
mysql> set @y = 3; // 用户自定义变量,直接赋值
mysql> select max(uid) into @y from user; // 使用 sql 命令查询结果赋值

举例说明：

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
-> //
Query OK, 0 rows affected (0.03 sec)
mysql> delimiter ;

算数运算

mysql> set @z=1+2;select @z;
mysql> set @x=1; set @y=2;set @z=@x*@y; select @z;
mysql> set @x=1; set @y=2;set @z=@x-@y; select @z;
mysql> set @x=1; set @y=2;set @z=@x/@y; select @z;

举例说明：

mysql> drop procedure if exists say;
mysql> delimiter //
mysql> create procedure say(
in bash char(20), in nologin char(25), out x int , out y int
)
begin
declare z int ;
set z=0;
select count(name) into @x from db9.user where shell=bash;
select count(name) into @y from userdb.user where shell=nologin;
set
z=@x+@y;
select z;
mysql>
end
call say("/bin/bash","/sbin/nologin",@x,@y);
//
+-------+
mysql> delimiter ;

mysql>call say("/bin/bash","/sbin/nologin",@x,@y);

条件结构说明：

mysql> drop procedure if exists say;
mysql> delimiter //
mysql> create procedure say(in x int(1) )
begin
if x <= 10 then
select * from userdb.user where id <=x;
end if;
end
//
mysql> delimiter ;
mysql> call say(1); # 条件判断成立

循环结构

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

控制结构

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