Question

1 Stored Routines 的应用

在资料库管理系统的应用中，不论是一般或网页的应用程式，它们在执行资料查询与维护的时候，都必须使用 SQL 叙述来请资料库执行各种不同的工作。在比较复杂的应用程式需求下，很常会遇到类似下列的一组工作：

SQL 叙述的特点是一次只能执行一件工作，所以要完成上列的工作，就必须执行数个 SQL 叙述。如果这样的一组工作是很常执行的，你就可以考虑把这些要执行的叙述建立为「Stored procedure」元件：

把这一组工作建立为 Stored procedure 元件以后，以后要执行这些工作时，就可以「呼叫、call」这个建立好的 Stored procedure 元件：

要建立人口数比「USA」多的国家表格时，只要传入指定的国家代码就可以了：

Stored procedures 是 Stored routines 其中一种元件，你可以视需要在资料库中建立许多不同用途的 Stored procedure。它可以包含你需要执行的一组工作，也可以依照需求设定必要的参数资料（例如上列「new_mycountry」中的国家代码)。呼叫这些建立好的 Stored procedure 可以帮你省掉很多繁复的工作，请资料库一次完成你要执行的工作。

Stored routines 另外提供一种「Stored functions」元件，除了 MySQL 资料库提供许多各种不同的函式外，你也可以建立自己的函式，这种函式称为 Stored functions。例如下列的范例：

你可以自己建立一个名称为「ROUND2」的 Stored functions，这个函式固定将一个指定的数值四舍五入到小数两位：

建立好需要的 Stored function 元件以后，它使用起来就跟你在使用 MySQL 提供的函式一样：

你同样可以在资料库中建立许多需要的 Stored functions，把一些比较复杂工作建立为 Stored functions 元件以后，你就可以跟使用 MySQL 提供的函式一样来使用它们，同样可以简化许多繁复的工作。

在 MySQL 资料库管理系统中，把 Stored procedures 与 Stored functions 合称为「Stored routines」。在后续的内容中，会把 Stored procedures 简称为「procedures」；把 Stored functions 简称为「functions」。

1.1 Stored Procedures 介绍

Stored procedures 元件也是一种可以建立、维护与删除的资料库元件。表格元件是用来储存资料用的；索引元件是储存索引与增加效率用的；而 Stored procedures 元件是用来「储存程序」用的，程序表示一组特定的工作，如果在使用资料的过程中，常常需要执行一组同样的工作，你就可以考虑把执行工作需要的叙述建立为 Stored procedures 元件。

下列是建立 Stored procedures 元件的基本语法：

下列是删除 Stored procedures 元件的基本语法：

下列是呼叫 Stored procedures 元件的基本语法：

1.2 Stored Functions 介绍

如果 MySQL 提供的函式无法完成你的工作，或是想要改善一些比较复杂的叙述，你都可以建立需要的 Sotred functions 元件。跟 Stored procedures 一样，它也是一种用来「储存程序」的元件，不过建立好的 Stored procedures 元件要使用「CALL」来呼叫，也就是请资料库执行储存在 Stored procedures 中的工作；要使用建立好的 Stored functions 元件，就跟使用 MySQL 提供的函式一样来使用它们。

下列是建立 Stored functions 元件的基本语法：

下列是删除 Stored functions 元件的基本语法：

2 在 MySQL Workbench 中管理 Stored routines

Stored routines 元件中可以包含许多要执行的 SQL 叙述，在后续的讨论中，它也可以包含宣告与设定变数，和控制执行流程的指令。所以 Stored routines 元件其实就有一点类似开发应用程式用的程式语言，不过它不会像程式语言那么复杂，而且大部份都是跟资料库相关的 SQL 叙述。

2.1 SQL Script、DELIMITER 与 Stored routines

建立需要的 Stored routines 元件要使用「CREATE PROCEDURE」或「CREATE FUNCTION」叙述，虽然它们跟其它的 SQL 叙述一样，也是请资料库执行一件工作，不过 Stored routines 通常会包含许多需要的叙述，所以通常会使用「SQL script」来执行建立 Stored routines 的工作。

SQL script 是一个包含许多 SQL 叙述的档案，你可以把想要执行的 SQL 叙述都集中在一个档案中。以建立课程范例资料库的「cmdev.sql」档案来说，它的内容会像这样：

MySQL 使用分号作为预设的 delimiter，delimiter 在 SQL script 档案中的使用是很重要的，MySQL 在执行档案中的叙述时，是以 delimiter 来分辨一个 SQL 叙述的范围。MySQL 提供「DELIMITER」指令，可以修改预设的 delimiter 符号：

在一般的应用时，你通常不会去修改预设的 delimiter 符号；可是在建立 Stored routines 元件的 SQL script 档案中就一定要使用了。下列是建立 Stored procedure 元件的基本内容：

在「MySQL Workbench」中选择功能表「File > New Query Tab」，接下来就可以输入下列建立 procedure 的叙述：

完成建立 procedure 的叙述后，要执行这个叙述来建立需要的 procedure 元件：

上列范例所建立的「show_countries」procedure 元件中，只有包含一个查询国家资料的叙述，如果一个 procedure 元件执行的工​​作只是这样的话，应该就不需要建立 procedure 元件了。所以 procedure 元件通常会包含许多要执行的叙述，这时候就一定要使用「BEGIN」与「END」。下列是建立包含多个叙述 Stored procedure 元件的基本内容：

以下列的「my_world_count」procedure 元件来说，它可以一次查询国家、语言与城市三个表格的数量：

使用 SQL script 建立 functions 同样要使用「DELIMITER」关键字设定 delimiter。「CREATE FUNCTION」的语法另外包含「RETURNS」与「RETURN」两个关键字。下列是建立 Stored functions 的基本内容：

以下列的「my_date」Stored function 来说，它会传回「年/月/日时:分:秒星期」格式的日期时间资料：

如果 function 元件包含许多要执行的叙述，也一定要使用「BEGIN」与「END」。下列是建立包含多个叙述 Stored functions 元件的基本内容：

下列建立「my_date2」Stored function 的叙述中，因为包含多个叙述，所以一定要使用「BEGIN」与「END」：

注：在 Stored routines 中使用「DECLARE」与「SET」在「Sotred Routines 的变数与流程」中讨论。

2.2 管理 Stored Procedures

除了使用 SQL script 建立需要的 Stored Procedures 外，你也可以使用「MySQL Workbench」提供的功能来管理 Stored Procedures。以建立 procedure 元件来说，在「Stored Procedures」目录上按滑鼠右键后选择「Create Stored Procedure…」：

MySQL Workbench 会帮你准备一个建立 procedure 元件的样版，你只要在「BEGIN」与「END」之间，输入这个 procedure 元件需要执行的叙述，再选择「Apply」按钮：

在确认的视窗选择「Apply」按钮：

最后选择「Finish」就可以建立指定的 Stored Procedure：

在建立好的 Stored Procedure 上按滑鼠右键后选择「Alter Stored Procedure…」和「Drop Stored Procedure…」可以修改或删除指定的 Stored Procedure。

2.3 管理 Stored Functions

你也可以使用「MySQL Workbench」提供的功能来管理 Stored functions。以建立 function 元件来说，在「Functions」目录上按滑鼠右键后选择「Create Function…」：

MySQL Workbench 会帮你准备一个建立 Function 元件的样版，输入这个 Function 的内容后，再选择「Apply」按钮：

在确认的视窗选择「Apply」按钮：

最后选择「Finish」就可以建立指定的 Function：

在建立好的 Function 上按滑鼠右键后选择「Alter Function…」和「Drop Function…」可以修改或删除指定的 Function。

3 Stored Routines 的参数

Stored routines 可以使用参数(parameters) 让使用者传送资料给 stored routines 使用，procedures 与 functions 都可以依照需要决定参数的个数与型态。

3.1 Stored Functions 的参数

Functions 参数的决定会比 procedures 简单，因为 functions 的参数只是用来接收资料后，在 functions 中使用。你必须决定每一个参数的名称和型态，再依照想要的顺序定义在 functions 中：

以下列一个合计功能的 function 来说，它需要两个「INT」型态的整数参数：

在呼叫「my_summary」的时候，依照参数的定义，指定两个要合计的整数数值，这个 function 会将两个传入的整数数值加起来后回传给你：

在呼叫 function 的时候，一定要依照参数的定义，传送正确个数的参数资料：

除了参数的个数外，你也要遵守参数型态的规定：

一个 function 的定义不一定需要参数，以下列的范例来说，呼叫「my_date」时并不需要传送任何参数资料，不过无论是否需要参数，在呼叫 function 时，名称后面的左右刮号是不可以省略的：

3.2 Stored Procedures 的参数

Procedures 参数的定义与 functions 大致上相同，除了必须决定每一个参数的名称、型态与顺序，你还需要决定每一个参数的用途：

下列是参数用途的说明：

• IN：「输入、input」用的参数。这种参数与 functions 中的参数完全一样，在呼叫 procedures 时传送资料给 procedures 用的
• OUT：「输出、output」用的参数。在呼叫 procedures 时，不能接收传送的资料，不过在执行 procedures 时，可以设定这类参数的值，新的值在执行完成后，可以回传给呼叫的地方使用
• INOUT：「输入与输出、input 与 output」用的参数。同时具有「IN」与「OUT」两种用途

下列是一个说明三种用途参数的范例：

呼叫 procedures 时要依照定义的参数个数与型态来传送资料：

在呼叫 Procedures 时传送的参数资料，会因为不同的用途而有不同的限制：

如果违反参数用途上的规定就会发生错误：

所以在呼叫 procedures 时，「OUT」与「INOUT」参数必须指定变数名称，这是因为「OUT」与「INOUT」参数在执行完成后会回传资料，使用变数名称才可以接收 procedures 回传的资料：

执行 procedures 以后，指定给「OUT」与「INOUT」的变数名称，就会储存 procedures 中设定的值：

如果在呼叫 procedures 之前，先把参数资料设定为使用者变数，再把它们指定给参数使用：

执行上列呼叫 procedures 的叙述后，你可以发现设定为「OUT」用途的参数是不能接收参数资料的；而下列查询使用者变数的叙述，可以发现设定为「IN」用途的参数没有回传资料的功能：

以下列的范例来说，呼叫「country_count」需要一个洲名的参数，执行以后，它会使用你的洲名执行查询国家的数量。这个洲名参数的需求，只是用来设定查询条件用的，并不需要回传资料，所以这样的参数适合设定为「IN」：

下列的范例先设定好一个使用者变数储存洲名，再呼叫「country_count」：

在 procedures 与 functions 中，MySQL 提供一种特别的查询叙述。一般的查询叙述是用来回传需要的资料用的，而这种查询叙述可以把「SELECT」子句中指定的资料指定给变数：

以下列的范例来说，呼叫「country_count2」需要一个洲名的参数，它会使用你的洲名执行查询国家的数量，不过执行以后，它会回传国家的数量给你。所以这个 procedure 需要第二个参数用来回传国家的数量，以这样的参数需求，国家的数量的参数适合设定为「OUT」：

呼叫「country_count2」时要提供洲名与接收国家数量的变数名称，在 procedure 执行以后，使用者变数「my_count」就会储存国家数量了：