CASE文でELSEになったものだけを精査用テーブルに入れるFUNCTION

用途

プロシージャとかじゃなくて、普通にCASEしながらinsert into selectでレコードを移したい時、例えばFK列やENUM列でマスタに未定義な値を精査する際にちょっとだけ面倒を無くしてくれるかも。

必要なものの準備

・精査用テーブルの作成

create_fnc_case_else_util.sql

-- テーブルの作成
drop table if exists wrk_case_else_value;
create table wrk_case_else_value(
   proc_name varchar(32) not null -- どこのCASEか
  ,else_val  varchar(128) ) engine=MyISAM default charset=utf8;

create unique index uidx_case_else_value on wrk_case_else_value(proc_name,else_val);

-- ファンクションの作成
drop function case_else_util;

delimiter //

create function case_else_util(proc_name varchar(32),else_val varchar(128),ret_val text) returns text

begin

insert ignore into wrk_case_else_value values(proc_name,ifnull(else_val,'ぬる'));
 return ret_val;

end//

delimiter ;

利用方法

サンプルSQL

create_cev_test.sql

-- 必要なテーブルを作る
-- 移行元テーブル1
drop table if exists trn_frm_item1;
create table trn_frm_item1 (
    id int not null auto_increment
   ,item_name varchar(12)
   ,primary key (id) );

-- 移行元テーブル2
drop table if exists trn_frm_item2;
create table trn_frm_item2 (
    id int not null auto_increment
   ,item_name varchar(12)
   ,primary key (id) );

-- 移行先テーブル
drop table if exists trn_to_item;
create table trn_to_item (
    id int not null auto_increment
   ,item_cd int
   ,primary key (id) );

-- マスタテーブル
drop table if exists mst_item;
create table mst_item (
    item_cd int not null
   ,item_name varchar(12) not null
   ,primary key (item_cd) );

-- 制約
alter table trn_to_item add constraint fk_to_item
 foreign key (item_cd) references mst_item(item_cd);

-- レコードを入れる
insert into mst_item values (1,'itemA'),(2,'itemB'),(3,'itemC'),(4,'itemD'),(5,'itemE');

insert into trn_frm_item1 values (null,'A'),(null,'B'),(null,'C'),(null,'D');

insert into trn_frm_item2 values (null,'E'),(null,null),(null,'F'),(null,'G');

insert_cev_test.sql

-- insert into select してみる。
insert into trn_to_item
select null as id
      ,ifnull((select item_cd from mst_item where item_name = x.item_name),null) as item_cd
from ( select case when item_name = 'A' then 'itemA'
                   when item_name = 'B' then 'itemB'
                   when item_name = 'C' then 'itemC'
                   when item_name = 'D' then 'itemD'
                   when item_name = 'E' then 'itemE'
                   else case_else_util('item_name',item_name,null) end as item_name
         from trn_frm_item1 ) x;

-- elseが1件も無いのでパターン外は吐かれない
mysql> select * from wrk_case_else_value;
Empty set (0.00 sec)

-- ここで出る。
insert into trn_to_item
select null as id
      ,ifnull((select item_cd from mst_item where item_name = x.item_name),null) as item_cd
from ( select case when item_name = 'A' then 'itemA'
                   when item_name = 'B' then 'itemB'
                   when item_name = 'C' then 'itemC'
                   when item_name = 'D' then 'itemD'
                   when item_name = 'E' then 'itemE'
                   else case_else_util('item_name',item_name,null) end as item_name
         from trn_frm_item2 ) x;

-- マスタに無いFとGとNULLのパターンが吐かれる。
mysql> select * from wrk_case_else_value;
+-----------+----------+
| proc_name | else_val |
+-----------+----------+
| item_name | F        |
| item_name | G        |
| item_name | ぬる     |
+-----------+----------+
3 rows in set (0.00 sec)

もっとスマートなやり方ありそうだけど。。。