mysql - 使用 MySQL 中的过程将多个条目插入表中
问题描述
我在 mysql 中创建了一个程序插入到表中,如下所示。
DELIMITER $$
CREATE PROCEDURE createOrderPR ( IN iv_productID INT,
IN iv_orderDate DATE,
IN iv_orderTime TIME,
IN iv_shopID INT,
IN iv_mobileNo varchar(10),
IN iv_quantity smallint,
IN iv_total float(10,2),
IN iv_discount float(10,2),
IN iv_taxable float(10,2),
IN iv_CGST float(10,2),
IN iv_SGST float(10,2)
)
BEGIN
DECLARE availQty smallint default 0;
SELECT stockCount INTO availQty FROM product WHERE productID = iv_productID;
SET availQty = availQty - iv_quantity;
start transaction;
set autocommit =0;
INSERT INTO orders(orderNo,productID,orderDate,orderTime,shopID,mobileNo,quantity,total,discount,taxable,CGST,SGST,orderStatus,deletionMark)
VALUES( null,iv_productID,iv_orderDate,iv_orderTime,iv_shopID,iv_mobileNo,iv_quantity,iv_total,iv_discount,iv_taxable,iv_CGST,iv_SGST,'Open',null);
UPDATE product SET stockCount = availQty WHERE productID = iv_productID;
COMMIT;
SELECT MAX(orderNo) FROM orders WHERE shopID = shopID AND mobileNo = mobileNo;
END
$$
目前它只允许单个记录。现在我需要插入多个记录,在这种情况下如何定义过程的IN参数。请建议。
解决方案
通过使用 JSON IN 参数解决了这个问题这些是示例代码(注意:表中添加的新列都没有变化)
DELIMITER $$
CREATE PROCEDURE createOrderMulti ( IN orderJson JSON,
IN length INT )
BEGIN
-- Date Declaration
DECLARE availQty smallint default 0;
DECLARE iv_productID INT;
DECLARE iv_orderDate DATE;
DECLARE iv_orderTime TIME;
DECLARE iv_shopID INT;
DECLARE iv_mobileNo varchar(10);
DECLARE iv_quantity smallint;
DECLARE iv_total float(10,2);
DECLARE iv_discount float(10,2);
DECLARE iv_taxable float(10,2);
DECLARE iv_CGST float(10,2);
DECLARE iv_SGST float(10,2);
DECLARE counter smallint default 0;
DECLARE item INT;
DECLARE orderNo INT DEFAULT null;
start transaction;
set autocommit = 0;
WHILE counter < length DO
-- Extract the JSON value
SELECT JSON_VALUE(orderJson, CONCAT('$[', counter, '].productID')) INTO iv_productID;
SELECT JSON_VALUE(orderJson, CONCAT('$[', counter, '].orderDate')) INTO iv_orderDate;
SELECT JSON_VALUE(orderJson, CONCAT('$[', counter, '].orderTime')) INTO iv_orderTime;
SELECT JSON_VALUE(orderJson, CONCAT('$[', counter, '].shopID')) INTO iv_shopID;
SELECT JSON_VALUE(orderJson, CONCAT('$[', counter, '].mobileNo')) INTO iv_mobileNo;
SELECT JSON_VALUE(orderJson, CONCAT('$[', counter, '].quantity')) INTO iv_quantity;
SELECT JSON_VALUE(orderJson, CONCAT('$[', counter, '].total')) INTO iv_total;
SELECT JSON_VALUE(orderJson, CONCAT('$[', counter, '].discount')) INTO iv_discount;
SELECT JSON_VALUE(orderJson, CONCAT('$[', counter, '].taxable')) INTO iv_taxable;
SELECT JSON_VALUE(orderJson, CONCAT('$[', counter, '].CGST')) INTO iv_CGST;
SELECT JSON_VALUE(orderJson, CONCAT('$[', counter, '].SGST')) INTO iv_SGST;
SELECT stockCount INTO availQty FROM product WHERE productID = iv_productID;
SET availQty = availQty - iv_quantity;
SET item = counter + 1;
INSERT INTO orders(orderNo,item,productID,orderDate,orderTime,shopID,mobileNo,quantity,total,discount,taxable,CGST,SGST,orderStatus,deletionMark)
VALUES( orderNo,item,iv_productID,iv_orderDate,iv_orderTime,iv_shopID,iv_mobileNo,iv_quantity,iv_total,iv_discount,iv_taxable,iv_CGST,iv_SGST,'Open',null);
SET orderNo = LAST_INSERT_ID();
UPDATE product SET stockCount = availQty WHERE productID = iv_productID;
SET counter = counter + 1;
END WHILE;
COMMIT;
--SELECT MAX(orderNo) FROM orders WHERE shopID = shopID AND mobileNo = mobileNo;
SELECT orderNo as orderNo;
END
$$
推荐阅读
- postgresql - PostGIS 栅格 AWS RDS
- css - CSS Grid:带有 span、calc() 和 CSS 变量的网格列在 WebKit 浏览器中不起作用
- cucumber - 在 Jenkins Run 期间忽略某些黄瓜标签
- mongodb - 配置数据库触发器时,数据库触发器匹配表达式中的 $or 选择器在第二级嵌套中不起作用
- mysql - 在没有循环的 mysql 中使用参数 XML 示例存储过程
- python-3.x - 使用美丽的汤刮时没有价值(旋转器和反弹而不是价值)
- web-scraping - Youtube 是否从描述区域中删除了“类别”?
- asp.net - 带有 src 的 HTML 5 音频到带有凭据的虚拟目录
- html - Body, HTML min-height:100% 没有得到整个高度
- python - Pyinstaller 交互式控制台的 locals() 替代品