Write a transaction block in Store procedure?

Question

hi frnds Write a transaction block in Store procedure?

Nitin Sontakke · Answer

Anjali, let me give one unasked for friendly advice. By just posting the interview questions here and reading their answer you are not going to gain much knowledge. Trust me. Understanding concepts from beginning is very important for which study is inevitable. It is a time consuming process, but there is no going around it. Please refer to books like SQL Server Unleashed for thorough coverage of such topics. Please try to take this in right spirit. I am telling this to your long term benefit.

Rathrola Prem Kumar · Answer

To perform the ROLLBACK in MySQL Stored Procedure, we must have to declare exit handler in stored procedure. There are two types of handler we can have in MySQL Stored Procedure. sqlexception sqlwarning sqlexception will execute when there is any error occurs during the query execution and sqlwarning will execute when any warning occurs in MySQL Stored Procedure. Let’s see how we can have those block in Stored Procedure. SAMPLE GOES HERE DELIMITER $$ CREATE PROCEDURE `transaction_sp` () BEGIN DECLARE exit handler for sqlexception BEGIN -- ERROR ROLLBACK; END; DECLARE exit handler for sqlwarning BEGIN -- WARNING ROLLBACK; END; START TRANSACTION; -- ADD option 5 INSERT INTO product_option(product_id,option_id,required) VALUES(insertedProductID,5,0); SET poid = (SELECT LAST_INSERT_ID()); INSERT INTO product_option_value(product_option_id,product_id,option_id,option_value_id,quantity,subtract,price,price_prefix,points,points_prefix,weight,weight_prefix) VALUES(poid,insertedProductID,5,50,0,0,4.99,'+',0,'+',0,'+'); -- ADD option 12 INSERT INTO product_option(product_id,option_id,required) VALUES(insertedProductID,12,1); -- ADD option 13 INSERT INTO product_option(product_id,option_id,required) VALUES(insertedProductID,13,0); COMMIT; END $$