mysqlのビューとストアドプロシージャーの作成

インフラ

2019年5月24日

ビューの操作

mysqlにおいてはviewとは仮想的な表を表します。viewに対しては通常のテーブルと同じようにSELECT句を使うことができます。下記にVIEWの作成と使い方について簡単に作成します。

ビューの作成

CREATE VIEW ビュー名 AS SELECT ~

ビューの変更

ALTER VIEW ビュー名 AS SELECT ~

ビューの削除

DROP VIEW ビュー名

作成したビューのスキーマを確認

SELECT * FROM information_schema.views WHERE table_name = "ビュー名";

もしくは下記を入力します。

SHOW CREATE VIEW ビュー名;

ストアドプロシージャーの操作

ストアドプロシージャーは、データの検索や抽出などの処理を事前に登録しそれを呼び出すことによりデータの検索や抽出を行います。下記にストアドプロシージャーの操作についてまとめていきます。

ストアドプロシージャの作成(引数なし)

CREATE PROCEDURE プロシージャ名 ()
  BEGIN
    SQL文
    SQL文
  END

ストアドプロシージャの作成(引数あり)

CREATE PROCEDURE プロシージャ名 (引数名 データ型)
  BEGIN
    SELECT * FROM test WHERE id = 引数
  END

ストアドプロシージャの内容確認

SHOW CREATE PROCEDURE プロシージャ名

ストアドプロシージャの実行
引数がある場合はプロシージャ名に引数を渡します。

CALL プロシージャ名()

ストアドプロシージャの削除

DROP PROCEDURE プロシージャ名

ストアドファンクション

ストアドファンクションでは独自の関数を作成することができる機能です。
関数なので条件分岐などもすることができます。

ストアドファンクションの作成

CREATE FUNCTION ファンクション名 (引数名 データ型) RETURNS データ型
  BEGIN
    SQL文
    RETURN 戻り値 または 式
  END

変数の定義

DECLARE 変数名 データ型;

データを変数に代入

データ INTO 変数名

ストアドファンクションの内容確認

SHOW CREATE FUNCTION ファンクション名;

ストアドプロシージャの削除

DROP PROCEDURE プロシージャ名

合計を求めるストアドファンクションのサンプル

DELIMITER //
CREATE FUNCTION fu_sales_num() RETURNS INT
BEGIN
  DECLARE s INT;
  SELECT SUM(quantity) INTO s FROM sales;
  RETURN s;
END
//

DELIMITER ;

SELECT fu_sales_num();

ストアドファンクションの複雑な処理例

DELIMITER //
CREATE PROCEDURE pr_update_goods(c CHAR(4), n SMALLINT)
 BEGIN
  DECLARE tc CHAR(4);
  SELECT goods_code INTO tc FROM goods WHERE goods_code = c AND stocks >= n;
  IF tc IS NULL THEN
    SELECT "在庫がありません"
    INSERT INTO order_goods(order_date, goods_code, quantity)
    values(NOW(), c, 100);
  ELSE
    SELECT "在庫から引きます";
    UPDATE goods SET stocks = stocks -n WHERE goods_code = c;
  END IF
 END
//
DELIMITER ;

CALL pr_update_goods("AAAA", 100);

トリガ

トリガとは、テーブルのデータに対して更新処理(INSERT UPDATE DELETE)があった場合に自動的にデータ処理をする機能です。

トリガの作成

DELIMITER //
CREATE TRIGGER tr_update_stocking AFTER INSERT ON stocking FOR EACH ROW
BEGIN
  UPDATE goods SET stocks = stocks + NEW.quantity
  WHERE goods_code = NEW.goods_code;
END
//

トリガの確認

SHOW TRIGGERS

トリガの削除

DROP TRIGGER トリガ名;

トランザクション
トランザクションを設定し任意のタイミングでコミットや特定の段階まで処理を戻すことができます。

トリガの開始と終了

START TRANSACTION;
COMMIT;
ROLLBACK;
SAVEPOINT a;
ROLLBACK TO a;

オートコミットのオン・オフ

SET AUTOCOMMIT = 0; # オフ
SET AUTOCOMMIT = 0; # オン

SELECT @@AUTOCOMMIT; # オートコミットの確認

---