MySQL存储过程学习笔记

最新推荐文章于 2022-05-28 16:43:39 发布
最新推荐文章于 2022-05-28 16:43:39 发布
阅读量650 收藏 1
点赞数

一、基本语法及简单实例

1、创建简单的测试环境

[sql] view plain copy print ?
  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)  
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)
 
mysql> CREATE TABLE t(s1 INT);
Query OK, 0 rows affected (0.06 sec)
 
mysql> INSERT INTO t VALUES(5);
Query OK, 1 row affected (0.02 sec)

2、选择分隔符

[sql] view plain copy print ?
  1. mysql> DELIMITER //  
mysql> DELIMITER //

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

3、创建存储过程

[sql] view plain copy print ?
  1. mysql> CREATE PROCEDURE p1() SELECT * FROM t;//  
  2. Query OK, 0 rows affected (0.08 sec)  
mysql> CREATE PROCEDURE p1() SELECT * FROM t;//
Query OK, 0 rows affected (0.08 sec)

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

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

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

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

4、调用存储过程

[sql] view plain copy print ?
  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)  
mysql> CALL p1()//
+------+
| s1   |
+------+
|    5 |
+------+
1 row in set (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)

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

5、参数(Parameter)

[sql] view plain copy print ?
  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)  
mysql> CREATE PROCEDURE p2(p INT) SET @x = p ;//
Query OK, 0 rows affected (0.02 sec)
 
mysql> CALL p2(123)//
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT @x//
+------+
| @x   |
+------+
|  123 |
+------+
1 row in set (0.01 sec)

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

[sql] view plain copy print ?
  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)  
mysql> CREATE PROCEDURE p3(OUT p INT)
    -> SET p = -5;//
Query OK, 0 rows affected (0.00 sec)
 
mysql> CALL p3(@y)//
Query OK, 0 rows affected (0.01 sec)
 
mysql> SELECT @y//
+------+
| @y   |
+------+
|   -5 |
+------+
1 row in set (0.00 sec)

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

6、变量(Variables)

[sql] view plain copy print ?
  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  
CREATE PROCEDURE P5()
BEGIN
 DECLARE a INT;
 DECLARE b INT;
 SET a = 5;
 SET b = 5;
 INSERT INTO t VALUES(a);
 SELECT s1 FROM t WHERE s1>= b;
END;
-------------------------------------------------
mysql> CALL p5();
+----+
| s1 |
+----+
|  5 |
|  5 |
+----+
2 rows in set
 
Query OK, 0 rows affected

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

[sql] view plain copy print ?
  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. +--------+  
CREATE PROCEDURE P6()
BEGIN
 DECLARE a,b INT DEFAULT 5;
 INSERT INTO t VALUES(a);
 SELECT s1 * a FROM t WHERE s1>= b;
END;
------------------------------------------------------
mysql> CALL p6();
+--------+
| s1 * a |
+--------+
|     25 |
|     25 |
|     25 |
+--------+

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

7、区块的定义使用

一般形式为

[sql] view plain copy print ?
  1. begin  
  2. ......  
  3. end;  
begin
......
end;

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

[sql] view plain copy print ?
  1. lable:begin  
  2. ...........  
  3. end lable;  
lable:begin
...........
end lable;

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

8、条件语句

一般形式为

[sql] view plain copy print ?
  1. if 条件 then  
  2. statement  
  3. else  
  4. statement  
  5. end if;  
if 条件 then
statement
else
statement
end if;

实例：

[sql] view plain copy print ?
  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  
CREATE PROCEDURE p7(IN param1 INT)
BEGIN
 DECLARE v1 INT;
 SET v1 = param1 + 1;
 IF v1 = 0 THEN
   INSERT INTO t VALUES(17);
 END IF;
 IF param1 = 0 THEN
   UPDATE t SET s1 = s1 + 1;
 ELSE
   UPDATE t SET s1 = s1 + 2;
 END IF;
END;//
-----------------------------------------------------------
mysql> SELECT * FROM t;
+----+
| s1 |
+----+
|  6 |
|  6 |
|  6 |
+----+
3 rows in set
 
mysql> CALL p7(0);
Query OK, 3 rows affected
 
mysql> CALL p7(0);
Query OK, 3 rows affected
 
mysql> SELECT * FROM t;
+----+
| s1 |
+----+
|  8 |
|  8 |
|  8 |
+----+
3 rows in set

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

[sql] view plain copy print ?
  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;//  
CREATE PROCEDURE p8(IN param1 INT)
BEGIN
 DECLARE v1 INT;
 SET v1 = param1 + 1;
 CASE v1
   WHEN 0 THEN INSERT INTO tVALUES(17);
   WHEN 1 THEN INSERT INTO tVALUES(18);
   ELSE INSERT INTO tVALUES(19);
 END CASE;
END;//


9、循环语句

1）while循环

[sql] view plain copy print ?
  1. [label:] WHILE expression DO  
  2. statements  
  3. END WHILE [label] ;  
[label:] WHILE expression DO
statements
END WHILE [label] ;

实例：

[sql] view plain copy print ?
  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; //   
CREATE PROCEDURE p9 () 
BEGIN 
  DECLARE v INT; 
  SET v = 0; 
  WHILE v < 5 DO 
    INSERT INTO t VALUES(v); 
    SET v = v + 1; 
  END WHILE; 
END; //


2）repeat until循环

[sql] view plain copy print ?
  1. [label:] REPEAT  
  2. statements  
  3. UNTIL expression  
  4. END REPEAT [label] ;  
[label:] REPEAT
statements
UNTIL expression
END REPEAT [label] ;

实例：

[sql] view plain copy print ?
  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; //  
CREATE PROCEDURE p10 () 
BEGIN 
  DECLARE v INT; 
  SET v = 0; 
  REPEAT 
    INSERT INTO t VALUES(v); 
    SET v = v + 1; 
    UNTIL v >= 5                                     
  END REPEAT; 
END; //


3）loop循环

[sql] view plain copy print ?
  1. [label:] LOOP  
  2. statements  
  3. END LOOP[label];  
[label:] LOOP
statements
END LOOP[label];

实例：

[sql] view plain copy print ?
  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; //  
CREATE PROCEDUREp11 () 
BEGIN 
  DECLARE v INT; 
  SET v = 0; 
  loop_label: LOOP 
    INSERT INTO t VALUES (v); 
    SET v = v + 1; 
    IF v >= 5 THEN 
      LEAVE loop_label; 
    END IF; 
  END LOOP; 
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 plain copy print ?
  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;  
CREATE PROCEDURE P12()
BEGIN
 DECLARE a INT;
 SET a = 5;
 DECLARE b INT;
 SET b = 5;
 INSERT INTO t VALUES(a);
 SELECT s1 FROM t WHERE s1>= b;
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 plain copy print ?
  1. CREATE PROCEDURE p13(OUT p INT)  
  2. SET p = -5;  
  3. CALL p13();  
CREATE PROCEDURE p13(OUT p INT)
SET p = -5;
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、创建简单的测试环境

[sql] view plain copy print ?
  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)  
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)
 
mysql> CREATE TABLE t(s1 INT);
Query OK, 0 rows affected (0.06 sec)
 
mysql> INSERT INTO t VALUES(5);
Query OK, 1 row affected (0.02 sec)

2、选择分隔符

[sql] view plain copy print ?
  1. mysql> DELIMITER //  
mysql> DELIMITER //

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

3、创建存储过程

[sql] view plain copy print ?
  1. mysql> CREATE PROCEDURE p1() SELECT * FROM t;//  
  2. Query OK, 0 rows affected (0.08 sec)  
mysql> CREATE PROCEDURE p1() SELECT * FROM t;//
Query OK, 0 rows affected (0.08 sec)

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

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

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

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

4、调用存储过程

[sql] view plain copy print ?
  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)  
mysql> CALL p1()//
+------+
| s1   |
+------+
|    5 |
+------+
1 row in set (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)

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

5、参数(Parameter)

[sql] view plain copy print ?
  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)  
mysql> CREATE PROCEDURE p2(p INT) SET @x = p ;//
Query OK, 0 rows affected (0.02 sec)
 
mysql> CALL p2(123)//
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT @x//
+------+
| @x   |
+------+
|  123 |
+------+
1 row in set (0.01 sec)

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

[sql] view plain copy print ?
  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)  
mysql> CREATE PROCEDURE p3(OUT p INT)
    -> SET p = -5;//
Query OK, 0 rows affected (0.00 sec)
 
mysql> CALL p3(@y)//
Query OK, 0 rows affected (0.01 sec)
 
mysql> SELECT @y//
+------+
| @y   |
+------+
|   -5 |
+------+
1 row in set (0.00 sec)

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

6、变量(Variables)

[sql] view plain copy print ?
  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  
CREATE PROCEDURE P5()
BEGIN
 DECLARE a INT;
 DECLARE b INT;
 SET a = 5;
 SET b = 5;
 INSERT INTO t VALUES(a);
 SELECT s1 FROM t WHERE s1>= b;
END;
-------------------------------------------------
mysql> CALL p5();
+----+
| s1 |
+----+
|  5 |
|  5 |
+----+
2 rows in set
 
Query OK, 0 rows affected

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

[sql] view plain copy print ?
  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. +--------+  
CREATE PROCEDURE P6()
BEGIN
 DECLARE a,b INT DEFAULT 5;
 INSERT INTO t VALUES(a);
 SELECT s1 * a FROM t WHERE s1>= b;
END;
------------------------------------------------------
mysql> CALL p6();
+--------+
| s1 * a |
+--------+
|     25 |
|     25 |
|     25 |
+--------+

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

7、区块的定义使用

一般形式为

[sql] view plain copy print ?
  1. begin  
  2. ......  
  3. end;  
begin
......
end;

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

[sql] view plain copy print ?
  1. lable:begin  
  2. ...........  
  3. end lable;  
lable:begin
...........
end lable;

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

8、条件语句

一般形式为

[sql] view plain copy print ?
  1. if 条件 then  
  2. statement  
  3. else  
  4. statement  
  5. end if;  
if 条件 then
statement
else
statement
end if;

实例：

[sql] view plain copy print ?
  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  
CREATE PROCEDURE p7(IN param1 INT)
BEGIN
 DECLARE v1 INT;
 SET v1 = param1 + 1;
 IF v1 = 0 THEN
   INSERT INTO t VALUES(17);
 END IF;
 IF param1 = 0 THEN
   UPDATE t SET s1 = s1 + 1;
 ELSE
   UPDATE t SET s1 = s1 + 2;
 END IF;
END;//
-----------------------------------------------------------
mysql> SELECT * FROM t;
+----+
| s1 |
+----+
|  6 |
|  6 |
|  6 |
+----+
3 rows in set
 
mysql> CALL p7(0);
Query OK, 3 rows affected
 
mysql> CALL p7(0);
Query OK, 3 rows affected
 
mysql> SELECT * FROM t;
+----+
| s1 |
+----+
|  8 |
|  8 |
|  8 |
+----+
3 rows in set

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

[sql] view plain copy print ?
  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;//  
CREATE PROCEDURE p8(IN param1 INT)
BEGIN
 DECLARE v1 INT;
 SET v1 = param1 + 1;
 CASE v1
   WHEN 0 THEN INSERT INTO tVALUES(17);
   WHEN 1 THEN INSERT INTO tVALUES(18);
   ELSE INSERT INTO tVALUES(19);
 END CASE;
END;//


9、循环语句

1）while循环

[sql] view plain copy print ?
  1. [label:] WHILE expression DO  
  2. statements  
  3. END WHILE [label] ;  
[label:] WHILE expression DO
statements
END WHILE [label] ;

实例：

[sql] view plain copy print ?
  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; //   
CREATE PROCEDURE p9 () 
BEGIN 
  DECLARE v INT; 
  SET v = 0; 
  WHILE v < 5 DO 
    INSERT INTO t VALUES(v); 
    SET v = v + 1; 
  END WHILE; 
END; //


2）repeat until循环

[sql] view plain copy print ?
  1. [label:] REPEAT  
  2. statements  
  3. UNTIL expression  
  4. END REPEAT [label] ;  
[label:] REPEAT
statements
UNTIL expression
END REPEAT [label] ;

实例：

[sql] view plain copy print ?
  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; //  
CREATE PROCEDURE p10 () 
BEGIN 
  DECLARE v INT; 
  SET v = 0; 
  REPEAT 
    INSERT INTO t VALUES(v); 
    SET v = v + 1; 
    UNTIL v >= 5                                     
  END REPEAT; 
END; //


3）loop循环

[sql] view plain copy print ?
  1. [label:] LOOP  
  2. statements  
  3. END LOOP[label];  
[label:] LOOP
statements
END LOOP[label];

实例：

[sql] view plain copy print ?
  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; //  
CREATE PROCEDUREp11 () 
BEGIN 
  DECLARE v INT; 
  SET v = 0; 
  loop_label: LOOP 
    INSERT INTO t VALUES (v); 
    SET v = v + 1; 
    IF v >= 5 THEN 
      LEAVE loop_label; 
    END IF; 
  END LOOP; 
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 plain copy print ?
  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;  
CREATE PROCEDURE P12()
BEGIN
 DECLARE a INT;
 SET a = 5;
 DECLARE b INT;
 SET b = 5;
 INSERT INTO t VALUES(a);
 SELECT s1 FROM t WHERE s1>= b;
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 plain copy print ?
  1. CREATE PROCEDURE p13(OUT p INT)  
  2. SET p = -5;  
  3. CALL p13();  
CREATE PROCEDURE p13(OUT p INT)
SET p = -5;
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;

不哭_死神
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Mysql存储过程学习笔记–建立简单的存储过程
12-15
一、存储过程 　存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户 通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。而...
三篇学会MySQL数据库【查询详解】
很高兴你能看到这里
05-24 1万+
MySQL万字教程，从入门到精通，第二篇查询，第三篇讲讲解视图，存储过程，触发器，事物，维护，备份恢复等高级知识，单独写一篇是因为查询使用频率太高，一万五千字，放上完美无水印脑图，方便下载后期复习，原创不易，一键三联，多多支持呀！⛽......
mysql子查询出错_MySQL 子查询（三） 派生表、子查询错误
weixin_35825868的博客
01-18 760
八、派生表派生表是一个表达式，用于在一个查询的FROM子句的范围内生成表。例如，在一个SELECT查询的FROM子句中的子查询，就是一个派生表。SELECT ... FROM (subquery) [AS] tbl_name ...[AS] tbl_name子句是必需的，因为FROM子句中的每个表都必须具有名称。且派生表中的任何列都必须具有唯一名称。为了便于说明，假设现在有这样一个表：CREATE...
1414 mysql_mysql常见错误
weixin_39669629的博客
01-26 1218
常见错误及处理办法1、[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '***'很简单，1064即为SQL语法错误，仔细检查错误提示信息所指语句附近改正即...
MySQL_10事务
TSCCG的博客
07-23 734
MySQL_10事务 1.什么是事务？ 一个事务其实就是一个完整的业务逻辑。 是一个最小的工作单元，不可再分。 什么是一个完整的业务逻辑？ ​ 假设进行转账，从A账户向B账户转账1000， ​ 将A账户的余额减去1000(update 语句) ​ 将B账户的余额加上1000(update 语句) ​ 这就是一个完整的业务逻辑。 以上操作是一个最小的工作单元，要么同时成功，要么同时失败,不可再分。 以上两个DML语句才会有事务这一说，其它语句和事务无关。 2.只有DML语句和事务有关 insert d
python-logging.config【日志模块】
gexiaobaoHelloWorld的专栏
09-03 3085
1，logging模块简介  Python的logging模块提供了通用的日志系统，熟练使用longgong模块可以方便开发者开发第三方模块或者是自己的Python应用。同样这个模块提供不同的日志级别，并可以采用不同的方式记录日志，比如文件，HTTP、GET/POST，SMTP，Socket等，甚至可以自己实现具体的日志记录方式。下文我将主要介绍如何使用文件方式记录log。         l
Mysql存储过程学习笔记--建立简单的存储过程
09-10
我们常用的操作数据库语言SQL语句在执行的时候需要要先编译，然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该...
MySQL视图及存储过程学习笔记
最新发布
10-12
MySQL视图及存储过程学习笔记
MySQL学习笔记MySQL学习笔记
05-03
数据的重要性也就在一定程度上影射了数据库的重要性，因为数据领域的最重要的安全问题、存储问题、关系问题等，很多方面的整合都需要依靠数据库来完成。 举个例子，现在有很多数据分析师，他们每天最基本的工作往往...
MySQL定时器EVENT学习笔记
12-15
— 创建事件 –每隔一秒自动调用e_test()存储过程 CREATE EVENT IF NOT EXISTS event_test ON SCHEDULE EVERY 1 SECOND ON COMPLETION PRESERVE DO CALL e_test(); — 开启事件 ALTER EVENT event
redis学习笔记--复制(Master/Slave)
new_ord的博客
10-25 493
复制 复制就是我们所说的主从复制，主服务器数据更新后根据配置和策略，自动同步到从服务器的master/slaver机制，Master以写为主，Slave以读为主，Redis使用默认的异步复制，其特点是低延迟和高性能。 配置从库(slave) 方式一：修改redis.conf配置文件 slaveof 主服务器IP 主库服务器port 方式二：使用slaveof 主服务器IP 主服务器port命令,但是每次主服务器断开都需要重新连接 查看复制信息 显示与复制相关的信息 info replication ma
肝了五万字把SQL数据库从基础到高级所有命令写的明明白白，内容实在丰富，MySQL这一篇就够了
很高兴你能看到这里
05-28 3545
肝了五万字把SQL数据库从基础到高级所有命令写的明明白白，内容实在丰富，MySQL这一篇就够了，如果觉得不错可以关注博主，一起进步 🦢
基本loggerg使用
终是庄周
08-02 890
1. 一个最基本的例子 使用Logging框架写Log基本上就三个步骤 引入loggerg类和logger工厂类 声明logger 记录日志 //1. 引入slf4j接口的Logger和LoggerFactory import org.slf4j.Logger; import org.slf4j.LoggerFactory; public class UserService { //2. 声...
mysql 查询时报 Empty set (0.00 sec)
weixin_44191019的博客
10-14 1万+
其实就是提示你实体表为空，说的是你的数据库或表里数据为空，或者查找的信息为空。可以直接在表里看一下数据，并核对一下查询语句。
MySQL讲义第 54 讲——MySQL用户权限管理之用户权限管理概述
睿思达DBA
12-06 377
MySQL讲义第 54 讲——MySQL用户权限管理之用户权限管理概述 如果用户使用 MySQL 中的各种资源，首先在连接 MySQL 时需要进行身份验证。当用户通过身份验证后，此用户只能进行权限范围内的各种操作。 MySQL 使用 mysql 数据库中的 4 张表来控制用户权限，分别为：mysql.user 表、mysql.db 表、mysql.tables_priv 表和mysql.columns_priv 表。 一、MySQL 权限的级别 MySQL 权限分为全局权限、数据库级别、数据库对象级别（表、
Empty set (0.00 sec)
热门推荐
huiqinbo的专栏
04-14 2万+
估计很多人在mysql  Command Line Client  doc端都遇到过这样的抛出：Empty set (0.00 sec) 其实就是提示你实体表为空，说的是你的数据库或表里数据为空，你添加上数据就可以了 更多交流，更多了解：QQ 444084929 ...
MySQL主从同步show master status; Empty set (0.01 sec)主库无master状态问题解决
lucky_ykcul的博客
10-29 1万+
在进行MySQL主从复制过程中出现主库master无状态的问题 MariaDB [(none)]> show master status; Empty set (0.01 sec) 查看master配置文件 [root@server1 local]# vim /etc/my.cnf log-bin=mysql-bin #启用二进制日志 server-id=1 正常情况下配置了log-b...
数据库-举例（前面的基础知识中使用的数据库）
hhyiyuanyu的博客
07-09 191
MariaDB [jikedb]&gt; show tables;+------------------+| Tables_in_jikedb |+------------------+| addressbook      || forums           || leibusi          || new              || table_string     || table...
mysql 存储过程学习
08-16
存储过程是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中，经过第一次编译后调用不需要再次编译。用户可以通过指定存储过程的名称并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* [mysql存储过程学习笔记](https://blog.csdn.net/qq_33157666/article/details/87877246)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v92^chatsearchT0_1"}}] [.reference_item style="max-width: 50%"] - *2* *3* [MySQL存储过程学习](https://blog.csdn.net/qq_42249896/article/details/108361298)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v92^chatsearchT0_1"}}] [.reference_item style="max-width: 50%"] [ .reference_list ]

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值