Store Procedure Error Handler 例外處理

  1. 1. 宣告 handler
  2. 2. RESIGNAL
  3. 3. 注意事項
  4. 4. 範例
1
2
3
4
5
6
7
8
9
declare exit handler for sqlexception
begin
GET DIAGNOSTICS CONDITION 1
@error_code = RETURNED_SQLSTATE,
@error_msg = MESSAGE_TEXT;
rollback;
insert into log_sql (log) values(@error_msg);
RESIGNAL;
end;

宣告 handler

使用 declare 宣告 handler

for 後面接的是情況,可以接 sqlexception、sqlwarning、not found

RESIGNAL

因為我們已經 catch 到 exception,等於外部執行這個 store procedure 明明遇到錯誤,可是錯誤訊息已經被 catch 住,如果需要回傳 mysql server 原始的錯誤訊息,可以使用 RESIGNAL

注意事項

  1. 要符合 declare 原則,在 set 還有操作 db 的 sql 語法之前
  2. 要在 declare 一般變數之後

範例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
drop procedure if exists removeProposalLabels;
delimiter #
create procedure removeProposalLabels (
params JSON
)
begin
declare trello_id varchar(10) character set utf8;
declare labels varchar(500) character set utf8;
declare loop_index int default 0;
declare label_id int;
-- log 錯誤訊息
declare exit handler for sqlexception
begin
GET DIAGNOSTICS CONDITION 1
@error_code = RETURNED_SQLSTATE,
@error_msg = MESSAGE_TEXT;
rollback;
insert into log_sql (log) values(@error_msg);
RESIGNAL;
end;

set trello_id = JSON_UNQUOTE(JSON_EXTRACT(params, '$.trello_id'));
set labels = JSON_EXTRACT(params, '$.labels');

start transaction;
while loop_index < JSON_LENGTH(labels) DO
set label_id = JSON_EXTRACT(labels,CONCAT('$[',loop_index,']'));
delete from proposal_label_mapping where proposal_label_mapping.trello_id = trello_id and proposal_label_mapping.label_id = label_id;

select loop_index + 1 into loop_index;
end while;
commit;
end#

delimiter ;