首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >MySQL使用while循环的函数插入多行

问MySQL使用while循环的函数插入多行
EN

Stack Overflow用户
提问于 2019-03-18 19:22:15
回答 1查看 254关注 0票数 0

我正在尝试在MySQL数据库中插入多个行。首先,我为address创建了插入函数,该函数连接到另一个只包含格式化地址的表,因此我在另一个函数中对其进行了插入。

然后,在第三个函数中,我尝试填充第三个表,首先为它生成伪地址。由于某些原因,即使是第一个较低级别的函数(address_insert())也由于SQL语法错误而无法执行,我不知道它是什么。有什么想法吗?

谢谢!第一个函数:

代码语言:javascript
复制
DROP PROCEDURE IF EXISTS address_insert;
CREATE PROCEDURE address_insert()
BEGIN
DECLARE i INT DEFAULT (SELECT COUNT(*) FROM hoop.address) + 1;
DECLARE counter INT DEFAULT 0;

WHILE (counter < 5) DO 
INSERT INTO hoop.address (adr_id, address, city, country, created_at, lat, lng, updated_at, zip)
VALUES                   (i, CONCAT("Address-", i), "City", "United States", CURRENT_TIMESTAMP, RAND(35, 50), RAND(80, 120), CURRENT_TIMESTAMP, "ZIPCODE");

    SET i = i + 1;
    SET counter = counter + 1; 
END WHILE;
END;

第二个函数:

代码语言:javascript
复制
DROP PROCEDURE IF EXISTS address_shop_insert;
CREATE PROCEDURE address_shop_insert()
BEGIN
DECLARE i INT DEFAULT (SELECT COUNT(*) FROM hoop.address_shop) + 1;
DECLARE counter INT DEFAULT 0;

WHILE (counter <= SELECT COUNT(*) FROM hoop.address) DO 
INSERT INTO hoop.address_shop_insert (formatted_address, adr_id)
VALUES                               ("Formatted Address", i);

SET i = i + 1;
SET counter = counter + 1; 
END WHILE;
END;

最后一个函数:

代码语言:javascript
复制
DROP PROCEDURE IF EXISTS merchant_shop_insert;
CREATE PROCEDURE merchant_shop_insert()
BEGIN
address_insert()
address_shop_insert()

DECLARE i INT DEFAULT (SELECT COUNT(*) FROM hoop.merchant_shop) + 1; 
DECLARE merchant_accounts INT DEFAULT (SELECT COUNT(*) FROM hoop.merchant_account) + 1;
DECLARE shop_address_id DEFAULT (SELECT COUNT(*) FROM hoop.address_shop) - 5;
DECLARE counter INT DEFAULT 1;
DECLARE account_mod INT DEFAULT 0;
DECLARE account_id INT DEFAULT 1;

WHILE (i < merchant_accounts) DO
 IF 
    SELECT MOD(account_mod, 5) = 0
    SET account_id = account_id + 1;
 END IF;

 IF counter = 5
    SET shop_address_id = shop_address_id + 1;
    SET counter = 1;
 END IF;

  INSERT INTO hoop.merchant_shop (shop_id, contact_name, contact_phone, created_at, shop_name, status, updated_at, acc_id, shop_adr_id) 
  VALUES (i, "Strawberry Peach", "+361/789-6544", CURRENT_TIMESTAMP, CONCAT("Shop", i), 1, CURRENT_TIMESTAMP, account_id, shop_address_id)

  SET i = i + 1;
  SET account_mod = account_mod + 1;
  SET counter = counter + 1;
END WHILE;
END;
EN

回答 1

Stack Overflow用户

发布于 2019-03-18 19:34:27

由于半列;结束了一个命令,因此您需要为创建存储过程定义一个特定的分隔符,否则,MySQL将不知道哪个分隔符表示该过程声明的结束。

代码语言:javascript
复制
DROP PROCEDURE IF EXISTS address_insert;    

-- Set the delimiter to $$
DELIMITER $$

-- Notice that every instructions IN the procedure will be ended by the regular delimiter ;
CREATE PROCEDURE address_insert()
BEGIN
    DECLARE i INT DEFAULT (SELECT COUNT(*) FROM hoop.address) + 1;
    DECLARE counter INT DEFAULT 0;

    WHILE (counter < 5) DO 
        INSERT INTO hoop.address (adr_id, address, city, country, created_at, lat, lng, updated_at, zip)
        VALUES                   (i, CONCAT("Address-", i), "City", "United States", CURRENT_TIMESTAMP, RAND(35, 50), RAND(80, 120), CURRENT_TIMESTAMP, "ZIPCODE");

        SET i = i + 1;
        SET counter = counter + 1; 
    END WHILE;
-- vv------------------------ Notice this
END$$

-- Set it back to ;
DELIMITER ;
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/55220227

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档