MySQL存储过程学习笔记

一、基本语法及简单实例 1、创建简单的测试环境 [sql] view plaincopy mysqlusetest; Database changed mysqlshowtables; Empty set (0.00sec) mysql CREATE TABLE t(s1 INT ); QueryOK,0 rows affected(0.06sec) mysql INSERT INTO t VALUES (5); QueryOK,1

一、基本语法及简单实例

1、创建简单的测试环境

[sql] view plaincopy

1. mysql> use test;  
2. Database changed  
3. mysql> show tables;  
4. Empty set (0.00 sec)  
5.    
6. mysql> CREATE TABLE t(s1 INT);  
7. Query OK, 0 rows affected (0.06 sec)  
8.    
9. mysql> INSERT INTO t VALUES(5);  
10. Query OK, 1 row affected (0.02 sec)  

2、选择分隔符

[sql] view plaincopy

1. mysql> DELIMITER //  

我们一般使用";"作为分隔符，但是在编写存储过程的时候这会带来一些问题，因为存储过程中有许多语句，修改会";"作为分隔符可使用语句"DELIMITER ;//"。

3、创建存储过程

[sql] view plaincopy

1. mysql> CREATE PROCEDURE p1() SELECT * FROM t;//  
2. Query OK, 0 rows affected (0.08 sec)  

"CREATE PROCEDURE"即为SQL语句部分，第二部分是过程名"p1"（这里需要注意的是存储过程名对大小写不敏感）。

第三部分 () 是参数列表，通常需要在其中添加参数，这里参数为空，但是"()"必须存在。

"SELECT * FROM t;"是存储过程的主体，注意哦，";"是主体的一部分哦，创建该存储过程的语句的真正结束符为"//"。

另外需要注意的一点是，和我们创建表一样，在创建存储过程前面需要检查是否存在同名的存储过程，即" DROP PROCEDURE IF EXISTS p1;"，没错这正是删除一个存储过程的SQL语句。另外，不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程。

4、调用存储过程

[sql] view plaincopy

1. mysql> CALL p1()//  
2. +------+  
3. | s1   |  
4. +------+  
5. |    5 |  
6. +------+  
7. 1 row in set (0.00 sec)  
8.    
9. Query OK, 0 rows affected (0.00 sec)  

这里只是简单的调用，在下一点关于参数的使用办法中有更为复杂的调用。

5、参数(Parameter)

[sql] view plaincopy

1. mysql> CREATE PROCEDURE p2(p INT) SET @x = p ;//  
2. Query OK, 0 rows affected (0.02 sec)  
3.    
4. mysql> CALL p2(123)//  
5. Query OK, 0 rows affected (0.00 sec)  
6.    
7. mysql> SELECT @x//  
8. +------+  
9. | @x   |  
10. +------+  
11. |  123 |  
12. +------+  
13. 1 row in set (0.01 sec)  

这是输入参数的例子，我们选择了会话变量@x证明成功的将参数传入了改变量。

[sql] view plaincopy

1. mysql> CREATE PROCEDURE p3(OUT p INT)  
2.     -> SET p = -5;//  
3. Query OK, 0 rows affected (0.00 sec)  
4.    
5. mysql> CALL p3(@y)//  
6. Query OK, 0 rows affected (0.01 sec)  
7.    
8. mysql> SELECT @y//  
9. +------+  
10. | @y   |  
11. +------+  
12. |   -5 |  
13. +------+  
14. 1 row in set (0.00 sec)  

这是输出参数的例子，我们选择会话变量@y去接收存储过程p3输出参数的值。

6、变量(Variables)

[sql] view plaincopy

1. CREATE PROCEDURE P5()  
2. BEGIN  
3.  DECLARE a INT;  
4.  DECLARE b INT;  
5.  SET a = 5;  
6.  SET b = 5;  
7.  INSERT INTO t VALUES(a);  
8.  SELECT s1 FROM t WHERE s1>= b;  
9. END;  
10. -------------------------------------------------  
11. mysql> CALL p5();  
12. +----+  
13. | s1 |  
14. +----+  
15. |  5 |  
16. |  5 |  
17. +----+  
18. 2 rows in set  
19.    
20. Query OK, 0 rows affected  

在过程中定义的变量并不是真正的定义，你只是在BEGIN/END（即复合语句）块内定义了而已。注意这些变量和会话变量不一样，不能使用修饰符@你必须清楚的在BEGIN/END块中声明变量和它们的类型。变量一旦声明，你就能在任何能使用会话变量、文字、列名的地方使用。还需要注意的一点是，在一个块内，我们需要把所有要使用的变量先声明，才能在后面使用，并且不能在声明变量的语句间夹杂其他使用变量的语句，否会报语法错误。

[sql] view plaincopy

1. CREATE PROCEDURE P6()  
2. BEGIN  
3.  DECLARE a,b INT DEFAULT 5;  
4.  INSERT INTO t VALUES(a);  
5.  SELECT s1 * a FROM t WHERE s1>= b;  
6. END;  
7. ------------------------------------------------------  
8. mysql> CALL p6();  
9. +--------+  
10. | s1 * a |  
11. +--------+  
12. |     25 |  
13. |     25 |  
14. |     25 |  
15. +--------+  

这里使用DEFAULT子句来设定初始值，如此我们可以不需要把DECLARE和SET语句的实现分开。

7、区块的定义使用

一般形式为

[sql] view plaincopy

1. begin  
2. ......  
3. end;  

也可以给区块起别名，如：

[sql] view plaincopy

1. lable:begin  
2. ...........  
3. end lable;  

可以用leave lable;跳出区块，执行区块以后的代码。

8、条件语句

一般形式为

[sql] view plaincopy

1. if 条件 then  
2. statement  
3. else  
4. statement  
5. end if;  

实例：

[sql] view plaincopy

1. CREATE PROCEDURE p7(IN param1 INT)  
2. BEGIN  
3.  DECLARE v1 INT;  
4.  SET v1 = param1 + 1;  
5.  IF v1 = 0 THEN  
6.    INSERT INTO t VALUES(17);  
7.  END IF;  
8.  IF param1 = 0 THEN  
9.    UPDATE t SET s1 = s1 + 1;  
10.  ELSE  
11.    UPDATE t SET s1 = s1 + 2;  
12.  END IF;  
13. END;//  
14. -----------------------------------------------------------  
15. mysql> SELECT * FROM t;  
16. +----+  
17. | s1 |  
18. +----+  
19. |  6 |  
20. |  6 |  
21. |  6 |  
22. +----+  
23. 3 rows in set  
24.    
25. mysql> CALL p7(0);  
26. Query OK, 3 rows affected  
27.    
28. mysql> CALL p7(0);  
29. Query OK, 3 rows affected  
30.    
31. mysql> SELECT * FROM t;  
32. +----+  
33. | s1 |  
34. +----+  
35. |  8 |  
36. |  8 |  
37. |  8 |  
38. +----+  
39. 3 rows in set  

过程很简单，可以看出调用两次即执行了两次UPDATE t SET s1= s1 + 1;语句。另外还有CASE指令，使用办法和IF一样简单，简单实例如下：

[sql] view plaincopy

1. CREATE PROCEDURE p8(IN param1 INT)  
2. BEGIN  
3.  DECLARE v1 INT;  
4.  SET v1 = param1 + 1;  
5.  CASE v1  
6.    WHEN 0 THEN INSERT INTO tVALUES(17);  
7.    WHEN 1 THEN INSERT INTO tVALUES(18);  
8.    ELSE INSERT INTO tVALUES(19);  
9.  END CASE;  
10. END;//  

9、循环语句

1）while循环

[sql] view plaincopy

1. [label:] WHILE expression DO  
2. statements  
3. END WHILE [label] ;  

实例：

[sql] view plaincopy

1. CREATE PROCEDURE p9 ()   
2. BEGIN   
3.   DECLARE v INT;   
4.   SET v = 0;   
5.   WHILE v 
6.     INSERT INTO t VALUES(v);   
7.     SET v = v + 1;   
8.   END WHILE;   
9. END; //   

2）repeat until循环

[sql] view plaincopy

1. [label:] REPEAT  
2. statements  
3. UNTIL expression  
4. END REPEAT [label] ;  

实例：

[sql] view plaincopy

1. CREATE PROCEDURE p10 ()   
2. BEGIN   
3.   DECLARE v INT;   
4.   SET v = 0;   
5.   REPEAT   
6.     INSERT INTO t VALUES(v);   
7.     SET v = v + 1;   
8.     UNTIL v >= 5                                       
9.   END REPEAT;   
10. END; //  

3）loop循环

[sql] view plaincopy

1. [label:] LOOP  
2. statements  
3. END LOOP[label];  

实例：

[sql] view plaincopy

1. CREATE PROCEDUREp11 ()   
2. BEGIN   
3.   DECLARE v INT;   
4.   SET v = 0;   
5.   loop_label: LOOP   
6.     INSERT INTO t VALUES (v);   
7.     SET v = v + 1;   
8.     IF v >= 5 THEN   
9.       LEAVE loop_label;   
10.     END IF;   
11.   END LOOP;   
12. END; //  

10、其他常用命令

1）showprocedure status

显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等

2）show createprocedure sp_name

显示某一个存储过程的详细信息

 

二、常见错误及处理办法

1、[Err] 1064 -You have an error in your SQL syntax; check the manual that corresponds to yourMySQL server version for the right syntax to use near '***'

很简单，1064即为SQL语法错误，仔细检查错误提示信息所指语句附近改正即可。

例：

[sql] view plaincopy

1. CREATE PROCEDURE P12()  
2. BEGIN  
3.  DECLARE a INT;  
4.  SET a = 5;  
5.  DECLARE b INT;  
6.  SET b = 5;  
7.  INSERT INTO t VALUES(a);  
8.  SELECT s1 FROM t WHERE s1>= b;  
9. END;  

提示信息为：

[Err] 1064 - You have an error in your SQL syntax; check the manualthat corresponds to your MySQL server version for the right syntax to use near'DECLARE b INT;

        SET b = 5;

        INSERT INTO t VALUES(a);

        SELECT s1 FROM t WHE' at line 5

提示在第5行，我们发现在变量声明语句"DECLARE b INT;"的前面有一条赋值语句"SET a = 5;"，只需将其放到所有变量声明语句之后即可。

2、[Err] 1318 -Incorrect number of arguments for PROCEDURE *.*; expected *, got *

如提示信息，database_name.procedure_name的存储过程传入的参数个数不对。

例：

[sql] view plaincopy

1. CREATE PROCEDURE p13(OUT p INT)  
2. SET p = -5;  
3. CALL p13();  

提示信息为：

[Err] 1318 - Incorrect number of arguments for PROCEDURE test.p13;expected 1, got 0

改为CALL p13(@a); 即可。

3、[Err] 1414 -OUT or INOUT argument 1 for routine *.* is not a variable or NEWpseudo-variable in BEFORE trigger

此信息也是提示我们传入的参数不对，*.*的存储过程参数为输出（或输入）参数，而我们可能传入相反的参数，例如要求为输出参数，而我们传入的参数非会话变量，即会报此错。

例：

CALL p13(a); -- 或者CALL p13(0);

提示信息：

[Err] 1414 - OUT or INOUT argument 1 for routine test.p13 is not avariable or NEW pseudo-variable in BEFORE trigger

改正：

CALL p13(@a);

SELECT @a;

 

参考资料：

1、《mysql 5.0存储过程学习总结》--平凡的世界http://www.ccvita.com/100.html

2、《MYSQL 5.0存储过程》--Peter Gulutzan 著 陈朋奕 译

http://blog.csdn.net/horace20 ^_^