Overview Of SQL Stored Procedures (part 2 )

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

  • Variables
  • Conditional statement
  • Switch statement
  • Loop
  • Cursors

Variable

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 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;

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 ,

  1. Declaration Statement
   DECLARE CURSOR_NAME CURSOR FOR SELECT_SQL_STATEMENT
  1. 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;
  1. Open Statement
   OPEN CURSOR_NAME;
  1. Fetch Statement
   FETCH CURSOR_NAME INTO VAR1
  1. 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

Why ? SQLSERVERTUTORIAL

Learn Material mysqltutorial

WHERE ? TECHREPUBLIC

Swarup Das

Swarup Das

Web Developer, Tech Enthusiast. Passionate about new technology and programming languages.

Leave a Reply

Your email address will not be published. Required fields are marked *