how to pass a list in to mysql procedure?

Question

Do you want to insert one to many data at once(list of data) in mysql procedure, you can use json method for that. then, you have to pass your list of data as a json by mysql json format. then you can insert list data using while loop.

Answer 1

json object

{"billNo":16,"date":"2017-13-11 09:05:01","customerName":"Vikas","total":350.0,"fixedCharges":100,"taxAmount":25.78,"status":"paid","product":[{"productId":"MRR11","categoryId":72,"categoryName":"Parker Pen","cost":200,"quantity":2,"log":{"supplierId":"725","supplierName":"Rihant General Stores"}},{"productId":"MRR12","categoryId":56,"categoryName":"Drawing Books","cost":150,"quantity":3,"log":{"supplierId":"725","supplierName":"Rihant General Stores"}}]}

create procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `json_example1`(json_object JSON)
  BEGIN

        DECLARE products json;
        DECLARE i INT DEFAULT 0;
        DECLARE categoryName VARCHAR(255) DEFAULT 0;
        /*assign json_object's value to products mysql json object*/
        SELECT json_object->"$.product" INTO products;

        loop1 : WHILE i < JSON_LENGTH(products) DO
                /*get categoryName from current products object in while loop */
                SET categoryName = JSON_EXTRACT(products,CONCAT('$[',i,'].categoryName'));
                /* insert categoryName into table */
                INSERT INTO product VALUES(NULL,SUBSTRING(categoryName,2,(LENGTH(categoryName)-2)));
                SET i = i + 1;
        END WHILE loop1;
    END

call procedure

mysql> call json_example1('{"billNo":16,"date":"2017-13-11 09:05:01","customerName":"Vikas","total":350.0,"fixedCharges":100,"taxAmount":25.78,"status":"paid","product":[{"productId":"MRR11","categoryId":72,"categoryName":"Parker Pen","cost":200,"quantity":2,"log":{"supplierId":"725","supplierName":"Rihant General Stores"}},{"productId":"MRR12","categoryId":56,"categoryName":"Drawing Books","cost":150,"quantity":3,"log":{"supplierId":"725","supplierName":"Rihant General Stores"}}]}');

output

mysql> select * from product;
+------------+---------------+
| product_id | product_name  |
+------------+---------------+
|          1 | Parker Pen    |
|          2 | Drawing Books |
+------------+---------------+
2 rows in set (0.03 sec)

extra -

CREATE TABLE product ( product_id int(11) NOT NULL AUTO_INCREMENT, product_name varchar(255) DEFAULT NULL, PRIMARY KEY (product_id) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;