Mysql中存储过程

1.存储过程简介（考试专用的废话）

存储过程是一组为完成特定功能的SQL语句集，经编译后存储在数据库中，用户可以通过给定的存储过程名称和参数（如果该存储过程带有参数）执行。

①存储过程执行速度更快

②存储过程减少网络流量

③存储过程被当做安全机制，被充分利用

 

2.创建存储过程

语法 CREATE PROCEDURE sp_name ([ proc_parameter ]) [ characteristics..] routine_body

proc_parameter指定存储过程的参数列表，列表形式如下： [IN|OUT|INOUT] param_name type
其中in表示输入参数，out表示输出参数，inout表示既可以输入也可以输出；

param_name表示参数称；type表示参数的类型，该类型可以是MYSQL数据库中的任意类型

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

LANGUAGE SQL ：说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL，SQL是LANGUAGE特性的唯一值
[NOT] DETERMINISTIC ：指明存储过程执行的结果是否正确。DETERMINISTIC 表示结果是确定的。每次执行存储过程时，相同的输入会得到
相同的输出。
[NOT] DETERMINISTIC 表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为[NOT] DETERMINISTIC
CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA：指明子程序使用SQL语句的限制。
CONTAINS SQL表明子程序包含SQL语句，但是不包含读写数据的语句；
NO SQL表明子程序不包含SQL语句；
READS SQL DATA：说明子程序包含读数据的语句；
MODIFIES SQL DATA表明子程序包含写数据的语句。
默认情况下，系统会指定为CONTAINS SQL
SQL SECURITY { DEFINER | INVOKER } ：指明谁有权限来执行。DEFINER 表示只有定义者才能执行
INVOKER 表示拥有权限的调用者可以执行。默认情况下，系统指定为DEFINER
COMMENT 'string' ：注释信息，可以用来描述存储过程或函数

routine_body:
    Valid SQL procedure statement or statements
routine_body是SQL代码的内容，可以用BEGIN...END来表示SQL代码的开始和结束

 

3.别要被上面的定义吓到，那都是理论，实践出真知。好，我们来一个简单的存储过程，Let·s Go

 

delimiter $$
create procedure proc1(out s int)
begin
select count(*) into s from person;
end
$$
delimiter ;

 说明：

 

①这里使用了delimiter，表示分隔符的意思。在Mysql中默认使用“；”做为分隔符。如果我们没有声明，编译器会把存储过程当成sql语句进行处理，那么存储过程的编译就会报错。所以要事先使用delimiter声明分割符，这样编译器才会将“；”当成存储过程的代码，用完之后在还原分隔符。

②存储过程有时需要输入，输出，输入输出参数。此例使用输出参数s，类型为int。如果有多个参数用逗号分开

③begin和end标识过程体的开始和结束

是不是很简单，好的继续

 

4.存储过程的参数

CREATE PROCEDURE([[IN |OUT |INOUT ] 参数名 数据类形...])
IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

 

mysql> set @s=0;
Query OK, 0 rows affected (0.00 sec)

mysql> call proc1(@s);
Query OK, 1 row affected (0.10 sec)

mysql> select @s;
+------+
| @s   |
+------+
|    5 |
+------+
1 row in set (0.00 sec)

 
OUT 输出参数:该值可在存储过程内部被改变，并可返回【参照IN】
INOUT 输入输出参数:调用时指定，并且可被改变和返回【参照IN】

 

 

5.定义变量

DECLARE variable_name [,variable_name...] datatype [DEFAULT value];
其中，datatype为MySQL的数据类型，如:int, float, date, varchar(length)
DECLARE l_int int unsigned default 4000000; 
DECLARE l_numeric number(8,2) DEFAULT 9.95; 
DECLARE l_date date DEFAULT '1999-12-31'; 
DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59'; 
DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';

变量赋值
 SET 变量名 = 表达式值 [,variable_name = expression ...]

 

6.存储过程的调用
用call和你过程名以及一个括号，括号里面根据需要，加入参数，参数包括输入参数、输出参数、输入输出参数【call proc1（@S）】

 

7.存储过程的查询
可用  select name from mysql.proc where db=’数据库名’;【存储过程和函数】
或者  select routine_name from information_schema.routines where routine_schema='数据库名';【存储过程和函数】
或者  show procedure status where db='数据库名';【存储过程】
查看当前存储过程的详细,可以使用
SHOW CREATE PROCEDURE 数据库.存储过程名;

 

8 修改存储过程
ALTER PROCEDURE
更改用CREATE PROCEDURE 建立的预先指定的存储过程，其不会影响相关存储过程或存储功能。
 
9 存储过程的删除
删除一个存储过程比较简单，和删除表一样：
DROP PROCEDURE
从MySQL的表格中删除一个或多个存储过程

 

10  存储过程的控制语句
(1). 变量作用域
内部的变量在其作用域范围内享有更高的优先权，当执行到end。变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，应为在存储
过程外再也不能找到这个申明的变量，但是你可以通过out参数或者将其值指派
给会话变量来保存其值。
 
 
mysql > DELIMITER // 
mysql > CREATE PROCEDURE proc3() 
     -> begin
     -> declare x1 varchar(5) default 'outer'; 
     -> begin
     -> declare x1 varchar(5) default 'inner'; 
     -> select x1; 
     -> end; 
     -> select x1; 
     -> end; 
     -> // 
mysql > DELIMITER ; 
 
 (2). 条件语句
Ⅰ. if-then -else语句
 
mysql > DELIMITER // 
mysql > CREATE PROCEDURE proc2(IN parameter int) 
     -> begin
     -> declare var int; 
     -> set var=parameter+1; 
     -> if var=0 then
     -> insert into t values(17); 
     -> end if; 
     -> if parameter=0 then
     -> update t set s1=s1+1; 
     -> else
     -> update t set s1=s1+2; 
     -> end if; 
     -> end; 
     -> // 
mysql > DELIMITER ; 

Ⅱ. case语句：
mysql > DELIMITER // 
mysql > CREATE PROCEDURE proc3 (in parameter int) 
     -> begin
     -> declare var int; 
     -> set var=parameter+1; 
     -> case var 
     -> when 0 then  
     -> insert into t values(17); 
     -> when 1 then  
     -> insert into t values(18); 
     -> else  
     -> insert into t values(19); 
     -> end case; 
     -> end; 
     -> // 
mysql > DELIMITER ;
 
(3). 循环语句
Ⅰ. while ···· end while：
mysql > DELIMITER // 
mysql > CREATE PROCEDURE proc4() 
     -> 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 ;
 
 
Ⅱ. repeat···· end repeat：
它在执行操作后检查结果，而while则是执行前进行检查。
mysql > DELIMITER // 
mysql > CREATE PROCEDURE proc5 () 
     -> 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 ; 
 

Ⅲ. loop ·····end loop:
loop循环不需要初始条件，这点和while 循环相似，同时和repeat循环一样不需要结束条件, leave语句的意义是离开循环。
mysql > DELIMITER // 
mysql > CREATE PROCEDURE proc6 () 
     -> 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 ; 
 
 
Ⅳ. LABLES 标号：
标号可以用在begin repeat while 或者loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。
 
(4). ITERATE迭代
Ⅰ. ITERATE:
通过引用复合语句的标号,来从新开始复合语句
mysql > DELIMITER // 
mysql > CREATE PROCEDURE proc10 () 
     -> begin
     -> declare v int; 
     -> set v=0; 
     -> LOOP_LABLE:loop 
     -> if v=3 then  
     -> set v=v+1; 
     -> ITERATE LOOP_LABLE; 
     -> end if; 
     -> insert into t values(v); 
     -> set v=v+1; 
     -> if v>=5 then
     -> leave LOOP_LABLE; 
     -> end if; 
     -> end loop; 
     -> end; 
     -> // 
mysql > DELIMITER ;

 

 

SqlServer存储过程： http://www.cnblogs.com/knowledgesea/archive/2013/01/02/2841588.html