Technical

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 ;