MySQL Stored Routines 入门:Stored Procedures和Stored Functions

作者: Arvin Chen 分类: SQL 来源: Break易站(www.breakyizhan.com)

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

 

  •   本文标题:MySQL Stored Routines 入门:Stored Procedures和Stored Functions - Break易站
    转载请保留页面地址:https://www.breakyizhan.com/sql/5596.html

    "MySQL Stored Routines 入门:Stored Procedures和Stored Functions"的笔记

    • SQL存储过程的优缺点

      优点

      • ①重复使用。存储过程可以重复使用,从而可以减少数据库开发人员的工作量。
      • ②减少网络流量。存储过程位于服务器上,调用的时候只需要传递存储过程的名称以及参数就可以了,因此降低了网络传输的数据量。
      • ③安全性。参数化的存储过程可以防止SQL注入式攻击,而且可以将Grant、Deny以及Revoke权限应用于存储过程。

      简单讲:

      • 1.存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
      • 2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时),可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
      • 3.存储过程可以重复使用,可减少数据库开发人员的工作量
      • 4.安全性高,可设定只有某些用户才具有对指定存储过程的使用权

      有一点需要注意的是,一些网上盛传的所谓的存储过程要比sql语句执行更快的说法,实际上是个误解,并没有根据,包括微软内部的人也不认可这一点,所以不能作为正式的优点,希望大家能够认识到这一点。
      缺点

      • 1:调试麻烦,但是用 PL/SQL Developer 调试很方便!弥补这个缺点。
      • 2:移植问题,数据库端代码当然是与数据库相关的。但是如果是做工程型项目,基本不存在移植问题。
      • 3:重新编译问题,因为后端代码是运行前编译的,如果带有引用关系的对象发生改变时,受影响的存储过程、包将需要重新编译(不过也可以设置成运行时刻自动编译)。
      • 4: 如果在一个程序系统中大量的使用存储过程,到程序交付使用的时候随着用户需求的增加会导致数据结构的变化,接着就是系统的相关问题了,最后如果用户想维护该系统可以说是很难很难、而且代价是空前的,维护起来更麻烦。
      Break易站 2019年6月28日 下午4:35
      微信返利机器人
      免费:淘宝,京东,拼多多优惠券
      腾讯,爱奇艺,优酷的VIP视频免费解析,免费看
      即刻扫描二维码,添加微信机器人!

    发表笔记

    电子邮件地址不会被公开。 必填项已用*标注