MySQL Articles - Page 381 of 439

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

We can create a stored procedure with both IN and OUT parameters to get multiple values from a MySQL table. To make it understand we are taking an example of a table named ‘student_info’ having the following data −mysql> Select * from student_info; +------+---------+------------+------------+ | id   | Name    | Address    | Subject    | +------+---------+------------+------------+ | 101  | YashPal | Amritsar   | History    | | 105  | Gaurav  | Jaipur     | Literature | | 110  | Rahul   | Chandigarh | History    | | 125  | Raman   | Bangalore  | Computers  | ... Read More

We can create a stored procedure with IN operator to update values in a MySQL table. To make it understand we are taking an example of a table named ‘student_info’ having the following data −mysql> Select * from student_info; +------+---------+------------+------------+ | id   | Name    | Address    | Subject    | +------+---------+------------+------------+ | 101  | YashPal | Amritsar   | History    | | 105  | Gaurav  | Jaipur     | Literature | | 110  | Rahul   | Chandigarh | History    | | 125  | Raman   | Bangalore  | Computers  | +------+---------+------------+------------+ 4 rows ... Read More

We can create a stored procedure with IN operator to delete values from a MySQL table. To make it understand we are taking an example of a table named ‘student_info’ having the following data −mysql> Select * from student_info; +------+---------+------------+------------+ | id   | Name    | Address    | Subject    | +------+---------+------------+------------+ | 100  | Aarav   | Delhi      | Computers  | | 101  | YashPal | Amritsar   | History    | | 105  | Gaurav  | Jaipur     | Literature | | 110  | Rahul   | Chandigarh | History    | +------+---------+------------+------------+ ... Read More

For treating the hexadecimal value as a number, we can use the CAST(… AS UNSIGNED) function. Following example will demonstrate it −mysql> Select 0x54, CAST(0x54 AS UNSIGNED); +------+------------------------+ | 0x54 | CAST(0x54 AS UNSIGNED) | +------+------------------------+ | T    |                     84 | +------+------------------------+ 1 row in set (0.01 sec)

As we know that in numeric contexts the hexadecimal values act like integers and in string contexts they act like binary string. It can be understood with the help of the following example,mysql> Select X'5455544F5249414C53504F494E54'; +---------------------------------+ | X'5455544F5249414C53504F494E54' | +---------------------------------+ | TUTORIALSPOINT                  | +---------------------------------+ 1 row in set (0.07 sec)But, if we are talking about default type of hexadecimal value in MySQL, then it is a string.

We can create a stored procedure with an IN operator to insert values in a MySQL table. To make it understand we are taking an example of a table named ‘student_info’ having the following data −mysql> Select * from student_info; +------+---------+-----------+------------+ | id   | Name    | Address   | Subject    | +------+---------+-----------+------------+ | 100  | Aarav   | Delhi     | Computers  | | 101  | YashPal | Amritsar  | History    | | 105  | Gaurav  | Jaipur    | Literature | | 110  | Rahul  | Chandigarh | History    | +------+---------+------------+------------+ 4 rows ... Read More

We can create a stored procedure with IN and OUT operators to SELECT records, based on some conditions, from MySQL table. To make it understand we are taking an example of a table named ‘student_info’ having the following data −mysql> Select * from student_info; +------+---------+------------+------------+ | id   | Name    | Address    | Subject    | +------+---------+------------+------------+ | 101  | YashPal | Amritsar   | History    | | 105  | Gaurav  | Jaipur     | Literature | | 110  | Rahul   | Chandigarh | History    | | 125  | Raman   | Bangalore  | ... Read More

Following example will demonstrate MySQL stored procedure with INOUT parameter −mysql> DELIMITER // ; mysql> Create PROCEDURE counter(INOUT count INT, IN increment INT)     -> BEGIN     -> SET count = count + increment;     -> END // Query OK, 0 rows affected (0.03 sec)Here, ‘count’ is the INOUT parameter, which can store and return values and ‘increment’ is the IN parameter, which accepts the values from user.mysql> DELIMITER ; mysql> SET @counter = 0; Query OK, 0 rows affected (0.00 sec) mysql> CALL counter(@Counter, 1); Query OK, 0 rows affected (0.00 sec) mysql> Select @Counter; ... Read More