MySQL 5.0 新特性教程 存储过程:第三讲

MySQL 5.0 新特性教程 存储过程:第三讲 - 应用软件 - 电脑教程网

MySQL 5.0 新特性教程 存储过程:第三讲

日期:2006-03-12   荐:

The New SQL Statements 新SQL语句

Variables 变量

  在复合语句中声明变量的指令是DECLARE。

  (1) Example with two DECLARE statements

  两个DECLARE语句的例子


CREATE PROCEDURE p8 ()

BEGIN

DECLARE a INT;

DECLARE b INT;

SET a = 5;

SET b = 5;

INSERT INTO t VALUES (a);

SELECT s1 * a FROM t WHERE s1 >= b;

END; // /* I won't CALL this */


  在过程中定义的变量并不是真正的定义,你只是在BEGIN/END块内定义了而已(译注:也就是形参)。


  注意这些变量和会话变量不一样,不能使用修饰符@你必须清楚的在BEGIN/END块中声明变量和它们的类型。

  变量一旦声明,你就能在任何能使用会话变量、文字、列名的地方使用。

  (2) Example with no DEFAULT clause and SET statement

  没有默认子句和设定语句的例子

CREATE PROCEDURE p9 ()

BEGIN

DECLARE a INT /* there is no DEFAULT clause */;

DECLARE b INT /* there is no DEFAULT clause */;

SET a = 5; /* there is a SET statement */

SET b = 5; /* there is a SET statement */

INSERT INTO t VALUES (a);

SELECT s1 * a FROM t WHERE s1 >= b;

END; // /* I won't CALL this */


  有很多初始化变量的方法。如果没有默认的子句,那么变量的初始值为NULL。你可以在任何时候使用SET语句给变量赋值。

  (3) Example with DEFAULT clause

  含有DEFAULT子句的例子

CREATE PROCEDURE p10 ()

BEGIN

DECLARE a, b INT DEFAULT 5;

INSERT INTO t VALUES (a);

SELECT s1 * a FROM t WHERE s1 >= b;

END; //



  我们在这里做了一些改变,但是结果还是一样的。在这里使用了DEFAULT子句来设定初始值,这就不需要把DECLARE和SET语句的实现分开了。

  (4) Example of CALL

  调用的例子

mysql> CALL p10() //

--------

| s1 * a |

--------

| 25 |

| 25 |

--------

2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


  结果显示了过程能正常工作

  (5) Scope

  作用域

CREATE PROCEDURE p11 ()

BEGIN

DECLARE x1 CHAR(5) DEFAULT 'outer';

BEGIN

DECLARE x1 CHAR(5) DEFAULT 'inner';

SELECT x1;

END;

SELECT x1;

END; //



  现在我们来讨论一下作用域的问题。例子中有嵌套的BEGIN/END块,当然这是合法的。同时包含两个变量,名字都是x1,这样也是合法的。内部的变量在其作用域内享有更高的优先权。当执行到END语句时,内部变量消失,此时已经在其作用域外,变量不再可见了,因此在存储过程外再也不能找到这个声明了的变量,但是你可以通过OUT参数或者将其值指派 给会话变量来保存其值。

  调用作用域例子的过程:

mysql> CALL p11()//

-------

| x1 |

-------

| inner |

-------

-------

| x1 |

-------

| outer |

-------


  我们看到的结果时第一个SELECT语句检索到最内层的变量,第二个检索到第二层的变量

Conditions and IF-THEN-ELSE 条件式和IF-THEN-ELSE

  1.

  现在我们可以写一些包含条件式的例子:



CREATE PROCEDURE p12 (IN parameter1 INT)

BEGIN

DECLARE variable1 INT;

SET variable1 = parameter1 1;

IF variable1 = 0 THEN

INSERT INTO t VALUES (17);

END IF;

IF parameter1 = 0 THEN

UPDATE t SET s1 = s1 1;

ELSE

UPDATE t SET s1 = s1 2;

END IF;

END; //


  这里是一个包含IF语句的过程。里面有两个IF语句,一个是IF语句END IF,另一个是IF语句ELSE语句END IF。我们可以在这里使用复杂的过程,但我会尽量使其简单让你能更容易弄清楚。

  2.

CALL p12 (0) //


  我们调用这个过程,传入值为0,这样parameter1的值将为0。


  3.

CREATE PROCEDURE p12 (IN parameter1 INT)

BEGIN

DECLARE variable1 INT;

SET variable1 = parameter1 1; <--

IF variable1 = 0 THEN

INSERT INTO t VALUES (17);

END IF;

IF parameter1 = 0 THEN

UPDATE t SET s1 = s1 1;

ELSE

UPDATE t SET s1 = s1 2;

END IF;

END; //


  这里变量variable1被赋值为parameter1加1的值,所以执行后变量variable1为1。

  4.

CREATE PROCEDURE p12 (IN parameter1 INT)

BEGIN

DECLARE variable1 INT;

SET variable1 = parameter1 1;

IF variable1 = 0 THEN <--

INSERT INTO t VALUES (17);

END IF;

IF parameter1 = 0 THEN

UPDATE t SET s1 = s1 1;

ELSE

UPDATE t SET s1 = s1 2;

END IF;

END; //



  因为变量variable1值为1,因此条件"if variable1 = 0"为假,

IF

……

END IF


  被跳过,没有被执行。

  5.

CREATE PROCEDURE p12 (IN parameter1 INT)

BEGIN

DECLARE variable1 INT;

SET variable1 = parameter1 1;

IF variable1 = 0 THEN

INSERT INTO t VALUES (17);

END IF;

IF parameter1 = 0 THEN <--

UPDATE t SET s1 = s1 1;

ELSE

UPDATE t SET s1 = s1 2;

END IF;

END; //


  到第二个IF条件,判断结果为真,于是中间语句被执行了

  6.

CREATE PROCEDURE p12 (IN parameter1 INT)

BEGIN

DECLARE variable1 INT;

SET variable1 = parameter1 1;

IF variable1 = 0 THEN

INSERT INTO t VALUES (17);

END IF;

IF parameter1 = 0 THEN

UPDATE t SET s1 = s1 1; <--

ELSE

UPDATE t SET s1 = s1 2;

END IF;

END; //



  因为参数parameter1值等于0,UPDATE语句被执行。如果parameter1值为NULL,则下一条UPDATE语句将被执行现在表t中有两行,他们都包含值5,所以如果我们调用p12,两行的值会变成6。

  7.

mysql> CALL p12(0)//

Query OK, 2 rows affected (0.28 sec)

mysql> SELECT * FROM t//

------

| s1 |

------

| 6 |

| 6 |

------

2 rows in set (0.01 sec)


  结果也是我们所期望的那样。

CASE 指令

  1.

CREATE PROCEDURE p13 (IN parameter1 INT)

BEGIN

DECLARE variable1 INT;

SET variable1 = parameter1 1;

CASE variable1

WHEN 0 THEN INSERT INTO t VALUES (17);

WHEN 1 THEN INSERT INTO t VALUES (18);

ELSE INSERT INTO t VALUES (19);

END CASE;

END; //


  如果需要进行更多条件真假的判断我们可以使用CASE语句。CASE语句使用和IF一样简单。

  我们可以参考上面的例子:


  2.

mysql> CALL p13(1)//

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t//

------

| s1 |

------

| 6 |

| 6 |

| 19 |

------

3 rows in set (0.00 sec)


  执行过程后,传入值1,如上面例子,值19被插入到表t中。

  Question

  问题

  问题: CALL p13(NULL) //的作用是什么?

  另一个:这个CALL语句做了那些动作?

  你可以通过执行后观察SELECT做了什么,也可以根据代码判断,在5秒内做出。


  Answer

  答案

mysql> CALL p13(NULL)//

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t//

------

| s1 |

------

| 6 |

| 6 |

| 19 |

| 19 |

------

4 rows in set (0.00 sec)


  答案是当你调用p13时,MySQL插入了另一条包含数值19的记录。原因是变量variable1的值为NULL,CASE语句的ELSE部分就被执行了。希望这对大家有意义。如果你回答不出来,没有问题,我们可以继续向下走。

Loops 循环语句

WHILE ... END WHILE

LOOP ... END LOOP

REPEAT ... END REPEAT

GOTO


  下面我们将会创建一些循环。我们有三种标准的循环方式:


  WHILE循环,LOOP循环以及REPEAT循环。还有一种非标准的循环方式:GO TO(译者语:最好不要用吧,用了就使流程混乱)。

WHILE ... END WHILE

CREATE PROCEDURE p14 ()

BEGIN

DECLARE v INT;

SET v = 0;

WHILE v < 5 DO

INSERT INTO t VALUES (v);

SET v = v 1;

END WHILE;

END; //


  这是WHILE循环的方式。我很喜欢这种方式,它跟IF语句相似,因此不需要掌握很多新的语法。这里的INSERT和SET语句在WHILE和END WHILE之间,当变量v大于5的时候循环将会退出。使用

"SET v = 0;"


  语句使为了防止一个常见的错误,如果没有初始化,默认变量值为NULL,而NULL和任何值操作结果都为NULL。

WHILE ... END WHILE example

mysql> CALL p14()//

Query OK, 1 row affected (0.00 sec)



  以上就是调用过程p14的结果不用关注系统返回是"one row affected"还是"five rows affected",因为这里的计数只对最后一个INSERT动作进行计数。

WHILE ... END WHILE example: CALL

mysql> select * from t; //

------

| s1 |

------

....

| 0 |

| 1 |

| 2 |

| 3 |

| 4 |

------

9 rows in set (0.00 sec)


  调用后可以看到程序向数据库中插入了5行。

REPEAT ... END REPEAT

CREATE PROCEDURE p15 ()

BEGIN

DECLARE v INT;

SET v = 0;

REPEAT

INSERT INTO t VALUES (v);

SET v = v 1;

UNTIL v >= 5

END REPEAT;

END; //



  这是一个REPEAT循环的例子,功能和前面WHILE循环一样。区别在于它在执行后检查结果,而WHILE则是执行前检查。(译者语:可能等同于DO WHILE吧)

REPEAT ... END REPEAT: look at the UNTIL: UNTIL的作用

CREATE PROCEDURE p15 ()

BEGIN

DECLARE v INT;

SET v = 0;

REPEAT

INSERT INTO t VALUES (v);

SET v = v 1;

UNTIL v >= 5 <--

END REPEAT;

END; //


  注意到UNTIL语句后面没有分号,在这里可以不写分号,当然你加上额外的分号更好。

  REPEAT ... END REPEAT: calling :调用

mysql> CALL p15()//

Query OK, 1 row affected (0.00 sec)

mysql> SELECT COUNT(*) FROM t//

----------

| COUNT(*) |

----------

| 14 |

----------

1 row in set (0.00 sec)


  我们可以看到调用p15过程后又插入了5行记录

LOOP ... END LOOP

CREATE PROCEDURE p16 ()

BEGIN

DECLARE v INT;

SET v = 0;

loop_label: LOOP

INSERT INTO t VALUES (v);

SET v = v 1;

IF v >= 5 THEN

LEAVE loop_label;

END IF;

END LOOP;

END; //


  以上是LOOP循环的例子。


  LOOP循环不需要初始条件,这点和WHILE循环相似,同时它又和REPEAT循环一样也不需要结束条件。


  LOOP ... END LOOP: with IF and LEAVE 包含IF和LEAVE的LOOP循环

CREATE PROCEDURE p16 ()

BEGIN

DECLARE v INT;

SET v = 0;

loop_label: LOOP

INSERT INTO t VALUES (v);

SET v = v 1;

IF v >= 5 THEN <--

LEAVE loop_label;

END IF;

END LOOP;

END; //


  在循环内部加入IF语句,在IF语句中包含LEAVE语句。这里LEAVE语句的意义是离开循环。

  LEAVE的语法是LEAVE加循环语句标号,关于循环语句的标号问题我会在后面进一步讲解。
  LOOP ... END LOOP: calling :调用

mysql> CALL p16()//

Query OK, 1 row affected (0.00 sec)

mysql> SELECT COUNT(*) FROM t//

----------

| COUNT(*) |

----------

| 19 |

----------

1 row in set (0.00 sec)


  调用过程p16后,结果是另5行被插入表t中。


Labels 标号

CREATE PROCEDURE p17 ()

label_1: BEGIN

label_2: WHILE 0 = 1 DO LEAVE label_2; END

WHILE;

label_3: REPEAT LEAVE label_3; UNTIL 0 =0

END REPEAT;

label_4: LOOP LEAVE label_4; END LOOP;

END; //


  最后一个循环例子中我使用了语句标号。现在这里有一个包含4个语句标号的过程的例子。我们可以在BEGIN、WHILE、REPEAT或者LOOP语句前使用语句标号,语句标号只能在合法的语句前面使用。因此"LEAVE label_3"意味着离开语句标号名定义为label_3的语句或复合语句。

End Labels 标号结束符

CREATE PROCEDURE p18 ()

label_1: BEGIN

label_2: WHILE 0 = 1 DO LEAVE label_2; END

WHILE label_2;

label_3: REPEAT LEAVE label_3; UNTIL 0 =0

END REPEAT label_3 ;

label_4: LOOP LEAVE label_4; END LOOP

label_4 ;

END label_1 ; //


  你也可以在语句结束时使用语句标号,和在开头时使用一样。这些标号结束符并不是十分有用。

  它们是可选的。如果你需要,他们必须和开始定义的标号名字一样当然为了有良好的编程习惯,方便他人阅读,最好还是使用标号结束符。


LEAVE and Labels 跳出和标号

CREATE PROCEDURE p19 (parameter1 CHAR)

label_1: BEGIN

label_2: BEGIN

label_3: BEGIN

IF parameter1 IS NOT NULL THEN

IF parameter1 = 'a' THEN

LEAVE label_1;

ELSE BEGIN

IF parameter1 = 'b' THEN

LEAVE label_2;

ELSE

LEAVE label_3;

END IF;

END;

END IF;

END IF;

END;

END;

END;//

LEAVE


  语句使程序跳出复杂的复合语句。


ITERATE

  迭代如果目标是ITERATE(迭代)语句的话,就必须用到LEAVE语句

CREATE PROCEDURE p20 ()

BEGIN

DECLARE v INT;

SET v = 0;

loop_label: LOOP

IF v = 3 THEN

SET v = v 1;

ITERATE loop_label;

END IF;

INSERT INTO t VALUES (v);

SET v = v 1;

IF v >= 5 THEN

LEAVE loop_label;

END IF;

END LOOP;

END; //



ITERATE

  (迭代)语句和LEAVE语句一样也是在循环内部的循环引用,它有点像C语言中的“Continue”,同样它可以出现在复合语句中,引用复合语句标号,ITERATE(迭代)意思是重新开始复合语句。

  那我们启动并观察下面这个循环,这是个需要迭代过程的循环:

ITERATE: Walking through the loop



深入循环

CREATE PROCEDURE p20 ()

BEGIN

DECLARE v INT;

SET v = 0;

loop_label: LOOP <--

IF v = 3 THEN

SET v = v 1;

ITERATE loop_label;

END IF;

INSERT INTO t VALUES (v);

SET v = v 1;

IF v >= 5 THEN

LEAVE loop_label;

END IF;

END LOOP;

END; //


  让这个已经定义了标号的循环运行起来。

ITERATE: Walking through the loop

CREATE PROCEDURE p20 ()

BEGIN

DECLARE v INT;

SET v = 0;

loop_label: LOOP

IF v = 3 THEN <--

SET v = v 1;

ITERATE loop_label;

END IF;

INSERT INTO t VALUES (v);

SET v = v 1;

IF v >= 5 THEN

LEAVE loop_label;

END IF;

END LOOP;

END; //



  v的值变成3,然后我们把它增加到4。

ITERATE: walking through the loop

CREATE PROCEDURE p20 ()

BEGIN

DECLARE v INT;

SET v = 0;

loop_label: LOOP

IF v = 3 THEN

SET v = v 1;

ITERATE loop_label; <--

END IF;

INSERT INTO t VALUES (v);

SET v = v 1;

IF v >= 5 THEN

LEAVE loop_label;

END IF;

END LOOP;

END; //


  然后开始ITERATE(迭代)过程。

ITERATE: walking through the loop

CREATE PROCEDURE p20 ()

BEGIN

DECLARE v INT;

SET v = 0;

loop_label: LOOP <--

IF v = 3 THEN

SET v = v 1;

ITERATE loop_label;

END IF;

INSERT INTO t VALUES (v);

SET v = v 1;

IF v >= 5 THEN

LEAVE loop_label;

END IF;

END LOOP;

END; //



  这里的ITERATE(迭代)让循环又回到了循环的头部。

ITERATE: walking through the loop

CREATE PROCEDURE p20 ()

BEGIN

DECLARE v INT;

SET v = 0;

loop_label: LOOP

IF v = 3 THEN

SET v = v 1;

ITERATE loop_label;

END IF;

INSERT INTO t VALUES (v);

SET v = v 1;

IF v >= 5 THEN

LEAVE loop_label; <--

END IF;

END LOOP;

END; //


  当v的值变为5时,程序将执行LEAVE语句

ITERATE: walking through the loop

CREATE PROCEDURE p20 ()

BEGIN

DECLARE v INT;

SET v = 0;

loop_label: LOOP

IF v = 3 THEN

SET v = v 1;

ITERATE loop_label;

END IF;

INSERT INTO t VALUES (v);

SET v = v 1;

IF v >= 5 THEN

LEAVE loop_label;

END IF;

END LOOP;

END; // <--



  LEAVE的结果就是跳出循环,使运行指令到达复合语句的最后一步。

GOTO

CREATE PROCEDURE p...

BEGIN

...

LABEL label_name;

...

GOTO label_name;

...

END;


  MySQL的存储过程中可以使用GOTO语句。虽然这不是标准SQL语句,而且在这里建立标号的方法也和惯例中的不一样。由于为了和其他DBMS兼容,这个语句会慢被淘汰,所以我们在MySQL参考手册中没有提及。

Grand combination

大组合

CREATE PROCEDURE p21

(IN parameter_1 INT, OUT parameter_2 INT)

LANGUAGE SQL DETERMINISTIC SQL SECURITY INVOKER

BEGIN

DECLARE v INT;

label goto_label; start_label: LOOP

IF v = v THEN LEAVE start_label;

ELSE ITERATE start_label;

END IF;

END LOOP start_label;

REPEAT

WHILE 1 = 0 DO BEGIN END;

END WHILE;

UNTIL v = v END REPEAT;

GOTO goto_label;

END;//


  上面例子中的语句包含了我们之前讲的所有语法,包括参数列表,特性参数,BEGIN/END块复合语句,变量声明,IF,WHILE,LOOP,REPEAT,LEAVE,ITERATE,GOTO。这是一个荒谬的存储过程,我不会运行它,因为里面有无尽的循环。但是里面的语法却十分合法。这些是新的流程控制和变量声明语句。下面我们将要接触更多新的东西。

未完待续...
标签: