Stored Procedure Overview

存储过程通常通过CALL语句进行调用。它可以有输入参数、输出参数和既有输入也有输出的参数。

创建存储过程

下面是一个存储过程的简单示例：

DELIMITER //

CREATE PROCEDURE Reset_animal_count() 
 MODIFIES SQL DATA
 UPDATE animal_count SET animals = 0;
//

DELIMITER ;

首先，修改了系统默认的语句结束符（;）为（//）。这个过程被命名为 Reset_animal_count 。 modified SQL DATA 表示该过程将执行类似于写操作的操作，并修改数据。它仅用于咨询目的。最后，是实际的SQL语句——UPDATE语句。

SELECT * FROM animal_count;
+---------+
| animals |
+---------+
|     101 |
+---------+

CALL Reset_animal_count();

SELECT * FROM animal_count;
+---------+
| animals |
+---------+
|       0 |
+---------+

下面是一个带有参数信息的更为复杂的实例，该实例来源于银行实际程序：

CREATE PROCEDURE
  Withdraw                             /* Routine name */
  (parameter_amount DECIMAL(6,2),     /* Parameter list */
  parameter_teller_id INTEGER,
  parameter_customer_id INTEGER)
  MODIFIES SQL DATA                   /* Data access clause */
  BEGIN                        /* Routine body */
    UPDATE Customers
        SET balance = balance - parameter_amount
        WHERE customer_id = parameter_customer_id;
    UPDATE Tellers
        SET cash_on_hand = cash_on_hand + parameter_amount
        WHERE teller_id = parameter_teller_id;
    INSERT INTO Transactions VALUES (
        parameter_customer_id,
        parameter_teller_id,
        parameter_amount);
  END;

参见 CREATE PROCEDURE 全语法释义.

为什么要使用存储过程?

一个关键原因是出于安全性考虑。银行通常使用存储过程，以防止应用程序和用户直接访问表。在多语言和多客户端环境中，通常使用存储过程来完成相同操作。

存储过程的清单和定义

要查看服务器上哪些存储过程正在运行，可以使用 SHOW PROCEDURE STATUS.

SHOW PROCEDURE STATUS\G
*************************** 1. row ***************************
                          Db: test
                     Name: Reset_animal_count
                       Type: PROCEDURE
                    Definer: root@localhost
                  Modified: 2013-06-03 08:55:03
                   Created: 2013-06-03 08:55:03
          Security_type: DEFINER
                Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

或直接在 INFORMATION_SCHEMA数据库中查询 routines table：

SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES 
  WHERE ROUTINE_TYPE='PROCEDURE';
+--------------------+
| ROUTINE_NAME       |
+--------------------+
| Reset_animal_count |
+--------------------+

To find out what the stored procedure does, use SHOW CREATE PROCEDURE.

SHOW CREATE PROCEDURE Reset_animal_count\G
*************************** 1. row ***************************
           Procedure: Reset_animal_count
            sql_mode: 
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `Reset_animal_count`()
    MODIFIES SQL DATA
UPDATE animal_count SET animals = 0
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

删除和更新存储过程

To drop a stored procedure, use the DROP PROCEDURE statement.

DROP PROCEDURE Reset_animal_count();

To change the characteristics of a stored procedure, use ALTER PROCEDURE. However, you cannot change the parameters or body of a stored procedure using this statement; to make such changes, you must drop and re-create the procedure using CREATE OR REPLACE PROCEDURE (which retains existing privileges), or DROP PROCEDURE followed CREATE PROCEDURE .

存储过程的权限

参见 Stored Routine Privileges.