（13） 存储过程入门
最后更新于：2022-04-01 23:40:09
**目录**
[TOC]
## 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」就会储存国家数量了：
';