MYSQL存储过程-练习1

MYSQL存储过程-练习1

创建book表

CREATE TABLE `book` (
  `boodid` int unsigned NOT NULL AUTO_INCREMENT,
  `bookname` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `author` varchar(30) COLLATE utf8mb4_general_ci NOT NULL,
  `info` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `year_publication` year NOT NULL,
  `num` smallint DEFAULT NULL,
  PRIMARY KEY (`boodid`),
  KEY `idx` (`year_publication`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='书本信息表'

创建存储过程

DELIMITER $
CREATE PROCEDURE insert_book10()
BEGIN
  DECLARE i INT;
  DECLARE bname VARCHAR(200);
  SET i = 1;
  WHILE i <=5 DO
    IF i = 1 THEN
      SET bname=CONCAT("HARRY POTER-魔法石",i);
    ELSEIF i = 2 THEN
      SET bname=CONCAT("HARRY POTER-密室",i);
    ELSEIF i = 3 THEN
      SET bname=CONCAT("HARRY POTER-凤凰社",i);
    ELSEIF i = 4 THEN
      SET bname=CONCAT("HARRY POTER-火焰杯",i);
    ELSE
      SET bname=CONCAT("HARRY POTER-死亡圣器",i);
    END IF;

    INSERT INTO book(bookname,author,info,year_publication,num) VALUE (bname,"JK罗琳","魔法故事","2000",i);
    SET i=i+1;
  END WHILE;
END $
DELIMITER;

执行存储过程

CALL insert_book10()

检验结果

mysql> select * from book;
+--------+---------------------------+----------+--------------+------------------+------+
| boodid | bookname                  | author   | info         | year_publication | num  |
+--------+---------------------------+----------+--------------+------------------+------+
|      1 | HARRY POTER-魔法石1       | JK罗琳   | 魔法故事     |             2000 |    1 |
|      2 | HARRY POTER-密室2         | JK罗琳   | 魔法故事     |             2000 |    2 |
|      3 | HARRY POTER-凤凰社3       | JK罗琳   | 魔法故事     |             2000 |    3 |
|      4 | HARRY POTER-火焰杯4       | JK罗琳   | 魔法故事     |             2000 |    4 |
|      5 | HARRY POTER-死亡圣器5     | JK罗琳   | 魔法故事     |             2000 |    5 |
+--------+---------------------------+----------+--------------+------------------+------+
5 rows in set (0.00 sec)