MySQL储存过程编程教程－第三章

kider

3.1 变量，字面量，参数和注释
让我们对变量类型定义，字面量和参数的回顾开始我们存储程序的学习，在这一章中，我们还将学到如何在代码中加入注释和文档。

3.1.1 变量
首先，让我们看一下MySQL存储过程是如何处理变量和字面量的，因为不具备对这些概念的了解，我们就不能创建一些更有意义的主题。

变量是一个值可以在程序执行过程中被改变的命名数据项。字面量（将在下一章进行探讨）是一个可以被赋值给变量的未命名数据项。通常，字面量是你程序中的硬代码，并且通常拿来赋值给变量，传递给参数，或者作为SELECT语句的参数。

DECLARE语句允许我们创建变量。这一点我们接下来将看到，它将出现在代码块中任何游标和处理及任何过程语句声明之前，DECLARE语句的语法如下：

DECLARE variable_name [,variable_name...] datatype [DEFAULT value];

多个变量可以在一个DECLARE语句中被声明，而且变量可以给出一个默认值（初始值）。如果你不给出DEFAULT子句，那么这个变量将会被赋予空值。

使用DEFAULT是一项很好的实践，因为除非你给出一个初始值，否则任何依赖于这个变量的后续操作都将在赋值之前返回NULL值。我们将在本章稍后部分给出这种错误类型的示例代码

datatype可以是任何你可以在CREATE TABLE语句中使用的有效MySQL数据类型。我们将在本章稍后部分给出详细的描述；Table 3-1给出了最常用的类型

kider

Table 3-1. 常用的MySQL数据类型

数据类型	解释	相应值的示例
INT, INTEGER	32位整数。取值范围为-21亿到+21亿，如果是非符号数，值可以达到42亿，但这样做就不能包括负数	123，345 -2,000,000,000
BIGINT	64位整数。取值范围为-9万亿到+9万亿或者非负的0到18万亿	9,000,000,000,000,000,000 -9,000,000,000,000,000,000
FLOAT	32位浮点数。取值范围为1.7e38 to 1.7e38或者非负的0到3.4e38	0.00000000000002 17897.890790 -345.8908770 1.7e21
DOUBLE	64位浮点数。取值范围接近无限（1.7e308）	1.765e203 -1.765e100
DECIMAL（precision,scale） NUMERIC（precision,scale）	定点数。存储情况取决于precision，能保存可能出现的数字范围。 NUMERIC通常用来保存重要的十进制数，例如现金	78979.00 -87.50 9.95
DATE	日期类型，没有详述时间	'1999-12-31'
DATETIME	日期和时间，时间精确到秒	'1999-12-31 23:59:59'
CHAR(length)	定长字符串。值会被空白填充至制定长度，最大长度为255字节	'hello world '
VARCHAR(length)	最大长度为64K的可变字符串	'Hello world'
BLOB, TEXT	最大64K长度，BLOB用来保存2进制数据，TEXT用来保存文本数据	任何能想象的内容
LONGBLOB, LONGTEXT	BLOB和TEXT的加长版本，存储能力达4GB	任何能想象的内容，但比BLOB和TEXT能存放更大的长度

Example 3-1 向我们演示了各种数据类型的声明

Example 3-1 数据类型声明演示

1. 
   
2. DECLARE l_int1      int default -2000000;
   
3. DECLARE l_int2      INT unsigned default 4000000;
   
4. DECLARE l_bigint1   BIGINT DEFAULT 4000000000000000;
   
5. DECLARE l_float     FLOAT DEFAULT 1.8e8;
   
6. DECLARE l_double    DOUBLE DEFAULT 2e45;
   
7. DECLARE l_numeric   NUMERIC(8,2) DEFAULT 9.95;
   
8. 
   
9. DECLARE l_date      DATE DEFAULT '1999-12-31';
   
10. DECLARE l_datetime  DATETIME DEFAULT '1999-12-31 23:59:59';
    
11. 
    
12. DECLARE l_char      CHAR(255) DEFAULT 'This will be padded to 255 chars';
    
13. DECLARE l_varchar   VARCHAR(255) DEFAULT 'This will not be padded';
    
14. 
    
15. DECLARE l_text      TEXT DEFAULT 'This is a really long string.  In stored programs
    
16.                     we can use text columns fairly freely, but in tables there are some
    
17.                     limitations regarding indexing and use in various expressions.';

复制代码

kider

3.1.2 字面常量
字面常量是你程序中的硬代码。通常你可以将字面常量用于赋值语句和条件比对（比如IF），存储过程，函数的参数或者SQL语句中。

下面是三大基本字面量类型

数字字面常量

数字字面量适用于表达数字并可以被定义为十进制数字（300, 30.45等），十六进制数或者科学计数法表示的数。科学计数法用于表达非常大或者精度要求极高的值。字母‘e’在这里表示该数值为将‘e’的左侧的数乘于10的‘e’右侧数值次幂，因此2.4e就等于2.4 x 104或者24,000。你不能在数字字面常量中使用逗号。

十六进制值使用的是传统的表示方法，在它的前面加上‘0x’。因此0xA表示十六进制中的‘A’，也就是十进制中的10。

日期字面常量

一个日期字面常量是一个类似于‘YYYY-MM-DD’这样的字符串，或者在DATATIME中以'YYYY-MM-DD HH24:MI:SS'这样的格式表示。所以'1999-12-31 23:59:59'表示上个世纪的最后一秒钟（除非你不相信有公元0年这种说法，上个世纪实际上终止与2000-12-32）。

字符字面常量

字符常量是任何被简单的包含在单引号中的值。如果你要在单引号中表示另一对单引号，那么你可以用两个双引号或者加上前缀的反斜杠(\')进行转意来表示他们。你也可以用双引号来闭合一个字符串，并且你可以使用转意序列字符（\t表示tab，\n表示换行，\\表示反斜杠，等等）。

3.1.3.变量命名规则
MySQL在变量命名方面具有惊人的灵活性。并不像其他大多数编程语言，MySQL允许非常长的变量名（大于255个字符）；他们可以包含特殊的字符并且以数字字符开始。尽管如此，我们还是建议您不要把MySQL这种灵活性的优势取代明智的命名习惯，并且避免使用过长的变量名（见第23章获得更多相关信息和最佳实践）

3.1.4 变量赋值
你可以使用SET语句操纵变量赋值，请使用如下语法：

        SET variable_name = expression [,variable_name = expression ...]

就像你所看到的，你完全可以通过使用一个SET语句来完成多次赋值。

大多数语言并不在对变量赋值时使用SET语句，于是结果就很容易造成了在不使用具体的SET来对变量赋值时造成的错误，就像Example 3-2。

Example 3-2.尝试不使用SET语句对变量赋值

1. 
   
2. mysql> Create procedure no_set_stmt(  )
   
3. BEGIN
   
4.         DECLARE i INTEGER;
   
5.         i=1;
   
6. END;
   
7. $$
   
8. 
   
9. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
   
10. corresponds
    
11. to your MySQL server version for the right syntax to use near
    
12. 'procedure no_set_stmt(  )
    
13. BEGIN
    
14.         DECLARE i INT;
    
15.         i=1;
    
16. END' at line 1
    
17. 
    

复制代码

就像经常会犯的存储程序编译错误，错误信息并不会直接指明当前缺失的SET语句，所以当检查你程序的编译错误时，应该加倍检查所有的变量赋值，确保他们包含SET。

kider

3.1.5 参数
参数是可以被主叫程序传入或传出与存储程序的变量。参数被函数或过程创建时定义于CREATE语句内，就像下面所展示的一样：

Create procedure|function(
       [[IN
|OUT
|INOUT
] parameter_name data_type...])

参数的命名和变量的命名具有相同的规则，其中的data_type可以是任何本地变量类型。参数可以附加上IN，OUT或者INOUT属性

IN

除非被具体定义，否则参数都假定IN属性。这意味这他们的值必须被主叫程序所指定，并且任何在存储程序内部对该参数的修改都不能在主叫程序中起作用

OUT

一个OUT参数可以被存储程序所修改，并且这个被修改的值可以在主叫程序中生效，主叫程序必须提供一个变量来接受由OUT参数输出的内容，但是存储程序本身并没有对这个可能已经初始化的变量的操作权限，当存储程序开始时，任何OUT变量的值都被赋值为NULL，不管这个值在主叫程序中是否被赋予其他值。

INOUT

INOUT参数同时扮演着IN和OUT参数的角色。那意味着，主叫程序可以提供一个值，而被叫程序自身可以修改这个参数的值，并且当存储程序结束时主叫程序对该修改后的值具有访问权限

IN，OUT和INOUT关键字只能被应用于存储过程而不适用于存储函数，在存储函数中所有的参数都被视为IN参数（虽然你不能指定IN关键字）

下面的三个例子举例说明了上面这些原理。

首先，虽然MySQL允许我们修改In参数，但这种修改在主叫程序中并不可见。Example 3-3的存储程序打印并修改了参数的值。当存储程序内部对于输入参数的修改被允许时，原本的变量(@p_in)并没有改变。

Example 3-3. IN参数的例子

1. 
   
2. mysql> CREATE PROCEDURE sp_demo_in_parameter(IN p_in INT)
   
3. BEGIN
   
4.     /* We can see the value of the IN parameter */
   
5.     SELECT p_in;
   
6.     /* We can modify it*/
   
7.     SET p_in=2;
   
8.     /* show that the modification took effect */
   
9.     select p_in;
   
10. END;
    
11. 
    
12. /* This output shows that the changes made within the stored program cannot be accessed
    
13. from
    
14.     the calling program (in this case, the mysql client):*/
    
15. 
    
16. mysql> set @p_in=1
    
17. 
    
18. Query OK, 0 rows affected (0.00 sec)
    
19. 
    
20. mysql> call sp_demo_in_parameter(@p_in)
    
21. 
    
22. +------+------------------------------------------+
    
23. | p_in | We can see the value of the IN parameter |
    
24. +------+------------------------------------------+
    
25. |    1 | We can see the value of the IN parameter |
    
26. +------+------------------------------------------+
    
27. 1 row in set (0.00 sec)
    
28. 
    
29. +------+-------------------------------------+
    
30. | p_in | IN parameter value has been changed |
    
31. +------+-------------------------------------+
    
32. |    2 | IN parameter value has been changed |
    
33. +------+-------------------------------------+
    
34. 1 row in set (0.00 sec)
    
35. 
    
36. Query OK, 0 rows affected (0.00 sec)
    
37. 
    
38. mysql> select @p_in,'We can''t see the changed value from the calling program'
    
39. 
    
40. +-------+---------------------------------------------------------+
    
41. | @p_in | We can't see the changed value from the calling program |
    
42. +-------+---------------------------------------------------------+
    
43. | 1     | We can't see the changed value from the calling program |
    
44. +-------+---------------------------------------------------------+
    
45. 1 row in set (0.00 sec)
    
46. 
    
47. Query OK, 0 rows affected (0.00 sec)

复制代码

接下来，在Example 3-4中，我们将验证OUT参数的行为。虽然主叫程序已经初始化了OUT参数的值，但是被叫程序无法看到这个值。无论如何，主叫程序只有在被叫过程执行完成后才能看到参数的改变

Example 3-4. OUT参数的例子

1. 
   
2. mysql> CREATE PROCEDURE sp_demo_out_parameter(OUT p_out INT)
   
3. 
   
4. BEGIN
   
5.     /* We can't see the value of the OUT parameter */
   
6.     SELECT p_out,'We can''t see the value of the OUT parameter';
   
7.     /* We can modify it*/
   
8.     SET p_out=2;
   
9.     SELECT p_out,'OUT parameter value has been changed';
   
10. 
    
11. END;
    
12. 
    
13. mysql> SET @p_out=1
    
14. 
    
15. Query OK, 0 rows affected (0.00 sec)
    
16. 
    
17. mysql> CALL sp_demo_out_parameter(@p_out)
    
18. 
    
19. +-------+-------------------------------------------------------------------+
    
20. | p_out | We can't see the value of the OUT parameter in the stored program |
    
21. +-------+-------------------------------------------------------------------+
    
22. |  NULL | We can't see the value of the OUT parameter in the stored program |
    
23. +-------+-------------------------------------------------------------------+
    
24.     1 row in set (0.00 sec)
    
25. 
    
26. +-------+--------------------------------------+
    
27. | p_out | OUT parameter value has been changed |
    
28. +-------+--------------------------------------+
    
29. |     2 | OUT parameter value has been changed |
    
30. +-------+--------------------------------------+
    
31. 1 row in set (0.00 sec)
    
32. 
    
33. Query OK, 0 rows affected (0.00 sec)
    
34. 
    
35. mysql> SELECT @p_out,"Calling program can see the value of the changed OUT parameter"
    
36. 
    
37. +----------------------------------------------------------------+
    
38. | Calling program can see the value of the changed OUT parameter |
    
39. +----------------------------------------------------------------+
    
40. | 2                                                              |
    
41. +----------------------------------------------------------------+
    
42. 1 row in set (0.00 sec)

复制代码

最后，Example 3-5将向我们演示INOUT参数的值，可以为我们的被叫程序所见，所修改并返回给它的主叫程序

Example 3-5.INOUT参数的例子

1. 
   
2. mysql> CREATE PROCEDURE sp_demo_inout_parameter(INOUT p_inout INT)
   
3. 
   
4. BEGIN
   
5. 
   
6.     SELECT p_inout,'We can see the value of the INOUT parameter in the stored program';
   
7. 
   
8.     SET p_inout=2;
   
9.     SELECT p_inout,'INOUT parameter value has been changed';
   
10. 
    
11. END;
    
12. //
    
13.     Query OK, 0 rows affected (0.00 sec)
    
14. 
    
15. set @p_inout=1
    
16. //
    
17. 
    
18. Query OK, 0 rows affected (0.00 sec)
    
19. 
    
20. call sp_demo_inout_parameter(@p_inout) //
    
21. 
    
22. +---------+-------------------------------------------------------------------+
    
23. | p_inout | We can see the value of the INOUT parameter in the stored program |
    
24. +---------+-------------------------------------------------------------------+
    
25. |       1 | We can see the value of the INOUT parameter in the stored program |
    
26. +---------+-------------------------------------------------------------------+
    
27. 1 row in set (0.00 sec)
    
28. 
    
29. +---------+----------------------------------------+
    
30. | p_inout | INOUT parameter value has been changed |
    
31. +---------+----------------------------------------+
    
32. |       2 | INOUT parameter value has been changed |
    
33. +---------+----------------------------------------+
    
34. 1 row in set (0.00 sec)
    
35. 
    
36. Query OK, 0 rows affected (0.00 sec)
    
37. 
    
38. select @p_inout ,"Calling program can see the value of the changed INOUT parameter"
    
39. //
    
40. 
    
41. +----------+------------------------------------------------------------------+
    
42. | @p_inout | Calling program can see the value of the changed INOUT parameter |
    
43. +----------+------------------------------------------------------------------+
    
44. | 2        | Calling program can see the value of the changed INOUT parameter |
    
45. +----------+------------------------------------------------------------------+
    
46. 1 row in set (0.00 sec)
    
47. 
    

复制代码

kider

3.1.6 用户变量
用于变量是在MySQL中被定义并且可以在存储程序中或存储程序之外被操作的变量。他们在MySQL3中就可以被使用，并不像存储程序，我们可以通过以下两种方法来使用用户变量：

因为用户变量具有独立于存储程序个体的作用于，他们可以用来描述那些能够被任何存储程序所读写的会话。这有些接近于在其他编程语言中全局变量的原理

用户变量可以给方法传递参数以第二种选择，存储程序对用户变量具有读写权限，这样可以避免使用参数传值的必要（见早些章节的“参数”或许更多有关参数的信息）

用户变量可以被MySQL命令行客户端从任何其他程序创建并操纵。来确保MySQL语句使用SET语句。Example 3-6展示了一些在MySQL客户端使用SET的例子

Example 3-6.在MySQL客户端操作用户变量

1. mysql> SELECT 'Hello World' into @x;
   
2. Query OK, 1 row affected (0.00 sec)
   
3. 
   
4. mysql> SELECT @x;
   
5. +-------------+
   
6. | @x          |
   
7. +-------------+
   
8. | Hello World |
   
9. +-------------+
   
10. 1 row in set (0.03 sec)
    
11. 
    
12. mysql> SET @y='Goodbye Cruel World';
    
13. Query OK, 0 rows affected (0.00 sec)
    
14. 
    
15. mysql> select @y;
    
16. +---------------------+
    
17.     | @y                  |
    
18. +---------------------+
    
19. | Goodbye Cruel World |
    
20. +---------------------+
    
21. 1 row in set (0.00 sec)
    
22. 
    
23. mysql> SET @z=1+2+3;
    
24. Query OK, 0 rows affected (0.00 sec)
    
25. 
    
26. mysql> select @z;
    
27. +------+
    
28. | @z   |
    
29. +------+
    
30. | 6    |
    
31. +------+
    
32. 1 row in set (0.00 sec)

复制代码

你可以在当前会话（比如connection）里从存储程序中使用任何用户变量。举例来说，Example 3-7展示了如何不使用过程参数向存储过程传递信息

Example 3-7.使用用户变量在主叫程序和被叫程序之间传递信息

1. mysql> CREATE PROCEDURE GreetWorld(  )
   
2.     ->  SELECT CONCAT(@greeting,' World');
   
3. Query OK, 0 rows affected (0.00 sec)
   
4. 
   
5. mysql> SET @greeting='Hello';
   
6. Query OK, 0 rows affected (0.00 sec)
   
7. 
   
8. mysql> CALL GreetWorld(  );
   
9. +----------------------------+
   
10. | CONCAT(@greeting,' World') |
    
11. +----------------------------+
    
12. | Hello World                |
    
13. +----------------------------+
    
14. 1 row in set (0.00 sec)
    
15. 
    
16. Query OK, 0 rows affected (0.00 sec)

复制代码

我们也可以用一个存储程序创建用户变量。这会使该变量在其他存储程序中都可用，正如同PHP中全局变量（global）所扮演的角色。举例来说，在Example 3-8中过程p1()创建了一个用户变量，这对过程p2（）也可见。

Example 3-8. 把用户变量当成全局变量交叉使用

1. mysql> CREATE PROCEDURE p1(  )
   
2.     ->  SET @last_procedure='p1';
   
3. Query OK, 0 rows affected (0.00 sec)
   
4. 
   
5. mysql> CREATE PROCEDURE p2(  )
   
6.     ->  SELECT CONCAT('Last procedure was ',@last_procedure);
   
7. Query OK, 0 rows affected (0.00 sec)
   
8. 
   
9. mysql> CALL p1(  );
   
10. Query OK, 0 rows affected (0.00 sec)
    
11. 
    
12. mysql> CALL p2(  );
    
13. +-----------------------------------------------+
    
14. | CONCAT('Last procedure was ',@last_procedure) |
    
15. +-----------------------------------------------+
    
16. | Last procedure was p1                         |
    
17. +-----------------------------------------------+
    
18. 1 row in set (0.00 sec)

复制代码

用户变量是一种可变数据类型，能够保存字符串，日期或者数字值。日期类型的转换是自动的。用户变量存在于一个持续的MySQL会话中，在此会话中的任何程序和语句都可以访问该用户变量，当然，别的会话则无法访问它。

在某些编程语言中（如PHP），独立于单个函数的变量的作用域必须用global关键字标示。在其他语言中对这些变量的定义语法可能是不同的，但是它们总是与“全局”变量相关联。在MySQL中，SET语句的global自子句允许你设定服务器域的系统变量，这个概念和PHP的全局变量不等价。因此，如果说用户变量是“全局”作用域的那会引起冲突并且应该予以避免。注意：你不能在SET语句的global子句中创建你自己的变量。

使用用户变量去实现跨越多个存储程序的变量在某些场合会非常有用。但是，你必须明确这样做的意图，并谨慎的使用之。如同所有编程语言一样，过度的使用作用域超越单个程序的全局变量会让你的代码不易读并且难于维护。使用这些变量的例程会变得高耦合并难以维护，测试和理解

提示：在你的存储程序中适时使用“用户”变量，过度使用超出程序作用域的变量会导致你的程序非模块化并难于维护

kider

3.1.7. 注释
MySQL存储程序支持两种不同风格的注释：

两个连字符跟上一个空格创建了一个到当前行末的注释

C语言风格的注释，用/*开始，以*/结束。我们称呼它为多行注释

单行注释对于变量声明和简单的语句很有效，多行注释对于创建大的注释块很有效，比如位于存储程序头部的注释块一样。

下面这块代码Example 3-9展示了这两种不同的注释风格

Example 3-9. 存储程序注释的例子

1. 
   
2. create procedure comment_demo
   
3.     (in p_input_parameter INT  -- Dummy parameter to illustrate styles
   
4.     )
   
5. /*
   
6. |    Program: comment_demo
   
7. |    Purpose: demonstrate comment styles
   
8. |    Author:  Guy Harrison
   
9. |    Change History:
   
10. |        2005-09-21 - Initial
    
11. |
    
12. */

复制代码

3.2. 操作符
MySQL包括大家在大多数语言中早已熟识的操作符，但是C风格的操作符（++， +=，etc）并不被支持。

操作符经常是和SET语句一起来改变变量的值，和比较语句如IF或者CASE，和循环控制表达式。Example 3-10展示了一些在存储程序中使用操作符的简单例子。

Example 3-10存储程序中操作符的例子

1. 
   
2. create procedure operators(  )
   
3. begin
   
4.         DECLARE a int default 2;
   
5.         declare b int default 3;
   
6.         declare c FLOAT;
   
7. 
   
8.         set c=a+b; select 'a+b=',c;
   
9.         SET c=a/b; select 'a/b=',c;
   
10.         SET c=a*b; Select 'a*b=',c;
    
11. 
    
12. 
    
13.             IF (a<b) THEN
    
14.                 select 'a is less than b';
    
15.         END IF;
    
16.         IF NOT (a=b) THEN
    
17.                 SELECT 'a is not equal to b';
    
18.         END IF;
    
19. end;

复制代码

有关不同种类的操作符（数学，比较，逻辑和位操作）将在下面的字章节中做出阐述

3.2.1.数学操作符
MySQL支持你在小学（付费补习班）中学到的基本数学操作符：加（+），减（-），乘（*）和除（/）。

此外，MySQL支持两种与除法操作相关的操作符：整除（DIV）操作符返回除法操作的整数部分，而模操作符（%）返回整除后的余数部分。Table 3-2给出了一个有关MySQL数学操作符的完整列表并给出了描述和用例。

Table 3-2.MySQL数学操作符

操作符	描述	用例
+	加	SET var1=2+2; à 4
-	减	SET var2=3-2; à 1
*	乘	SET var3=3*2; à 6
/	除	SET var4=10/3; à 3.3333
DIV	整除	SET var5=10 DIV à3; 3
%	模	SET var6=10%3; à 1

kider

3.2.2.比较操作符
比较操作符比较两个值并返回TRUE，FALSE，UNKNOWN（通常如果一个值被比较后返回NULL或者UNKNOWN）。他们通常被使用在IF，CASE，和循环控制表达式中。

Table 3-3 总结了MySQL比较操作符

Table 3-3. 比较操作符

操作符	描述	示例	示例结果
>	是否大于	1>2	False
<	是否小于	2<1	False
<=	是否小于等于	2<=2	True
>=	是否大于等于	3>=2	True
BETWEEN	是否位于两个值之间	5 BETWEEN 1 AND 10	True
NOT BETWEEN	是否不位于两个值之间	5 NOT BETWEEN 1 AND 10	False
IN	值位于列表中	5 IN (1,2,3,4)	False
NOT IN	值不位于列表中	5 NOT IN (1,2,3,4)	True
=	等于	2=3	False
<>, !=	不等于	2<>3	False
<=>	Null安全等于（如果两个值均为Null返回TRUE）	NULL<=>NULL	True
LIKE	匹配简单模式	"Guy Harrison" LIKE "Guy%"	True
REGEXP	匹配扩展正则表达式	"Guy Harrison" REGEXP "[Gg]reg"	False
IS NULL	值为空	0 IS NULL	False
IS NOT NULL	值不为空	0 IS NOT NULL	True

3.2.3 逻辑操作符
逻辑操作符操作三个逻辑值TRUE，FALSE和NULL并返回类似的这三个值。那些操作符通常被用来和比较操作符相结合创建除更为复杂的表达式

对于大多数逻辑操作符而言，如果其中任何值被比较得出为NULL，那么最终的结果就为NULL。在创建逻辑表达式时记住这样一个事实很重要，否则，你的代码就可能隐含一些微小的错误

AND操作符比较两个逻辑表达式，并且只在两个表达式都为真是才返回TRUE。Table 3-4展示了AND操作符所生成的可能的值

Table 3-4. AND操作符的一些事实

AND	TRUE	FALSE	NULL
TRUE	TRUE	FALSE	NULL
FALSE	FALSE	FALSE	NULL
NULL	NULL	NULL	NULL

OR操作符比对两个逻辑表达式，并且只要其中的一个表达式为真即返回TRUE（Table 3-5）

Table 3-5. OR操作符的一些事实

OR	TRUE	FALSE	NULL
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	NULL
NULL	TRUE	NULL	NULL

XOR操作符只有在两个值不完全为真时才返回TRUE。Table 3-6展示了XOR表达式的可能值

Table 3-6. XOR操作符的一些事实

XOR	TRUE	FALSE	NULL
TRUE	FALSE	TRUE	NULL
FALSE	TRUE	FALSE	NULL
NULL	NULL	NULL	NULL

Example 3-11 展示了如何使用AND操作符连接多个比较语句

Example 3-11.逻辑语句实践的例子

1. 
   
2. CREATE FUNCTION f_title(in_gender CHAR(1),
   
3.                         in_age INT, in_marital_status VARCHAR(7))
   
4.   RETURNS VARCHAR(6)
   
5. BEGIN
   
6.   DECLARE title VARCHAR(6);
   
7.   IF in_gender='F' AND in_age<16 THEN
   
8.      SET title='Miss';
   
9.   ELSEIF in_gender='F' AND in_age>=16 AND in_marital_status='Married' THEN
   
10.      SET title='Mrs';
    
11.   ELSEIF in_gender='F' AND in_age>=16 AND in_marital_status='Single' THEN
    
12.      SET title='Ms';
    
13.   ELSEIF in_gender='M' AND in_age<16 THEN
    
14.      SET title='Master';
    
15.   ELSEIF in_gender='M' AND in_age>=16 THEN
    
16.      SET title='Mr';
    
17.   END IF;
    
18.   RETURN(title);
    
19. END;
    

复制代码

3.2.4位操作符
位操作符主要是对二进制值进行操作。Table 3-7列出了位操作符

Table 3-7. 位操作符

操作符	用途
|	OR
&	AND
<<	Shift bits to left
>>	Shift bits to right
~	NOT or invert bits

位操作符类似于逻辑操作符，除了他们的操作对象是二进制的位并得出一个结果

举例来说，考虑整数（二进制的101）和4（二进制的010）。或操作符操作的是二进制中的每一位；所以5|2=7，因为101|010=111，这就是十进制中的7

位操作符AND操作二进制位中的每一位，只有在二者都为true时才设置位数。所以5&6=7，因为101|110=111，那就等于4

kider

3.3. 表达式
表达式是字面量，变量和操作符的集合，用以计算出某些需要的值。条件执行和流程控制表达式经常会依赖于这些值来决定循环条件和代码分支。

Example 3-12 演示了几种表达式

Example 3-12.表达式用例

1. Myvariable_name
   
2. Myvariable_name+1
   
3. ABS(Myvariable_name)
   
4. 3.14159
   
5. IF(Myvariable='M','Male','Female')
   
6. (2+4)/12

复制代码

3.4 内建函数
你可以在MySQL语句中使用大多数MySQL产品允许的函数，你能在MySQL手册中找到他们的详尽文档，并且我们在第9章提供了大多数函数的明细和样例，我们也将在第十章介绍如何在你的MySQL存储程序中使用自己创建的“存储”函数。

在SQL中可以被使用的函数未必都能在存储程序中被编组操作符（多行）调用。那些函数包括SUM, COUNT, MIN, MAX和AVG。MySQL允许表达式中包括函数，但是就像Example 3-13所演示的那样，他们会返回NULL值

Example 3-13在存储过程中的函数调用返回NULL

1. 
   
2. mysql> create procedure functions(  )
   
3. begin
   
4.         DECLARE a int default 2;
   
5.         declare b int default 3;
   
6.         declare c FLOAT;
   
7. 
   
8.         SET c=SUM(a); select c;
   
9. 
   
10. end;
    
11. 
    
12. Query OK, 0 rows affected (0.00 sec)
    
13. 
    
14. mysql> call functions(  );
    
15. 
    
16. +------+
    
17. | c    |
    
18. +------+
    
19. | NULL |
    
20. +------+
    
21. 1 row in set (0.00 sec)
    

复制代码

MySQL函数被归类为以下几个类型

字符串函数

这些函数主要对字符串变量执行操作，比方说：你可以连接字符串，在字符串中查找字符，得到子串和其他常规操作

数学函数

这些函数主要对数字进行操作，比方说：你可以进行乘方（平方），三角函数（sin，cos），随机数函数和对数函数等。

日期和时间函数

折现函数主要的操作对象是日期和时间，比方说：你可以得到当前时间，从一个日期上加上或减去一个时间间隔，找出两个日期间的差异，获取某个确定的时间点（比如：得到一天中某时间的小时数）

其它函数

这些函数包括了所有不容易被分入上面类别中函数。他们包括类型转换函数，流程控制函数（比如：CASE），信息反馈函数（比如服务器版本）和加密函数

Table 3-8总结了大多数常用的函数；相见第九章来获取函数语法和样例的完整覆盖

Table 3-8. 经常被使用的MySQL函数

函数	描述
ABS(number)	返回提供数字的绝对值。比方说， ABS(-2.3)=2.3.
CEILING(number)	返回下一个最大整数，比方说， CEILING(2.3)=3.
CONCAT(string1[,string2,string3,...])	返回所有提供字符串的连接形式的值
CURDATE	返回当前时间（不带时间）
DATE_ADD(date,INTERVAL amount_type)	给提供的时间值加上一个时间间隔并返回一个新时间。正确的形式有SECOND, MINUTE, HOUR, DAY, MONTH和YEAR
DATE_SUB(date,INTERVAL interval_type)	从提供的时间值上减去一个时间间隔并返回一个新的时间。正确的形式有SECOND, MINUTE, HOUR, DAY, MONTH和YEAR
FORMAT(number,decimals)	返回一个指定精确度的数值，并给与以1000为单位的分割（通常使用“，”）
GREATEST(num1,num2[,num3, ... ])	返回所有提供参数中的最大数
IF(test, value1,value2)	测试一个逻辑条件，如果为真则返回value1，如果为假返回value2
IFNULL(value,value2)	返回第一个值，除非第一个值为空；这样的话返回第二个值
INSERT(string,position,length,new)	把一个字符串插入到另一个字符串中
INSTR(string,substring)	返回一个字符串中子串的位置
ISNULL(expression)	如果参数为空则返回1，否则返回0
LEAST(num1,num2[,num3, ... ])	返回参数列表中的最小值
LEFT(string,length)	返回字符串最左边的部分
LENGTH(string)	返回字符串中的字节数。CHAR_LENGTH可以被用来返回字符数（这会在你使用多字节字符集是产生差异）
LOCATE(substring,string[,number])	返回字符串中子串的位置，可选的第三个参数为在父字符串中开始搜索的位置
LOWER(string)	返回给定字符串的小写形式
LPAD(string,length,padding)	返回字符串 str, 其左边由字符串padding 填补到length 字符长度，第三个参数为填充字符
LTRIM(string)	删除所有字符串中的前缀空格
MOD(num1,num2)	返回第一个数除于第二个数后的模（余数部分）
NOW	返回当前日期和时间
POWER(num1,num2)	返回num1的num2次方
RAND([seed])	返回一个随机数。seed可被用于随机数生成器的种子数
REPEAT(string,number)	返回一个重复number次string的字符串
REPLACE(string,old,new)	用new替换所有出现old的地方
ROUND(number[,decimal])	舍去给定数值的指定精度的位数
RPAD(string,length,padding)	返回字符串 str, 其右边由字符串padding 填补到length 字符长度，第三个参数为填充字符
RTRIM(string)	删除字符串尾部的空格
SIGN(number)	如果number小于0则返回-1，如果大于0则返回1，如果为0则返回0
SQRT(number)	返回number的平方根
STRCMP(string1,string2)	如果两个值相同则返回0，若根据当前分类次序，第一个参数小于第二个，则返回 -1，其它情况返回 1 。
SUBSTRING(string,position,length)	从字符串指定位置开始返回length个字符
UPPER(string)	将指定字符串转换为大写
VERSION	返回MySQL服务器当前版本号的字符串

函数可以被用在任何接受表达式的场合，在SET语句，条件语句中（IF，CASE），和循环控制子句。Example 3-14展示了在SET和IF子句中使用函数的例子


Example 3-14.在SET和IF子句中使用函数的例子

1. 
   
2. CREATE PROCEDURE function_example(  )
   
3. BEGIN
   
4. 
   
5.   DECLARE TwentyYearsAgoToday DATE;
   
6.   DECLARE mystring VARCHAR(250);
   
7. 
   
8.   SET TwentyYearsAgoToday=date_sub(curdate(  ), interval 20 year);
   
9. 
   
10.   SET mystring=concat('It was ',TwentyYearsAgoToday,
    
11.       ' Sgt Pepper taught the band to play...');
    
12. 
    
13.   SELECT mystring;
    
14. 
    
15.   IF (CAST(SUBSTR(version(  ),1,3) AS DECIMAL(2,1)) <5.0) THEN
    
16.     SELECT 'MySQL versions earlier than 5.0 cannot run stored programs - you
    
17.             must be hallucinating';
    
18.   ELSE
    
19.         SELECT 'Thank goodness you are running 5.0 or higher!';
    
20.   END IF;
    
21. 
    
22. END$$
    
23. 
    
24. CALL function_example(  )$$
    
25. 
    
26. +---------------------------------------------------------+
    
27. | mystring                                                |
    
28. +---------------------------------------------------------+
    
29. | It was 1985-11-22 Sgt Pepper taught the band to play... |
    
30. +---------------------------------------------------------+
    
31. 1 row in set (0.03 sec)
    
32. 
    
33. +-----------------------------------------------+
    
34. | Thank goodness you are running 5.0 or higher! |
    
35. +-----------------------------------------------+
    
36. | Thank goodness you are running 5.0 or higher! |
    
37. +-----------------------------------------------+
    
38. 1 row in set (0.03 sec)
    

复制代码

kider

3.5.数据类型
MySQL存成程序允许你将任何MySQL表列中的数据类型赋给变量。我们早在Table 3-1就提供了大多数数据类型。

在MySQL存储程序中的所有变量都是单纯的标量，也就是变量存储的只是单纯的个体，存储程序中没有与数组，记录或结构体等你可以在别的语言中找到的对应的事物。

3.5.1 字符串数据类型
MySQL提供两种基本的字符串数据类型：CHAR和VARCHAR，CHAR存储定长字符串，而VARCHAR存储可变长度的字符串。如果CHAR变量被赋予小于其声明长度的值，那么他将空白填充至声名长度。这样的事在VARCHAR变量中不会发生。

如果在MySQL表中，对于CHAR和VARCHAR的选择就非常重要，因为这直接关系到磁盘存储空间需求。虽然，在存储程序中，额外的内存需求将会最小化，并且CHAR和VARCHAR的是用在所有的表达式中都是可以相互替换的，但是两者的不同应用都有其各自小小的又是。我们通常使用VARCHAR，因为他们能存放长一些的字符串。

CHAR数据类型最大可以存放255个自己的数据，而VARCHAR最大可以存放65,532 字节的数据

3.5.1.1 枚举数据类型
枚举数据类型用来存放一系列允许的值。这些值可以用他们的字符串值或者他们在这一列数据中的索引值进行访问。如果你试图将一个没有出现在列表中的值插入到ENUM中，MySQL将回应你一个警告并且在sql_mode中包含“strict”值时向这个枚举数据插入一个NULL值

Example 3-15 在程序中使用枚举ENUM

1. 
   
2. CREATE PROCEDURE sp_enums(in_option ENUM('Yes','No','Maybe'))
   
3. BEGIN
   
4.   DECLARE position INTEGER;
   
5.   SET position=in_option;
   
6.   SELECT in_option,position;
   
7. END
   
8. --------------
   
9. 
   
10. Query OK, 0 rows affected (0.01 sec)
    
11. 
    
12. --------------
    
13. CALL sp_enums('Maybe')
    
14. --------------
    
15. 
    
16. +-----------+----------+
    
17. | in_option | position |
    
18. +-----------+----------+
    
19. | Maybe     |        3 |
    
20. +-----------+----------+
    
21. 1 row in set (0.00 sec)
    
22. 
    
23. Query OK, 0 rows affected (0.00 sec)
    
24. 
    
25. --------------
    
26. CALL sp_enums(2)
    
27. --------------
    
28. 
    
29. +-----------+----------+
    
30. | in_option | position |
    
31. +-----------+----------+
    
32. | No        |        2 |
    
33. +-----------+----------+
    
34. 1 row in set (0.00 sec)
    
35. 
    
36. Query OK, 0 rows affected (0.00 sec)
    
37. 
    
38. --------------
    
39. CALL sp_enums('What?')
    
40. --------------
    
41. 
    
42. ERROR 1265 (01000): Data truncated for column 'in_option' at row 1

复制代码

3.5.1.2.SET数据类型
SET很想枚举SET类型，但是在SET中可以插入多个列表中的值（见Example 3-16）。和ENUM一样，尝试在SET中插入列表中不存在的值时，如果打开“strict”模式，则会受到一个错误提示或是警告。

Example 3-16.存储程序中SET变量的行为

3.5.2. 数字数据类型

MySQL支持两种族系的数字类型：

精确数据类型比如INT和DECIMAL类型

近似数字类型比如FLOAT

精确数据类型存放一个要求精度的数字。不同的INT数据类型（INT, BIGINT, TINYINT）在他们对于存储空间上的要求是不同的，这也限制了他们所能存放数据的大小。其中的任何一种类型都能带符号（可以被存放正数或负数）或无符号数据，这也限制了数据类型所能存放数据的最大值（无符号双精确度类型可能是能存放最大值的整型）。Table 3-9展示了各种整形的存储范围

Table 3-9. 各种整型数的存储范围

数据类型	空间要求（bit）	有符号最大值	无符号最大值
TINYINT	8	127	255
SMALLINT	16	32767	65535
MEDIUMINT	24	8388607	16777215
INT	32	2147483647	4294967295
BIGINT	64	9223372036854775807	9223372036854775807

浮点数数据类型（FLOAT, DOUBLE, REAL）存储变量的大小和精度。在MySQL表中，FLOAT类型使用32bit的存储空间，而DOUBLE使用64bit的存储空间

需要注意的是，虽然，浮点数数据类型存放数字的近似值。大多数情况下这并不重要，但是在某些清苦按下你可能会用到近似数据类型，例如DECIMAL或NUMERIC，为了避免对浮点数数学操作过程中产生舍入错误。

kider

3.5.3. 日期和时间数据类型
MySQL以秒为精度存放日期-时间数据，在MySQL表中，日期列只能存放日期-时间数据的日期部分，而DATETIME可以用来存放日期和时间的所有部分

3.5.4. TEXT和BLOB数据类型
在MySQL表中，TEXT数据类型可以存放64K的数据，而LONGTEXT可以存放4,294,967,295字符，BLOB和LONGBLOB可以存放类似的数据范围，但是它可以用来存放二进制数据。


3.6.MySQL 5 "Strict" 模式
当STRICT_TRANS_TABLES或者STRICT_ALL被包含在sql_mode设置变量的可选参数中时，MySQL 5“strict”模式将会被启用，STRICT_ALL_TABLES将导致任何在列中尝试插入无效值的行为返回错误信息。STRICT_TRANS_TABLES和它具有相同的作用，但是只有在开启事务功能的表中才会起作用

如果两个设定都没有起作用，MySQL将会接受更新指令或将无效值“最佳匹配”为合法列值，举例来说，如果你尝试在一个整型列中插入一个字符串值，MySQL会将该列值设置为0。同时将会在这类“截断”发生时报告一个警告信息。

严格模式在你的INSERT语句中丢失列时同样会给出一个错误提示，除非该列被设置有DEFAULT子句。

当你在对没有事务支持的表执行多行更新或插入操作时，STRICT_ALL_TABLES可以产生一些可疑的副作用。因为，对于没有事务支持的表而言不具备回滚功能，错误可能将在一定数量的有效行更新后发生。这意味着在这个没有事务支持的表中产生了严格模式的错误，这个SQL语句或许部分执行成功。这是一个被渴望已久的行为，所以因为这个原因，MySQL5.0中STRICT_TRANS_TABLES是缺省设置

你可以在任何时候用SET语句修改严格模式的设置：

SET sql_mode='STRICT_ALL_TABLES'

严格模式同样决定着存储程序如何对待尝试将不正确的值赋予变量。如果任何一个严格模式生效，那么当尝试给变量赋予不正确的值时，将会收到错误信息。如果严格模式未被开启，那么只会产生警告

注意这个行为受到sql_mode设定的控制并且将在程序创建时生效，而并不是运行时。所以当一个严格的存储程序被创建时，它仍然是严格的，除非sql_mode设定随后被放宽。同样的道理，如果程序在没有设定严格模式的前提下被创建那么它在无效赋值的时候将产生警告而不是错误。而在程序运行时它并不关注sql_mode是否生效。

3.6.1. 存储程序行为和严格模式
在MySQL存储程序中除了用户变量之外，所有的其他变量都必须在使用前被声明，你可以使用@符号将变量定义在存储程序之外。此外，MySQL存储程序中的变量必须被赋予某个确定的数据类型，并且在程序执行过程中数据类型不得被更改。在这方面，MySQL存储程序语言有点像诸如C，Java和C#等“强类型”语言，而却别与如Perl和PHP这样的动态语言

当在严格模式下创建程序是，就像在早些章节所解释的那样，存储程序将在尝试将无效数据赋值给变量是货使用了不恰当的变量赋值是，都会弹出一个错误。这种被拒绝的赋值包含尝试将字符串赋予数字数据或者尝试赋值时使用了超出声明存储空间的变量。

虽然，当存储过程程序在非严格模式下被创建时，MySQL将使用最佳匹配来适应不正确数据并且只是给出警告而不是错误。这允许你可以把个别的字符串赋给整型数，如果你不能谨慎的确认你在赋值时总是能你的变量选择合适的类型，那么这种非严格的行为可能导致不预期的结果和微妙的错误，因为通常最好在严格模式下常见存储程序，这样可以产生一个错无而不至于让你在程序的测试过程中被忽略掉

3.6.2. 程序样例
我们将举例说明并比较MySQL存储程序在非严格模式下与其他几种编程语言的差别

Example 3-17展示了意图用Java程序打印出字符串"99 bottles of beer on the wall"时进行烦复的整型数和字符串值之间的转换的操作。不幸的是为了烦复的约束，程序员偶尔会将变量c声明为int，而不是String。Java编译器将在检测到尝试把字符串表达式转换成整型数时在编译期报告一个错误，而程序也不得不在失败中结束。

Example 3-17. Java程序的类型检测

1. 
   
2. $cat simplejava.java
   
3. package simplejava;
   
4. 
   
5. public class SimpleJava {
   
6. 
   
7.         public static void main(String[] args) {
   
8.                 String b;
   
9.                 int a;
   
10.                 int c;
    
11.                 a=99;
    
12.                 b="Bottles of beer on the wall";
    
13.                 c=a+" "+c;
    
14. 
    
15.                 System.out.println(c);
    
16. 
    
17.         }
    
18. }
    
19. 
    
20. $javac simplejava.java
    
21. simplejava.java:11: incompatible types
    
22. found   : java.lang.String
    
23. required: int
    
24.                 c=a+" "+c;
    
25.                        ^
    
26. 1 error

复制代码

现在，让我们看一个与之类似的例子（在动态类型语言中，这个例子中是PHP），在PHP和Perl中，变量的数据类型在运行时可以随着需求而改变。在Example 3-18，变量c刚开始是一个数字值，但当它被一个字符串变量赋值时，这个数据类型动态的改变为字符创型，而程序本身任然能按照需求正常的工作。

Example 3-18. PHP中的动态变量类型

1. 
   
2. $cat simplephp.php
   
3. <?php
   
4.                 $a=99;
   
5.                 $b="Bottles of beer on the wall";
   
6.                 $c=0;          #c is a number
   
7.                 $c=$a." ".$b;  #c is now a string
   
8. 
   
9.                 print $c."\n";
   
10. ?>
    
11. 
    
12. $php simplephp.php
    
13. 99 Bottles of beer on the wall
    

复制代码

现在，让我们看一下在没有开启严格模式的MySQL存储程序版本中的逻辑。就像Example 3-19所展示的那样。这个存储过程和前一个例子中程序具有相同的数据类型错误，变量c应该被定义为VARCHAR，但是在这个例子中他被声明为INT

Example 3-19. MySQL存储程序非严格模式下的类型检测

1. 
   
2. CREATE PROCEDURE strict_test(  )
   
3. BEGIN
   
4. 
   
5.   DECLARE a INT;
   
6.   DECLARE b VARCHAR(20);
   
7.   DECLARE c INT;
   
8. 
   
9.   SET a=99;
   
10.   SET b="Bottles of beer on the wall";
    
11.   SET c=CONCAT(a," ",b);
    
12.   SELECT c;
    
13. END
    
14.     --------------
    
15. 
    
16. Query OK, 0 rows affected (0.01 sec)
    
17. 
    
18. mysql> call strict_test(  );
    
19. +------+
    
20. | C    |
    
21. +------+
    
22. |   99 |
    
23. +------+
    
24. 1 row in set (0.00 sec)
    
25. 
    
26. Query OK, 0 rows affected, 2 warnings (0.00 sec)
    
27. 
    
28. mysql> SHOW WARNINGS;
    
29. +---------+------+----------------------------------------+
    
30. | Level   | Code | Message                                |
    
31. +---------+------+----------------------------------------+
    
32. | Warning | 1265 | Data truncated for column 'b' at row 1 |
    
33. | Warning | 1265 | Data truncated for column 'c' at row 1 |
    
34. +---------+------+----------------------------------------+
    
35. 2 rows in set (0.01 sec)
    
36. 
    
37. 
    

复制代码

在没有开启严格模式的情况下，MySQL并不会在尝试对一个整型变量赋予字符串值时报告一个错误，并且也不是动态的将数据类型转换为整型。取而代之，它只会不预期的，不正确的把字符串表达式的数字部分赋予整型变量。但是，如果你是在严格模式下创建的存储过程，我们就能收到一个运行时的错误，就像Example 3-20所展示的那样。

Example 3-20.存储程序严格模式下的类型检测

1. 
   
2. mysql> CALL strict_test(  );
   
3. ERROR 1406 (22001): Data too long for column 'b' at row 1
   

复制代码

在绝大多数时候你总是希望把程序在严格模式下运行，虽然一个非严格的程序可以在某些严格的程序产生错误是任然保持运行，但是非严格程序所产生的不可预期的错误和不适合的行为总是会带给你的程序过高的风险。始终记住：存储程序的行为依赖于程序创建时sql_mode变量的设置，而并非运行时。

提示：存储程序应该始终运行在严格模式下来避免不正规赋值过程中的不可预期的行为。存储程序严格模式决定与程序创建是sql_mode变量的设定，而与运行时无关

通常，程序员的责任就是保证数据类型的正确适用，如Bruce Eckel在他的文章中指出的那样“强类型vs强大的测试”(http://www.mindview.net/WebLog/log-0025), 强类型只不过是计算机语言提供的一种看似安全检测手段的幻觉，而正确的行为只有通过强大的测试才能获得，你不能假设在声明变量时给定的某个确定的类型就表明你已经隐含的完成了适当的变量类型校验。


3.7.第三章结语
在这一章中我们提供了用MySQL存储程序语言来创建程序片段的概览。MySQL存储程序语言是一种基于ANSI SQL:2003 PSM标准的块结构语言，他支持所以你期望在过程语言中应该具备的要素。对于存储程序语言而言，你所需要熟悉主要有以下几个方面:

• DECLARE语句，允许你定义并初始化程序变量。
• 存储程序参数，允许你向存储过程传入或传出信息。
• SET语句，允许你改变程序变量的值。
• MySQL函数，操作符和数据类型，MySQL存储程序语言有效的利用了MySQL SQL语言中的大多数等价物。
  

存储程序类型检查非常强烈的依赖于sql_mode设置变量的设定情况，如果一个程序在sql_mode变量包含任何一个严格设定（STRICT_TRANS_TABLES或STRICT_ALL_TABLES），那么程序在进行无效赋值是将产生一个错误。如果其中的任何一个都没有生效，那么存储程序将在无效赋值发生时产生一个错误（事实上是警告），但是它任然能继续执行。非严格存储程序模式的行为可能导致不预期的，微妙的错误，因此我们强烈建议你在通常情况下请在严格模式下创建你的存储程序。

cqb0202

顶顶，很不错