当前位置:Gxlcms > mysql > MySQL存储过程学习笔记

MySQL存储过程学习笔记

时间:2021-07-01 10:21:17 帮助过:18人阅读

一、基本语法及简单实例 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. mysql> CREATE TABLE t(s1 INT);
  6. Query OK, 0 rows affected (0.06 sec)
  7. mysql> INSERT INTO t VALUES(5);
  8. 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. 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. mysql> CALL p2(123)//
  4. Query OK, 0 rows affected (0.00 sec)
  5. mysql> SELECT @x//
  6. +------+
  7. | @x |
  8. +------+
  9. | 123 |
  10. +------+
  11. 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. mysql> CALL p3(@y)//
  5. Query OK, 0 rows affected (0.01 sec)
  6. mysql> SELECT @y//
  7. +------+
  8. | @y |
  9. +------+
  10. | -5 |
  11. +------+
  12. 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. 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. mysql> CALL p7(0);
  25. Query OK, 3 rows affected
  26. mysql> CALL p7(0);
  27. Query OK, 3 rows affected
  28. mysql> SELECT * FROM t;
  29. +----+
  30. | s1 |
  31. +----+
  32. | 8 |
  33. | 8 |
  34. | 8 |
  35. +----+
  36. 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 < 5 DO
  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 ^_^

人气教程排行