-- A5M2で実行時に[SQL] - [スラッシュ(/)のみの行でSQLを区切る]に変えてから実行する
CREATE PROCEDURE src05.v_inst_merge_laundering(target_table VARCHAR(255))
SQL SECURITY INVOKER
BEGIN
    -- スキーマ名
    DECLARE schema_name VARCHAR(50) DEFAULT (SELECT DATABASE());
    -- プロシージャ名
    DECLARE procedure_name VARCHAR(100) DEFAULT 'v_inst_merge_laundering';
    -- プロシージャの引数
    DECLARE procedure_args JSON DEFAULT JSON_OBJECT('target_table', target_table);

    -- 例外処理
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
            GET DIAGNOSTICS CONDITION 1
                @error_state = RETURNED_SQLSTATE, @error_msg = MESSAGE_TEXT;
            CALL medaca_common.put_error_log(schema_name, procedure_name, procedure_args,
                                    'v_inst_merge_launderingでエラーが発生', @error_state, @error_msg);
            SET @error_msg = (
                CASE
                WHEN LENGTH(@error_msg) > 128 THEN CONCAT(SUBSTRING(@error_msg, 1, 125), '...')
                ELSE @error_msg
                END
            );
            SIGNAL SQLSTATE '45000'
                SET MYSQL_ERRNO = @error_state, MESSAGE_TEXT = @error_msg;
        END;

    SET @error_state = NULL, @error_msg = NULL;

    CALL medaca_common.put_info_log(schema_name, procedure_name, procedure_args,
                            '【洗替】３：HCO施設コードの洗替① 開始'
    );

    SET @update_institution = "
        UPDATE (
                SELECT
                    v_inst_cd,
                    v_inst_cd_merge,
                    fcl_name,
                    fcl_kn_name,
                    fmt_addr,
                    prft_cd
                FROM
                    internal05.v_inst_merge_t
            ) AS vimt,
            $$target_table$$ AS tt
        SET         
            tt.inst_cd = (
                CASE 
                    WHEN
                        tt.inst_clas_cd = '1'
                    THEN
                        tt.inst_cd
                    WHEN
                        (tt.inst_clas_cd = '2' OR tt.inst_clas_cd = '3')
                    THEN
                        vimt.v_inst_cd_merge
                END
            ),
            tt.v_inst_cd = vimt.v_inst_cd_merge,
            tt.inst_name = vimt.fcl_name,
            tt.inst_name_kana = vimt.fcl_kn_name,
            tt.address = vimt.fmt_addr,
            tt.pref_cd = vimt.prft_cd,
            tt.dwh_upd_dt = SYSDATE()
        WHERE
                tt.v_inst_cd = vimt.v_inst_cd
            AND (tt.inst_clas_cd IN ('1', '2', '3'))
    ";
    SET @update_institution = REPLACE(@update_institution, "$$target_table$$", target_table);  
    PREPARE update_institution_stmt from @update_institution;
    EXECUTE update_institution_stmt;

    CALL medaca_common.put_info_log(schema_name, procedure_name, procedure_args,
                            '【洗替】３：HCO施設コードの洗替① 終了'
    );

END