戻る

ストアドプロシージャーを使いこなす

ストアドプロシージャーを作成する

CREATE PROCEDURE ストアドプロシージャ名()
BEGIN
	SQL文1
	
	SQL文2
END

デリミタを変更する

DELIMITER //

★

DELIMITER //
CREATE PROCEDURE pr1()
BEGIN
SELECT * FROM tb;
SELECT * FROM tb1;
END
//

DELIMITER ;

ストアドプロシージャーの実行

CALL ストアドプロシージャ名

CALL pr1;

ストアドプロシージャでの引数の記述

PROCEDURE ストアドプロシージャ名(引数名 データ型);

★

DELIMITER //
CREATE PROCEDURE pr2(d INT)
BEGIN
SELECT * from tb WHERE uria>=d;
END
//

DELIMITER ;

CALL pr2(100);

CALL pr2(200);

ストアドプロシージャの内容を表示する

SHOW CREATE PROCEDURE プロシージャ名;

★

SHOW CREATE PROCEDURE pr1;

ストアドプロシージャを削除する

DROP PROCEDURE ストアドプロシージャ名;

★

DROP PROCEDURE pr1;

ストアドファンクションを作成する

CREATE FUNCTION ストアドファンクション名(引数 データ型) RETURNS 返す値のデータ型
BEGIN
	SQL文…
	RETURN 返す値:式
END

★

DELIMITER //
CREATE FUNCTION fu1(sintyo INT) RETURNS DOUBLE
BEGIN
RETURN sintyo * sintyo * 22 / 10000;
END
//

DELIMITER ;

SELECT fu1(174);

DECLAREによる変数の定義

DECLARE 変数名 データ型

DELIMITER //

CREATE FUNCTION fu2() RETURNS DOUBLE
BEGIN
DECLARE r DOUBLE;
SELECT AVG(uria) INTO r FROM tb;
RETURN r;
END
//

DELIMITER ;

SELECT fu2();

ストアドファンクションの削除

DROP FUNCTION ストアドファンクション名;

ストアドファンクションの内容表示

SHOW CREATE FUNCTION ストアドファンクション名;

★

SHOW CREATE FUNCTION fu2;

トリガの作成

CREATE TRIGGER トリガ名 BEFORE(あるいはAFTER) DELETEなどのコマンド
ON テーブル名 FOR EACH ROW
BEGIN
	更新前(OLD.カラム名)または更新後(NEW.カラム名)を使った処理
END

★

CREATE TABLE tb1M SELECT * FROM tb1;

DELIMITER //

CREATE TRIGGER tr1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
INSERT INTO tb1M VALUES(OLD.bang,OLD.nama,OLD.tosi);
END
//

DELIMITER ;

DELETE FROM tb1;

SELECT * FROM tb1M;

INSERT INTO tb1 SELECT * FROM tb1M;

トリガの確認

SHOW TRIGGERS;

トリガの削除

DROP TRIGGER トリガ名;

★

DROP TRIGGER tr1;

SHOW TRIGGERS;

---

問

次のテーブル「tb」を利用し、引数「t」(INT型)として月(tuki)の4～6のどれかの値を指定すると、その月の売上(uria)の合計(INT型)を返すストアドファンクション「f_uria」を作成してください。

★

DELIMITER //

CREATE FUNCTION f_uria(t INT) RETURNS INT
BEGIN
DECLARE u INT;
SELECT SUM(uria) INTO u FROM tb WHERE tuki=t;
RETURN u;
END
//

DELIMITER ;

SELECT f_uria(4);

Copyright © 2012 YOHSUKE NAKANO