MySQL 存储过程与数据库交互

虽然我们可以使用 MySQL 存储程序语言来执行传统的编程任务，但实际上几乎所有的存储程序都会通过执行 SQL 语句来与数据库进行交互。下面将介绍怎样在存储过程中使用 SQL 语句。

存储程序中使用非 SELECT SQL

当我们在存储程序中包含一条不返回结果集的 SQL 语句（如 UPDATE、INSERT 或 SET 语句）时，它的执行方式将与在其他上下文中执行时完全相同（如从 PHP 调用或从 MySQL 命令行发出）。

存储程序中的 SQL 语句与存储程序外的语法相同。SQL 语句可以完全访问任何存储程序变量，这些变量可用于通常提供给 SQL 的文字或表达式的任何地方。

你可以在存储程序中使用所有主要类别的 SQL 语句。可以不受限制地使用 DML、DDL 和实用程序语句。

示例：在存储程序中嵌入非 SELECT 语句，如：

CREATE PROCEDURE simple_sqls()
BEGIN
   -- 声明一个变量 i，默认值为 1
   DECLARE i INT DEFAULT 1;

   -- 实用语句示例，设置不自动提交
   SET autocommit=0;

   -- DDL 语句示例，创建数据表
   DROP TABLE IF EXISTS test_table ;
   CREATE TABLE test_table (
       id        INT PRIMARY KEY,
       some_data VARCHAR(30)
   ) ENGINE=innodb;

   -- INSERT 语句中使用变量 i
   WHILE (i<=10) DO
        INSERT INTO TEST_TABLE VALUES(i, CONCAT("record ",i));
        SET i=i+1;
   END WHILE;

   -- UPDATE 语句中使用变量 i
   SET i=5;
   UPDATE test_table
      SET some_data=CONCAT("I updated row ",i)
    WHERE id=i;

   -- DELETE 语句中使用变量 i
   DELETE FROM test_table WHERE id>i;

END;

mysql 广告位

使用带有 INTO 子句的 SELECT 语句

如果 SELECT 语句只返回一条记录，则可以在 SELECT 语句中使用 INTO 语句将该记录返回到存储过程的变量中。

这种 SELECT 语句的格式如下：

SELECT expression1 [, expression2 ....]
   INTO variable1 [, variable2 ...]
   other SELECT statement clauses

示例：展示了如何检索单个客户的详细信息，客户 ID 作为参数传递。如：

CREATE PROCEDURE get_customer_details(in_customer_id INT)
BEGIN
   -- 声明变量
   DECLARE l_customer_name     VARCHAR(30);
   DECLARE l_contact_surname   VARCHAR(30);
   DECLARE l_contact_firstname VARCHAR(30);

   SELECT customer_name, contact_surname,contact_firstname
     INTO l_customer_name,l_contact_surname,l_contact_firstname
     FROM customers
    WHERE customer_id=in_customer_id;
END;

如果 SQL 语句返回多于一条记录，就会出现运行时错误。例如，如果我们省略了示例中的 WHERE 子句，在尝试运行存储过程时就会出现以下错误：

mysql> CALL get_customer_details(2);
ERROR 1172 (42000): Result consisted of more than one row

使用无限制 SELECT 语句

MySQL 存储过程（但不是函数）可以向调用程序返回结果集（但遗憾的是，不能直接返回给另一个存储过程）。只要返回结果集的 SQL 语句与 INTO 子句或游标无关，存储过程就会返回结果集。我们称这些 SQL 语句为无限制语句。这类 SQL 语句通常是 SELECT 语句，不过存储过程中也可以包含其他返回结果集的语句 SHOW、EXPLAIN、DESC 等。

在许多示例中，我们都使用了无限制的 SELECT 语句来返回存储过程的执行信息。为了调试目的，或者为了向用户或调用程序返回一些有用的状态信息，你很可能也会这样做。

例如：显示了一个存储过程的示例，该存储过程使用这一功能返回特定部门的雇员列表。

CREATE DEFINER=`root`@`localhost` PROCEDURE `demo`(in_department_id INT)
BEGIN
   SELECT * FROM departments
    WHERE department_id=in_department_id;

   SELECT * FROM employees
    WHERE department_id=in_department_id;
END

调用存储过程，输出如下：

mysql> call demo(1);
+---------------+-----------------+
| department_id | department_name |
+---------------+-----------------+
|             1 | 人事部          |
+---------------+-----------------+
1 row in set (0.05 sec)

+-------------+---------------+---------------+--------------+
| employee_id | department_id | employee_name | employee_age |
+-------------+---------------+---------------+--------------+
|           1 |             1 | 张三          |           22 |
|           2 |             1 | 李四          |           27 |
+-------------+---------------+---------------+--------------+
2 rows in set (0.12 sec)

Query OK, 0 rows affected (0.00 sec)

在某些方面，使用存储过程以这种方式返回结果集的功能与创建视图以支持特定查询的功能类似。与视图一样，存储过程可以封装复杂的 SQL 操作，从而使用户更容易检索数据，而不必了解模式设计的复杂性。在存储过程中封装 SQL 还能提高安全性，因为在返回结果集之前，可以执行复杂的验证检查，甚至加密/解密。

与视图不同，存储过程可以返回多个结果集，如上面示例。返回多个结果集是一种方便的方法，可以在对数据库的一次调用中封装生成多组应用程序数据所需的所有逻辑。

将结果集返回给另一个存储过程

我们知道可以将结果集返回给调用程序（如 PHP），但有没有办法将结果集返回给另一个存储过程呢？

遗憾的是，将结果集从一个存储过程传递到另一个存储过程的唯一方法是通过临时表传递结果。这是一种笨拙的解决方案，而且由于临时表的作用域贯穿整个会话，因此会产生许多与使用全局变量相同的可维护性问题。但如果一个存储程序需要向另一个存储程序提供结果，那么临时表就是最好的解决方案。例如：

CREATE DEFINER=`root`@`localhost` PROCEDURE `demo`()
BEGIN
    -- 如果存在，则删除临时表
    DROP TEMPORARY TABLE IF EXISTS departments_tmp;
    -- 创建临时表
    CREATE TEMPORARY TABLE departments_tmp AS
    SELECT * FROM departments;
END

调用存储过程，输出如下：

mysql> call demo();
Query OK, 2 rows affected (0.01 sec)

mysql> select * from departments_tmp;
+---------------+-----------------+
| department_id | department_name |
+---------------+-----------------+
|             1 | 人事部          |
|             2 | 技术部          |
+---------------+-----------------+
2 rows in set (0.06 sec)

下面示例中，我们从临时表中消耗临时表中的数据。在实际操作中，这几乎等同于将结果集从一个存储过程传递到另一个存储过程。

CREATE DEFINER=`root`@`localhost` PROCEDURE `demo2`()
BEGIN
    DECLARE l_department_id INT;
    DECLARE l_department_name VARCHAR(255);
    DECLARE l_last_sale INT DEFAULT 0;
    -- 定义临时部门表的游标
    DECLARE dept_tmp_csr CURSOR FOR
    SELECT department_id,department_name FROM departments_tmp;
    -- 定义错误处理器
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_sale=1;
    
    -- 调用其他过程
    CALL demo();
    
    -- 迭代临时表
    OPEN dept_tmp_csr;
    myLoop:LOOP
        FETCH dept_tmp_csr INTO l_department_id,l_department_name;
        IF l_last_sale THEN
            LEAVE myLoop;
        END IF;
        
        SELECT l_department_id,l_department_name;
    END LOOP myLoop;
    CLOSE dept_tmp_csr;
END

调用存储过程，输出如下：

mysql> call demo2();
+-----------------+-------------------+
| l_department_id | l_department_name |
+-----------------+-------------------+
|               1 | 人事部            |
+-----------------+-------------------+
1 row in set (0.03 sec)

+-----------------+-------------------+
| l_department_id | l_department_name |
+-----------------+-------------------+
|               2 | 技术部            |
+-----------------+-------------------+
1 row in set (0.07 sec)

Query OK, 0 rows affected (0.01 sec)

注意：在 MySQL 中，临时表的作用域仅限于创建表的特定会话，当该会话结束时，临时表就会自动解除分配。因此，我们不必担心清理临时表，也不必担心该表可能同时被其他会话更新。