MySQL-存储过程代码笔记-in、out、inout、declare局部变量和if...else语句

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> use test;
Database changed
mysql> create table testA
    -> (
    -> id int not null,
    -> num int not null
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> delimiter $$
mysql> insert into testA values (1,10),(2,20),(3,30)$$
Query OK, 3 rows affected (0.86 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create procedure protest1()        //不带参数的函数过程
    -> begin
    -> select id, num from testA;
    -> end $$
Query OK, 0 rows affected (0.87 sec)

mysql> call protest1 $$
+----+-----+
| id | num |
+----+-----+
|  1 |  10 |
|  2 |  20 |
|  3 |  30 |
+----+-----+
3 rows in set (0.03 sec)

Query OK, 0 rows affected (0.05 sec)

mysql> create procedure protest2 (in cid int, in cnum int)       
                             //使用in 带参数的函数过程
    -> begin
    -> update testA set num = cnum where id = cid;
    -> end $$
Query OK, 0 rows affected (0.84 sec)

mysql> select * from testA $$
+----+-----+
| id | num |
+----+-----+
|  1 |  10 |
|  2 |  20 |
|  3 |  30 |
+----+-----+
3 rows in set (0.00 sec)

mysql> call protest2(3,100) $$
Query OK, 1 row affected (0.00 sec)

mysql> select * from testA $$
+----+-----+
| id | num |
+----+-----+
|  1 |  10 |
|  2 |  20 |
|  3 | 100 |
+----+-----+
3 rows in set (0.00 sec)

mysql> create procedure pro_test3(in cia int,out cnum int)   //带in、out
    -> begin
    -> select cnum from testA where id = cia;              //查询参数写错以至于查询结果出错
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> call pro_test3(2,@cnum) $$
+------+
| cnum |
+------+
| NULL |
+------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> drop procedure pro_test3;
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure pro_test3(in cid int,out cnum int)
    -> begin
    -> select num from testA where id = cid;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> call pro_test3(2,@cnum) $$
+-----+
| num |
+-----+
|  20 |
+-----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> delimiter $$
mysql> create procedure pro_test4(inout cid int)        //inout
    -> begin
    -> declare v_count int;                             //declare 局部变量
    -> if cid > 3 then                                  //使用if...else 语句
    -> set v_count = 100;
    -> else
    -> set v_count = 180;
    -> end if;
    -> set cid = v_count;
    -> end $$
Query OK, 0 rows affected (0.03 sec)

mysql> set @cid = 2;
    -> call pro_test4(@cid);
    -> select @cid $$
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+------+
| @cid |
+------+
|  180 |
+------+
1 row in set (0.00 sec)