#聲明語句結(jié)束符，可以自定義:
delimiter $$
#聲明存儲(chǔ)過程
create procedure 存儲(chǔ)過程名（in 參數(shù)名 參數(shù)類型）
begin
#定義變量
declare 變量名 變量類型
#變量賦值
set 變量名 = 值
 sql 語句1；
 sql 語句2；
 ...
end$$
#恢復(fù)為原來的語句結(jié)束符
delimiter ；（有空格）

mysql> delimiter $$
mysql> create procedure text()
 -> begin
 -> select * from stu.a_player;
 -> end $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;

call 存儲(chǔ)過程名(實(shí)際參數(shù));
mysql> call text;
+----+----------+-------+
| id | name  | score |
+----+----------+-------+
| 1 | zhangsan | 88 |
| 2 | lisi  | 89 |
| 3 | wangwu | 67 |
| 4 | zhaoliu | 90 |
| 5 | xuli  | 80 |
| 6 | keke  | 75 |
+----+----------+-------+
6 rows in set (0.00 sec)

mysql> drop procedure text;

mysql> create procedure test1(in in_id int(2))
 -> begin
 -> select * from stu.a_player where id=in_id;
 -> end $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;

#將4傳遞給in_id變量，執(zhí)行事務(wù)
mysql> call test1(4); 
+----+---------+-------+
| id | name | score |
+----+---------+-------+
| 4 | zhaoliu | 90 |
+----+---------+-------+
1 row in set (0.00 sec)

#將6傳遞給in_id變量，執(zhí)行事務(wù)
mysql> call test1(6);
+----+------+-------+
| id | name | score |
+----+------+-------+
| 6 | keke | 75 |
+----+------+-------+
1 row in set (0.00 sec)

mysql> delimiter $$
mysql> create procedure test2(out aa int) 
 -> begin
 -> select aa;
 -> set aa=2;
 -> select aa;
 -> end $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
#將@aa變量傳遞給test2 事務(wù)
mysql> call test2(@aa);
+------+
| aa |
+------+
| NULL |
+------+
#out向調(diào)用者輸出參數(shù)，不接收輸入的參數(shù)，所以aa為null
1 row in set (0.00 sec)
+------+
| aa |
+------+
| 2 |
+------+
事務(wù)將aa變量設(shè)置為2（設(shè)置的是全局），則可進(jìn)行輸出
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> select @aa;
+------+
| @aa |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
#事務(wù)外查詢變量，已經(jīng)被修改

mysql> delimiter //
mysql> create procedure test3(in num1 int,out num2 int,inout num3 int)
 -> begin
 -> select num1,num2,num3;
 -> set num1=10,num2=20,num3=30;
 -> select num1,num2,num3;
 -> end //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call test3(@num1,@num2,@num3);
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
| 1 | NULL | 3 |
+------+------+------+
1 row in set (0.00 sec)
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
| 10 | 20 | 30 |
+------+------+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

mysql> select @num1,@num2,@num3;
+-------+-------+-------+
| @num1 | @num2 | @num3 |
+-------+-------+-------+
|  1 | 20 | 30 |
+-------+-------+-------+
1 row in set (0.00 sec)