1、什么是存储过程
1
2
3
4
5
6
7
8
9
<code>mysql> delimiter ;</code>
<code>mysql> </code><code>create</code> <code>procedure</code> <code>proc1(</code><code>out</code> <code>s </code><code>int</code><code>) </code>
<code> </code><code>-> </code><code>begin</code>
<code> </code><code>-> </code><code>select</code> <code>count</code><code>(*) </code><code>into</code> <code>s </code><code>from</code> <code>mysql.</code><code>user</code><code>;</code>
<code> </code><code>-> </code><code>end</code>
<code> </code><code>-> //</code>
<code>mysql> call proc1(@s);</code>
<code>mysql> </code><code>select</code> <code>@s;</code>
2、创建存储过程
create procedure([[in] |out |inout])
2.1、in values
10
11
12
<code>mysql> delimiter;</code>
<code>mysql> </code><code>create</code> <code>procedure</code> <code>pin(</code><code>in</code> <code>p_in </code><code>int</code><code>) </code>
<code> </code><code>-> </code><code>select</code> <code>p_in;</code>
<code> </code><code>-> </code><code>set</code> <code>p_in=2;</code>
<code>mysql> </code><code>set</code> <code>@p_in=1;</code>
<code>mysql> call pin(@p_in);</code>
<code>mysql> </code><code>select</code> <code>@p_in;</code>
2.2、out values
example 1:
<code>mysql> </code><code>create</code> <code>procedure</code> <code>pout(</code><code>out</code> <code>p_out </code><code>int</code><code>) </code>
<code> </code><code>-> </code><code>select</code> <code>p_out;</code>
<code> </code><code>-> </code><code>set</code> <code>p_out=2;</code>
<code>mysql> </code><code>set</code> <code>@p_out=1;</code>
<code>mysql> call pout(@p_out);</code>
<code>mysql> </code><code>select</code> <code>@p_out</code>
example 2:
13
<code>mysql> </code><code>create</code> <code>procedure</code> <code>pinout(inout p_out </code><code>int</code><code>) </code>
<code> </code><code>-> </code><code>select</code> <code>p_inout;</code>
<code> </code><code>-> </code><code>set</code> <code>p_inout=2;</code>
<code>mysql> </code><code>set</code> <code>@p_inout=1;</code>
<code>mysql> </code><code>select</code> <code>@p_inout</code>
<code>mysql> call pinout(@p_inout);</code>
2.3、null values
<code>mysql> </code><code>create</code> <code>definer=`root`@`localhost` </code><code>procedure</code> <code>`test1`(n1 </code><code>int</code><code>) </code>
<code> </code><code>-> </code><code>begin</code><code>;</code>
<code> </code><code>-> </code><code>set</code> <code>@x=0;</code>
<code> </code><code>-> repeat </code><code>set</code> <code>@x=@x+1;</code>
<code> </code><code>-> </code><code>insert</code> <code>into</code> <code>world.t3 </code><code>values</code> <code>(@x);</code>
<code> </code><code>-> until @x>n1</code>
<code> </code><code>-> </code><code>end</code> <code>pepeat</code>
<code> </code><code>-> </code><code>end</code><code>;</code>
<code>mysql> call test1(10) </code>
2、循环插入语句
<code>mysql> show </code><code>create</code> <code>tables t3\G;</code>
<code>mysql> delimiter //</code>
<code>mysql> </code><code>create</code> <code>procedure</code> <code>t3(n1 </code><code>int</code><code>)</code>
<code> </code><code>-> </code><code>begin</code>
<code> </code><code>-> </code><code>set</code> <code>@x=0;</code>
<code> </code><code>-> repeat </code><code>set</code> <code>@x=@x+1;</code>
<code> </code><code>-> </code><code>insert</code> <code>int</code> <code>t3 </code><code>values</code> <code>(@x);</code>
<code> </code><code>-> until @x>n1</code>
<code> </code><code>-> </code><code>end</code><code>;</code>
<code> </code><code>-> //</code>
<code>mysql> call t3(10);</code>
<code>mysql> </code><code>select</code> <code>* </code><code>from</code> <code>t3;</code>
3、变量定义
declare variable_name [,variable_name...] datatype [default value];
datatype:int,float,date,varchar(length)
<code>decalare l_int </code><code>int</code> <code>unsigned </code><code>default</code> <code>4000000;</code>
<code>decalare l_numeric number(8,2) </code><code>default</code> <code>9.95;</code>
<code>decalare l_datetime datetime </code><code>default</code> <code>'1999-12-31 23:59:59'</code><code>;</code>
<code>decalare l_varchar </code><code>varchar</code><code>(255) </code><code>default</code> <code>'This will not be padded'</code><code>;</code>
4、变量赋值
set 变量名 = 表达式值 [,variable_name = expression ...]
<code>mysql> </code><code>create</code> <code>procedure</code> <code>p1() </code><code>set</code> <code>@last_procedure =</code><code>'p1'</code><code>;</code>
<code>mysql> </code><code>create</code> <code>procedure</code> <code>p2() </code><code>set</code> <code>selcet coucat(</code><code>'Last procedure was'</code><code>,@last_procedure);</code>
<code>mysql> call p1();</code>
<code>mysql> call p2();</code>
5、变量的作用域
6、注解
--
/*...*/
7、结构化
7.1、if
<code>mysql > delimiter //</code>
<code>mysql > </code><code>create</code> <code>procedure</code> <code>proc2(</code><code>in</code> <code>p1 </code><code>int</code><code>)</code>
<code> </code><code>--> begin</code>
<code> </code><code>--> declare var int;</code>
<code> </code><code>--> set var=p1+1;</code>
<code> </code><code>--> if var=1 then insert into t values(11);</code>
<code> </code><code>--> end if;</code>
<code> </code><code>--> if var=2 then insert into t values(22);</code>
<code> </code><code>--> else insert into t varlues(33);</code>
<code> </code><code>--> end;</code>
<code> </code><code>--> //</code>
<code>mysql > delimiter ;</code>
7.2、case
<code>mysql > </code><code>create</code> <code>procedure</code> <code>proc3(</code><code>in</code> <code>p1 </code><code>int</code><code>)</code>
<code> </code><code>--> case var</code>
<code> </code><code>--> when 1 then insert into t values(17);</code>
<code> </code><code>--> when 2 then insert into t values(18);</code>
<code> </code><code>--> else insert into t values(19);</code>
<code> </code><code>--> end case;</code>
7.3、while
<code>mysql > </code><code>create</code> <code>procedure</code> <code>proc4(</code><code>in</code> <code>p1 </code><code>int</code><code>)</code>
<code> </code><code>--> set var=0;</code>
<code> </code><code>--> while var<6 do</code>
<code> </code><code>--> insert into t values(var);</code>
<code> </code><code>--> set var=var+1</code>
<code> </code><code>--> end while;</code>
7.4、repeat
<code>mysql > </code><code>create</code> <code>procedure</code> <code>proc5(</code><code>in</code> <code>p1 </code><code>int</code><code>)</code>
<code> </code><code>--> declare v int;</code>
<code> </code><code>--> set v=0;</code>
<code> </code><code>--> repeat</code>
<code> </code><code>--> insert into t values(v);</code>
<code> </code><code>--> set v=v+1;</code>
<code> </code><code>--> until v>=5</code>
<code> </code><code>--> end repeat;</code>
7.5、loop
14
15
<code>mysql > </code><code>create</code> <code>procedure</code> <code>proc6(</code><code>in</code> <code>p1 </code><code>int</code><code>)</code>
<code> </code><code>--> loop_lable:loop</code>
<code> </code><code>--> if v >=5 then</code>
<code> </code><code>--> leave loop_lable;</code>
<code> </code><code>--> end loop;</code>
7.6、iterate
<code>mysql > </code><code>create</code> <code>procedure</code> <code>proc7()</code>
<code> </code><code>--> declare v int default 0;</code>
<code> </code><code>--> if v<10 then insert loop_lable;</code>
<code> </code><code>--> elseif v>20 then leave loop_lable;</code>
<code> </code><code>--> else select 'v is between 10 and 20';</code>
<code> </code><code>--> end loop loop_lable;</code>
8、查看存储过程状态
<code>mysql > show </code><code>procedure</code> <code>status </code><code>like</code> <code>'p%'</code><code>\G;</code>
<code>mysql > </code><code>select</code> <code>* </code><code>from</code> <code>information_schema.Routines</code>
<code> </code><code>-> </code><code>where</code> <code>routine_name = </code><code>'p1'</code>
<code> </code><code>-> </code><code>and</code>
<code> </code><code>->routing_type=</code><code>'procedure'</code><code>\G;</code>
9、查看存储过程内容
<code>mysql > show </code><code>create</code> <code>procedure</code> <code>p2\G;</code>
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、删除存储过程
<code>mysql > </code><code>drop</code> <code>procedure</code> <code>p2;</code>
12、总结
目前,mysql不支持对已存在的存储过程修改代码,只能先drop掉重写。
存储过程可以调用其他存储过程
存储过程的参数不要与数据表中的字段同名
存储过程的参数可以使用在中文,不顾需要在定义是加上character set gbk,例如:
<code>mysql > </code><code>create</code> <code>procedure</code> <code>p1(</code><code>in</code> <code>u_name </code><code>varchar</code><code>(30) </code><code>character</code> <code>set</code> <code>gbk,</code><code>out</code> <code>u_score </code><code>int</code><code>)</code>
本文转自 tanzhenchao 51CTO博客,原文链接:http://blog.51cto.com/cmdschool/1696843,如需转载请自行联系原作者