2014-07-13 16:29:54
来源：中存储网
mysql> create procedure tax_wages(in s_id
smallint,in s_wages smallint)
-> begin
-> declare c
int;
-> declare tax
int;
-> set
c=s_wages/1000;
-> case c
-> when 0 then
update tax_table set tax=0,wages=s_wages
-> where
id=s_id;
-> when 1 then
update tax_table set tax=(s_wages-1000)*0.02,wages=s_wages
-> where
id=s_id;
-> when 2 then
update tax_table set tax=(s_wages-2000)*0.04+20,wages=s_wage
->
-> where
id=s_id;
-> else update
tax_table set tax=(s_wages-3000)*0.1+60,wages=s_wages
-> where
id=s_id;
-> end case;
-> end;//
Query OK, 0 rows affected (0.00 sec)
mysql> call tax_wages(1,2500);//
Query OK, 1 row affected (0.00 sec)
mysql> select * from tax_table;//
+----+------+-------+------+
| id | name | wages | tax |
+----+------+-------+------+
| 1 | 陈一 | 2500 | 10
|
| 2 | 林二 | 0
| 0 |
| 3 | 张三 | 0
| 0 |
| 4 | 李四 | 0
| 0 |
| 5 | 王五 | 0
| 0 |
+----+------+-------+------+
5 rows in set (0.00 sec)
mysql> drop procedure tax_wages;//
Query OK, 0 rows affected (0.00 sec)
mysql> create procedure tax_wages(in s_id
smallint,in s_wages smallint)
-> begin
-> declare c
int;
-> set
c=floor(s_wages/1000);
-> case c
-> when 0 then
update tax_table set tax=0,wages=s_wages
-> where
id=s_id;
-> when 1 then
update tax_table set tax=(s_wages-1000)*0.02,wages=s_wages
-> where
id=s_id;
-> when 2 then
update tax_table set tax=(s_wages-2000)*0.04+20,wages=s_wage
-> where
id=s_id;
-> else update
tax_table set tax=(s_wages-3000)*0.1+60,wages=s_wages
-> where
id=s_id;
-> end case;
-> end;//
Query OK, 0 rows affected (0.00 sec)
mysql> call tax_wages(1,1500);//
Query OK, 1 row affected (0.00 sec)
mysql> select * from tax_table;//
+----+------+-------+------+
| id | name | wages | tax |
+----+------+-------+------+
| 1 | 陈一 | 1500 | 10
|
| 2 | 林二 | 0
| 0 |
| 3 | 张三 | 0
| 0 |
| 4 | 李四 | 0
| 0 |
| 5 | 王五 | 0
| 0 |
+----+------+-------+------+
5 rows in set (0.01 sec)
mysql> call tax_wages(2,3000);//
Query OK, 1 row affected (0.00 sec)
mysql> call tax_wages(3,1000);//
Query OK, 1 row affected (0.02 sec)
mysql> call tax_wages(4,5000);//
Query OK, 1 row affected (0.00 sec)
mysql> select * from tax_table;//
+----+------+-------+------+
| id | name | wages | tax |
+----+------+-------+------+
| 1 | 陈一 | 1500 | 10
|
| 2 | 林二 | 3000 | 60
|
| 3 | 张三 | 1000
| 0 |
| 4 | 李四 | 5000 | 260 |
| 5 | 王五 | 0
| 0 |
+----+------+-------+------+
5 rows in set (0.00 sec)