MySQL数据库新特性之存储过程入门教程

MySQL数据库新特性之存储过程入门教程

 

http://database.51cto.com/art/201107/273027_2.htm

 

在MYSQL 5中,终于引入了存储过程这一新特性,这将大大增强MYSQL 的数据库处理能力,在本文中,将指导读者快速掌握MYSQL 5的存储过程的基本知识,带领用户入门。

存储过程介绍

存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程可由应用程序通过一个调用来执行，而且允许用户声明变量 。同时，存储过程可以接收和输出参数、返回执行存储过程的状态值，也可以嵌套调用。

存储过程的特点

作为存储过程，有以下这些优点：

(1)减少网络通信量。调用一个行数不多的存储过程与直接调用SQL语句的网络通信量可能不会有很大的差别，可是如果存储过程包含上百行SQL语句，那么其性能绝对比一条一条的调用SQL语句要高得多。

(2)执行速度更快。存储过程创建的时候，数据库已经对其进行了一次解析和优化。其次，存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接中读取。

(3)更强的安全性。存储过程是通过向用户授予权限(而不是基于表)，它们可以提供对特定数据的访问，提高代码安全，比如防止 SQL注入。

(4) 业务逻辑可以封装存储过程中，这样不仅容易维护，而且执行效率也高

当然存储过程也有一些缺点，比如：

1 可移植性方面：当从一种数据库迁移到另外一种数据库时，不少的存储过程的编写要进行部分修改。

2 存储过程需要花费一定的学习时间去学习,比如学习其语法等。

在MYSQL中，推荐使用MYSQL Query Browswer(http://dev.mysql.com/doc/query-browser/en/)这个工具去进行存储过程的开发和管理。

下面分步骤来学习MYSQL中的存储过程。

1 定义存储过程的结束符

在存储过程中，通常要输入很多SQL语句，而SQL语句中每个语句以分号来结束，因此要告诉存储过程，什么位置是意味着整个存储过程结束，所以我们在编写存储过程前，先定义分隔符，我们这里定义“//”为分隔符，我们使用DELIMITER //这样的语法，就可以定义结束符了，当然你可以自己定义其他喜欢的符号。

2 如何创建存储过程

下面先看下一个简单的例子，代码如下：

1. DELIMITER //  
2. CREATE PROCEDURE `p2` ()  
3. LANGUAGE SQL  
4. DETERMINISTIC  
5. SQL SECURITY DEFINER  
6. COMMENT 'A procedure' 
7. BEGIN 
8.     SELECT 'Hello World !';  
9. END// 

下面讲解下存储过程的组成部分：

1)首先在定义好终结符后，使用CREATE PROCEDURE+存储过程名的方法创建存储过程，LANGUAGE选项指定了使用的语言，这里默认是使用SQL。

2)DETERMINISTIC关键词的作用是，当确定每次的存储过程的输入和输出都是相同的内容时，可以使用该关键词，否则默认为NOT DETERMINISTIC。

3) SQL SECURITY关键词，是表示调用时检查用户的权限。当值为INVOKER时，表示是用户调用该存储过程时检查，默认为DEFINER,即创建存储过程时检查。

4) COMMENT部分是存储过程的注释说明部分。

5)在BEGIN END部分中，是存储过程的主体部分。

3 调用存储过程的方法

调用存储过程的方法很简单，只需要使用call命令即可，后面跟要调用存储过程的名称及输入的变量列表，比如：

1. CALL stored_procedure_name (param1, param2, ....)  
2. CALL procedure1(10 , 'string parameter' , @parameter_var); 

4 修改和删除存储过程

可以用ALTER的语法去修改存储过程的主要特征和参数，要修改其存储过程的主体部分的话，必须要先删除然后再重建。比如下面修改存储过程num_from_employee的定义。将读写权限改为MODIFIES SQL DATA，并指明调用者可以执行。代码执行如下：

1. ALTER PROCEDURE num_from_employee   
2. MODIFIES SQL DATA SQL SECURITY INVOKER ; 

而删除存储过程的语法为使用DROP关键词即可。如下

1. DROP PROCEDURE IF EXISTS p2; 

5 存储过程的参数

下面来学习下存储过程中的参数，先看下存储过程中的参数形式，如下：

1. CREATE PROCEDURE proc1 ()-----这个存储过程中是空的参数列表  
2.  
3. CREATE PROCEDURE proc1 (IN varname DATA-TYPE)-----这个存储过程中有一个输入参数，名称为varname，后面是跟数据类型DATA-TYPE,IN参数是默认的，因此可以省略不写  
4.  
5. CREATE PROCEDURE proc1 (OUT varname DATA-TYPE)-----这个存储过程中varname为输出参数  
6.  
7. CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE)-----这个存储过程中，varname既是输入参数也是输出参数 

下面具体看个例子，首先是IN输入参数的例子，如下：

1. DELIMITER //  
2. CREATE PROCEDURE `proc_IN` (IN var1 INT)  
3. BEGIN 
4. SELECT var1 + 2 AS result;  
5. END// 

输出OUT参数例子如下：

1. DELIMITER //  
2. CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100))  
3. BEGIN 
4. SET var1 = 'This is a test';  
5. END // 

IN-OUT的例子：

1. DELIMITER //  
2. CREATE PROCEDURE `proc_INOUT` (OUT var1 INT)  
3. BEGIN 
4. SET var1 = var1 * 2;  
5. END // 

6 如何定义变量

下面讲解下MYSQL 5存储过程中，如何定义变量。

必须显式地在存储过程的一开始声明变量，并指出它们的数据类型，一但声明了变量后，就可以在存储过程中使用，定义变量的语法如下：

1. DECLARE varname DATA-TYPE DEFAULT defaultvalue 

举例说明：

1. DECLARE a, b INT DEFAULT 5;  
2. DECLARE str VARCHAR(50);  
3. DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;  
4. DECLARE v1, v2, v3 TINYINT; 

一旦定义好变量，就可以在存储过程中对其进行赋初值,并进行各类相关的操作，比如：

1. DELIMITER //  
2. CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20))  
3. BEGIN 
4. DECLARE a, b INT DEFAULT 5;  
5. DECLARE str VARCHAR(50);  
6. DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;  
7. DECLARE v1, v2, v3 TINYINT;  
8. INSERT INTO table1 VALUES (a);  
9. SET str = 'I am a string';  
10. SELECT CONCAT(str,paramstr), today FROM table2 WHERE b>=5;  
11. END // 

MYSQL存储过程的语法结构

MYSQL存储过程中支持IF，CASE，ITERATE，LEAVE LOOP，WHILE和REPEAT等语法结构和语句，在本文中，着重介绍IF,CASE和WHILE语法，因为它们使用的最为广泛。

IF 语句

if语句使用的是if…then end if的语法结构，例子如下：

1. DELIMITER //  
2. CREATE PROCEDURE `proc_IF` (IN param1 INT)  
3. BEGIN 
4. DECLARE variable1 INT;  
5. SET variable1 = param1 + 1;  
6. IF variable1 = 0 THEN 
7. SELECT variable1;  
8. END IF;  
9. IF param1 = 0 THEN 
10. SELECT 'Parameter value = 0';  
11. ELSE 
12. SELECT 'Parameter value <= 0';  
13. END IF;  
14. END // 

CASE语句

当有很多IF语句时，就应该考虑使用CASE语句了，它是多分支选择语句，有两种写法：

第一种写法：

1. DELIMITER //  
2. CREATE PROCEDURE `proc_CASE` (IN param1 INT)  
3. BEGIN 
4. DECLARE variable1 INT;  
5. SET variable1 = param1 + 1;  
6. CASE variable1  
7. WHEN 0 THEN 
8. INSERT INTO table1 VALUES (param1);  
9. WHEN 1 THEN 
10. INSERT INTO table1 VALUES (variable1);  
11. ELSE 
12. INSERT INTO table1 VALUES (99);  
13. END CASE;  
14. END // 

另外一种写法：

1. DELIMITER //  
2. CREATE PROCEDURE `proc_CASE` (IN param1 INT)  
3. BEGIN 
4. DECLARE variable1 INT;  
5. SET variable1 = param1 + 1;  
6. CASE 
7. WHEN variable1 = 0 THEN 
8. INSERT INTO table1 VALUES (param1);  
9. WHEN variable1 = 1 THEN 
10. INSERT INTO table1 VALUES (variable1);  
11. ELSE 
12. INSERT INTO table1 VALUES (99);  
13. END CASE;  
14. END // 

WHILE语句

WHILE语句跟普通编程语言中的while语句差不多，例子如下：

1. DELIMITER //  
2. CREATE PROCEDURE `proc_WHILE` (IN param1 INT)  
3. BEGIN 
4. DECLARE variable1, variable2 INT;  
5. SET variable1 = 0;  
6. WHILE variable1   
7. INSERT INTO table1 VALUES (param1);  
8. SELECT COUNT(*) INTO variable2 FROM table1;  
9. SET variable1 = variable1 + 1;  
10. END WHILE;  
11. END // 

8 MYSQL存储过程中的游标

MYSQL中的游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。MYSQL中的游标的语法如下：

1. DECLARE cursor-name CURSOR FOR SELECT ...; /* 声明一个游标，名称为cursor-name,并用CURSOR FOR SELECT*/  
2. DECLARE CONTINUE HANDLER FOR NOT FOUND /*指定当遍历完结果集后，游标如何继续处理*/  
3. OPEN cursor-name; /*打开游标 */  
4. FETCH cursor-name INTO variable [, variable]; /* 将变量赋值给游标*/  
5. CLOSE cursor-name; /*使用后关闭游标*/ 

一个具体的例子如下：

1. DELIMITER //  
2. CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT)  
3. BEGIN 
4. DECLARE a, b, c INT;  
5. DECLARE cur1 CURSOR FOR SELECT col1 FROM table1;  
6. DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;  
7. OPEN cur1;  
8. SET b = 0;  
9. SET c = 0;  
10. WHILE b = 0 DO  
11. FETCH cur1 INTO a;  
12. IF b = 0 THEN 
13. SET c = c + a;  
14. END IF;  
15. END WHILE;  
16. CLOSE cur1;  
17. SET param1 = c;  
18. END // 

其中，DECLARE cur1 CURSOR FOR SELECT col1 FROM table1;

表示将从table1表中选取col1列的内容放到游标curl中，即每次游标遍历的结果都放在curl中，要注意游标只能向前遍历，而不能向后，并且注意，游标不能更新，最后关闭游标。