Stored Procedure-statements

 

1. If else

If else is similar to the select structure statement, similar to If else in programming language. Syntax:

If expression then execution statement
[Elseif expression then execution statement]
[Else execution statement]
End if;

Print the level based on the score. The Code is as follows:

Drop procedure if exists proc_test_statement; Create procedure proc_test_statement (in num int (11), out result varchar (255) begin if (Num> = 80) then set result = 'excellent '; elseif (Num> = 70) then set result = 'good'; elseif (Num> = 60) then set result = 'pass'; else set result = 'failed '; end if; end; call proc_test_statement (90, @ n); select @ n;

The execution result is as follows:

Ii. Case

Case is similar to switch... case in programming languages. Syntax:

Case expression
When expression then execution statement
When expression then execution statement
Else execution statement
End case;

Print the level corresponding to the letter. The Code is as follows:

Drop procedure if exists proc_test_case; Create procedure proc_test_case (in scode varchar (255), out result varchar (255) Begin case scode when 'a 'then set result = 'excellent '; when 'B' then set result = 'good'; When 'C' then set result = 'pass'; else set result = 'failed'; end case; end; call proc_test_case ('D', @ result); select @ result;

The execution result is as follows:

3. While

The while statement is similar to the while statement in programming language. Syntax:

While expression do

Execution statement

End while

Add a number from 1. The Code is as follows:

DROP PROCEDURE IF EXISTS proc_test_while;CREATE PROCEDURE proc_test_while(    IN time int(11),     OUT result int(11) )BEGIN     DECLARE n INT DEFAULT 1;    SET result = 0;    WHILE (n <= time) DO        SET result = result + n;        SET n = n + 1;     END WHILE;END;CALL proc_test_while(100, @result);SELECT @result;

The execution result is as follows:

Iv. Repeat

The pepeat statement is similar to the do... while statement in the program language. The syntax is as follows:

Repeat
Execute the statement;
Until expression
End repeat

Add a number from 1. The Code is as follows:

DROP PROCEDURE IF EXISTS proc_test_pepeat;CREATE PROCEDURE proc_test_pepeat(    IN time int(11),     OUT result int(11) )BEGIN     DECLARE n INT DEFAULT 1;    SET result = 0;    REPEAT             SET result = result + n;            SET n = n + 1;        UNTIL (n > time)     END REPEAT;END;CALL proc_test_pepeat(100, @result);SELECT @result;

The result is as follows:

5. Loop and leave and iterate

The use of loop should be combined with leave and iterate. The loop flag is an unconditional loop. Leave is similar to the break statement. It jumps out of the loop and jumps out of the begin end. iterate is similar to the continue to end the loop.

Calculate the even number or less of a number. The Code is as follows: