MySQL Articles - Page 372 of 439

mysql> DELIMITER // mysql> CREATE PROCEDURE get_factorial(IN N INT)     -> BEGIN     ->    SET @@GLOBAL.max_sp_recursion_depth = 255;     ->    SET @@session.max_sp_recursion_depth = 255;     ->     ->    CALL factorial_recursive (N, @factorial);     ->     ->    SELECT @factorial;     -> END // Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER // mysql> CREATE PROCEDURE factorial_recursive(IN N INT, OUT factorial INT)     -> BEGIN     ->    IF N = 1 THEN     ->       SET factorial := 1;     -> ... Read More

With the help of MySQL MAKE_SET() function, we can return the values of columns from MySQL table as a set of values. To understand it, we are taking the example of Student_Name table which has the following data −mysql> Select * from Student_Name; +---------+-------+---------+ | FName   | Mname | Lname   | +---------+-------+---------+ | Rahul   | NULL  | Singh   | | Gaurav  | Kumar | NULL    | | Harshit | NULL  | Khurana | | Yash    | Pal   | Sharma  | +---------+-------+---------+ 4 rows in set (0.00 sec)Now, suppose if we want to make ... Read More

MySQL MAKE_SET() function will return nothing if there are all NULL at the place of strings. Following example will demonstrate it −Examplemysql> Select MAKE_SET(2, NULL,NULL,NULL); +-----------------------------+ | MAKE_SET(2, NULL,NULL,NULL) | +-----------------------------+ |                             | +-----------------------------+ 1 row in set (0.00 sec)

A stored procedure is called recursive if it calls itself. Basically, this concept is called recursion. MySQL limits the recursion so the errors will be less rigorous. We can check this limit with the help of the following query −mysql> Show variables LIKE '%recur%'; +------------------------+-------+ | Variable_name          | Value | +------------------------+-------+ | max_sp_recursion_depth |   0   | +------------------------+-------+ 1 row in set (0.01 sec)We can change this value up to 255 with the help of the following query −mysql> SET @@GLOBAL.max_sp_recursion_depth = 255// Query OK, 0 rows affected (0.00 sec) mysql> Show variables LIKE ... Read More

Following is a stored procedure which fetches the records from name column of table ‘student_info’ having the following data −mysql> Select * from Student_info; +-----+---------+------------+------------+ | id  | Name    | Address    | Subject    | +-----+---------+------------+------------+ | 101 | YashPal | Amritsar   | History    | | 105 | Gaurav  | Chandigarh | Literature | | 125 | Raman   | Shimla     | Computers  | | 127 | Ram     | Jhansi     | Computers  | +-----+---------+------------+------------+ 4 rows in set (0.00 sec) mysql> Delimiter // mysql> CREATE PROCEDURE cursor_defined(OUT val ... Read More

As we know that CONCAT() function returns NULL if any of the arguments is NULL but CONCAT_WS() function returns NULL only if the first argument i.e. the separator is NULL and it ignores any other NULL. We can say this is the advantage of CONCAT_WS() function over CONCAT() function when we want to concatenate the values from the column and any of the columns have NULL as its value. To understand it, we consider the example from the table ‘Student_name; which have the following data −mysql> Select * from Student_Name; +---------+-------+---------+ | FName   | Mname | Lname   | ... Read More

MySQL FORMAT() function, converts a number to a format like #, ###, ###.### which is rounded up to the number of decimal places specified and returns the result as a string, can be used to retrieve the output having decimal values of a column in a specified format. To understand it, we are taking an example of table ‘estimated_cost’ which have the following data −mysql> Select * from estimated_cost; +----+-----------------+-----------+---------------+ | Id | Name_Company    | Tender_id | Tender_value  | +----+-----------------+-----------+---------------+ | 1  | ABC Ltd.        | 110       | 256.3256879   | | 2 ... Read More

We must have to declare NOT FOUND handler while working with MySQL cursor because it handles the situation when cursor could not find any row. It also handles the situation when the cursor reaches the end of the row because every time we call FETCH statement the cursor finds to attempt the next row in the result set. Following is the syntax to declare NOT FOUND handler −DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_name = value;Here var_name is the name of any variable and value would be the value of that variable. For example, we can declare it as ... Read More

Problem Statement How can we overcome the property of CONCAT() function that it returns NULL if any one of the argument is NULL, especially when we want to concatenate the values from the column and any of the columns have NULL as its value? The above-said property is not useful especially in the case when we want to concatenate the values from the column and any of the columns have NULL as its value. To overcome this, we can use IFNULL() function along with CONCAT() function. To ... Read More

Actually, CONCAT_WS() function returns NULL if and only if the first argument of it i.e. the separator is NULL. An example is as below −mysql> Select CONCAT_ws(NULL, 'Tutorial', 'Point', '.com'); +-------------------------------------------+ | CONCAT_ws(NULL, 'Tutorial', 'Point', '.com') | +-------------------------------------------+ | NULL                                      | +-------------------------------------------+ 1 row in set (0.00 sec)Otherwise, MySQL CONCAT_WS() function ignores NULL if we place NULL at any other position in CONCAT_WS() function while linking the strings. Following examples will exhibit it −mysql> Select CONCAT_ws('s', 'Tutorial', 'Point', '.com', NULL); +-----------------------------------------------+ | ... Read More