The default rules used by the parser for parsing names of built-in-function can be changed by enabling IGNORE_SPACE SQL mode. When we enable this mode, the parser relaxes the requirement that there be no whitespace between the function name and the following parenthesis. For example, after enabling IGNORE_SPACE SQL mode both of the following function calls are legal −Select SUM(Salary) from employee; Select SUM (Salary) from employee;But, in this case, the parser treats the function name as reserved words. It means that a space following the names no longer represents as an identifier.

MySQL IF statement implements a basic conditional construct within a stored procedure. Its syntax is as follows −IF expression THEN Statements; END IF;It must end with a semicolon. To demonstrate the use of IF statement within MySQL stored procedure, we are creating the following stored procedure which is based on the values, as shown below, of the table named ‘student_info’ −mysql> Select * from student_info; +------+---------+------------+------------+ | id   | Name    | Address    | Subject    | +------+---------+------------+------------+ | 101  | YashPal | Amritsar   | History    | | 105  | Gaurav  | Jaipur     | ... Read More

Actually, when a parser encounters a word that is the name of a built-in function, it must determine whether the name represents a function call or is instead a non-expression reference to an identifier such as a table or column name. consider the following queries −1. Select sum(salary) from employee; 2. Create table sum (i int);In the first query SUM is a reference to a function call and in the second query, it is referencing to table name.Parser follows the following rules to distinguish whether their names are being used as function calls or as identifiers in non-reference context −Rule1 ... Read More

MySQL SUBSTRING_INDEX() function will return the same string as output if the argument ‘count’ has the value greater than the total number of occurrences of delimiter. It can be demonstrated with the following example −mysql> Select SUBSTRING_INDEX('My Name is Ram','a',3); +-----------------------------------------+ | SUBSTRING_INDEX('My Name is Ram','a',3) | +-----------------------------------------+ | My Name is Ram                          | +-----------------------------------------+ 1 row in set (0.00 sec)The above query returns the same string because 3 is greater than the total number of occurrences of delimiter provided as argument i.e. ‘a’. There are only two ‘a’ in the string.

In MySQL stored procedure, user variables are referenced with an ampersand i.e. @, prefixed to the user variable names. For example, @A, @B, etc. are user variables. To demonstrate it, we are creating the following procedure −mysql> DELIMITER // ; mysql> CREATE PROCEDURE Proc_Uservariables()    -> BEGIN    -> SET @A = 100;    -> SET @B = 500;    -> SELECT @A,@B,@A+@B;    -> END // Query OK, 0 rows affected (0.00 sec) mysql> Delimiter ; // mysql> CALL Proc_Uservariables(); +------+------+-------+ | @A   | @B   | @A+@B | +------+------+-------+ |  100 |  500 |   600 | +------+------+-------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.01 sec)

As we know that the default type of a bit values assigned to user variables is binary strings but we can also assign a bit value to a number by using the following two methods −By using CAST() functionWith the help of CAST(… AS UNSIGNED) the bit value can be assigned a number. The following example will illustrate it −mysql> SET @abc = CAST(0b1000011 AS UNSIGNED); Query OK, 0 rows affected (0.00 sec) mysql> Select @abc; +------+ | @abc | +------+ | 67   | +------+ 1 row in set (0.00 sec)By adding 0(+0)The bit value can be assigned ... Read More

By default, the bit values assigned to the user variables are binary strings. It can be illustrated by assigning the bit value to a user variable and then by retrieving them as follows −mysql> SET @abc = 0b1000011; Query OK, 0 rows affected (0.00 sec) mysql> Select @abc; +------+ | @abc | +------+ | C    | +------+ 1 row in set (0.00 sec)The above result set shows that the default type of a bit value assigned to user variables are binary strings.

Actually, Bit values are returned as binary values but we can also display them in the printable form with the help of following −By adding 0We can display Bit values in printable form by adding 0 to them. Following the example from the bit_testing table can be used to understand it −mysql> Select bittest+0 from bit_testing; +-----------+ | bittest+0 | +-----------+ |       170 | |         5 | |         5 | +-----------+ 3 rows in set (0.00 sec)By using conversion function BIN(), OCT(), HEX()We can also display Bit values in ... Read More

Bit-field notation is the notation with the help of which we can write bit-field values. The syntax of Bit-field notation is as follows −Syntaxb’value’   OR 0bvalueHere, the value is a binary value written by using zeros and ones.The mainly Bit-filed notation is convenient for specifying values to be assigned to BIT columns of MySQL table. Following example will demonstrate it −mysql> Create table bit_testing (bittest BIT(8)); Query OK, 0 rows affected (1.09 sec) mysql> INSERT INTO bit_testing SET bittest = b'10101010'; Query OK, 1 row affected (0.07 sec) mysql> INSERT INTO bit_testing SET bittest = b'0101'; Query ... Read More

Local variables are those variables that are declared within the stored procedure. They are only valid within the BEGIN…END block where they are declared and can have any SQL data type. To demonstrate it, we are creating the following procedure −mysql> DELIMITER // ; mysql> Create Procedure Proc_Localvariables()    -> BEGIN    -> DECLARE X INT DEFAULT 100;    -> DECLARE Y INT;    -> DECLARE Z INT;    -> DECLARE A INT;    -> SET Y = 250;    -> SET Z = 200;    -> SET A = X+Y+Z;    -> SELECT X, Y, Z, A;    -> ... Read More