今日は、ストアドプロシージャ実践編の日。
とある日
一本前に上げた。
ストアドプロシージャ知識編の記事でストアドプロシージャの知識を得た。
ので、今回はストアドプロシージャを作成しようと思います。
ストアドプロシージャのための準備
今回作成するストアドプロシージャで行う処理は、一番売れてる商品を指定した数だけ求めるです。
テーブル構成
ストアドプロシージャで使用するテーブルが2つ。
Menus
メニューを管理するマスターテーブル。
Field |Type |Null|Key|Default |Extra | -----------------|------------|----|---|-----------------|-----------------| menu_id |bigint(20) |NO |PRI| |auto_increment | menu_title |varchar(100)|NO |UNI| | | price |bigint(20) |NO | | | | tax_included |bigint(20) |NO | | | | explanation |text |NO | | | | image_path |varchar(100)|NO | | | | currnet_timestamp|timestamp |NO | |CURRENT_TIMESTAMP|DEFAULT_GENERATED| delete_flag |tinyint(4) |NO | |0 | | CREATE TABLE menus( menu_id INT8 PRIMARY KEY AUTO_INCREMENT, menu_title VARCHARACTER(100) UNIQUE NOT NULL, price INT8 NOT NULL, tax_included INT8 NOT NULL, explanation TEXT NOT NULL, image_path VARCHARACTER(100) NOT NULL, currnet_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, delete_flag INT1 DEFAULT 0 NOT NULL );
Members
会員管理をするマスターテーブル。
Field |Type |Null|Key|Default |Extra | -----------|------------|----|---|-----------------|-----------------| member_id |bigint(20) |NO |PRI| |auto_increment | mailaddress|varchar(255)|NO |UNI| | | pass_word |varchar(10) |NO | | | | delete_flag|tinyint(4) |NO | |0 | | time_stamp |timestamp |NO | |CURRENT_TIMESTAMP|DEFAULT_GENERATED| CREATE TABLE members( member_id INT8 PRIMARY KEY AUTO_INCREMENT, mailaddress VARCHARACTER(255) UNIQUE NOT NULL , pass_word VARCHARACTER(10) NOT NULL, delete_flag INT1 DEFAULT 0 NOT NULL, time_stamp timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL );
Orders
注文履歴を管理するトランザクションテーブル。
Field |Type |Null|Key|Default |Extra | -----------------|----------|----|---|-----------------|-----------------| order_id |bigint(20)|NO |PRI| |auto_increment | member_id |bigint(20)|NO |MUL| | | menu_id |bigint(20)|NO |MUL| | | currnet_timestamp|timestamp |NO | |CURRENT_TIMESTAMP|DEFAULT_GENERATED| CREATE TABLE orders( order_id INT8 PRIMARY KEY AUTO_INCREMENT, member_id INT8 NOT NULL, menu_id INT8 NOT NULL, currnet_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, CONSTRAINT fk_member_id FOREIGN KEY (member_id) REFERENCES members (member_id) ON DELETE CASCADE, CONSTRAINT fk_menus_id FOREIGN KEY (menu_id) REFERENCES menus (menu_id) ON DELETE CASCADE );
テーブルデータ
今回行うのは、一番売れてる商品を求めるので、商品がなければ話にならないのでそれらの情報です。
Menus
menu_id|menu_title |price|tax_included|explanation |image_path |currnet_timestamp |delete_flag|
-------|--------------------|-----|------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|------------------------|-------------------|-----------|
1|のり弁当 | 306| 330|人気ナンバーワン商品の『のり弁当シリーズ』の品質と価値を高め、白身魚のフライを増量するとともに、ソースの味を選べるようにして、商品の品質と価値を高めました。 |のり弁当.jpg |2020-06-09 04:50:55| 0|
2|特のりタル弁当 | 389| 420|人気ナンバーワン商品の『のり弁当シリーズ』の品質と価値を高め、白身魚のフライを増量するとともに、ソースの味を選べるようにして、商品の品質と価値を高めました。 |特のりタル弁当.jpg |2020-06-09 04:50:55| 0|
3|チーズおかかのり弁当 | 362| 390|人気ナンバーワン商品の『のり弁当シリーズ』の品質と価値を高め、白身魚のフライを増量するとともに、ソースの味を選べるようにして、商品の品質と価値を高めました。『チーズおかかのり弁当』は、温かいごはんに醤油が染みたおかかと海苔の旨味に、濃厚なチーズのコクと風味が渾然とした絶妙な味わいをご堪能ください。 |チーズおかかのり弁当.jpg |2020-06-09 04:50:55| 0|
4|チーズおかか特のりタル弁当 | 445| 480|人気ナンバーワン商品の『のり弁当シリーズ』の品質と価値を高め、白身魚のフライを増量するとともに、ソースの味を選べるようにして、商品の品質と価値を高めました。『チーズおかかのり弁当』は、温かいごはんに醤油が染みたおかかと海苔の旨味に、濃厚なチーズのコクと風味が渾然とした絶妙な味わいをご堪能ください。 |チーズおかか特のりタル弁当.jpg |2020-06-09 04:50:55| 0|
5|新BIGのり弁当 | 510| 550|人気ナンバーワン商品の『のり弁当シリーズ』の品質と価値を高め、白身魚のフライを増量するとともに、ソースの味を選べるようにして、商品の品質と価値を高めました。新しい『ＢＩＧのり弁当』は、のり弁当と一緒に、スパイシーなソースが食欲をそそる“焼きそば”ならぬ“焼きスパ”もお楽しみいただけます。ウィンナーと目玉焼きがのって、ボリューム満点です。 |新BIGのり弁当.jpg |2020-06-09 04:50:55| 0|
6|ソースマヨかつ＆カルビ焼肉コンビ弁当 | 445| 480|人気のあるおかずを2種類のせたボリューム満点の商品です。ごはんが進む味をお楽しみください。 |ソースマヨかつ＆カルビ焼肉コンビ弁当.jpg |2020-06-09 04:50:55| 0|
7|とり天タルタル＆しょうが焼きコンビ弁当 | 445| 480|人気のあるおかずを2種類のせたボリューム満点の商品です。ごはんが進む味をお楽しみください。 |とり天タルタル＆しょうが焼きコンビ弁当.jpg |2020-06-09 04:50:55| 0|
8|野菜が摂れる塩ちゃんぽん | 425| 460|『塩ちゃんぽん』には、ちゃんぽん麺、豚バラ肉と、キャベツ、玉葱、もやし、人参、コーンの５種の野菜、きくらげ、かまぼこが入っており、１日に必要とされる野菜量の1/3を摂ることができる、体に嬉しいメニューです。 |野菜が摂れる塩ちゃんぽん.jpg |2020-06-09 04:50:55| 0|
9|から揚げ弁当 | 362| 390|本醸造醤油とにんにくを強めたしっかりとした味付けは、ごはんとの相性が抜群です。 |から揚げ弁当.jpg |2020-06-09 04:50:55| 0|
10|特から揚げ弁当 | 454| 490|本醸造醤油とにんにくを強めたしっかりとした味付けは、ごはんとの相性が抜群です。 |特から揚げ弁当.jpg |2020-06-09 04:50:55| 0|
INSERT INTO menus(menu_title,price,tax_included,explanation,image_path) VALUES ('のり弁当','306','330','人気ナンバーワン商品の『のり弁当シリーズ』の品質と価値を高め、白身魚のフライを増量するとともに、ソースの味を選べるようにして、商品の品質と価値を高めました。','のり弁当.jpg');
INSERT INTO menus(menu_title,price,tax_included,explanation,image_path) VALUES ('特のりタル弁当','389','420','人気ナンバーワン商品の『のり弁当シリーズ』の品質と価値を高め、白身魚のフライを増量するとともに、ソースの味を選べるようにして、商品の品質と価値を高めました。','特のりタル弁当.jpg');
INSERT INTO menus(menu_title,price,tax_included,explanation,image_path) VALUES ('チーズおかかのり弁当','362','390','人気ナンバーワン商品の『のり弁当シリーズ』の品質と価値を高め、白身魚のフライを増量するとともに、ソースの味を選べるようにして、商品の品質と価値を高めました。『チーズおかかのり弁当』は、温かいごはんに醤油が染みたおかかと海苔の旨味に、濃厚なチーズのコクと風味が渾然とした絶妙な味わいをご堪能ください。','チーズおかかのり弁当.jpg');
INSERT INTO menus(menu_title,price,tax_included,explanation,image_path) VALUES ('チーズおかか特のりタル弁当','445','480','人気ナンバーワン商品の『のり弁当シリーズ』の品質と価値を高め、白身魚のフライを増量するとともに、ソースの味を選べるようにして、商品の品質と価値を高めました。『チーズおかかのり弁当』は、温かいごはんに醤油が染みたおかかと海苔の旨味に、濃厚なチーズのコクと風味が渾然とした絶妙な味わいをご堪能ください。','チーズおかか特のりタル弁当.jpg');
INSERT INTO menus(menu_title,price,tax_included,explanation,image_path) VALUES ('新BIGのり弁当','510','550','人気ナンバーワン商品の『のり弁当シリーズ』の品質と価値を高め、白身魚のフライを増量するとともに、ソースの味を選べるようにして、商品の品質と価値を高めました。新しい『ＢＩＧのり弁当』は、のり弁当と一緒に、スパイシーなソースが食欲をそそる“焼きそば”ならぬ“焼きスパ”もお楽しみいただけます。ウィンナーと目玉焼きがのって、ボリューム満点です。','新BIGのり弁当.jpg');
INSERT INTO menus(menu_title,price,tax_included,explanation,image_path) VALUES ('ソースマヨかつ＆カルビ焼肉コンビ弁当','445','480','人気のあるおかずを2種類のせたボリューム満点の商品です。ごはんが進む味をお楽しみください。','ソースマヨかつ＆カルビ焼肉コンビ弁当.jpg');
INSERT INTO menus(menu_title,price,tax_included,explanation,image_path) VALUES ('とり天タルタル＆しょうが焼きコンビ弁当','445','480','人気のあるおかずを2種類のせたボリューム満点の商品です。ごはんが進む味をお楽しみください。','とり天タルタル＆しょうが焼きコンビ弁当.jpg');
INSERT INTO menus(menu_title,price,tax_included,explanation,image_path) VALUES ('野菜が摂れる塩ちゃんぽん','425','460','『塩ちゃんぽん』には、ちゃんぽん麺、豚バラ肉と、キャベツ、玉葱、もやし、人参、コーンの５種の野菜、きくらげ、かまぼこが入っており、１日に必要とされる野菜量の1/3を摂ることができる、体に嬉しいメニューです。','野菜が摂れる塩ちゃんぽん.jpg');
INSERT INTO menus(menu_title,price,tax_included,explanation,image_path) VALUES ('から揚げ弁当','362','390','本醸造醤油とにんにくを強めたしっかりとした味付けは、ごはんとの相性が抜群です。','から揚げ弁当.jpg');
INSERT INTO menus(menu_title,price,tax_included,explanation,image_path) VALUES ('特から揚げ弁当','454','490','本醸造醤油とにんにくを強めたしっかりとした味付けは、ごはんとの相性が抜群です。','特から揚げ弁当.jpg');
Members
member_id|mailaddress |pass_word|delete_flag|time_stamp |
---------|----------------------------------|---------|-----------|-------------------|
1|Nozomi_Fukushima@wgqgahp.wfs |JdyHLLN1 | 0|2020-06-09 05:24:42|
2|kazuyuki07587@jfgxnd.go |Q96UyEVE | 0|2020-06-09 05:24:42|
3|naoko9075@qazyrca.bfu |p6lrqTw- | 0|2020-06-09 05:24:42|
4|giichi878@wbbe.sa |mm1R3iPp | 0|2020-06-09 05:24:42|
5|rinohashiguchi@gdzjb.oix |4xTtZrZN | 0|2020-06-09 05:24:42|
6|karin936@cmdvknqjz.lg |yg2Kvk6c | 0|2020-06-09 05:24:42|
7|ihamazaki@lxlkcplnq.pif |UD6YA_hL | 0|2020-06-09 05:24:42|
8|takenobu46784@yibxqdqell.fstxd.xyt|2ydokRIx | 0|2020-06-09 05:24:42|
9|yuka670@aoimeq.ut |YDRNo13_ | 0|2020-06-09 05:24:42|
10|Yukiharu_Motomura@qadwssrpjq.et |-w3icLuA | 0|2020-06-09 05:24:42|
INSERT INTO members(mailaddress,pass_word) VALUES ('Nozomi_Fukushima@wgqgahp.wfs','JdyHLLN1');
INSERT INTO members(mailaddress,pass_word) VALUES ('kazuyuki07587@jfgxnd.go','Q96UyEVE');
INSERT INTO members(mailaddress,pass_word) VALUES ('naoko9075@qazyrca.bfu','p6lrqTw-')
INSERT INTO members(mailaddress,pass_word) VALUES ('giichi878@wbbe.sa','mm1R3iPp');
INSERT INTO members(mailaddress,pass_word) VALUES ('rinohashiguchi@gdzjb.oix','4xTtZrZN');
INSERT INTO members(mailaddress,pass_word) VALUES ('karin936@cmdvknqjz.lg','yg2Kvk6c');
INSERT INTO members(mailaddress,pass_word) VALUES ('ihamazaki@lxlkcplnq.pif','UD6YA_hL');
INSERT INTO members(mailaddress,pass_word) VALUES ('takenobu46784@yibxqdqell.fstxd.xyt','2ydokRIx')
INSERT INTO members(mailaddress,pass_word) VALUES ('yuka670@aoimeq.ut','YDRNo13_');
INSERT INTO members(mailaddress,pass_word) VALUES ('Yukiharu_Motomura@qadwssrpjq.et','-w3icLuA');
Orders
注文履歴は多いことに越したことはないですが、あまり多く書くと大変ので100件だけ表示します。
Field |Type |Null|Key|Default |Extra | -----------------|----------|----|---|-----------------|-----------------| order_id |bigint(20)|NO |PRI| |auto_increment | member_id |bigint(20)|NO |MUL| | | menu_id |bigint(20)|NO |MUL| | | currnet_timestamp|timestamp |NO | |CURRENT_TIMESTAMP|DEFAULT_GENERATED| INSERT INTO orders(member_id,menu_id) VALUES ('10','1'); INSERT INTO orders(member_id,menu_id) VALUES ('2','1'); INSERT INTO orders(member_id,menu_id) VALUES ('9','1'); INSERT INTO orders(member_id,menu_id) VALUES ('9','9'); INSERT INTO orders(member_id,menu_id) VALUES ('3','9'); INSERT INTO orders(member_id,menu_id) VALUES ('8','1'); INSERT INTO orders(member_id,menu_id) VALUES ('7','8'); INSERT INTO orders(member_id,menu_id) VALUES ('7','2'); INSERT INTO orders(member_id,menu_id) VALUES ('4','7'); INSERT INTO orders(member_id,menu_id) VALUES ('5','1'); INSERT INTO orders(member_id,menu_id) VALUES ('3','10'); INSERT INTO orders(member_id,menu_id) VALUES ('6','7'); INSERT INTO orders(member_id,menu_id) VALUES ('2','1'); INSERT INTO orders(member_id,menu_id) VALUES ('2','9'); INSERT INTO orders(member_id,menu_id) VALUES ('7','8'); INSERT INTO orders(member_id,menu_id) VALUES ('10','9'); INSERT INTO orders(member_id,menu_id) VALUES ('7','4'); INSERT INTO orders(member_id,menu_id) VALUES ('8','2'); INSERT INTO orders(member_id,menu_id) VALUES ('4','1'); INSERT INTO orders(member_id,menu_id) VALUES ('7','6'); INSERT INTO orders(member_id,menu_id) VALUES ('1','3'); INSERT INTO orders(member_id,menu_id) VALUES ('8','3'); INSERT INTO orders(member_id,menu_id) VALUES ('10','9'); INSERT INTO orders(member_id,menu_id) VALUES ('5','2'); INSERT INTO orders(member_id,menu_id) VALUES ('3','10'); INSERT INTO orders(member_id,menu_id) VALUES ('3','5'); INSERT INTO orders(member_id,menu_id) VALUES ('10','8'); INSERT INTO orders(member_id,menu_id) VALUES ('4','8'); INSERT INTO orders(member_id,menu_id) VALUES ('10','6'); INSERT INTO orders(member_id,menu_id) VALUES ('1','6'); INSERT INTO orders(member_id,menu_id) VALUES ('6','5'); INSERT INTO orders(member_id,menu_id) VALUES ('1','8'); INSERT INTO orders(member_id,menu_id) VALUES ('6','9'); INSERT INTO orders(member_id,menu_id) VALUES ('2','7'); INSERT INTO orders(member_id,menu_id) VALUES ('2','9'); INSERT INTO orders(member_id,menu_id) VALUES ('6','10');
各テーブルのデータが挿入出来ました。
自分が使用してるテーブルには、ここに記述していないデータも含まれるので、結果が異なることがありますがご了承ください。
いざ実践！
ストアドプロシージャの名前はランキング上位商品なのでmenurankingtopって感じで。
ストアドプロシージャ作成
CREATE PROCEDURE `menurankingtop`(IN input_data INT(3)) BEGIN SELECT menu_id, menu_title, price, tax_included , explanation , image_path FROM menus INNER JOIN (SELECT menu_id , COUNT(*) c1 FROM orders GROUP BY menu_id ) t1 USING (menu_id) ORDER BY c1 DESC LIMIT input_data ; END
やってることはいったって普通ですね。
SELECT menu_id , COUNT(*) c1 FROM orders GROUP BY menu_id
商品ごとの購入回数を求めてる。
それをmenu_idで結合して、購入回数が多い順でソートしてそれの上位を指定数求めるだけです。
ストアドプロシージャを呼び出す
ストアドプロシージャを呼び出す時は、CALLを使用する。
今回はINを指定してるので引数を忘れず記述する。
CALL menurankingtop(3); menu_id|menu_title |price|tax_included|explanation |image_path | -------|-------------------|-----|------------|------------------------------------------------------------------------------|-----------------------| 1|のり弁当 | 306| 330|人気ナンバーワン商品の『のり弁当シリーズ』の品質と価値を高め、白身魚のフライを増量するとともに、ソースの味を選べるようにして、商品の品質と価値を高めました。|のり弁当.jpg | 9|から揚げ弁当 | 362| 390|本醸造醤油とにんにくを強めたしっかりとした味付けは、ごはんとの相性が抜群です。 |から揚げ弁当.jpg | 7|とり天タルタル＆しょうが焼きコンビ弁当| 445| 480|人気のあるおかずを2種類のせたボリューム満点の商品です。ごはんが進む味をお楽しみください。 |とり天タルタル＆しょうが焼きコンビ弁当.jpg| CALL menurankingtop(1); menu_id|menu_title|price|tax_included|explanation |image_path| -------|----------|-----|------------|------------------------------------------------------------------------------|----------| 1|のり弁当 | 306| 330|人気ナンバーワン商品の『のり弁当シリーズ』の品質と価値を高め、白身魚のフライを増量するとともに、ソースの味を選べるようにして、商品の品質と価値を高めました。|のり弁当.jpg |
完成！
〆
作成したストアドプロシージャは、複雑ではないもののストアドプロシージャについての知識が増えたりととても面白かった。今回は使用しなかったストアドファンクションもいずれ触ってみたいと考えている。
