/ 中存储网

MySQL存储过程基础学习

2014-07-13 15:54:02 来源:中存储网

    一、 简介

    存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象,任何一个设计良好的数据库应用程序都应该用到存储过程。

    虽然常用术语是存储过程(stored procedure),但MySQL实际上实现了两中类型,除了存储过程外,还有存储函数(stored routine),它们统称为存储例程。

     二、基本格式
1、存储过程
  CREATE PROCEDURE 过程名 ([过程参数[,...]])
  [特性 ...] 过程体

如创建: CEATE PROCEDURE p1 (a INT)

SELECT a;

调用一下:CALL p1(8);  

将显示:

+------+

| a    |

+------+

|    8 |

+------+

1 row in set (0.00 sec)
2、存储函数
  CREATE FUNCTION 函数名 ([函数参数[,...]])
  RETURNS 返回类型
  [特性 ...] 函数体

如创建:CREATE FUNCTION f1 (x INT)

RETURNS INT

RETURN x;       /* 过程函数一次只能返回一个值

调用一下:SELECT f1 (3);

将显示:

+-------+

| f1(3) |

+-------+

|     3 |

+-------+

1 row in set (0.00 sec)

3、过程参数
  [ IN | OUT | INOUT ] 参数名 参数类型
4、函数参数
  参数名 参数类型
5、返回类型
  有效的MySQL数据类型即可
6、过程体/函数体格式如下
BEGIN
有效的SQL语句
END

7、特性(一般不要求)
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

8、存储过程中参数的IN,OUT,INOUT类型

    存储过程可以接受输入参数,并把参数返回给调用方。不过,对于每个参数,需要声明其参数名、数据类型,还要指定此参数是用于向过程传递信息,还是从过程传回信息,或是二者兼有。

    为声明参数的作用,使用如下3个关键字之一:

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

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

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

如果仅仅想把数据传给 MySQL 存储过程,那就使用“in” 类型参数;如果仅仅从 MySQL 存储过程返回值,那就使用“out” 类型参数;如果需要把数据传给 MySQL 存储过程,还要经过一些计算后再传回给我们,此时,要使用“inout” 类型参数。

对于任何声明为OUT或INOUT的参数,,当调用存储过程时需要在参数名前加上@符号,这样该参数就可以在过程外调用了。

 下面举三个实例:MySQL 存储过程 “in” 参数:跟 C 语言的函数参数的值传递类似, MySQL 存储过程内部可能会修改此参数,但对 in 类型参数的修改,对调用者(caller)来说是不可见的(not visible)。

create procedure pr_param_in(   in id int -- in 类型的 MySQL 存储过程参数)begin   if (id is not null) then      set id = id + 1;   end if;    select id as id_inner;end;set @id = 10; call pr_param_in(@id); select @id as id_out;mysql> call pr_param_in(@id);+----------+| id_inner |+----------+|       11 |+----------+ mysql> select @id as id_out;+--------+| id_out |+--------+| 10     |+--------+可以看到:用户变量 @id 传入值为 10,执行存储过程后,在过程内部值为:11(id_inner),但外部变量值依旧为:10(id_out)。

MySQL 存储过程 “out” 参数:从存储过程内部传值给调用者。在存储过程内部,该参数初始值为 null,无论调用者是否给存储过程参数设置值。

create procedure pr_param_out(   out id int)begin   select id as id_inner_1;    /* id 初始值为 null*/    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;set @id = 10; call pr_param_out(@id); select @id as id_out;mysql> set @id = 10;mysql>mysql> call pr_param_out(@id);    /*外部变量不能传给存储过程*/+------------+| id_inner_1 |+------------+|       NULL |+------------+ +------------+| id_inner_3 |+------------+|          1 |+------------+ mysql> select @id as id_out;  /*过程将改变的值传给外部变量*/+--------+| id_out |+--------+| 1      |+--------+可以看出,虽然我们设置了用户定义变量 @id 为 10,传递 @id 给存储过程后,在存储过程内部,id 的初始值总是 null(id_inner_1)。最后 id 值(id_out = 1)传回给调用者。

MySQL 存储过程 inout 参数跟 out 类似,都可以从存储过程内部传值给调用者。不同的是:调用者还可以通过 inout 参数传递值给存储过程。

drop procedure if exists pr_param_inout; create procedure pr_param_inout(   inout id int)begin   select id as id_inner_1;  -- id 值为调用者传进来的值    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;set @id = 10; call pr_param_inout(@id); select @id as id_out;mysql> set @id = 10; mysql>mysql> call pr_param_inout(@id);+------------+| id_inner_1 |+------------+|         10 |+------------+ +------------+| id_inner_2 |+------------+|         11 |+------------+ +------------+| id_inner_3 |+------------+|         11 |+------------+mysql>mysql> select @id as id_out;+--------+| id_out |+--------+| 11     |+--------+从结果可以看出:我们把 @id(10),传给存储过程后,存储过程最后又把计算结果值 11(id_inner_3)传回给调用者。 MySQL 存储过程 inout 参数的行为跟 C 语言函数中的引用传值类似。

通过以上例子:如果仅仅想把数据传给 MySQL 存储过程,那就使用“in” 类型参数;如果仅仅从 MySQL 存储过程返回值,那就使用“out” 类型参数;如果需要把数据传给 MySQL 存储过程,还要经过一些计算后再传回给我们,此时,要使用“inout” 类型参数。

9、声明和设置变量

(1) 声明变量

   在存储例程使用局部变量之前,必须声明局部变量,变量声明通过DECLARE语句实现,其原型如下:

   DECLARE variable_name TYPE ;

如:DECLARE x VARCHAR(254);

   在声明变量时,声明必须放在BEGIN/END块中。此外声明必须在执行该块任何其它语句之前进行。

(2) 设置变量

SET语句用来设置声明的存储例程变量值。其原型如下:

SET  variable_name =value;

如下声明和设置变量过程:

  DECLARE  x  INT;

SET  x = 155;

也可使用SELECT…..INTO语句设置变量。

  如:

SELECT  155  INTO  x;

当然,此变量是声明该变量的BEGIN/END块作用范围内的一个局部变量。如果希望在存储例程外使用此变量,需要将其作为OUT变量传递。

10、执行存储例程

    执行存储例程一般是通过CALL和SELECT语句来完成的。

    三、多语句存储例程

单语句存储例程非常有用,但存储例程的真正功能在于它能够封装和执行多个SQL语句。

下面介绍创建多语句存储例程时常用的语法。

  1、BEGIN和END

当创建多语句存储例程时,需要将语句包围在BEGIN/END块中。

此块的原型如下:

BEGIN

     statement 1;

     statement 2;

     ……

     statement N;

END

注意,块中每条语句必须以分号结尾。

 2、条件语句

IF-ELSEIF-ELSE语句

 和C语言中if语句相似。

其原型如下:

IF condition1 THEN

statement1;

ELSEIF condition2 THEN

Statement2;

…….

END IF

CASE语句

需要比较一组可能的值时CASE语句很有用。也是一种条件判断语句。

其原型如下:

CASE

WHEN condition1 THEN statement1;

WHEN condition2 THEN statement2;

………

END CASE;

3、迭代

有些任务需要能够重复地执行一组语句。下面介绍能够迭代执行和退出循环的各种方法。

ITERATE语句

执行ITERATE语句将使嵌入该语句的LOOP、REPEAT或WHILE循环返回顶部,并在此执行。

其原型如下:

ITERATE  label

LEAVE语句

在得到变量的值或特定任务的结果后,可能希望通过LEAVE命令立即退出循环或BEGIN/END块。

其原型如下:

LEAVE  label

LOOP语句

LOOP语句将不断的迭代处理定义在其代码块中的一组语句,直到遇到LEAVE为止。

其原型如下:

[begin_label:]  LOOP

   Statement_list

END LOOP  [end_label]

      REPEAT语句

       REPEAT语句在操作上几乎与WHILE相同,很想C语言中的DO….WHERE语句。

      其原型如下:

 REPEAT

       Statement_list

 UNTIL condition

 END REPEAT

WHILE语句

 其原型如下:

  WHILE condition DO

    Statement_list

        END WHILE

下面写一个循环语句的存储过程:

DELIMITER $$

DROP PROCEDURE IF EXISTS `yyw`.`p2` $$

CREATE PROCEDURE `yyw`.`p2` ()

BEGIN

        declare v int;

 set v=0;

 LOOP_LABLE:loop

          if v=3 then

      set v=v+1;

      ITERATE LOOP_LABLE;

   end if;