mysql儲存過程案例解析

賀子_DBA時代發表於2018-01-27
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
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章