存储过程 ,游标

1 存储过程简介

    存储过程是一段代码，由存储在一个数据库的目录中、声明式的和过程式的sql语句组成，可以从一个程序、触发器或者另一个存储过程调用它从而激活它。存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。 
    一个存储过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。
2 存储过程优缺点
    2.1 优点
        2.1.1  执行速度更快。有两个原因：首先，在存储过程创建的时候，数据库已经对其进行了一次解析和优化，它只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。其次，存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用。
        2.1.2 减少网络通信量。调用一个SQL语句不多的存储过程与直接调用SQL语句的网络通信量可能不会有很大的差别，但如果存储过程包含较多SQL语句，那么其性能绝对比一条一条的调用SQL语句要高得多。
        2.1.3 安全性高。可设定只有某此用户才具有对指定存储过程的使用权。
        2.1.4 可维护性高。更新存储过程通常比更改、测试以及重新部署程序集需要较少的时间和精力。
        2.1.5 不依赖某种宿主语言。如果用多种语言开发，某些通用代码不用重复。
        2.1.6 布式工作。应用程序和数据库的编码工作可以分别独立进行，而不会相互压制。
        2.1.7 存储过程可以重复使用,可减少数据库开发人员的工作量。
    2.2 缺点
        2.2.1 SQL本身是一种结构化查询语言，加上了一些控制（赋值、循环和异常处理等），本质上还是过程化的，面对复杂的业务逻辑，过程化的处理会很吃力。这一点算致命伤。
        2.2.2 不便于调试。基本上没有较好的调试器，很多时候是用print来调试，但用这种方法调试长达数百行的存储过程简直是噩梦。
        2.2.3 无法适应数据库的切割（水平或垂直切割）。数据库切割之后，存储过程并不清楚数据存储在哪个数据库中。
        2.2.4 精通SQL的新手越来越少。

3 创建存储过程

    3.1 创建语法如下

CREATE PROCEDURE存储过程名 (参数列表)BEGIN    SQL语句代码块END

    3.2 例如：计算两个数之和

delimiter //create procedure procedureAdd (a int, IN b int)begin    declare c int;    if a is null then        set a = 0;    end if;    if b is null then        set b = 0;    end if;    set c = a + b;    select c as sum;end;//delimiter ;

    3.3 注意：
    由括号包围的参数列必须总是存在。如果没有参数，也该使用一个空参数列()。每个参数默认都是一个IN参数,要指定为其它参数,可在参数名之前使用关键词 OUT或INOUT
    在mysql客户端定义存储过程的时候使用delimiter命令来把语句定界符从;变为//，创建完之后在将分隔符更改为;号。

4 查看存储过程

    查看数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等。

show procedure status \G

    或者

select name from mysql.proc where db = 'your_db_name' and 'type' = 'PROCEDURE';

    查看某个已存在的存储过程的详细信息（其中procedureAdd为以创建的存储过程名）

show create procedure procedureAdd \G

5 调用存储过程

    5.1 基本语法

call 存储过程名(参数列表)

    5.2 例如

call procedureAdd(10, 30);

    或者

set @a = 10;set @b = 30;call procedureAdd(@a, @b);

    注意：在使用SET定义变量时应遵循SET的语法规则 SET @变量名=初始值；

6 修改存储过程

    6.1 基本语法

ALTER PROCEDURE 存储过程名 [characteristic ...]

    注意：characteristic是存储过程创建时的特征，在CREATE PROCEDURE语句中已经介绍过。只要设定了其中的值，存储过程的特征就随之变化。
    如果要修改存储过程的内容，可以使用先删除再重新定义存储过程的方法。存储过程某些的特征如下
    characteristic:
    COMMENT 'string'
    | LANGUAGE SQL
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

    | SQL SECURITY { DEFINER | INVOKER }

    6.2 例如：修改特征

    查看默认特征值

mysql> SELECT SPECIFIC_NAME,SQL_DATA_ACCESS, ROUTINE_COMMENT FROM information_schema.Routines WHERE ROUTINE_NAME='procedureAdd';+---------------+-----------------+-----------------+| SPECIFIC_NAME | SQL_DATA_ACCESS | ROUTINE_COMMENT |+---------------+-----------------+-----------------+| procedureAdd  | CONTAINS SQL    |                 |+---------------+-----------------+-----------------+

    将读写权限改为READS SQL DATA，并加上注释信息'This is a test!',代码执行如下：

mysql> ALTER  PROCEDURE  procedureAdd READS SQL DATA COMMENT 'This is a test!';

    查看修改后的特征值

mysql> SELECT SPECIFIC_NAME,SQL_DATA_ACCESS, ROUTINE_COMMENT FROM information_schema.Routines WHERE ROUTINE_NAME='procedureAdd';+---------------+-----------------+-----------------+| SPECIFIC_NAME | SQL_DATA_ACCESS | ROUTINE_COMMENT |+---------------+-----------------+-----------------+| procedureAdd  | READS SQL DATA  | This is a test! |+---------------+-----------------+-----------------+

    说明：从查询的结果可以看出，访问数据的权限（SQL_DATA_ACCESS）已经变成READS SQL DATA，函数注释（ROUTINE_COMMENT）已经变成了"This is a test!"。

7 删除存储过程

    7.1 基本语法

DROP PROCEDURE  IF  EXISTS 存储过程名

    7.2 例如

DROP PROCEDURE  IF  EXISTS procedureAdd;

    注意：不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程。

*

8 存储过程局部变量

    8.1 介绍

    在一个存储过程内部，可以声明局部变量。他们可以用来存储中间临时结果。如果我们在一个存储过程中需要一个局部变量，必须使用DECLARE VARIABLE语句引入它。通过声明，就确定了变量的数据类型，并且也可以指定初始值。（如果使用了DECLARE VARIABLE语句，他们必须作为BEGIN-END语句块的第一条语句包含其中）

    8.2 例如：

delimiter //create procedure test(out num1 integer) begin     declare num2 integer default 100;     set num1 = num2; end//delimiter ;

    调用存储过程

call test(@num);select @num;

9 存储过程和用户变量

    9.1 介绍

    用户变量总有一个全局特性，即便它在一个存储过程内部创建，在存储过程结束后他们依然保留。在存储过程之外创建的用户变量，仍然可以在存储过程中保留他们自己的值。

    9.2 例如

delimiter //create procedure user_variable() begin     set @varTest = 1; end//delimiter ;

    调用存储过程后查看varTest值为1

call user_variable();select @varTest;

    说明：set语句是sql本身的一部分，它可以讲一个值赋给用户变量和局部变量，也可使用任何随机表达式。

10 存储过程与游标

    10.1 介绍

    常规的select语句可能返回多行，使用游标（cursor）可以处理这一点，把数据一行一行的取入到存储过程中。使用游标需要用到四个特殊语句：declare sursor，open sursor，fetch cursor，和close cursor。

    如果使用declare cursor语句声明一个游标，我们就把它连接到了一个表表达式。接下来就可以使用fetch cursor语句来把产生的结果一行一行的获取到存储过程中。在某个时刻，结果中只有一行可见，也就是当前行。它就好像是指向结果中一行的一个箭头，这也是游标这个名字的来历。使用fetch cursor这条语句，我们可以把游标移动到下一行，当处理完所有的行，可以使用close cursor语句来删除结果。

    10.2 游标作用及属性

    作用:
        就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作；
    属性：
        游标是只读的，也就是不能更新它；
        游标是不能滚动的，也就是只能在一个方向上进行遍历，不能在记录之间随意进退，不能跳过某些记录；  
        避免在已经打开游标的表上更新数据。

    10.3 如何使用游标

    声明游标

DECLARE cursor_name CURSOR FOR SELECT语句;  

    打开游标

OPEN cursor_name; 

    移动游标

FETCH cursor_name INTO variable list;  

    关闭游标

CLOSE cursor_name;  

    10.4 游标实例

    创建测试表及数据

CREATE TABLE test.users (    ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,    user_name varchar(60) NOT NULL DEFAULT '',    user_pass varchar(64) NOT NULL DEFAULT '',    PRIMARY KEY (ID)   )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert into test.users values(1,'name1', 'pass1');insert into test.users values(2,'name2', 'pass2');insert into test.users values(3,'name3', 'pass3');insert into test.users values(4,'name4', 'pass4');insert into test.users values(5,'name5', 'pass5');

    创建游标存储过程

delimiter |create procedure test_cursor (in param int(10),out result varchar(90))begin    declare name varchar(20);    declare pass varchar(20);    declare done int;    declare cur_test CURSOR for select user_name,user_pass from test.users;    declare continue handler FOR SQLSTATE '02000' SET done = 1;    if param then        select concat_ws(',',user_name,user_pass) into result from test.users where id=param;    else        open cur_test;        repeat            fetch cur_test into name, pass;            select concat_ws(',',result,name,pass) into result;            until done end repeat;        close cur_test;    end if;end;|delimiter ;

    各行命令详解

    1行，告诉MySQL解释器，输入结束命令改为|，默认为;(命令本身与存储过程无关)    2行，创建一个存储过程，注意：如果我把out result varchar(90)改成out result varchar，返回的结果中只有一个字符。    3行，开始    4行，定义一个变量name    5行，定义变量pass    6行，定义一个结束标识    7行，定义一个光标，指向select user_name,user_pass from test.users;语句    8行，如果sqlstate等于02000时，把done设置成1,也就是找不到数据时    9，11,18行，if判断    10行，根据参数，把数据取出来，放到result中，concat_ws函数表示concat with separator,即有分隔符的字符串连接，如连接后以逗号分隔    12行，打开光标    13，16行，repeat循环，根php的do while原理一样    14行，从光标中取出数据。    15行，将数据合并起来    17行，关闭光标    18,19行，标签闭合。    20行，

    结果反馈

mysql> call test_cursor(3,@test);                                               Query OK, 1 row affected (0.00 sec)mysql> select @test;+-------------+| @test       |+-------------+| name3,pass3 |+-------------+1 row in set (0.00 sec)mysql> call test_cursor('',@test);Query OK, 1 row affected, 2 warnings (0.00 sec)mysql> select @test;+-------------------------------------------------------------------------+| @test                                                                   |+-------------------------------------------------------------------------+| name1,pass1,name2,pass2,name3,pass3,name4,pass4,name5,pass5,name5,pass5 |+-------------------------------------------------------------------------+1 row in set (0.00 sec)

 转载请注明出处：http://blog.csdn.net/jesseyoung/article/details/34420839