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;

mysql儲存過程案例解析

相關文章