Home>

After checking for the same book, let's create a procedure to insert it. (As below)

The CREATE TABLE set is as follows.
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,'History of Soccer','Good Sports', 7000);
INSERT INTO Book VALUES (2,'Girls who know soccer','Number of trees', 13000);
INSERT INTO Book VALUES (3,'Understanding Soccer','Korean Media', 22000);
INSERT INTO Book VALUES (4,'Golf Bible','Korean Media', 35000);
INSERT INTO Book VALUES (5,'Figure Textbook','Good Sports', 8000);INSERT INTO Book VALUES (6,'Weightlifting Steps','Good Sports', 6000);
INSERT INTO Book VALUES (7,'Baseball Memories','Ideal Media', 20000);
INSERT INTO Book VALUES (8,'Please baseball','Ideal media', 13000);
INSERT INTO Book VALUES (9,'Olympic Games','Sanseido', 7500);
INSERT INTO Book VALUES (10,'Olympic Champions','Pearson', 13000);
INSERT INTO Customer VALUES (1,'Park Ji Sung','Manchester England', '000-5000-0001');
INSERT INTO Customer VALUES (2,'Kim Yuna','Seoul, Republic of Korea', '000-6000-0001');
INSERT INTO Customer VALUES (3,'Chan Milan','Gangwon-do, Republic of Korea', '000-7000-0001');
INSERT INTO Customer VALUES (4,'Chu Shin Soo',' US Cleveland', '000 -8000-0001');
INSERT INTO Customer VALUES (5,'Pak Se-ri','Daejeon, Republic of Korea', 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;
Corresponding source code
delimiter //
create procedure BookInsertOrUpdate (
    myBookID integer,
    myBookName varchar (40),
    myPublisher varchar (40),
    myPrice int)begin begin
    declare mycount integer;
    select count (*) into my count from book
    where bookname like myBookName;
    if mycount! = 0 then
        set SQL_SAFE_UPDATES = 0;
        update book set price = my price
        where bookname like mybookName;
    else else
     insert into book (bookid, bookname, publisher, price)
        VALUES (myBookID, myBookName, myPublisher, myPrice);
    end if;
END;
// //
delimter;

CALL BookInsertOrUpdate (15,'Sports Fun','Garden Science Book', 25000);
select * from book;
call BookInsertOrUpdate (15,'Sports Fun','First Book', 18000);
select * from book;
A procedure for returning the average price of a book.
delimiter //
create procedure AveragePrice (
out AverageVal integer)
Begin
select avg (price) into Average Val
from book where price is not null;
end;
// //
call AveragePrice (@myValue);
select @myValue;
Supplementary information (FW/tool version, etc.)

If the book has the same name in this procedure, the publisher would like to change it to the input data as well as the price of the book.
First of all, I wrote a procedure that expresses only the price of a book by the input price when the book name is the same.

  • Answer # 1

    I don't understand the intent of the question

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

    However, changing the price with the publisher with a book name that does not have a unique setting
    It's not practical. Should normally be changed using the primary key bookid