mysql 存储过程（二）

1）if-then-else语句：
mysql> create procedure findgender(in op int)
    -> begin
    -> declare gender varchar(10);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3
mysql> delimiter //
mysql> select * from user;
    -> //
+----+-------+
| id | name  |
+----+-------+
|  1 | jack  |
|  2 | lucy  |
|  3 | james |
+----+-------+
3 rows in set (0.05 sec)

mysql> create procedure findgender(in op int)
    -> begin
    -> declare gender varchar(10);
    -> if op=0 then
    -> set gender='male';
    -> else
    -> set gender='female';
    -> end if;
    -> select gender;
    -> end //
Query OK, 0 rows affected (0.06 sec)

mysql> set @gender=1;
    -> call findgender(@gender);//
Query OK, 0 rows affected (0.00 sec)

+--------+
| gender |
+--------+
| female |
+--------+
1 row in set (0.04 sec)

Query OK, 0 rows affected (0.05 sec)






2）case-when-then语句：

mysql> create procedure findday(in op int)
    -> begin
    -> declare day varchar(20);
    -> case op
    -> when 1 then set day='monday';
    -> when 2 then set day='tuesday';
    -> when 3 then set day='wednesday';
    -> when 4 then set day='thursday';
    -> when 5 then set day='friday';
    -> when 6 then set day='saterday';
    -> when 7 then set day='sunday';
    -> else  set day='parameter error';
    -> end case;
    -> select day;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> set @op=5;
    -> call findday(@op);//
Query OK, 0 rows affected (0.00 sec)

+--------+
| day    |
+--------+
| friday |
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)


使用存储过程实现登陆和注册：
注册：
mysql> desc user;
    -> //
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| pass  | varchar(50) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> create procedure register(in name varchar(20),in pa varchar(50))
    -> begin
    -> insert into user
    -> end/
    -> //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'end/' at line 4
mysql> create procedure register(in name varchar(20),in pa varchar(50))
    -> begin
    -> insert into user(name,pass)
    -> values(name,pa);
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> set @name='cainiao';
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> set @pa='cainiao';
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> call register(@name,@pa);
    -> //
Query OK, 1 row affected (0.09 sec)

mysql> select * from user;
    -> //
+----+---------+-------------------------------------------+
| id | name    | pass                                      |
+----+---------+-------------------------------------------+
|  1 | cai     | NULL                                      |
|  2 | cai     | cai                                       |
|  3 | NULL    | *0576945B87B58FCAEC83302BF28521A5C9A6CC16 |
|  4 | cai     | *0576945B87B58FCAEC83302BF28521A5C9A6CC16 |
|  5 | cainiao | cainiao                                   |
+----+---------+-------------------------------------------+
5 rows in set (0.00 sec)

上面的语句需要注意的是：参数的类型长度一定要跟数据库定义的长度相同，不然是无法插入的。
加密密码：
mysql> create procedure register2(in name varchar(20),in pa varchar(50))
    -> begin
    -> insert into user(name,pass)
    -> values(name,password(pa));
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> call register1(@name,@pa);
    -> //
Query OK, 1 row affected (0.07 sec)

mysql> select * from user;
    -> //
+----+---------+-------------------------------------------+
| id | name    | pass                                      |
+----+---------+-------------------------------------------+
|  1 | cai     | NULL                                      |
|  2 | cai     | cai                                       |
|  3 | NULL    | *0576945B87B58FCAEC83302BF28521A5C9A6CC16 |
|  4 | cai     | *0576945B87B58FCAEC83302BF28521A5C9A6CC16 |
|  5 | cainiao | cainiao                                   |
|  6 | cainiao | *60196FA8FB6DBA3EC6B0700C6CDB93ECE0829011 |
+----+---------+-------------------------------------------+
6 rows in set (0.00 sec)
判断用户名密码是否为空：
mysql> create procedure register01(in name varchar(20),in pa varchar(50))
    -> begin
    -> declare result varchar(50);
    -> if name=''&&pa='' then
    -> set result='register fail';
    -> else
    -> insert into user(name,pass)
    -> values(name,password(pa));
    -> set result='register success';
    -> end if;
    -> select result;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> set @name01='';
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> set @pa01='';
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> call register01(@name01,@pa01);
    -> //
+---------------+
| result        |
+---------------+
| register fail |
+---------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> set @name01='huoniao';
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> set @pa01='huoniao';
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> call register01(@name01,@pa01);
    -> //
+------------------+
| result           |
+------------------+
| register success |
+------------------+
1 row in set (0.10 sec)

Query OK, 0 rows affected (0.10 sec)

mysql> select * from user;
    -> //
+----+---------+-------------------------------------------+
| id | name    | pass                                      |
+----+---------+-------------------------------------------+
|  1 | cainiao | *60196FA8FB6DBA3EC6B0700C6CDB93ECE0829011 |
|  2 | cainiao | *60196FA8FB6DBA3EC6B0700C6CDB93ECE0829011 |
|  3 |         |                                           |
|  4 | huoniao | *95787E1FB8F0CC3A73789D5F401212633777B679 |
+----+---------+-------------------------------------------+
4 rows in set (0.00 sec)

登陆：
mysql> create procedure login05(in na varchar(20),in pa varchar(50))
    -> begin
    -> declare tip varchar(50);
    -> declare result int;
    -> if na='' && pa='' then
    -> set tip='please input your name or password';
    -> set result=0;
    -> else
    -> select count(*) into result from user as u where u.name=name and u.pass=password(pa);
    -> end if;
    -> if result=1 then
    -> set tip='login success';
    -> end if;
    -> select tip;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> call login05(@name,@pa);//
+------------------------------------+
| tip                                |
+------------------------------------+
| please input your name or password |
+------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> call login05('huoniao','huoniao');//
+---------------+
| tip           |
+---------------+
| login success |
+---------------+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)