I’m not an expert just learned along the way, while I was tackling a problem to optimize my queries ?
Post Author: Swaroop Das, Web Developer
Date: July 6, 2020
What is a Stored Procedure?
In a simple term, it’s just a bunch of SQL instructions (queries) run together, just like a function in other programming languages…
When to use it?
Let’s say, you have a set of instructions that need to run in sequence…
Where to use it?
Periodic updating of data is necessary.
Why use it?
- They allow modular programming.
- They allow faster execution.
- They reduce network traffic.
- They can be used as a security mechanism.
Understanding Delimiters
A delimiter is a sequence of characters that separate SQL statements…
DELIMITER $$
CREATE PROCEDURE PROCEDURE_NAME()
BEGIN
SELECT name FROM customer;
END $$
DELIMITER ;
How to Write Your Own Stored Procedure
DELIMITER $$
CREATE PROCEDURE PROCEDURE_NAME()
BEGIN
/* Procedure code */
END $$
DELIMITER ;
Common Commands
CALL PROCEDURE_NAME();DROP PROCEDURE IF EXISTS PROCEDURE_NAME;SHOW PROCEDURE STATUS;
Variables and Conditional Statements
DECLARE done INT DEFAULT FALSE;
IF CONDITION THEN
/* Statement */
ELSE
/* Alternate */
ENDIF;
Switch Case Example
SELECT id,
(
CASE
WHEN qty > 0 AND stock_status = 1 THEN 'ENABLE'
WHEN qty <= 0 AND stock_status = 0 THEN 'DISABLE'
END
) AS product_enable
FROM products;
Loops and Cursors
DELIMITER $$
CREATE PROCEDURE updateStatus()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE id INT;
DECLARE cursor_products CURSOR FOR SELECT Id FROM products;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cursor_products;
read_loop: LOOP
FETCH cursor_products INTO id;
IF done THEN
LEAVE read_loop;
END IF;
UPDATE products SET stock_status=IF(qty>0,1,0) WHERE Id=id;
END LOOP;
CLOSE cursor_products;
END $$
DELIMITER ;
Conclusion:
To conclude the overview of the stored procedure, we have gone through the basics of store procedure to handle conditional, switch case, loop and even cursor to iterate over a row of a table. To learn more about store procedure visit the below reference links as a starting point. > All the Best. To embark on the journey towards the stored procedure. ? ? ### Happy Coding!
Ready to embark on your digital journey with TechSevin?Contact us to discuss how our Adobe-certified experts can elevate your digital presence.
to elevate your digital presence.
