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
SELECT 'Duplicate key in index';
§ 如果 SQLSTATE 2300错误 (重复的健值 )发生,执行 SELECT语句(向调用程序发一条消息)后继续执行
DECLARE CONTINUE HANDER FOR SQLSTATE '23000'
SELECT 'Duplicate key in index';
§ 当游标或者 SQL 选择语句没有返回值时,设置 l_done=1 后继续执行
DECLARE CONTINUE HANDLER FOR NOT
FOUND
SET l_done=1;
§ 此例除了用 SQLSTATE 变量而不是命名条件以外,跟前一个例子一样
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000 '
SET l_done=1;
§ 此例除了用 MySQL 的错误码变量而不是命名条件或者 SQLSTATE 变量以外,跟前两个例子一样
DECLARE CONTINUE HANDLER FOR 1329
SET l_done=1;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29654823/viewspace-2150572/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29654823/viewspace-2150572/