MySQL Stored Procedure Programming

Operators

MySQL operators include the familiar operators common to most programming languages, although C-style operators (++,—,+=, etc.) are not supported.

Operators are typically used within the SET statement to change the value of a variable, within comparison statements such as IF or CASE, and in loop control expressions. Example 3-10 shows a few simple examples of using operators within stored programs.

Example 3-10. Examples of operators in a stored program

create procedure operators(  )
begin
        DECLARE a int default 2;
        declare b int default 3;
        declare c FLOAT;

        set c=a+b; select 'a+b=',c;
        SET c=a/b; select 'a/b=',c;
        SET c=a*b; Select 'a*b=',c;


            IF (a<b) THEN
                select 'a is less than b';
        END IF;
        IF NOT (a=b) THEN
                SELECT 'a is not equal to b';
        END IF;
end;

The various types of operators (mathematical , comparison , logical, and bitwise) are described in the following subsections.

Mathematical Operators

MySQL supports the basic mathematical operators you learned about in elementary school (pay attention class!): addition (+), subtraction (-), multiplication (*), and division (/).

In addition, MySQL supports two additional operators related to division: the DIV operator returns only the integer portion of division, while the modulus operator (%) returns only the remainder from a division. Table 3-2 lists, describes, and provides an example of the MySQL mathematical operators.

Table 3-2. MySQL mathematical operators

Operator	Description	Example
+	Addition	SET var1=2+2; → 4
-	Subtraction ...