转载

mysql存储过程案例解析

mysql的存储过程案例解析

DROP PROCEDURE IF EXISTS `P_CreateID`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `P_CreateID`(in tablenames VARCHAR(50) ,out BH VARCHAR(40))

BEGIN

declare _time int;

DECLARE _exp int default 0;

DECLARE last VARCHAR(20);

DECLARE len TINYINT DEFAULT 0;

DECLARE curNo int DEFAULT 0;

DECLARE shortname char(20);

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET _exp = 1;

set _time = UNIX_TIMESTAMP(NOW());

SELECT currentNo, noLen, shortle into curNo, len, shortname from db_code where realName = tablenames;

-- SET BH=shortname;

if curNo > 10000 then

set curNo=0;

UPDATE db_code set currentNo=0 where realName = tablenames;

end if;

if len <> 0 then

set last = RIGHT(POWER(10,len)+curNo+2,len);

set BH = concat(_time,last);

update db_code set currentNo=currentNo+2 where realName = tablenames;

end if;

if _exp = 1 then

set BH='error';

end if;

end

DELIMITER ;

涉及到的表的语句：

DROP TABLE IF EXISTS `db_code`;

CREATE TABLE `db_code` (

`shortle` varchar(10) NOT NULL,

`currentNo` int(12) unsigned NOT NULL DEFAULT '0',

`noLen` tinyint(3) unsigned NOT NULL DEFAULT '6',

`realName` varchar(50) NOT NULL,

PRIMARY KEY (`realName`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

其中涉及到的 mysql函数，RIGHT(str,len)返回字符串str的最右面len个字符。

mysql> select RIGHT('foobarbar', 4);

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

| RIGHT('foobarbar', 4) |

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

| rbar |

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

1 row in set (0.00 sec)

针对power(m，n)的函数：power(m，n)=m的n次方；

mysql> select power(3,2);

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

| power(3,2) |

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

| 9 |

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

1 row in set (0.08 sec)

一：首先说下mysql存储过程中定义参数的时候，in out inout的区别

通过下面例子总结：

1) 如果仅仅想把数据传给MySQL存储过程，那就用in类型参数;(不打算返回给用户）

2) 如果仅仅从MySQL存储过程返回值，那就用out类型参数；（初始值总是null)

3) 如果需要把数据传给MySQL存储过程经过计算再传回给我们，那就用inout类型参数。

1）先说 in类型

IN参数只用来向过程传递信息，为默认值。 MySQL存储过程"in"参数：跟C语言的函数参数的值传递类似，MySQL存储过程内部可能会修改此参数，但in类型参数的修改对调用者(caller)来说是不可见的(not visible)

in 例子：

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.00 sec)

正确调用该存储过程方式

1）直接写入参数值

mysql> call pr_param_in(10);

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

| id_inner |

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

| 11 |

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

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

2）使用变量data调用,先给变量data赋值，然后把变量传给存储过程。

mysql> set @data=10;

Query OK, 0 rows affected (0.00 sec)

mysql> call pr_param_in(@data);

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

| id_inner |

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

| 11 |

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

1 row in set (0.00 sec)

mysql> select @data;

+-------+

| @data |

+-------+

| 10 |

+-------+

1 row in set (0.00 sec)

通过结论可以知道：用户变量@data传入值为10，执行存储过程后，在过程内部值为：11（id_inner）, 但外部变量值依旧为：10（id_out）

注意：如果不写需要传入的参数值，会报错，如下所示

mysql> call pr_param_in();

ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE liuwenhe.pr_param_in; expected 1, got 0

报错意思是：存储过程的参数的个数不对，期望是1个，得到了0个

2）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;

-> //

delimiter //

create procedure pr_param_out(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> 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)

注意调用时，不能显现的写上out参数的具体值，会报错，如下所示：

mysql> call pr_param_out(10);

ERROR 1414 (42000): OUT or INOUT argument 1 for routine liuwenhe.pr_param_out is not a variable or NEW pseudo-variable in BEFORE trigger

实验证明：可以看出，虽然我们设置了用户定义变量@id为10，传递@id给存储过程后，在存储过程内部，id的初始值总是 null（id_inner_1）。最后id值（id_out=1）传回给调用者，区别于in类型，out类型会影响存储过程外面的变量。

3）inout类型

INOUT参数可以向过程传递信息，如果值改变，则可再从过程外调用。 MySQL存储过程"inout"参数跟out类似，都可以从存储过程内部传值给调用者。不同的是：调用者还可以通过inout参数传递至给存储过程，但是out类型参数在存储过程中起始值总是null;

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语言函数中的引用传值类似。

综上所述：mysql存储过程中，in类型就是传入给存储过程的值，并且只在过程中变化，不影响外面的值（也就是不能返回给用户值）；out类型不能传入给存储过程，初始值总是null，但是可以在过程中发生变化，并且把变化后的值返回给用户；inout类型既可以传入给存储过程又可以把变化之后的值返回给用户；但是这三种类型都需要用户在调用的时候输入值或者用变量代替。

二：关于mysql中存储过程中的异常捕获：

有几种错误处理的声明形式：

§ 如果任何错误（不是 NOT FOUND ） , 设置 l_error 为 1 后继续执行：

DECLARECONTINUEHANDLER FORSQLEXCEPTION SET l_error=1;

§ 如果发生任何错误（不是 NOT FOUND），执行 ROLLBACK和产生一条错误消息后退出当前块或存储过程。

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

ROLLBACK;

SELECT 'Error occurred – terminating';

END;

§ 如果 MySQL 1062错误 (重复的健值 )发生，执行 SELECT语句（向调用程序发一条消息）后继续执行

DECLARE CONTINUE HANDER FOR 1062