もう一度向き合うmysql(4)〜プロシージャー、ファンクションなどとか〜

引き続き前回の続き。

information_schema

テーブル/インデックスとかユーザの権限などの定義情報を保存しているテーブル。
自動的にできる。mysql5.0から導入された模様。詳細は割愛！

ビュー

SELECTの結果を仮想的なテーブルのように扱う。

CREATE VIEW hoge_view AS SELECT foo, bar FROM hoge_table;

SELECT * FROM hoge.hoge_view

おおもとのhoge_tableから、foo,barカラムだけの仮想的なテーブルを作成。

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

複数のSQLをまとめて関数のように保存。
ファンクションは戻り値あり、プロシージャは戻り値ありなし指定できる。

• ストアドプロシージャ

DELIMITER //
CREATE PROCEDURE hoge_pro (IN x INTEGER, OUT y INTEGER)
BEGIN
    SELECT count(*) INTO y FROM hoge_table WHERE id > x;
END
//
DELIMITER ;


CALL hoge_pro(100, @y);
SELECT @y

• ストアドファンクション

DELIMITER //
CREATE FUNCTION hoge_func (x INTEGER) RETURNS INTEGER
DETERMINISTIC
BEGIN
    DECLARE ret INTEGER;
    SET ret = x * 100;
    RETURN ret;
END
//
DELIMITER ;


UPDATE hoge_table SET id=hoge_func(id);

トリガ

特定のテーブルにINSERT,UPDAT,DELETEの処理の前後に行う処理を定義できる

DELIMITER //
CREATE TRIGGER hoge_tri
BEFORE UPDATE ON hoge_table
FOR EACH ROW
BEGIN
    INSERT INTO hoge_table_log(update_time, after_num, before_num) VALUES (CURRENT_TIMESTAMP(), NEW.num, OLD.num);
END;
//
DELIMITER ;

こんな感じで定義するとUPDATEがあるとログテーブルにレコードを自動的にインサートするように

感謝致します。