MySQLの変数

システム変数

select @@変数名

ユーザー定義変数

select @変数名

ストアドプロシージャ

1. ストアドプロシージャの概念

ストアドプロシージャとは、変数、ロジック制御、データ操作のためのSQLステートメントを含む、完全なビジネス操作をカプセル化したものです（Javaのメソッドに似ています）。

2. ストアドプロシージャの構文

-- 10.グループ加入のメッセージを送信
-- 1) メッセージ情報テーブルMessageDataにデータを挿入
-- 2) グループメッセージ情報テーブルGroupMessageLogにデータを挿入
drop procedure if exists process_group_join_request;
delimiter $$

create procedure process_group_join_request(requester_id int, group_id int)
begin
    -- メッセージ内容を保存する変数
    declare msg_content text;
    -- メッセージカテゴリIDを保存する変数
    declare msg_category_id int;
    -- メッセージIDを保存する変数
    declare msg_id int;
    -- エラーメッセージを示す変数
    declare has_error int default 0;
    -- グループ管理者のIDを保存する変数
    declare manager_id int;
    declare loop_exit_flag int default 0;
    -- メッセージタイプと名前を保存するカーソル
    declare category_cursor cursor for select id, name from MessageCategory where name = 'グループ加入申請';
    -- SQL例外が発生した場合の処理
    declare continue handler for sqlexception set has_error = 1;
    -- カーソルがデータを見つけられない場合の処理
    declare continue handler for not found set loop_exit_flag = 1;
    
    -- カーソルを開く
    open category_cursor;
    -- 値を取得
    fetch category_cursor into msg_category_id, msg_content;
    
    -- メッセージ情報テーブルMessageDataにデータを挿入
    insert into MessageData (id, sender_id, content, category_id, send_time) values (default, requester_id, msg_content, msg_category_id, default);
    if row_count() < 0 then
        set has_error = 1;
    end if;
    
    -- グループメッセージ情報テーブルGroupMessageLogにデータを挿入
    -- MessageDataに追加されたデータのIDを取得
    select id into @temp_id from MessageData where sender_id = requester_id order by id desc limit 1;
    set msg_id = @temp_id;
    -- カーソルを閉じる
    close category_cursor;
    -- 追加するグループのグループ長のIDを取得
    select owner_id into @temp_group_id from GroupDetails where id = group_id;
    set manager_id = @temp_group_id;
    
    insert into GroupMessageLog (id, message_id, group_id, receiver_id, `status`) values (default, msg_id, group_id, manager_id, default );
    if row_count() < 0 then
        set has_error = 1;
    end if;
end
$$
delimiter ;
-- ストアドプロシージャを呼び出す
call process_group_join_request(38,  1);

2.1 一つのテーブルのデータを別のテーブルに挿入する方法

カーソルを使用してクエリ結果を記録し、カーソルをループ処理する

-- カーソルループの終了フラグを保存する変数を宣言
-- デフォルトは0
declare loop_exit_flag int default 0;
-- クエリ結果を保存するカーソル変数を宣言
declare result_cursor cursor for select column_1, column_2... from table_name where conditions;
-- カーソルが値を見つけられない場合の処理を宣言
-- loop_exit_flag = 1 はカーソルループの終了を意味する
declare continue handler for not found set loop_exit_flag = 1;
-- カーソルの使用法
-- まずカーソルを開く
open result_cursor;
-- カーソルをループ
while loop_exit_flag <> 1 do 
-- 値を取得
fetch result_cursor into var_1, var_2...;
end while;
-- カーソルを閉じる
close result_cursor;

2.2 単一のクエリ結果を保存する方法

select ... into var_1 from table_name where conditions;

トランザクション

1. トランザクションの概念
2. トランザクションの特性

3. トランザクションの使用

-- 銀行口座の送金をシミュレートするトランザクション
-- 銀行口座テーブルを作成：システムIDと口座番号
drop table if exists `bank_accounts`;
create table `bank_accounts`(
    id int primary key auto_increment not null comment 'システムID',
    account_number varchar(20) not null comment '口座番号',
    balance decimal(10,2) not null comment '残高'

)charset=utf8;
-- 2件のデータを挿入
insert into `bank_accounts` (id, account_number, balance) values 
    (default, 'A0001', 1000),
    (default, 'A0002', 0);
    
    
-- ストアドプロシージャのコード例
-- 送金をシミュレートするストアドプロシージャを作成
drop procedure if exists perform_bank_transfer;
-- ステートメントの終了記号を一時的に変更し、元に戻す必要がある
delimiter $$
-- ストアドプロシージャの定義構文、パラメータは括弧内に記述
create procedure perform_bank_transfer(sender_acc varchar(20), receiver_acc varchar(20), amount decimal(10,2))
-- 開始と終了はペアで出現
begin
    declare temp_balance decimal(10,2) default 0;
    -- エラーがあるかどうかを記録するための変数
    declare has_error int default 0;
    -- 例外エラーの処理を定義
    declare continue handler for sqlexception 
    begin
        set has_error = 1;
    end;
    -- トランザクションを開始
    start transaction;
    -- 1、口座1から1000円を引き出す（お金を引く）
    update `bank_accounts` set balance = balance - amount where account_number = sender_acc;
    
    if row_count() = 0 then  
        set has_error = 1;
    end if;
    
    set temp_balance = (select balance from (select balance from bank_accounts where account_number = sender_acc) as t1 where balance > 0);
    if temp_balance < 0 then 
        set has_error = 1;
    end if;
    
    -- 2、1000円を口座2に預ける
    update `bank_accounts` set balance = balance + amount where account_number = receiver_acc;
    if row_count() = 0 then  
        set has_error = 1;
    end if;
    -- コミットまたはロールバックを判断
    if has_error = 0 then
        commit;
    else
        rollback;
    end if;
    
end $$
delimiter ;

call perform_bank_transfer('A0001', 'A0002', 9000);