MySQL入门 (十二) : Stored Routines 入门

1 Stored Routines的应用

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

mysql_13_snap_01

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

mysql_13_snap_02

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

mysql_13_snap_03

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

mysql_13_snap_04

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

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

mysql_13_snap_05

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

mysql_13_snap_06

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

mysql_13_snap_07

你同样可以在资料库中建立许多需要的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元件的基本语法:

mysql_13_snap_08

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

mysql_13_snap_09

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

mysql_13_snap_10

1.2 Stored Functions介绍

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

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

mysql_13_snap_11

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

mysql_13_snap_12

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_13_snap_13

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

mysql_13_snap_14

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

mysql_13_snap_15

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

mysql_13_snap_16

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

mysql_13_snap_65

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

mysql_13_snap_19

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

mysql_13_snap_20

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

mysql_13_snap_21

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

mysql_13_snap_22

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

mysql_13_snap_23

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

mysql_13_snap_24

注:在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_13_snap_63

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

mysql_13_snap_57

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

mysql_13_snap_58

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

mysql_13_snap_59

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

2.3 管理Stored Functions

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

mysql_13_snap_64

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

mysql_13_snap_60

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

mysql_13_snap_61

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

mysql_13_snap_62

在建立好的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中:

mysql_13_snap_36

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

mysql_13_snap_37

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

mysql_13_snap_38

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

mysql_13_snap_39

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

mysql_13_snap_40

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

mysql_13_snap_41

3.2 Stored Procedures的参数

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

mysql_13_snap_42

下列是参数用途的说明:

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

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

mysql_13_snap_43

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

mysql_13_snap_44

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

mysql_13_snap_45

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

mysql_13_snap_46

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

mysql_13_snap_47

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

mysql_13_snap_48

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

mysql_13_snap_49

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

mysql_13_snap_50

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

mysql_13_snap_54

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

mysql_13_snap_52

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

mysql_13_snap_53

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

mysql_13_snap_54

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

mysql_13_snap_55

 

翻译自: http://www.codedata.com.tw/database/mysql-tutorial-13-stored-routines/

更多参考:

MySQL 超新手入门教程系列

MySQL入门 (一) : 资料库概论与MySQL的安装

MySQL入门 (二) : SELECT 基础查询

MySQL入门 (三) : 运算式与函式

MySQL入门 (四) : JOIN 与UNION 查询

MySQL入门 (五) : CRUD 与资料维护

MySQL入门 (六) : 字元集与资料库

MySQL入门 (七) : 储存引擎与资料型态

MySQL入门 (八) : 表格与索引

MySQL入门 (九) : 子查询 Subquery

MySQL入门 (十) : Views

MySQL入门 (十一) : Prepared Statements

MySQL入门 (十三) : Stored Routines 的变数与流程

 

本文: MySQL入门 (十二) : Stored Routines 入门

Loading

Add a Comment

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.