ストアドプロシージャ内でCALLしたストアドプロシージャの例外をキャッチする方法

Question

いつもお世話になっております。

タイトルの通りなのですが、Aというストアドプロシージャ内でBというストアドプロシージャをCALLした時、Bでエラーが発生した場合にA側で例外をキャッチすることは技術的に可能なのでしょうか？
調べてみたのですが、参考になりそうな文献が見つからず、技術的に可能なことをしようとしているのか不安になり質問した次第です。

　DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
  BEGIN
    GET DIAGNOSTICS CONDITION 1
		@sqlstate = RETURNED_SQLSTATE,
        @errno = MYSQL_ERRNO,
        @text = MESSAGE_TEXT;
    SIGNAL SQLSTATE '45000'
		SET MESSAGE_TEXT = @text,
        MYSQL_ERRNO = @errno;
    ROLLBACK;
  END;

　〜何かの処理〜

  # ↓この例外をキャッチしてエラーとしたい。
　CALL B;

MySQLのバージョンは5.7です。

Answer 1

BのエラーとAでの掴み方次第でしょう
ロジカルなエラー（たとえばカーソルが一巡したみたいな）以外の
普通のエラーがBで発生したら、不正終了するのが限界だと思います。

以前の質問でこういうのがありました

• MYSQLで例外処理について

sample

Bの処理のデータ登録で処理が失敗

失敗の種類によりますね

• MySQL :: MySQL 5.6 リファレンスマニュアル :: B.3 サーバーのエラーコードおよびメッセージ

で、まず拾いたいエラーを確認します。

たとえば、tbl_bのプラマリー設定をして重複エラーになる場合

エラー: 1062 SQLSTATE: 23000 (ER_DUP_ENTRY)
メッセージ: '%s' はキー %d で重複しています 

ということで"23000"を拾えば分岐に使えます。

• tbl_a,tbl_b作成

create table tbl_a(id int primary key auto_increment,val int);
insert into tbl_a values(1,100),(2,110),(3,120);
create table tbl_b(id int primary key,val int);
insert into tbl_b values(1,200),(2,210),(3,220);

• まずproc_b作成

drop procedure if exists proc_b;
delimiter //
create procedure proc_b()
begin 
insert into tbl_b values(4,430);
end 
//
delimiter ;

※tbl_bに対してprimary keyを指定して投入します。
今回はid=4なので、1回めは成功し、2回目以降は失敗します

• proc_aを作成

上記踏まえproc_aを作成します

drop procedure if exists proc_a;
delimiter //
create procedure proc_a()
begin 
declare done int default 0;
declare continue handler for sqlstate '23000' set done = 1;
call proc_b;
if done=0 then
insert into tbl_a(val) values(130);
end if;
end 
//
delimiter ;

※命題ではトランザクションをロールバックするような記載でしたが
そもそもproc_bがエラーステータス23000を返してこないときだけ投入すればいいでしょう

実行

call proc_a;

1回目は成功するので、tbl_a,tbl_bそれぞれにレコードが1つずつ増えます。
再度proc_aをコールします。

call proc_a;

2回目はproc_bが失敗するのでproc_aもinsert 処理をしません。

Comments

[shori0128]

ご回答ありがとうございます。
Bの処理のデータ登録で処理が失敗したら、Aのデータ登録処理をロールバックしたいと考えています。
それをAの冒頭にある例外処理でキャッチしたいのですが、それは難しいということですか？
ごく最近になってストアドプロシージャを使うようになったので、いまいち処理の内容が理解しきれていないので、見当違いな質問してるのかもしれないのですが、どうでしょうか？
エラー自体は単純な不正エラーでキャッチ出来れば良いのですが。

[yambejp]

今回のケースならエラーはキャッチできます。
サンプル追記しておいたので一度試してみてください

[yambejp]

回答でお伝えした「普通のエラー」とはproc_b自体が構文エラーを
返して実行を失敗するとか、その手の話です

[shori0128]

ありがとうございました！無事実現したい動作ができました！