LAMPのお勉強日記 (15)
LAMP環境のお勉強日記
承前
上記エントリに呼応して、自分を「人体実験」することにした。
目的
- 自分が何をやったのか記録する
- 自分が何を出来るのか公開する
はじめに
>mysql -u root -p
Enter password:[パスワード]
mysql>
使用するデータベースを指定する
mysql>use db1
今回学んだこと
- 「ストアドプロシージャ」について
- 「ストアドファンクション」について
- 「トリガ」について
「ストアドプロシージャ」
いくつかのSQL文をまとめて、1コマンドで実行できるようにするもの。
- 「create procedure [ストアドプロシージャ名]() begin [sql文] end」
- ただし、sql文を入力する際にデリミタ「;」を入力するとその段階で実行されてしまうので、この文の前後に
「delimiter //」〜「delimiter ;」を付ける。
- 「call [ストアドプロシージャ名]()」で実行
ex) mysql>delimiter // mysql>create procedure pr1() ->begin ->select * from tb; ->select * from tb1; ->end ->// mysql>delimiter ; mysql>call pr1(); +------+------+------+ | bang | uria | tuki | +------+------+------+ | a103 | 101 | 4 | | a102 | 54 | 5 | | a104 | 181 | 4 | | a101 | 184 | 4 | | a103 | 17 | 5 | | a101 | 300 | 5 | | a102 | 205 | 6 | | a104 | 93 | 5 | | a103 | 12 | 6 | | a107 | 87 | 6 | +------+------+------+ 10 rows in set (0.20 sec) +------+------------+------+ | bang | nama | tosi | +------+------------+------+ | a101 | shunin-sat | 40 | | a102 | takahashi | 28 | | a103 | nakagawa | 20 | | a104 | watanabe | 23 | | a105 | nishizawa | 35 | | NULL | baito-isid | 18 | +------+------------+------+ 6 rows in set (0.25 sec)
引数を指定して実行するストアドプロシージャ
「procedure [ストアドプロシージャ名(引数名 データ型);」
ex) mysql>delimiter // mysql>create procedure pr2(d int) ->begin ->select * from tb where uria>=d; ->end ->// mysql>delimiter ; mysql>call pr2(200); +------+------+------+ | bang | uria | tuki | +------+------+------+ | a101 | 300 | 5 | | a102 | 205 | 6 | +------+------+------+
ストアドプロシージャの内容を表示・削除
表示「show create procedure [プロシージャ名];」
ex) mysql>show create procedure pr1; +-----------+----------+-------------------------------------------------------- --------------------------------------------+----------------------+------------ ----------+--------------------+ | Procedure | sql_mode | Create Procedure | character_set_client | collation_c onnection | Database Collation | +-----------+----------+-------------------------------------------------------- --------------------------------------------+----------------------+------------ ----------+--------------------+ | pr1 | | CREATE DEFINER=`root`@`localhost` PROCEDURE `pr1`() begin select * from tb; select * from tb1; end | sjis | sjis_japanese_ci | latin1_swedish_ci | +-----------+----------+-------------------------------------------------------- --------------------------------------------+----------------------+------------ ----------+--------------------+
削除「drop procedure [プロシージャ名]」
mysql>drop procedure pr1; mysql>drop procedure pr2;
「ストアドファンクション」
「ストアドプロシージャ」とほぼ同じだが、戻り値がある点が異なる。
ちょうど関数のようなもの。ユーザ定義関数と呼ばれることもある。
- 「create function [ストアドファンクション名(引数 データ型)] returns [返す値のデータ型] begin [SQL文] return [返す値・式] end]
- 実行は、「select [ストアドファンクション名(引数)]
例1−標準体重を返すストアドファンクション
ex) mysql>delimiter // mysql>create function fu1(sintyo int) returns double ->begin ->return sintyo * sintyo * 22/10000; ->end ->// mysql>delimiter ; mysql> select fu1(172); +----------+ | fu1(172) | +----------+ | 65.0848 | +----------+
例2−レコードの平均値を返すストアドファンクション
- 変数の定義「declare 変数名 データ型」
ex) mysql>delimiter // mysql>create function fu2() returns double ->begin ->declare r double; ->select avg(uria) into r from tb; ->return r; ->end ->// mysql>delimiter ; mysql>select fu2(); +-------+ | fu2() | +-------+ | 123.4 | +-------+
ストアドファンクションの内容を表示・削除
表示「show create function [ストアドファンクション名];」
ex) mysql>show create function fu1; +----------+----------+--------------------------------------------------------- -------------------------------------------------------------------+------------ ----------+----------------------+--------------------+ | Function | sql_mode | Create Function | character_s et_client | collation_connection | Database Collation | +----------+----------+--------------------------------------------------------- -------------------------------------------------------------------+------------ ----------+----------------------+--------------------+ | fu1 | | CREATE DEFINER=`root`@`localhost` FUNCTION `fu1`(sintyo int) RETURNS double begin return sintyo * sintyo * 22 / 10000; end | sjis | sjis_japanese_ci | latin1_swedish_ci | +----------+----------+--------------------------------------------------------- -------------------------------------------------------------------+------------ ----------+----------------------+--------------------+
削除「drop function [ストアドファンクション名]」
mysql>drop function fu1; mysql>drop function fu2;
「トリガ」
テーブルに対してある処理が行われると、それが引き金(トリガ)となってコマンドが実行される仕組み。
たとえば、「insert」「update」「delete」などのコマンドに対応させておくと便利。
- 「create trigger [トリガ名] before・after [deleteなどのコマンド] on [テーブル名] for each row begin [更新前(old.カラム名)又は更新後(new.カラム名)を使った処理] end」
トリガが呼び出されるタイミングは、
コマンド | 実行前(old.カラム名)※「before」を使う | 実行後(new.カラム名)※「after」を使う |
---|---|---|
insert | ||
delete | ||
update |
例1−削除したデータを保存する
ex) mysql>create table tb1_back like tb1; mysql>delimiter // mysql>create trigger tr1 before delete on tb1 for each row ->begin ->insert into tb1_back values(old.bang,old.nama,old.tosi); ->end ->// mysql>delimiter ; mysql>select * from tb1; +------+------------+------+ | bang | nama | tosi | +------+------------+------+ | a101 | shunin-sat | 40 | | a102 | takahashi | 28 | | a103 | nakagawa | 20 | | a104 | watanabe | 23 | | a105 | nishizawa | 35 | | NULL | baito-isid | 18 | +------+------------+------+ mysql>select * from tb1_back; Empty set (0.00 sec) mysql>delete from tb1; mysql> select * from tb1; Empty set (0.00 sec) mysql> select * from tb1_back; +------+------------+------+ | bang | nama | tosi | +------+------------+------+ | a101 | shunin-sat | 40 | | a102 | takahashi | 28 | | a103 | nakagawa | 20 | | a104 | watanabe | 23 | | a105 | nishizawa | 35 | | NULL | baito-isid | 18 | +------+------------+------+
deleteされた「tb1」の内容が「tb_back」に挿入されている。
トリガの確認・削除
確認「show triggers;」
mysql>show triggers; +---------+--------+-------+---------------------------------------------------- ----------------+--------+---------+----------+----------------+---------------- ------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_c lient | collation_connection | Database Collation | +---------+--------+-------+---------------------------------------------------- ----------------+--------+---------+----------+----------------+---------------- ------+----------------------+--------------------+ | tr1 | DELETE | tb1 | begin insert into tb1_back values(old.bang,old.nama,old.tosi); end | BEFORE | NULL | | root@localhost | sjis | sjis _japanese_ci | latin1_swedish_ci | +---------+--------+-------+---------------------------------------------------- ----------------+--------+---------+----------+----------------+---------------- ------+----------------------+--------------------+
削除「drop trigger [トリガ名];」
今回はここまで。
次回は「トランザクション」の予定。