十二、MySQL5的新特性：存储过程和函数
从MySQL5开始增加了存储过程特性。下面是一个包括存储过程的实例声明：
CREATE PROCEDURE procedure1 /* name存储过程名*/
(IN parameter1 INTEGER) /* parameters参数*/
BEGIN /* start of block语句块头*/
DECLARE variable1 CHAR(10); /* variables变量声明*/
IF parameter1 = 17 THEN /* start of IF IF条件开始*/
SET variable1 = 'birds'; /* assignment赋值*/
ELSE
SET variable1 = 'beasts'; /* assignment赋值*/
END IF; /* end of IF IF结束*/
INSERT INTO table1 VALUES (variable1);/* statement SQL语句*/
END /* end of block语句块结束*/
什么样的SQL语句在Mysql存储过程中才是合法的呢？你可以创建一个包含INSERT, UPDATE,DELETE, SELECT, DROP,CREATE,REPLACE等的语句。你唯一需要记住的是如果代码中包含MySQL扩充功能，那么代码将不能移植。在标准SQL语句中：任何数据库定义语言都是合法的。
下面从简单的开始：
mysql> create procedure sp1() create table t_test (c1 date);
Query OK, 0 rows affected (0.00 sec)
mysql> create procedure sp2() select * from t_test;
Query OK, 0 rows affected (0.00 sec)
mysql> create procedure sp3() drop table t_test;
Query OK, 0 rows affected (0.00 sec)
mysql> call sp1;
Query OK, 0 rows affected (0.15 sec)
mysql> call sp2;
Empty set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> call sp3;
Query OK, 0 rows affected (0.03 sec)
mysql> drop procedure sp1;
Query OK, 0 rows affected (0.00 sec)
mysql> drop procedure sp2;
Query OK, 0 rows affected (0.00 sec)
mysql> drop procedure sp3;
Query OK, 0 rows affected (0.00 sec)
mysql>
复杂一点的，Characteristics Clauses 特征子句。
CREATE PROCEDURE sp()
LANGUAGE SQL <--
NOT DETERMINISTIC <--
SQL SECURITY DEFINER <--
COMMENT 'A Procedure' <--
SELECT CURRENT_DATE, RAND() FROM t;
这里我给出的是一些能反映存储过程特性的子句。子句内容在括号之后，主体之前。这些子句都是可选的。
LANGUAGESQL <--这个LANGUAGESQL子句是没有作用的。仅是为了说明下面过程的主体使用SQL语言编写。这条是系统默认的，但你在这里声明是有用的，因为某些DBMS（IBM的DB2）需要它，如果你关注DB2的兼容问题最好还是用上。此外，今后可能会出现除SQL外的其他语言支持的存储过程。
NOTDETERMINISTIC<--是传递给系统的信息。这里一个确定过程的定义就是那些每次输入一样输出也一样的程序。在这个案例中，既然主体中含有SELECT语句，那返回肯定是未知的因此我们称其NOT DETERMINISTIC。但是MySQL内置的优化程序不会注意这个，至少在现在不注意。
SQLSECURITY DEFINER<--意味着在调用时检查创建过程用户的权限，告诉MySQL服务器检查创建过程的用户就可以了，当过程已经被调用，就不检查执行调用过程的用户了。而另一个选项（INVOKER）则是告诉服务器在这一步仍然要检查调用者的权限。
COMMENT 'A Procedure' <--是一个可选的注释说明。最后，注释子句会跟过程定义存储在一起。
特征子句也有默认值，如果省略了就相当于：
LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY DEFINER COMMENT ''
如上面过程跟下面语句是等效的：
CREATE PROCEDURE sp()
SELECT CURRENT_DATE, RAND() FROM t;
让我们更进一步的研究怎么在存储过程中定义参数，就和Oracle中的一样：
1.CREATE PROCEDURE p5() ... //参数列表是空的
2.CREATE PROCEDURE p5([IN] name data-type) ... //有一个输入参数，这里的词IN可选，因为默认参数为IN（input）
3.CREATE PROCEDURE p5(OUT name data-type) ... //有一个输出参数
4.CREATE PROCEDURE p5(INOUT name data-type) ... //有一个参数，既能作为输入也可以作为输出
举例：
mysql> CREATE PROCEDURE p5(p INT) SET @x = p;
Query OK, 0 rows affected (0.00 sec)
mysql> CALL p5(12345);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x;
+-------+
| @x |
+-------+
| 12345 |
+-------+
1 row in set (0.00 sec)
mysql> CREATE PROCEDURE p6 (OUT p INT) SET p = -5;
Query OK, 0 rows affected (0.00 sec)
mysql> CALL p6(@y);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @y;
+------+
| @y |
+------+
| -5 |
+------+
1 row in set (0.00 sec)
现在我们展开的详细分析一下过程体：
CREATE PROCEDURE p7 ()
BEGIN
SET @a = 5;
SET @b = 5;
INSERT INTO t VALUES (@a);
SELECT s1 * @a FROM t WHERE s1 >= @b;
END;
完成过程体的构造就是BEGIN/END块。这个BEGIN/END语句块和Pascal语言中的BEGIN/END是基本相同的，和C语言的框架是很相似的。我们可以使用块去封装多条语句。在这个例子中，我们使用了多条设定会话变量的语句，然后完成了一些insert和select语句。如果你的过程体中有多条语句，那么你就需要BEGIN/END块了。BEGIN/END块也被称为复合语句，在这里你可以进行变量定义和流程控制。
评论