Overview Of SQL Stored Procedures

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

Blog