Mysql的存储过程procedure

Mysql的存储过程procedure


语法：
Note:
==
需要先执行该语句：
delimiter $ [意思是告诉mysql语句的结尾换成以$结束]
==
1、存储过程的语法
create procedure procedure_name()
begin
//声明变量
declare var_name var_type [default value];
==选择结构 
1、if
if conditon then
statement1
elseif condition then
statement2
else
statement3
end if;
2、case
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE
Or:
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE
=========
==循环结构
 1、while condition do
    statement4
    end while;
 2、REPEAT
     statement5
    UNTIL condition
    END REPEAT;
 3、LOOP……END LOOP


//other code
end$






实例1：
存储过程中带有变量
delimiter $
create procedure p3()
begin
declare age int default 18;
set age:=age + 20;
select age;
end$


实例2：
带参数的存储过程
[in/out/inout]参数名 参数类型


create procedure p4(width int,height int)
begin
if width > height then
select '你挺胖';
elseif width < height then
select '你挺瘦';
else
select '你挺方';
end if;
end$
【结果】
mysql> call p4(1,2)$
+--------+
| 你挺瘦 |
+--------+
| 你挺瘦 |
+--------+
1 row in set (0.00 sec)


Query OK, 0 rows affected (0.04 sec)




实例3：
功能：从 1 到 100 的和
create procedure p5()
begin
declare sum int default 0;
declare num int default 0;
while num <= 100 do
set sum:=sum+num;
set num:=num+1;
end while;
select sum;
end$
【结果】
mysql> call p5()$
+------+
| sum  |
+------+
| 5050 |
+------+
1 row in set (0.00 sec)


Query OK, 0 rows affected (0.05 sec)


实例4：利用 while 循环结构
功能:从1 到 N 的和
create procedure p6(in n int)
begin
declare sum int default 0;
declare num int default 0;
while num <= n do
set sum:=sum+num;
set num:=num+1;
end while;
select sum;
end$
【结果】
mysql> call p6(200)$
+-------+
| sum   |
+-------+
| 20100 |
+-------+
1 row in set (0.00 sec)


Query OK, 0 rows affected (0.04 sec)


===利用参数的[in \out ]


create procedure p6(in n int,out total int)
begin
declare num int default 0;
--这里要赋初始值
set total:=0;
while num <= n do
set total:=total+num;
set num:=num+1;
end while;
end$


【结果】
mysql> call p6(100,@sum)$
Query OK, 0 rows affected (0.00 sec)


mysql> select @sum$
+------+
| @sum |
+------+
| 5050 |
+------+
1 row in set (0.00 sec)


====利用参数[inout]


create procedure p7(inout total int)
begin
declare num int default 0;
declare n int;
set n:=total;
while num <= n do
set total:=total+num;
set num:=num+1;
end while;
end$
【结果】
mysql> set @sum=0$
Query OK, 0 rows affected (0.00 sec)


mysql> call p7(@sum)$
Query OK, 0 rows affected (0.00 sec)


mysql> select @sum$
+------+
| @sum |
+------+
|    0 |
+------+
1 row in set (0.00 sec)


mysql> set @sum=100$
Query OK, 0 rows affected (0.00 sec)


mysql> call p7(@sum)$
Query OK, 0 rows affected (0.00 sec)


mysql> select @sum$
+------+
| @sum |
+------+
| 5150 |
+------+
1 row in set (0.00 sec)


实例5：利用循环结构 repeat
create procedure p8()
begin
declare total int default 0;
declare num   int default 0;


repeat
set total:=total+num;
set num:=num+1;
until num>100
end repeat;
select total;
end$
【结果】
mysql> call p8()$
+-------+
| total |
+-------+
|  5050 |
+-------+
1 row in set (0.02 sec)


Query OK, 0 rows affected (0.06 sec)




2、查看已有的存储过程
  show procedure status;
3、调用存储过程
  call procedure_name();