创建和使用多语句存储过程-MySQL数据库设计与实现

2023-10-29 理论教育版权反馈

【摘要】：单语句存储过程非常有用，但存储过程的真正功能在于它能够封装和执行多个语句。因为这两个应用程序都需要能够计算奖金数额，所以这个任务非常适合使用存储函数实现。本节余下部分主要介绍创建多语句存储过程时常用的语法。

单语句存储过程非常有用，但存储过程的真正功能在于它能够封装和执行多个语句。事实上，为此专门提供了一种语言，允许完成相当复杂的任务，例如根据条件计算和迭代处理。例如，假设由一个内部销售部门推动公司的收入的增加。为让该部门完成这个艰难的目标，要向员工们的月薪中增加奖金，奖金的数目与该员工销售收入成正比。公司在内部处理工资，使用一个定制Java程序来计算和打印每年年终时的员工奖金；但是，同时为销售部门提供了一个基于Web的界面，可以实时监视奖金的变化（及奖金数额）。因为这两个应用程序都需要能够计算奖金数额，所以这个任务非常适合使用存储函数实现。创建这个存储函数的语法如下：

然后，如下调用calculate bonus函数：

此函数返回类似下面的结果：

尽管这个示例包含了一些新语法（后面将介绍所有这些语法），但它还是非常简单的。本节余下部分主要介绍创建多语句存储过程时常用的语法。

1.BEGIN和END块

当创建多语句存储过程时，需要将语句包围在BEGIN/END块中。此块的形式如下：

注意，块中每条语句必须以分号结尾。

2.条件

基于运行时信息执行任务是严格控制条件输出的关键。存储过程语法为执行条件计算提供了的两种众所周知的构造：IF-ELSEIF-ELSE语句和CASE语句。本节介绍这两种构造。

➢ IF-ELSEIF-ELSE

IF-ELSEIF-ELSE语句是计算条件语句最常用的方式之一。事实上，即使是新手程序员，也可能已经在很多情况下使用过这个语句。因此，对此介绍应当并不陌生。其形式如下：

例如，假设修改了前面创建的calculate—bonus存储过程，确定奖金比例不仅基于销售情况，还要基于销售人员在公司供职的年数：

➢ CASE

需要比较一组可能的值时CASE语句很有用。虽然这个任务肯定可以使用IF语句完成，但使用CASE语句将极大地提高可读性。其形式如下：

考虑如下示例，它将客户的状态和一组值进行比较，设置一个包含适当销售税率的变量：

另外，可以通过如下形式减少键入的代码：

3.迭代

有些任务（例如向表中插入一些新记录）需要能够重复地执行一组语句。本节介绍能够迭代执行和退出循环的各种方法。

➢ ITERATE(www.chuimin.cn)

执行ITERATE语句将使嵌入该语句的LOOP、REPEAT或WHILE循环返回到顶部，并再次执行。其形式如下：

➢ LEAVE

在得到变量的值或特定任务的结果之后，可能希望通过LEAVE命令立即退出循环或BEGIN/END块。其形式如下：

LEAVE的示例将在下面对LOOP的介绍中给出。也可以从前一个示例中发现该例子。

➢ LOOP

LOOP语句将不断地迭代处理定义在其代码块中的一组语句，直到遇到LEAVE为止。其形式如下：

MySQL存储过程无法接受数组作为输入参数，但可以传入并解析一个定界字符串来模拟此行为。例如，假设为客户提供一个界面，可以从10种公司服务中选择要对哪些服务有更多了解。该界面可以表现为一个多选框、复选框或其他方式；使用什么方式并不重要，因为最终这些值将在传给存储过程之前连接成一个字符串（例如使用PHP的implode（）函数）。例如，该字符串可能如下，其中每个数字表示所需要服务的数值标识符：1，3，4.7，8，9，10。

解析此字符串并向数据库插入这些值的存储过程如下：

现在调用service_info，如下：

执行之后，request_info表会包含如下3条记录：

➢ REPEAT

REPEAT语句在操作上几乎与WHILE相同，只要某个条件为真，就一直循环处理指定的一条语句或一组语句。但是，与WHILE不同，REPEAT在每次迭代之后而不是之前计算条件，很像PHP的DO_WHILE结构。其形式如下：

例如，假设要测试一组新的应用程序，希望构建一个存储过程，它可以使用指定的一些测试记录填充一个表。此过程如下：

在rows参数中传入5，执行此过程，得到如下结果：

➢ WHILE

WHILE语句在很多（甚至可能是全部）现代程序语言中都很常见，只要某个条件或一组条件为真，就一直迭代处理一条或几条语句。其形式如下：

下面重新改写前面介绍REPEAT时创建的test data过程，这一次使用州ILE循环：

执行此过程将得到与REPEAT一节类似的结果。