MySQL5.0新特性教程 存储过程:第三讲

<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 728x15, 创建于 08-4-23MSDN */ google_ad_slot = "3624277373"; google_ad_width = 728; google_ad_height = 15; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 160x600, 创建于 08-4-23MSDN */ google_ad_slot = "4367022601"; google_ad_width = 160; google_ad_height = 600; //--> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

The New SQL Statements 新SQL语句

Variables 变量

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

  (1) Example with two DECLARE statements

  两个DECLARE语句的例子



WHILE ... END WHILE 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(译者语:最好不要用吧,用了就使流程混乱)。







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的语句或复合语句。


共2页: 上一页 1 [2] 下一页 <script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 728x15, 创建于 08-4-23MSDN */ google_ad_slot = "3624277373"; google_ad_width = 728; google_ad_height = 15; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 160x600, 创建于 08-4-23MSDN */ google_ad_slot = "4367022601"; google_ad_width = 160; google_ad_height = 600; //--> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
阅读更多
个人分类: 数据库
想对作者说点什么? 我来说一句

MySQL5.0 存储过程教程 中文

2007年04月18日 2.12MB 下载

MySql存储过程以及MyBatis3

2013年12月09日 1.82MB 下载

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭