Overview Of SQL Stored Procedures (part 2 )
Hello Everyone, In the last post, I have covered the basics of how to creating and deleting Store procedures. You can visit the previous post here. In this post, I will go through the below topics and how to use in, Store procedures
- Conditional statement
- Switch statement
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
DECLARE done INT DEFAULT FALSE;
To handle the Logical behavior 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; // 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 behavior 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;
SELECT id, IF(`qty` > 0 && `stock_status` = 1,'ENABLE','DISABLE') as product_enable FROM products;
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;
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 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 expression DO /* Statement */ END WHILE;
Loop will go-on until the condition is satisfied. when false the execution of block stop
LOOP_NAME: LOOP /* Statement */ END LOOP;
You can leave LOOP if a certain case is true. As shown below :
LOOP_NAME: LOOP IF CONDITION THEN LEAVE LOOP_NAME; END IF; /* Statement */ END LOOP;
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
- Fetch Statement
FETCH CURSOR_NAME INTO VAR1
- Close Statement
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 ;
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. 😊 😊
Why ? SQLSERVERTUTORIAL
Learn Material mysqltutorial
WHERE ? TECHREPUBLIC