MySQL CREATE PROCEDURE语句的用法（附带实例）

简单地说，存储过程就是一条或者多条 SQL 语句的集合，可视为批文件，但是其作用不仅限于批处理。存储函数允许将业务逻辑或数据处理逻辑封装在数据库中，从而提高应用程序的性能和可维护性。

MySQL 中创建存储过程，需要使用 CREATE PROCEDURE 语句，基本语法格式如下：

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

各参数解释如下：
1) CREATE PROCEDURE 为用来创建存储过程的关键字。

2) sp_name 为存储过程的名称。

3) proc_parameter 为指定存储过程的参数列表，列表形式如下：

[ IN | OUT | INOUT ] param_name type

其中，IN 表示输入参数，OUT 表示输出参数，INOUT 表示既可以输入也可以输出；param_name 表示参数名称；type 表示参数的类型，该类型可以是 MySQL 数据库中的任意类型。

4) characteristics 指定存储过程的特性，有以下取值：

• 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：
  • CONTAINS SQL 表明子程序包含 SQL 语句，但是不包含读写数据的语句；
  • NO SQL 表明子程序不包含 SQL 语句；
  • READS SQL DATA 说明子程序包含读数据的语句；
  • MODIFIES SQL DATA 表明子程序包含写数据的语句。
• SQL SECURITY { DEFINER | INVOKER }：指明谁有权限来执行，默认情况下，系统指定为 DEFINER：
  • DEFINER 表示只有定义者才能执行；
  • INVOKER 表示拥有权限的调用者可以执行。

• COMMENT 'string'：注释信息，可以用来描述存储过程或存储函数。

5) routine_body 是 SQL 代码的内容，可以用 BEGIN...END 来表示 SQL 代码的开始和结束。

编写存储过程并不是一件简单的事情，可能需要复杂的 SQL 语句，并且要有创建存储过程的权限。但是，使用存储过程将简化操作，减少冗余的操作步骤；同时，还可以减少操作过程中的失误，提高效率。因此，存储过程是非常有用的，而且应该尽可能地学会使用。

下面的代码演示了一个存储过程的创建，其名称为“AvgFruitPrice”，返回所有水果的平均价格：

CREATE PROCEDURE AvgFruitPrice ()
    BEGIN
       SELECT AVG(f_price) AS avgprice
    FROM fruits;
    END;

上述代码中，名为“AvgFruitPrice”的存储过程使用 CREATE PROCEDURE AvgFruitPrice() 语句定义。此存储过程没有参数，但是后面的 () 仍然需要。BEGIN 和 END 语句用来限定存储过程体，过程本身仅是一个简单的 SELECT 语句（AVG() 为求字段平均值的函数）。

【实例】创建查看表 fruits 的存储过程，SQL 语句如下：

CREATE PROCEDURE Proc()
     BEGIN
        SELECT * FROM fruits;
     END ;

上述代码创建了一个查看表 fruits 的存储过程，每次调用这个存储过程的时候都会执行 SELECT 语句查看表的内容，代码的执行过程如下：

MySQL> DELIMITER //
MySQL> CREATE PROCEDURE Proc()
   -> BEGIN
   -> SELECT * FROM fruits;
   -> END //
Query OK, 0 rows affected (0.00 sec)
　　　　　　
MySQL> DELIMITER ;

这个存储过程和使用 SELECT 语句查看表的效果得到的结果是一样的。

当然，存储过程也可以是很多复杂语句的组合，其本身也可以调用其他的函数来组成更加复杂的操作。

“DELIMITER //”语句的作用是将 MySQL 的结束符设置为“//”。因为 MySQL 默认的语句结束符号为分号（;），为了避免与存储过程中 SQL 语句结束符相冲突，需要使用 DELIMITER 改变存储过程的结束符，并以“END //”结束存储过程。存储过程定义完毕之后再使用“DELIMITER ;”恢复默认结束符。DELIMITER 也可以指定其他符号作为结束符。

【实例】创建名称为“CountProc”的存储过程，SQL 语句如下：

CREATE PROCEDURE CountProc (OUT param1 INT)
    BEGIN
       SELECT COUNT(*) INTO param1 FROM fruits;
    END;

上述代码的作用是创建一个获取表 fruits 中的记录条数的存储过程，其名称是 CountProc；COUNT(*) 计算后把结果放入参数 param1 中。执行结果如下：

mysql> DELIMITER //
mysql> CREATE PROCEDURE CountProc(OUT param1 INT)
-> BEGIN
-> SELECT COUNT(*) INTO param1 FROM fruits;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;

当使用 DELIMITER 命令时，应该避免使用反斜杠（\）字符，因为反斜杠是 MySQL 的转义字符。