How to create Stored Procedure in MySQL | INSERT | UPDATE | JAVA INTERVIEW

DELIMITER $$

DROP PROCEDURE IF EXISTS `my_cart_db`.`addMoreElemetToCartAndUpdateProduct` $$
CREATE PROCEDURE `my_cart_db`.`addMoreElemetToCartAndUpdateProduct` (IN p_id INT,IN p_qty INT,IN p_name varchar(100))
BEGIN
insert into cart_item(product_id,quantity,product_name) values(p_id,p_qty,p_name);
update product set quantity=p_qty where id=p_id;

END $$

DELIMITER ;
####################################################
But we need database and its tables

DROP TABLE IF EXISTS `my_cart_db`.`cart_item`;
CREATE TABLE `my_cart_db`.`cart_item` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`product_id` int(10) unsigned DEFAULT NULL,
`product_code` varchar(45) DEFAULT NULL,
`quantity` int(10) unsigned DEFAULT NULL,
`product_name` varchar(500) DEFAULT NULL,
`user_id` varchar(45) DEFAULT NULL,
`udate` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `my_cart_db`.`product`;
CREATE TABLE `my_cart_db`.`product` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(145) DEFAULT NULL,
`image_path1` varchar(445) DEFAULT NULL,
`price` double DEFAULT NULL,
`sub_category_id` int(10) unsigned DEFAULT NULL,
`stock` double unsigned DEFAULT NULL,
`unit` varchar(445) DEFAULT NULL,
`mrp` double DEFAULT NULL,
`image_path2` varchar(445) DEFAULT NULL,
`image_path3` varchar(445) DEFAULT NULL,
`title` varchar(445) DEFAULT NULL,
`description` text,
`quantity` int(10) unsigned DEFAULT NULL,
`offerPrice` double DEFAULT NULL,
`discount` int(10) unsigned DEFAULT NULL,
`discount_price` double DEFAULT NULL,
`category_id` int(10) unsigned DEFAULT NULL,
`code` varchar(20) DEFAULT NULL,
`rulling` varchar(45) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`page` varchar(20) DEFAULT NULL,
`gaddi` varchar(20) DEFAULT NULL,
`bundle` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8;
#minoractivity