• Tags ,
  •         
  • www.breakyizhan.com
  •    

    1 宣告与使用变数

    在Stored routines中,除了可以宣告需要的参数外,如果需要处理比较复杂的资料,你也可以宣告「区域变数、local variables」。下列是宣告区域变数的语法与位置:

    mysql_14_snap_01

    下列是几种宣告区域变数的范例:

    mysql_14_snap_02

    宣告需要的区域变数后,你就可以在stored routines中使用它们,需要指定变数值的话,可以使用下列两种语法:

    mysql_14_snap_03

    下列是宣告与使用「SET」叙述指定变数的范例:

    mysql_14_snap_04

    下列的范例使用「SELECT」叙述,把查询叙述回传的资料指定给变数:

    mysql_14_snap_05

    在Stored routines中宣告区域变数,一定要放在「BEGIN」与「END」区块中:

    mysql_14_snap_06

    在一个Stored routines中,除了基本的「BEGIN」与「END」区块,也可以再使用「BEGIN」与「END」设定一个区块,每一个区块都可以宣告需要的区域变数:

    mysql_14_snap_07

    在「BEGIN」与「END」区块中宣告的区域变数,只有在宣告的区块中有效,这也是它称为区域变数的原因:

    mysql_14_snap_08

    如果你使用一个已经被清除的区域变数,在建立stored routines时不会有问题,不过使用的时候就会发生错误:

    mysql_14_snap_09

    在同一个区块宣告变数时,不可以使用同样的变数名称;不过你可以在内层区块中,使用外层区块已经使用过的变数名称,可是要特别注意它们的有效范围:

    mysql_14_snap_10

    注:在撰写stored routines时,如果在多个区块中宣告变数,应该还是使用不同的变数名称会好一些。

    2 判断

    建立与使用stored routines可以帮你一次执行许多叙述,简化资料库的操作;除了这个好处外,stored routines还提供许多判断的语法,让你可以执行需要的判断,再根据判断的结果执行不同的工作。

    2.1 IF

    MySQL在stored routines中提供「IF」叙述,你可以在「IF」叙述中设定判断的条件,与条件成立时要执行的工作。下列是「IF」叙述的语法:

    mysql_14_snap_11

    下列的procedure范例接收一个表示体重的整数参数,它会使用这个参数来判断体重是否太重,如果超过100公斤的话,就会显示「You are heavy!」:

    mysql_14_snap_12

    呼叫上列的「test_weight」procedure范例会有下列的结果:

    mysql_14_snap_13

    如果你希望体重超过100公斤时,显示「You are heavy!」,体重没有超过100公斤时,显示「Good!」。这样的需求可以在「IF」叙述中使用「ELSEIF」判断其它需要的条件:

    mysql_14_snap_14

    呼叫上列的「test_weight2」procedure范例会有下列的结果:

    mysql_14_snap_15

    你可以依照需求在「IF」叙述中使用多个「ELSEIF」来判断不同的条件,也可以使用一个「ELSE」来处理所有条件都不成立时要执行的工作:

    mysql_14_snap_16

    呼叫上列的「test_weight3」procedure范例会有下列的结果:

    mysql_14_snap_17

    标准体重会依照身高与性别而不同,所以会有类似下列这样的表格:

    身高范围 性别 标准体重
    160~164 58
    52
    165~169 60
    56
    170~174 64
    60

    下列是一个依照上列表格所完成的标准体重函式:

    mysql_14_snap_18

    完成可以传回标准体重的「std_weight」函式以后,就可以用在下列这个判断体重的procedure中:

    mysql_14_snap_19

    2.2 CASE

    在stored routines中还可以使用「CASE」叙述执行条件判断的工作。「CASE」叙述有两种语法,第一种语法跟「IF」叙述是很类似的:

    mysql_14_snap_20

    以判断体重的需求来说,使用「CASE」叙述同样可以完成:

    mysql_14_snap_21

    「CASE」叙述还可以使用下列这种语法:

    mysql_14_snap_22

    这样的语法很适合使用在类似「ENUM」资料型态的判断,例如下列这个判断季节的procedure:

    mysql_14_snap_23

    使用这种「CASE」语法来执行判断工作时,要特别注意错误资料的处理:

    mysql_14_snap_24

    你应该加入「ELSE」来预防错误资料造成的问题:

    mysql_14_snap_25

    3 回圈

    在stored routines中如果需要执行一个工作多次的时候,就可以使用「回圈、loops」,搭配使用判断与回圈,把一些固定又繁复的工作撰写成stored routines储存起来,可以大幅度简化资料库的操作。

    3.1 WHILE

    下列是可以用来执行一个工作多次的「WHILE」回圈语法:

    mysql_14_snap_26

    你必须依照需求设定「WHILE」回圈语法中的判断条件,由它来控制回圈是否继续执行:

    mysql_14_snap_27

    下列的「summary_while」范例可以为你从1开始加总到参数指定的数字:

    mysql_14_snap_28

    3.2 REPEAT

    下列是可以用来执行一个工作多次的「REPEAT」回圈语法:

    mysql_14_snap_29

    你必须依照需求在「REPEAT」回圈语法中的「UNTIL」设定判断条件,由它来控制回圈是否继续执行:

    mysql_14_snap_30

    下列的「summary_repeat」范例可以为你从1开始加总到参数指定的数字:

    mysql_14_snap_31

    3.3 LOOP

    下列是可以用来执行一个工作多次的「LOOP」回圈语法:

    mysql_14_snap_32

    如果只是单纯的使用「LOOP」回圈的话,只要进入回圈后,就会不断重复执行回圈中的叙述,永远不会停止:

    mysql_14_snap_33

    4 标签

    在使用「BEGIN-END」、「WHILE」、「REPEAT」与「LOOP」四种区块时,都可以为它们设定「标签、label」:

    mysql_14_snap_34

    标签是由你自己为这些区块取的名字,下列使用「LOOP」回圈来说明标签的设定规则,这个规则同样适用在其它三种区块:

    mysql_14_snap_35

    在一般的状况下,通常不需要为区块设定标签。如果为了控制sotred routines的执行流程,才会设定区块的标签。设定标签以后,就可以搭配使用「LEAVE」叙述来控制流程,下列是「LEAVE」叙述在「LOOP」回圈中的效果:

    mysql_14_snap_36

    「LEAVE」叙述在其它三种区块中有同样的效果:

    mysql_14_snap_37

    搭配使用「LEAVE」叙述来控制流程,就可以控制「LOOP」回圈在需要的时候离开。下列的「summary_loop」范例可以为你从1开始加总到参数指定的数字:

    mysql_14_snap_38

    设定标签以后,也可以搭配使用「ITERATE」叙述来控制流程,下列是「ITERATE」叙述在「LOOP」回圈中的效果:

    mysql_14_snap_39

    「ITERATE」叙述不可以使用在「BEGIN-END」区块中,不过它在其它两种区块中有同样的效果:

    mysql_14_snap_40

    下列的「summary_iterate」范例可以为你从1开始加总到参数指定的数字,不过额外使用「ITERATE」叙述控制,让这个function只会加总奇数:

    mysql_14_snap_41

     
    转载请保留页面地址:https://www.breakyizhan.com/sql/6220.html