Hello Everyone, In the last post, I have covered the basics of how to create and delete Store procedures. In this post, I will go through the below topics and how to use in, Store procedures
- Variables
- Conditional statement
- Switch statement
- Loop
- Cursors
- Variable
Variables
Unlike other programming languages, a Variable is just a placeholder for the actual value. As the meaning of variable is, not consistent or having a fixed pattern; liable to change. Every Variable has 2 things i.e Name and DataType. To define/declare a variable. As shown below :
DECLARE VARIABLE_NAME DATATYPE DEFAULT VALUE
Example :
DECLARE done INT DEFAULT FALSE;
Conditional Statement
To handle the Logical behaviour of the SQL statement, we use the conditional statement. They allow us to handle the condition based upon a particular scenario. As shown below :
IF CONDITION THEN
/* Statement */
ENDIF;
or alternative syntax
IF(CONDITION,TRUE VALUE, FALSE VALUE)
If the condition is satisfied, then the code surrounded by the IF block is executed. if the code has else block then the ELSE block is executed. You also have nested block as shown below :
IF CONDITION THEN
/* Statement */
IF CONDITION THEN
/* Statement */
ELSE
/* Statement */
ENDIF;
ELSE
/* Statement */
ENDIF;
To handle cases where the logical behaviour many outcomes. You can also have if-else-if-else or IF-ELSE-IF ladder. As shown below.
IF CONDITION THEN
/* Statement */
ELSE IF CONDITION THEN
/* Statement */
ELSE
/* Statement */
ENDIF;
Example :
SELECT id,
IF(`qty` > 0 && `stock_status` = 1,'ENABLE','DISABLE') as product_enable
FROM products;
Switch Statement
An Alternate to IF-ELSE-IF ladder is SWITCH CASE, were operation is performed based upon the input value. As shown below.
WHEN
CASE CONDITION THEN /* Statement */
CASE CONDITION THEN /* Statement */
ELSE RESULT
END CASE;
Example :
SELECT id,
(
CASE
WHEN `qty` > 0 && `stock_status` = 1 THEN 'ENABLE'
WHEN `qty` <= 0 && `stock_status` = 0 THEN 'DISABLE'
END
) as product_enable
FROM products;
Loop Statement
Loop is crucial to any programming language as it allows us to perform the same task iteratively if the condition is true. There are different variant of LOOPS WHILE and just a simple LOOP. As shown below :
WHILE LOOP
WHILE expression DO
/* Statement */
END WHILE;
Loop will go-on until the condition is satisfied. when false the execution of block stop
LOOP
LOOP_NAME: LOOP
/* Statement */
END LOOP;
You can leave LOOP if a certain case is true. As shown below :
LEAVE LOOP
LOOP_NAME: LOOP
IF CONDITION THEN
LEAVE LOOP_NAME;
END IF;
/* Statement */
END LOOP;
Cursors
What is Cursors ?
A cursor allows you to iterate a set of rows returned by a query and process each row individually.
For any MySQL cursor, there are 5 parts ,
- Declaration Statement
DECLARE CURSOR_NAME CURSOR FOR SELECT_SQL_STATEMENT - Handler Statement : Handler allows us to handle the scenario when the cursor hits the end of the rows.
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; - Open Statement
OPEN CURSOR_NAME; - Fetch Statement
FETCH CURSOR_NAME INTO VAR1 - Close Statement
CLOSE CURSOR_NAME;
Complete Syntax :
DECLARE VAR1 DATA_TYPE;
DECLARE CURSOR_NAME CURSOR FOR SELECT FIELD FROM TABLE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN CURSOR_NAME;
FETCH CURSOR_NAME INTO VAR1;
CLOSE CURSOR_NAME;
Example of Loop & Cursor :
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 learn more about store procedures visit the below reference links as a starting point.
All the Best. To embark on the journey towards the stored procedure. 😊 😊
Happy Coding!
Reference
Top comments (0)