引用：百度百科

存储过程

　　存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

　　注意：本文主要简述mysql中存储过程的使用，其他数据库可能有所出入。

存储过程的好处：
　　1.由于数据库执行动作时，是先编译后执行的。然而存储过程是一个编译过的代码块，所以执行效率要比T-SQL语句高。
　　2.一个存储过程在程序在网络中交互时可以替代大堆的T-SQL语句，所以也能降低网络的通信量，提高通信速率。
　　3.通过存储过程能够使没有权限的用户在控制之下间接地存取数据库，从而确保数据的安全。

编写存储过程时用到的注释：

　　-- 这是一个存储过程（--后至少一个空格）

　　#这是一个存储过程（#后无需空格）

查看数据库中的存储过程：

　　show procedure status;
　　show procedure status  where db = 'mianshi';　　-- 查询数据库中的存储过程的信息
　　select name from MySQL.proc where db = 'mianshi' and type = 'PROCEDURE'; 　　-- 查询数据库中所有的存储过程的名字
　　show create procedure testusually;　　-- 查看存储过程或函数的创建代码

删除一个存储过程：

　　drop procedure stu_add;　　-- 这边stu_add是存储过程的名字

　　 drop procedure if exists stu_add;

创建一个简单的存储过程：

　　create procedure hello() select 'hello';　　-- 注意存储过程名字后面的（）必须加上，即使没有参数也需要

调用存储过程：

　　call hello();　　-- mysql中只能使用call来调用存储过程

　　call add_stu('lisi', null);　　-- mysql中的存储过程没有默认值，所以必须指定参数，如果没有值可以使用null来代替

存储过程参数的类型以及使用方法：

  　create or replace procedure add_stu(in sname VARCHAR(30), in sage int)　　-- mysql中这边不能使用replace

    is　　-- mysql中这边不能使用is或者as

    BEGIN

        set @sname = sname;　　-- @sname和@sage都是临时变量，将参数的值赋值给它们

        set @sage = sage;

        insert into student(sname, sage) values(@sname, @sage);
　　　　 return sname;　　-- mysql中不能使用return

        select * from student where sid = (select max(sid) from student);

    end;

如果存储过程中有多条sql语句，那么必须使用begin end关键字，并且每条sql语句后面都要使用；号隔开。

至于参数的字段类型(上例中的varcher和int)和数据库中的字段类型一致，但是某些类型需要指明其具体长度，否则出错。

IN

OUT

IN OUT

默认

必须指定

必须指定

值被：传递给子程序。子程序不能改变参数值。

值被：返回调用环境。子程序会改变参数值。

值被：传递给子程序，返回调用环境。子程序可以改变参数值。

可以是表达式，常量、或者是有值的变量。

必须是一个变量，这个变量是否有值不重要。值不会被传进去。

必须是一个变量，而且变量应该有值。

　这三个类型的具体区别与使用方法见下面的代码：引用：http://blog.csdn.net/cao478208248/article/details/28122819

## IN   IN参数只用来向过程传递信息，为默认值。

## MySQL存储过程"in"参数：跟C语言的函数参数的值传递类似，MySQL存储过程内部可能会修改此参数，

## 但in类型参数的修改对调用者(caller)来说是不可见的(not visible)

mysql>use test;

mysql> drop procedure if exists pr_param_in;

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> delimiter //

mysql> create procedure pr_param_in(in id int)

    -> begin

    -> if (id is not null) then

    ->     set id=id+1;

    -> end if;

    -> select id as id_inner;

    -> end;

    -> //

Query OK, 0 rows affected (0.03 sec)

mysql> delimiter ;

mysql> set @id=10;

Query OK, 0 rows affected (0.00 sec)

mysql> call pr_param_in(@id);

+----------+

| id_inner |

+----------+

|       11 |

+----------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @id as id_out;

+--------+

| id_out |

+--------+

|     10 |

+--------+

1 row in set (0.00 sec)

##  可以看到用户变量@id传入值为10，执行存储过程后，在过程内部值为：11（id_inner）,

##  但外部变量值依旧为：10（id_out）

参数IN

## OUT   OUT参数只用来从过程传回信息。

## MySQL存储过程"out"参数：从存储过程内部传值给调用者。

## 在存储过程内部，该参数初始值为 null，无论调用者是否给存储过程参数设置值。

mysql> drop procedure if exists pr_param_out;

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> delimiter //

mysql> create procedure pr_param_out(out id int)

    -> begin

    -> select id as id_inner_1;

    -> if (id is not null) then

    ->     set id=id+1;

    ->     select id as id_inner_2;

    -> else

    ->     select 1 into id;

    -> end if;

    -> select id as id_inner_3;

    -> end;

    -> //

Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

mysql> set @id=10;

Query OK, 0 rows affected (0.00 sec)

mysql> call pr_param_out(@id);

+------------+

| id_inner_1 |

+------------+

|       NULL |

+------------+

1 row in set (0.01 sec)

+------------+

| id_inner_3 |

+------------+

|          1 |

+------------+

1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> select @id as id_out;

+--------+

| id_out |

+--------+

|      1 |

+--------+

1 row in set (0.00 sec)

## 可以看出，虽然我们设置了用户定义变量@id为10，传递@id给存储过程后，在存储过程内部，

## id的初始值总是 null（id_inner_1）。最后id值（id_out=1）传回给调用者。

参数OUT

## INOUT INOUT参数可以向过程传递信息，如果值改变，则可再从过程外调用。

## MySQL存储过程"inout"参数跟out类似，都可以从存储过程内部传值给调用者。

## 不同的是：调用者还可以通过inout参数传递至给存储过程。

mysql> drop procedure if exists pr_param_inout;

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> delimiter //

mysql> create procedure pr_param_inout(inout id int)

    -> begin

    -> select id as id_inner_1;

    -> if (id is not null) then

    ->     set id=id+1;

    ->     select id as id_inner_2;

    -> else

    ->     select 1 into id;

    -> end if;

    -> select id as id_inner_3;

    -> end;

    -> //

Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

mysql> set @id=10;

Query OK, 0 rows affected (0.00 sec)

mysql> call pr_param_inout(@id);

+------------+

| id_inner_1 |

+------------+

|         10 |

+------------+

1 row in set (0.00 sec)

+------------+

| id_inner_2 |

+------------+

|         11 |

+------------+

1 row in set (0.00 sec)

+------------+

| id_inner_3 |

+------------+

|         11 |

+------------+

1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> select @id as id_out;

+--------+

| id_out |

+--------+

|     11 |

+--------+

1 row in set (0.00 sec)

## 从结果可以看出：我们把 @id(10）传给存储过程后，存储过程最后又把计算结果值11(id_inner_3)

## 传回给调用者。MySQL存储过程inout参数的行为跟C语言函数中的引用传值类似。

参数IN OUT

=========================================================================================  
通过以上例子：  
1)  如果仅仅想把数据传给MySQL存储过程，那就用in类型参数；  
2)  如果仅仅从MySQL存储过程返回值，那就用out类型参数；  
3)  如果需要把数据传给MySQL存储过程经过计算再传回给我们，那就用inout类型参数。

存储过程中使用的其他关键字和流程控制语句：

1、delimiter

　　用来改变mysql的输入结束符，delimiter默认是";",但是如果在命令行客户端中，存储过程中可能使用BEGIN   END，其中可能包括;,所以可以使用这个命令改变输入结束符。
例如：delimiter //;　　运行成功后，命令结束符变为//，之后这样使用select * from student//

2、declare

　　一般用来在存储过程中声明变量，mysql中只能在begin后面使用，否则出错。可以给变量设置默认值。

例如：declare a, b int default 0;     或者     declare a int;

3、set

　　SET用于设置不同类型的变量。这些变量会影响服务器或客户端的操作。SET可以用于向用户变量或系统变量赋值。如果要同时声明变量并且给变量赋值，需要在变量名称前面加上@，如果变量已经被声明（declare），则可以直接赋值。

例如：SET @var_name = 12;　　select @var_name;

　　set和declare的区别：

　　1）作用范围

　　set可以在一个会话的任何地方声明，作用域是整个会话，称为会话变量。

　　declare只能在存储过程中使用，称为存储过程变量。

　　2）初始化

　　在调用存储过程时，以declare声明的变量都会被初始化为 null或者默认值。

　　set定义的会话变量（即@开头的变量）不会被再初始化，在一个会话内，只须初始化一次，之后在会话内都是对上一次计算的结果，就相当于在是这个会话内的全局变量。

4、case...when...then...else

　　case后面为一个需要判断的表达式，when后面可以是一个变量或者条件语句（条件语句应该可以，可以自己测试一下），成立则执行其后的then语句，否则继续判断其他分支。当所有分支的条件都不成立时，执行else分支。条件表达式可以由“=、<、<=、>、>=、!=”等条件运算符组成，并且可以使用AND、OR、NOT对多个表达式进行组合。（只会执行一个分支，当执行完后，以及退出case语句）

例如：

drop procedure if exists test_case;

create procedure test_case(a int)

BEGIN

    declare i int default 0;

    case a

        when a > 0 and a < 2

            then set i = 1;

        when a > 0 and a < 3

            then set i = 2;

        else

            set i = 3;

    end case;

    select i as result;

end;

call test_case(7);

5、if...else...

　　当IF中条件成立时，执行THEN后的语句，否则判断ELSEIF中的条件，成立则执行其后的t语句，否则继续判断其他分支。当所有分支的条件均不成立时，执行ELSE分支。

条件表达式可以由“=、<、<=、>、>=、!=”等条件运算符组成，并且可以使用AND、OR、NOT对多个表达式进行组合。（只会执行一个分支，当执行完后，以及退出if语句）

例如：

drop procedure if exists test_first;

create procedure test_first(id int)

BEGIN

    if id > 10 and id < 20 THEN

        select '10-20' as 'result';

    ELSEIF id > 20 and id < 30 THEN

        select '20-30' as 'result';

    ELSEIF id > 30 and id < 40 THEN

        select '30-40' as 'result';

    ELSE

        select 'we can`t find it' as 'result';

    end if;

END;

call test_first(55);

IF作为一条语句，在END IF后需要加上分号";"以表示语句结束。

 6、循环语句

1)while...do...end while循环

　　当while中的条件成立时，执行do后面的语句，否则结束循环。条件表达式可以由“=、<、<=、>、>=、!=”等条件运算符组成，并且可以使用AND、OR、NOT对多个表达式进行组合。

例如：

drop procedure if exists test_while;

create procedure test_while()

BEGIN

    declare i , a int DEFAULT ;

    set i=;

    select a as 'before';

    while i <  do

        set a = a + ;

        set i = i + ;

    end while;

    select a as 'after';

END;

call test_while();

2)repeat...until...end repeat循环

　　先执行一次循环体，再判断until中的条件是否满足，如果满足，退出循环。

例如：

drop procedure if exists test_repeat;

create procedure test_repeat()

BEGIN

    set @i = 1;

    repeat

        set @i = @i + 1;

    until

        @i>5

    end repeat;

    select @i as result;

end;

call test_repeat();

3)loop...end loop循环

　　loop循环需要配合标号，end 标号和if...else...语句来使用

例如：

drop procedure if exists test_loop;

create procedure test_loop()

BEGIN

    declare i int default 0;

    my_loop:LOOP

        set i = i + 1;

        if

            i > 5

        then

            leave my_loop;

        end if;

    end loop;

    select i as result;

end;

call test_loop();

mysql中goto是保留关键字，所以暂时不能使用goto来完成循环。

 7、其他关键字和语句块

1）Labels 标号和 END Labels 结束标号

2）iterate语句

最新文章

  1. solr课程学习系列-solr的概念与结构（1）
  2. Java对象 json之间的转换（json-lib）
  3. XHTML标签的嵌套规则--很基础很重要
  4. PHP工程师面临成长瓶颈
  5. iOS多工程依赖
  6. Chrome不支持showModalDialog模态对话框和无法返回returnValue的问题
  7. photosho 等距复制或旋转复制
  8. HTML DOM （文档对象模型）
  9. webpack配置css相关loader注意先后顺序
  10. 部落划分Group[JSOI2010]
  11. Docker 导出 &amp; 导入
  12. Servlet 随记：
  13. gym 101081 E. Polish Fortress 几何
  14. centos安装多个tomcat
  15. django在centos部署
  16. 《Effective C++》笔记
  17. Ionic 中badge的应用
  18. linux ping命令
  19. MongoDB副本集的工作原理
  20. element组件知识点总结

热门文章

  1. 使用Robo 3T 软件管理MongoDB数据库如何执行命令行shell
  2. 六、Scrapy中Download Middleware的用法
  3. C语言考试
  4. hdu2015 偶数求和【C++】
  5. vue 实现点赞
  6. windows下db2的一些使用心得(不含安装)
  7. 洛谷 P1796 汤姆斯的天堂梦_NOI导刊2010提高（05）
  8. Android LaunchAnyWhere (Google Bug 7699048)漏洞具体解释及防御措施
  9. 【转】shell中的内建命令, 函数和外部命令
  10. HDOJ 2196 Computer 树的直径