sql ストアドプロシージャ質問です。

Question

前提・実現したいこと

同じ図書があるかどうかを点検した後、挿入するプロシージャを作ってみましょう。(下のように)

CREATE TABLEセットは下記の通りです。

DROP DATABASE IF EXISTS  madang;
DROP USER IF EXISTS  madang@localhost;
create user madang@localhost identified WITH mysql_native_password  by 'madang';
create database madang;
grant all privileges on madang.* to madang@localhost with grant option;
commit;
 
USE madang;

CREATE TABLE Book (
  bookid      INTEGER PRIMARY KEY,
  bookname    VARCHAR(40),
  publisher   VARCHAR(40),
  price       INTEGER 
);

CREATE TABLE  Customer (
  custid      INTEGER PRIMARY KEY,  
  name        VARCHAR(40),
  address     VARCHAR(50),
  phone       VARCHAR(20)
);

CREATE TABLE Orders (
  orderid INTEGER PRIMARY KEY,
  custid  INTEGER ,
  bookid  INTEGER ,
  saleprice INTEGER ,
  orderdate DATE,
  FOREIGN KEY (custid) REFERENCES Customer(custid),
  FOREIGN KEY (bookid) REFERENCES Book(bookid)
);

INSERT INTO Book VALUES(1, 'サッカーの歴史', 'グッドスポーツ', 7000);
INSERT INTO Book VALUES(2, 'サッカーを知っている女の子', '木の数', 13000);
INSERT INTO Book VALUES(3, 'サッカーの理解', '大韓メディア', 22000);
INSERT INTO Book VALUES(4, 'ゴルフバイブル', '大韓メディア', 35000);
INSERT INTO Book VALUES(5, 'フィギュア教本', 'グッドスポーツ', 8000);
INSERT INTO Book VALUES(6, '重量挙げ段階別技術', 'グッドスポーツ', 6000);
INSERT INTO Book VALUES(7, '野球の思い出', '理想のメディア', 20000);
INSERT INTO Book VALUES(8, '野球をお願い', '理想のメディア', 13000);
INSERT INTO Book VALUES(9, 'オリンピックの話', '三省堂', 7500);
INSERT INTO Book VALUES(10, 'Olympic Champions', 'Pearson', 13000);

INSERT INTO Customer VALUES (1, 'パク·チソン', 'イギリスのマンチェスター', '000-5000-0001');
INSERT INTO Customer VALUES (2, 'キム·ヨナ', '大韓民国 ソウル', '000-6000-0001');  
INSERT INTO Customer VALUES (3, 'チャン·ミラン', '大韓民国 江原道', '000-7000-0001');
INSERT INTO Customer VALUES (4, 'チュ·シンス', '米国のクリーブランド', '000-8000-0001');
INSERT INTO Customer VALUES (5, 'パク·セリ', '大韓民国の大田',  NULL);

INSERT INTO Orders VALUES (1, 1, 1, 6000, STR_TO_DATE('2014-07-01','%Y-%m-%d')); 
INSERT INTO Orders VALUES (2, 1, 3, 21000, STR_TO_DATE('2014-07-03','%Y-%m-%d'));
INSERT INTO Orders VALUES (3, 2, 5, 8000, STR_TO_DATE('2014-07-03','%Y-%m-%d')); 
INSERT INTO Orders VALUES (4, 3, 6, 6000, STR_TO_DATE('2014-07-04','%Y-%m-%d')); 
INSERT INTO Orders VALUES (5, 4, 7, 20000, STR_TO_DATE('2014-07-05','%Y-%m-%d'));
INSERT INTO Orders VALUES (6, 1, 2, 12000, STR_TO_DATE('2014-07-07','%Y-%m-%d'));
INSERT INTO Orders VALUES (7, 4, 8, 13000, STR_TO_DATE( '2014-07-07','%Y-%m-%d'));
INSERT INTO Orders VALUES (8, 3, 10, 12000, STR_TO_DATE('2014-07-08','%Y-%m-%d')); 
INSERT INTO Orders VALUES (9, 2, 10, 7000, STR_TO_DATE('2014-07-09','%Y-%m-%d')); 
INSERT INTO Orders VALUES (10, 3, 8, 13000, STR_TO_DATE('2014-07-10','%Y-%m-%d'));

CREATE TABLE Imported_Book (
  bookid      INTEGER,
  bookname    VARCHAR(40),
  publisher   VARCHAR(40),
  price       INTEGER 
);

INSERT INTO Imported_Book VALUES(21, 'Zen Golf', 'Pearson', 12000);
INSERT INTO Imported_Book VALUES(22, 'Soccer Skills', 'Human Kinetics', 15000);
commit;

該当のソースコード

delimiter //

create procedure BookInsertOrUpdate(
	myBookID integer,
    myBookName varchar(40),
    myPublisher varchar(40),
    myPrice int)
begin
	declare mycount integer;
    select count(*) into mycount from book
    where bookname like myBookName;
    if mycount!=0 then
		set SQL_SAFE_UPDATES=0;
        update book set price=myprice
        where bookname like mybookName;
    else
	 insert into book(bookid,bookname,publisher,price)
        VALUES(myBookID,myBookName,myPublisher,myPrice);
    end if;
END;
//

delimter ;


CALL BookInsertOrUpdate(15, 'スポーツの楽しみ', '庭の科学書', 25000);
select * from book;

call BookInsertOrUpdate(15,'スポーツの楽しみ','第一書籍',18000);
select * from book;

図書の平均価格を返却するプロシージャです。

delimiter //

create procedure AveragePrice (
out AverageVal integer)
Begin
select avg(price) into AverageVal
from book where price is not null;
end;
//
call AveragePrice (@myValue);
select @myValue;

補足情報（FW/ツールのバージョンなど）

この該当プロシージャに本の名前が同じであれば、本の価格だけでなく、出版社も入力データに変更したいと思います。
上に書いたのは先ず本の名前が同じ場合、本の価格だけを入力価格で表すプロシージャを作ってみました。

Comments

[Orlofsky]

MySQL では ストアドプロシージャ と言います。タイトルを修正してください。

コードは https://teratail.com/help/question-tips#questionTips3-7 の [コード] に修正してください。

[caffrey]

直しましたが、難しいですね。
始めたばかりですから..

[yambejp]

テーブルのサンプルがないとプロシージャだけではなんとも言えません

[caffrey]

サンプルはどうやって上げればいいですか。
ただsqlファイルで上げればいいんですか？

[yambejp]

create table + insert　であげてください

[caffrey]

追加しました。

Answer 1

提示したコードは正常に動作していますか？異常があればそのことを質問に明示しましょう。

INSERT ... ON DUPLICATE KEY UPDATE があるから、わざわざストアドプロシージャにする必要があるだろうか？

Comments

[caffrey]

はい、正常に実行されています。
でもデータを追加で受け入れたいのですが、それが正常にできなくて質問しました。

Answer 2

質問の意図がわかりませんが

update book set price=myprice
↓↓↓
update book set price=myprice,publisher=myPublisher

ただ、ユニーク設定もしていない書籍名で出版社と価格を変えるのは
実践的ではありません。普通に主キーであるbookidを利用して変更すべきです

主キーを使ったinsert/update

念の為・・・

CREATE TABLE Book (
  bookid      INTEGER PRIMARY KEY,
  bookname    VARCHAR(40),
  publisher   VARCHAR(40),
  price       INTEGER 
);

INSERT INTO Book VALUES
(1, 'サッカーの歴史', 'グッドスポーツ', 7000),
(2, 'サッカーを知っている女の子', '木の数', 13000),
(3, 'サッカーの理解', '大韓メディア', 22000);

とりあえず挿入

insert into book values
(15, 'スポーツの楽しみ', '庭の科学書', 25000)
on duplicate key update set bookname=values(bookname),publisher=values(publisher),price=values(price);

主キーが競合するデータを挿入しようとして更新

insert into book values
(15,'スポーツの楽しみ','第一書籍',18000)
on duplicate key update bookname=values(bookname),publisher=values(publisher),price=values(price);

Comments

[caffrey]

データをbookidで新たに入力値を追加しましたが、それが正しいのかよくわかりません。
これが追加になったんですか？

delimiter //
create procedure BookInsertblisher(
myBookID integer,
myBookName varchar(40),
myPublisher varchar(40),
myPrice int)
begin
declare mycount integer;
select count(*) into mycount from book
where bookname like myBookName;
if mycount!=0 then
set SQL_SAFE_UPDATES=0;
update book set price=myprice,publisher=myPublisher
where bookname like mybookName;
else
insert into book(bookid,bookname,publisher,price)
VALUES(myBookID,myBookName,myPublisher,myPrice);
end if;
END;
//

CALL BookInsertblisher(16, 'スポーツセンター', '1本', 10000);
select * from book;

こうして入力して、bookidに16というデータを追加しました。
これが正しいのかはよく分かりません。

[yambejp]

主キーを利用してinsert/updateを切り替えるならon duplicateで処理してください

[caffrey]

insert into book(bookid,bookname,publisher,price)
VALUES(myBookID,myBookName,myPublisher,myPrice);
ON DUPLICATE KEY UPDATE title = 'myPublisher', price=10000

例えば、このように入れればいいでしょうか?
一旦実行はされません。 現在のコードはですね。

[caffrey]

delimiter //
create procedure BookInsertblisher(
myBookID integer,
myBookName varchar(40),
myPublisher varchar(40),
myPrice int)
begin
declare mycount integer;
select count(*) into mycount from book
where bookname like myBookName;
if mycount!=0 then
set SQL_SAFE_UPDATES=0;
update book set price=myprice,publisher=myPublisher
where bookname like mybookName;
else
insert into book(bookid,bookname,publisher,price)
VALUES(myBookID,myBookName,myPublisher,myPrice);
update book set publisher = 'publisher2' where bookname = 'スポーツセンター';
end if;
END;
//

このように変えて試してみましたが下のように表示されました。

22:53:55 create procedure BookInsertblisher( myBookID integer, myBookName varchar(40), myPublisher varchar(40), myPrice int) begin declare mycount integer; select count(*) into mycount from book where bookname like myBookName; if mycount!=0 then set SQL_SAFE_UPDATES=0; update book set price=myprice,publisher=myPublisher where bookname like mybookName; else insert into book(bookid,bookname,publisher,price) VALUES(myBookID,myBookName,myPublisher,myPrice); update book set publisher = 'publisher2' where bookname = 'スポーツセンター'; end if; END; Error Code: 1304. PROCEDURE BookInsertblisher already exists 0.000 sec

[caffrey]

あ、解決しました。 おかげさまで勉強するのに役に立ったです。 ありがとうございます。

[yambejp]

念の為sample追記しておきました