为什么要用存储过程

一个复杂的数据操作。如果你在前台处理的话。可能会涉及到多次数据库连接。但如果你用存储过程的话。就只有一次。从响应时间上来说有优势。
也就是说存储过程可以给我们带来运行效率提高的好处。

另外，程序容易出现 BUG 不稳定，而存储过程，只要数据库不出现问题，基本上是不会出现什么问题的。也就是说从安全上讲，使用了存储过程的系统更加稳定。

优点:

1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
  2.当对数据库进行复杂操作时(如对多个表进行 Update,Insert,Query,Delete 时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。这些操作，如果用程序来完成，就变成了一条条的 SQL 语句，可能要多次连接数据库。而换成存储，只需要连接一次数据库就可以了。
  3.存储过程可以重复使用,可减少数据库开发人员的工作量。

  4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权。

大多数高级的数据库系统都有statement   cache的，所以编译sql的花费没什么影响。但是执行存储过程要比直接执行sql花费更多（检查权限等），所以对于很简单的sql，存储过程没有什么优势。     

mysql存储过程语法,用于参考:

创建:

1. mysql > DELIMITER //  
2. mysql > CREATE PROCEDURE demo_in_parameter(IN p_in int)  
3. -> BEGIN   
4. -> SELECT p_in;   
5. -> SET p_in=2;   
6. -> SELECT p_in;   
7. -> END;   
8. -> //  
9. mysql > DELIMITER ; 

执行结果:

1. mysql > SET @p_in=1;  
2. mysql > CALL demo_in_parameter(@p_in);  
3. +------+  
4. | p_in |  
5. +------+  
6. |   1  |   
7. +------+  
8.  
9. +------+  
10. | p_in |  
11. +------+  
12. |   2  |   
13. +------+  
14.  
15. mysql> SELECT @p_in;  
16. +-------+  
17. | @p_in |  
18. +-------+  
19. |  1    |  
20. +-------+  

以上可以看出，p_in虽然在存储过程中被修改，但并不影响@p_id的值

 

Ⅱ.OUT参数例子

创建:

1. mysql > DELIMITER //  
2. mysql > CREATE PROCEDURE demo_out_parameter(OUT p_out int)  
3. -> BEGIN 
4. -> SELECT p_out;  
5. -> SET p_out=2;  
6. -> SELECT p_out;  
7. -> END;  
8. -> //  
9. mysql > DELIMITER ; 

执行结果:

1. mysql > SET @p_out=1;  
2. mysql > CALL sp_demo_out_parameter(@p_out);  
3. +-------+  
4. | p_out |   
5. +-------+  
6. | NULL  |   
7. +-------+  
8.  
9. +-------+  
10. | p_out |  
11. +-------+  
12. |   2   |   
13. +-------+  
14.  
15. mysql> SELECT @p_out;  
16. +-------+  
17. | p_out |  
18. +-------+  
19. |   2   |  
20. +-------+  

Ⅲ. INOUT参数例子

创建:

1. mysql > DELIMITER //   
2. mysql > CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int)   
3. -> BEGIN 
4. -> SELECT p_inout;  
5. -> SET p_inout=2;  
6. -> SELECT p_inout;   
7. -> END;  
8. -> //   
9. mysql > DELIMITER ; 

 

 

执行结果:

1. mysql 
2. mysql > CALL demo_inout_parameter(@p_inout) ;  
3. +---------+  
4. | p_inout |  
5. +---------+  
6. |    1    |  
7. +---------+  
8.  
9. +---------+  
10. | p_inout |   
11. +---------+  
12. |    2    |  
13. +---------+  
14.  
15. mysql > SELECT @p_inout;  
16. +----------+  
17. | @p_inout |   
18. +----------+  
19. |    2     |  
20. +----------+ 

(4). 变量

Ⅰ. 变量定义

DECLARE variable_name [,variable_name...] datatype [DEFAULT value];

其中，datatype为MySQL的数据类型，如:int, float, date, varchar(length)

例如:

1. DECLARE l_int int unsigned default 4000000;  
2. DECLARE l_numeric number(8,2) DEFAULT 9.95;  
3. DECLARE l_date date DEFAULT '1999-12-31';  
4. DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';  
5. DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';   

 

 

Ⅱ. 变量赋值

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

 

Ⅲ. 用户变量

 

ⅰ. 在MySQL客户端使用用户变量

1. mysql > SELECT 'Hello World' into @x;  
2. mysql > SELECT @x;  
3. +-------------+  
4. |   @x        |  
5. +-------------+  
6. | Hello World |  
7. +-------------+  
8. mysql > SET @y='Goodbye Cruel World';  
9. mysql > SELECT @y;  
10. +---------------------+  
11. |     @y              |  
12. +---------------------+  
13. | Goodbye Cruel World |  
14. +---------------------+  
15.  
16. mysql > SET @z=1+2+3;  
17. mysql > SELECT @z;  
18. +------+  
19. | @z   |  
20. +------+  
21. |  6   |  
22. +------+  

ⅱ. 在存储过程中使用用户变量

1. mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');  
2. mysql > SET @greeting='Hello';  
3. mysql > CALL GreetWorld( );  
4. +----------------------------+  
5. | CONCAT(@greeting,' World') |  
6. +----------------------------+  
7. |  Hello World               |  
8. +----------------------------+  

 

ⅲ. 在存储过程间传递全局范围的用户变量

1. mysql> CREATE PROCEDURE p1()   SET @last_procedure='p1';  
2. mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_proc);  
3. mysql> CALL p1( );  
4. mysql> CALL p2( );  
5. +-----------------------------------------------+  
6. | CONCAT('Last procedure was ',@last_proc  |  
7. +-----------------------------------------------+  
8. | Last procedure was p1                         |  
9. +-----------------------------------------------+  

 

 

注意:

①用户变量名一般以@开头

②滥用用户变量会导致程序难以理解及管理

 

(5). 注释

 

MySQL存储过程可使用两种风格的注释

双模杠：--

该风格一般用于单行注释

c风格： 一般用于多行注释

例如：

 

1. mysql > DELIMITER //  
2. mysql > CREATE PROCEDURE proc1 --name存储过程名  
3. -> (IN parameter1 INTEGER)   
4. -> BEGIN   
5. -> DECLARE variable1 CHAR(10);   
6. -> IF parameter1 = 17 THEN   
7. -> SET variable1 = 'birds';   
8. -> ELSE 
9. -> SET variable1 = 'beasts';   
10. -> END IF;   
11. -> INSERT INTO table1 VALUES (variable1);  
12. -> END   
13. -> //  
14. mysql > DELIMITER ;  

 

4.      MySQL存储过程的调用

用call和你过程名以及一个括号，括号里面根据需要，加入参数，参数包括输入参数、输出参数、输入输出参数。具体的调用方法可以参看上面的例子。

5.      MySQL存储过程的查询

我们像知道一个数据库下面有那些表，我们一般采用show tables;进行查看。那么我们要查看某个数据库下面的存储过程，是否也可以采用呢？答案是，我们可以查看某个数据库下面的存储过程，但是是令一钟方式。

我们可以用

select name from mysql.proc where db=’数据库名’;

或者

select routine_name from information_schema.routines where routine_schema='数据库名';

或者

show procedure status where db='数据库名';

进行查询。

如果我们想知道，某个存储过程的详细，那我们又该怎么做呢？是不是也可以像操作表一样用describe 表名进行查看呢？

答案是：我们可以查看存储过程的详细，但是需要用另一种方法：

SHOW CREATE PROCEDURE 数据库.存储过程名;

就可以查看当前存储过程的详细。

 

6.      MySQL存储过程的修改

ALTER PROCEDURE

更改用CREATE PROCEDURE 建立的预先指定的存储过程，其不会影响相关存储过程或存储功能。

 

7.      MySQL存储过程的删除

删除一个存储过程比较简单，和删除表一样：

DROP PROCEDURE

从MySQL的表格中删除一个或多个存储过程。

 

8.      MySQL存储过程的控制语句

(1). 变量作用域

内部的变量在其作用域范围内享有更高的优先权，当执行到end。变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，应为在存储
过程外再也不能找到这个申明的变量，但是你可以通过out参数或者将其值指派
给会话变量来保存其值。

 

 

1. mysql > DELIMITER //  
2. mysql > CREATE PROCEDURE proc3()  
3.      -> begin 
4.      -> declare x1 varchar(5) default 'outer';  
5.      -> begin 
6.      -> declare x1 varchar(5) default 'inner';  
7.      -> select x1;  
8.      -> end;  
9.      -> select x1;  
10.      -> end;  
11.      -> //  
12. mysql > DELIMITER ;  

 

 (2). 条件语句

Ⅰ. if-then -else语句

 

 

 

1. mysql > DELIMITER //  
2. mysql > CREATE PROCEDURE proc2(IN parameter int)  
3.      -> begin 
4.      -> declare var int;  
5.      -> set var=parameter+1;  
6.      -> if var=0 then 
7.      -> insert into t values(17);  
8.      -> end if;  
9.      -> if parameter=0 then 
10.      -> update t set s1=s1+1;  
11.      -> else 
12.      -> update t set s1=s1+2;  
13.      -> end if;  
14.      -> end;  
15.      -> //  
16. mysql > DELIMITER ;  

Ⅱ. case语句： 

1. mysql > DELIMITER //  
2. mysql > CREATE PROCEDURE proc3 (in parameter int)  
3.      -> begin 
4.      -> declare var int;  
5.      -> set var=parameter+1;  
6.      -> case var  
7.      -> when 0 then   
8.      -> insert into t values(17);  
9.      -> when 1 then   
10.      -> insert into t values(18);  
11.      -> else   
12.      -> insert into t values(19);  
13.      -> end case;  
14.      -> end;  
15.      -> //  
16. mysql > DELIMITER ; 

 

(3). 循环语句

Ⅰ. while ···· end while：

1. mysql > DELIMITER //  
2. mysql > CREATE PROCEDURE proc4()  
3.      -> begin 
4.      -> declare var int;  
5.      -> set var=0;  
6.      -> while var<6 do  
7.      -> insert into t values(var);  
8.      -> set var=var+1;  
9.      -> end while;  
10.      -> end;  
11.      -> //  
12. mysql > DELIMITER ; 

 

 

Ⅱ. repeat···· end repeat：

它在执行操作后检查结果，而while则是执行前进行检查。

1. mysql > DELIMITER //  
2. mysql > CREATE PROCEDURE proc5 ()  
3.      -> begin   
4.      -> declare v int;  
5.      -> set v=0;  
6.      -> repeat  
7.      -> insert into t values(v);  
8.      -> set v=v+1;  
9.      -> until v>=5  
10.      -> end repeat;  
11.      -> end;  
12.      -> //  
13. mysql > DELIMITER ;  

 

Ⅲ. loop ·····end loop:

loop循环不需要初始条件，这点和while 循环相似，同时和repeat循环一样不需要结束条件, leave语句的意义是离开循环。

1. mysql > DELIMITER //  
2. mysql > CREATE PROCEDURE proc6 ()  
3.      -> begin 
4.      -> declare v int;  
5.      -> set v=0;  
6.      -> LOOP_LABLE:loop  
7.      -> insert into t values(v);  
8.      -> set v=v+1;  
9.      -> if v >=5 then 
10.      -> leave LOOP_LABLE;  
11.      -> end if;  
12.      -> end loop;  
13.      -> end;  
14.      -> //  
15. mysql > DELIMITER ;  

 

 

Ⅳ. LABLES 标号：

标号可以用在begin repeat while 或者loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。

 

(4). ITERATE迭代

Ⅰ. ITERATE:

通过引用复合语句的标号,来从新开始复合语句

1. mysql > DELIMITER //  
2. mysql > CREATE PROCEDURE proc10 ()  
3.      -> begin 
4.      -> declare v int;  
5.      -> set v=0;  
6.      -> LOOP_LABLE:loop  
7.      -> if v=3 then   
8.      -> set v=v+1;  
9.      -> ITERATE LOOP_LABLE;  
10.      -> end if;  
11.      -> insert into t values(v);  
12.      -> set v=v+1;  
13.      -> if v>=5 then 
14.      -> leave LOOP_LABLE;  
15.      -> end if;  
16.      -> end loop;  
17.      -> end;  
18.      -> //  
19. mysql > DELIMITER ;