php08 - ninja22 @ ウィキー

前のページ　＜　｜　＞　次のページ

(9) MySQLでその他の便利な機能を使う

ビュー、ストアドプロシージャ、ストアドファンクション、トリガ

ビュー

ビューはMySQLのバージョン5.0以降に対応しています。
ビューとは、selectした結果をテーブルのように残しておくことができる機能である。
しかしビューには、レコードやカラムにあるデータそのものは存在しない。
「AテーブルのBカラムとCとDの条件で集める。」という命令情報で構成されている。

●ビューの使い方
指定したテーブルの指定したカラムだけ、指定した条件で集める。
データ抽出条件をビューで実行し「お好みの形式をしたテーブル」を保存
↓
selectやupdateが利用可能。
ビューの値を更新すると、更新後のテーブルにあるデータを反映する。

●ビューの作成
create view ビューの名前 as select カラム名 from テーブル名 where 条件

ストアドプロシージャ

ストアドプロシージャはMySQLのバージョン5.0以降に対応しています。
いくつものSQL文を1つにまとめ、それを「CALL ××」というコマンドだけで実行
できるようにしたものをストアドプロシージャ（Stored Procedure）という。
直訳：貯蔵した手順
ただし、重要なデータが蓄積されたデータベースでストアドプロシージャを使うとき
は注意が必要である。
{

●ストアドプロシージャの使い方
(1) ストアドプロシージャの作成
create procedure ストアドプロシージャ名()
begin
SQL文1
SQL文2
end
SQL文1例）select * from tb;
SQL文2例）select * from tb1;
ただし、この場合「；」で終了と判断されるので、下記に
それを回避する方法を記す。

(2) ストアドプロシージャの「；」を無効にするパターン
デリミタをいったん「//」に変えて入力する。
delimiter //
create procedure pr1()
begin
select * from tb;
select * from tb1;
end
//
実行後に、デリミタをもとに戻す。
delimiter ;

●ストアドプロシージャの実行
上で作成したストアドプロシージャを呼び出してみよう。
call pr1;

●条件付きストアドプロシージャの作成
ストアドプロシージャの引数を使って条件付けしてみます。
procedure ストアドプロシージャ名(引数名 データ型);
例）
delimiter //
create procedure pr2(d INT)
begin
select * from tb where uria>=d;
end
//
delimiter ;
引数付きでストアドプロシージャを呼び出す。
call pr2(200);
結果）

bang	uria	tuki
A101	300	5
A102	205	6

●ストアドプロシージャのその他の操作
ストアドプロシージャの内容表示
show create procedure プロージャ名;
ストアドプロシージャの削除
drop procedure ストアドプロシージャ名;

ストアドファンクション

ストアドプロシージャの引数を返す版。
●ストアドファンクションの使い方
(1) ストアドファンクションの作成
create function ストアドファンクション名(引数 データ型) returns 返す値のデータ型
begin
SQL文1
return 返す値・式
end

(2) ストアドファンクションで標準体重を計算する
「標準体重＝身長（cm）×身長（cm）×22／10000」を使って
ストアドファンクション「fl1」を作ってみる。
delimiter //
create function fl1(sintyo INT) returns double
begin
return sintyo * sintyo *22/10000;
end
//
delimiter ;

次に、引数を指定して呼び出してみる。
身長174cmで計算してみる。callでなくselectでfl1()の値を表示させる。
select fl1(174);
結果）

fl1(174)

66.6072

(2) ストアドファンクションでレコードの平均値を計算する
delimiter //
create function fl2() returns double
begin
declare r double;
select avg(uria) into r from tb;
return r;
end
//
delimiter ;

selectコマンドで平均値を表示させる。
select fl2()

●ストアドファンクションのその他の操作
ストアドファンクションの内容表示
show create function ストアドファンクション名;

ストアドファンクションの削除
drop function ストアドファンクション名;

トリガ

トリガ機能が使えるのはMySQLのバージョン5.0以降です。
テーブルに対してある処理が行われると、それが引き金となって
コマンドが実行される仕組みである。
●トリガを作成する
create trigger トリガ名 before(またはafter) deleteなどのコマンド
on テーブル名 for each row
begin
更新前(OLD.カラム名)または更新後(NEW.カラム名)を使った処理
end

●テーブルtb1で削除したレコードを、tb1Mに挿入するトリガを作成
delimiter //
create trigger tr1 before delete on tb1 for each row
begin
insert into tb1M values(OLD.bang, OLD.nama, OLD.tosi);
end
//
delimiter ;

一気にテーブルtb1を削除してみる。
delete from tb1;

本当に削除されたか確認する。
select * from tb1;

コピー先のテーブルtb1Mを見てみる。
select * from tb1M;

さらに、tb1Mに挿入されたデータをtb1に復活させる。
insert into tb1 select * from tb1M;

●トリガのその他の操作
トリガの内容表示
show triggers;

トリガの削除
drop trigger トリガ名;

※ XAMPP 1.7.7 [PHP: 5.3.8] での設定項目です。

---

前のページ　＜　｜　＞　次のページ

◆　◆　◆

---