参数类型
• MySQL 存储过程,共有三种参数类型 IN,OUT,INOUT
Create procedure 名称 (类型 参数名 数据类型 ,类型 参数名 数据类型)
in 输入参数 作用是给存储过程传值,必须在调用存储过程时赋值,在存储过程中该参数的值不允许修改;默认类型是 in
out 输出参数 该值可在存储过程内部被改变,并可返回
inout 输入 / 输出参数 调用时指定,并且可被改变和返回
注意:此三中类型的变量在存储过程中调用时不需要加 @ 符号 !!!
实验:
mysql> delimiter //
mysql> create procedure p4(in shellname char(20)) //默认就是in参数
-> begin
-> select count(name) from user where shell=shellname;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call p4("/bin/bash");
+-------------+
| count(name) |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> call p4("/sbin/nologin");
+-------------+
| count(name) |
+-------------+
| 35 |
+-------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> call p4();
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE db9.p4; expected 1, got
给输入参数给与变量:
mysql> set @x = "/sbin/nologin"; //只有用户变量需要加@
Query OK, 0 rows affected (0.00 sec)
mysql> call p4(@x);
+-------------+
| count(name) |
+-------------+
| 35 |
+-------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
out 输出参数 该值可在存储过程内部被改变,并可返回
实验:
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter //
mysql> create procedure p7(out number int(2))
-> begin
-> set number = 9;
//定义变量不能加@符合,加了就相当于用户定义参数
-> select number;
-> select count(id) into number from user;
-> select number;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call p7();
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE db9.p7; expected 1, got 0
mysql> select @w; //给于用户定义变量,
相当于占位
+------+
| @w |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
mysql> call p7(@w);
+--------+
| number |
+--------+
| 9 |
+--------+
1 row in set (0.00 sec)
+--------+
| number |
+--------+
| 41 |
+--------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> call p7(8);
ERROR 1414 (42000): OUT or INOUT argument 1 for routine db9.p7 is not a variable or NEW pseudo-variable in BEFORE trigger
//out参数不能赋值,只能输出;
算法运算:
算数运算
• 算数运算符号
例子
+ 加法运算 SET @var1=2+2; 4
- 减法运算 SET @var2=3-2; 1
* 乘法运算 SET @var3=3*2 ;
/ 除法运算 SET @var4=10/3; 3.333333333
DIV 整除运算 SET @var5=10 DIV 3; 3
%取模 SET @var6=10%3 ; 1
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> delimiter //
mysql> create procedure say(in bash char(10),in nologin char(15),out x int(2),out y int(2))
-> begin
-> declare z int(2);
-> set z =0;
-> select count(shell) into x from user where shell=bash;
-> select count(shell) into y from user where shell=nologin;
-> set z = x + y;
-> select z;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call say("/bin/bash","/sbin/nologin",@x,@y);
+------+
| z |
+------+
| 37 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)