1、什么是存储过程
|
1
2
3
4
5
6
7
8
9
|
mysql> delimiter ;
mysql>
create
procedure
proc1(
out
s
int
)
->
begin
->
select
count
(*)
into
s
from
mysql.
user
;
->
end
-> //
mysql> delimiter ;
mysql> call proc1(@s);
mysql>
select
@s;
|
2、创建存储过程
create procedure([[in] |out |inout])
2.1、in values
|
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql> delimiter;
mysql>
create
procedure
pin(
in
p_in
int
)
->
begin
->
select
p_in;
->
set
p_in=2;
->
select
p_in;
->
end
-> //
mysql> delimiter ;
mysql>
set
@p_in=1;
mysql> call pin(@p_in);
mysql>
select
@p_in;
|
2.2、out values
example 1:
|
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql> delimiter;
mysql>
create
procedure
pout(
out
p_out
int
)
->
begin
->
select
p_out;
->
set
p_out=2;
->
select
p_out;
->
end
-> //
mysql> delimiter ;
mysql>
set
@p_out=1;
mysql> call pout(@p_out);
mysql>
select
@p_out
|
example 2:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
mysql> delimiter;
mysql>
create
procedure
pinout(inout p_out
int
)
->
begin
->
select
p_inout;
->
set
p_inout=2;
->
select
p_inout;
->
end
-> //
mysql> delimiter ;
mysql>
set
@p_inout=1;
mysql>
select
@p_inout
mysql> call pinout(@p_inout);
mysql>
select
@p_inout
|
2.3、null values
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
mysql> delimiter;
mysql>
create
definer=`root`@`localhost`
procedure
`test1`(n1
int
)
->
begin
;
->
set
@x=0;
-> repeat
set
@x=@x+1;
->
insert
into
world.t3
values
(@x);
-> until @x>n1
->
end
pepeat
->
end
;
-> //
mysql> delimiter ;
mysql> call test1(10)
mysql>
select
@p_inout
|
2、循环插入语句
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
mysql> show
create
tables t3\G;
mysql> delimiter //
mysql>
create
procedure
t3(n1
int
)
->
begin
->
set
@x=0;
-> repeat
set
@x=@x+1;
->
insert
int
t3
values
(@x);
-> until @x>n1
->
end
;
-> //
mysql> delimiter ;
mysql> call t3(10);
mysql>
select
*
from
t3;
|
3、变量定义
declare variable_name [,variable_name...] datatype [default value];
datatype:int,float,date,varchar(length)
|
1
2
3
4
|
decalare l_int
int
unsigned
default
4000000;
decalare l_numeric number(8,2)
default
9.95;
decalare l_datetime datetime
default
'1999-12-31 23:59:59'
;
decalare l_varchar
varchar
(255)
default
'This will not be padded'
;
|
4、变量赋值
set 变量名 = 表达式值 [,variable_name = expression ...]
|
1
2
3
4
|
mysql>
create
procedure
p1()
set
@last_procedure =
'p1'
;
mysql>
create
procedure
p2()
set
selcet coucat(
'Last procedure was'
,@last_procedure);
mysql> call p1();
mysql> call p2();
|
5、变量的作用域
6、注解
--
/*...*/
7、结构化
7.1、if
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
mysql > delimiter //
mysql >
create
procedure
proc2(
in
p1
int
)
--> begin
--> declare var int;
--> set var=p1+1;
--> if var=1 then insert into t values(11);
--> end if;
--> if var=2 then insert into t values(22);
--> else insert into t varlues(33);
--> end if;
--> end;
--> //
mysql > delimiter ;
|
7.2、case
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
mysql > delimiter //
mysql >
create
procedure
proc3(
in
p1
int
)
--> begin
--> declare var int;
--> set var=p1+1;
--> case var
--> when 1 then insert into t values(17);
--> when 2 then insert into t values(18);
--> else insert into t values(19);
--> end case;
--> end;
--> //
mysql > delimiter ;
|
7.3、while
|
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql > delimiter //
mysql >
create
procedure
proc4(
in
p1
int
)
--> begin
--> declare var int;
--> set var=0;
--> while var<6 do
--> insert into t values(var);
--> set var=var+1
--> end while;
--> end;
--> //
mysql > delimiter ;
|
7.4、repeat
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
mysql > delimiter //
mysql >
create
procedure
proc5(
in
p1
int
)
--> begin
--> declare v int;
--> set v=0;
--> repeat
--> insert into t values(v);
--> set v=v+1;
--> until v>=5
--> end repeat;
--> end;
--> //
mysql > delimiter ;
|
7.5、loop
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql > delimiter //
mysql >
create
procedure
proc6(
in
p1
int
)
--> begin
--> declare v int;
--> set v=0;
--> loop_lable:loop
--> insert into t values(v);
--> set v=v+1;
--> if v >=5 then
--> leave loop_lable;
--> end if;
--> end loop;
--> end;
--> //
mysql > delimiter ;
|
7.6、iterate
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql > delimiter //
mysql >
create
procedure
proc7()
--> begin
--> declare v int default 0;
--> loop_lable:loop
--> set v=v+1;
--> if v<10 then insert loop_lable;
--> elseif v>20 then leave loop_lable;
--> else select 'v is between 10 and 20';
--> end if;
--> end loop loop_lable;
--> end;
--> //
mysql > delimiter ;
|
8、查看存储过程状态
|
1
2
3
4
5
|
mysql > show
procedure
status
like
'p%'
\G;
mysql >
select
*
from
information_schema.Routines
->
where
routine_name =
'p1'
->
and
->routing_type=
'procedure'
\G;
|
9、查看存储过程内容
|
1
|
mysql > show
create
procedure
p2\G;
|
10、修改存储过程
alter procedure proc_name [characteristic ...]
characteristic:
comment 'sting'
| language sql
| {contains sql | no sql | reads sql data | modifies sql data}
| sql security { definer | invoker}
11、删除存储过程
|
1
|
mysql >
drop
procedure
p2;
|
12、总结
目前，mysql不支持对已存在的存储过程修改代码，只能先drop掉重写。
存储过程可以调用其他存储过程
存储过程的参数不要与数据表中的字段同名
存储过程的参数可以使用在中文，不顾需要在定义是加上character set gbk,例如：
|
1
|
mysql >
create
procedure
p1(
in
u_name
varchar
(30)
character
set
gbk,
out
u_score
int
)
|
本文转自 tanzhenchao 51CTO博客，原文链接：http://blog.51cto.com/cmdschool/1696843，如需转载请自行联系原作者
![2024Mysql And Redis基础与进阶操作系列（9）作者——LJS[含MySQL存储过程之局部、系统变量、参数传递、流程控制-判断/case具体详步骤；注意点及常见报错问题所对应的解决方法]](https://ucc.alicdn.com/pic/developer-ecology/56pfyutwi4tv4_c53df19098d2427391fe36eeb35f84c8.png?x-oss-process=image/resize,h_160,m_lfit)