第23章、存储程序和视图

本章讨论存储的程序和视图，这些数据库对象是根据存储在服务器上供以后执行的SQL代码定义的数据库对象。

存储的程序包括这些对象：

• 存储例程，即存储过程和函数。使用该CALL语句调用存储过程 。过程没有返回值，但可以修改其参数供调用者稍后检查。它也可以生成返回给客户端程序的结果集。存储的功能与内置功能非常相似。您可以在表达式中调用它并在表达式评估期间返回一个值。
• 触发。触发器是一个与表关联的已命名数据库对象，当表发生特定事件（如插入或更新）时，该对象将被激活。
• 活动。事件是服务器按计划运行的任务。

视图是被存储的查询，当被引用时产生结果集。视图充当虚拟表格。

定义存储程序

每个存储的程序都包含一个由SQL语句组成的主体。该语句可能是一个复合语句，由多个由;字符分隔的语句组成。例如，以下的存储过程的主体由一个BEGIN...END包含SET语句的块和一个REPEAT本身包含另一个SET语句的循环组成。：

CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
  SET @x = 0;
  REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END;

默认情况下delimiter使用;分隔语句，MySQL遇到;号就执行对应的SQL语句。可以使用delimiter重新定义分隔符，通常我们定义为//或者$$。

delimiter //
CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
  SET @x = 0;
  REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END
//

使用存储例程（过程和函数）

存储的例程是一组可存储在服务器中的SQL语句。完成此操作后，客户端不需要重新发布单个语句，而是可以引用存储的例程。

存储的例程在某些情况下特别有用：

• 当多个客户端应用程序以不同语言编写或在不同平台上工作时，需要执行相同的数据库操作。
• 安全至关重要时。例如，银行为所有常见操作使用存储过程和函数。这提供了一致且安全的环境，并且例程可以确保每个操作都被正确记录。在这样的设置中，应用程序和用户将不能直接访问数据库表，但只能执行特定的存储例程。

存储的例程还使您能够在数据库服务器中拥有函数库。这是现代应用程序语言所共有的一项功能，可在内部启用此类设计（例如，通过使用类）。即使在数据库使用范围之外，使用这些客户端应用程序语言功能对程序员也是有益的。

存储的例程语法

过程

• CREATE PROCEDURE
• DROP PROCEDURE
• ALTER PROCEDURE

函数

• CREATE FUNCTION
• DROP FUNCTION
• ALTER FUNCTION

使用CALL语句调用过程。

函数不能递归，过程允许递归但默认情况是禁用的。要启动递归，需要将max_sp_recursion_depth服务器系统变量设置为大于零的值。存储过程递归增加了对线程堆栈空间的需求。如果增加值max_sp_recursion_depth，可能需要通过增加thread_stack服务器启动时的值来增加线程堆栈大小。

使用触发器

触发器是一个与表关联的命名数据库对象，当表发生特定事件时会激活该对象。触发器的一些用途是执行要插入到表中的值的检查或对更新中涉及的值执行计算。

触发器定义为在语句插入，更新或删除关联表中的行时激活。这些行操作是触发事件。例如，可以通过 INSERT或LOAD DATA语句插入行，并为每个插入的行激活插入触发器。触发器可以设置为在触发事件之前或之后激活。例如，可以在插入表的每一行之前或每更新一行之后激活触发器。

创建触发器：CREATE TRIGGER。 删除触发器：DROP TRIGGER，删除数据库表时触发器也会被一并删除。

下面是一个简单的例子，它将一个触发器与一个表相关联，以激活INSERT操作。触发器充当累加器，将插入到表格的其中一列中的值相加。

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
       FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.01 sec)

该CREATE TRIGGER语句创建一个名为ins_sum与account表关联的触发器。它还包括指定触发器动作时间，触发事件以及触发器激活时要执行的操作的子句：

• 关键字BEFORE指示触发器动作时间。在这种情况下，触发器会在每行插入表之前激活。这里另一个允许的关键字是AFTER。
• 关键字INSERT表示触发事件; 即激活触发器的操作类型。在该示例中，INSERT 操作会导致触发器激活。您也可以创建触发器DELETE和 UPDATE操作。
• 以下声明FOR EACH ROW 定义了触发器主体; 即每次触发器激活时要执行的语句，对于受触发事件影响的每一行都会发生一次。在该示例中，触发器主体很简单 SET ，它将插入到amount列中的值累加到用户变量中。该语句引用该列NEW.amount意味着 “ 要插入到新行中的amount列的值。”

要使用触发器，请将累加器变量设置为零，执行INSERT语句，然后查看该变量后面的值：

mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql> SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
|               1852.48 |
+-----------------------+