Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
As we know that whenever an exception occurred in MySQL stored procedure, it is very important to handle it by throwing proper error message because if we do not handle the exception, there would be a chance to fail application with that certain exception in a stored procedure. MySQL provides a handler that throws an error message and exits the execution. To demonstrate it, we are using the following example in which we are trying to insert a duplicate value in a Primary key column.Examplemysql> Delimiter // mysql> Create Procedure Insert_Studentdetails3(S_Studentid INT, S_StudentName Varchar(20), S_Address Varchar(20)) -> BEGIN ... Read More
Actually, INTERVAL() function uses the binary search for searching the bigger number than the number at first argument. So, that is why if we want INTERVAL() function to work efficiently the list of numbers would be in ascending order. Following is a good way to use INTERVAL() function −mysql> Select INTERVAL(50,20,32,38,40,50,55);
In this case, MySQL INTERVAL() function returns the index number of the last number in argument list plus 1. In other words, the last index number in the list plus 1 would be returned by this function. Following example will demonstrate it −mysql> Select INTERVAL(50,20,32,38,40); +--------------------------+ | INTERVAL(50,20,32,38,40) | +--------------------------+ | 4 | +--------------------------+ 1 row in set (0.00 sec)
Basically, IN() comparison function checks whether a value is within a set of values or not. If the value is within a set of values then it returns 1 otherwise 0. Its syntax can be as follows;Expression IN (val1, val2, …, valN)Here, The expression is the value that is to be searched within the set of N values within IN list.Val1, val2, …, valN is the set of N values, forms the IN list, from which the search happens.Examplemysql> Select 100 IN (50, 100, 200, 400, 2000); +------------------------------+ | 100 IN (50, 100, 200, 400, 2000) | +------------------------------+ | ... Read More
In MySQL, both IS and IS NOT operators are used to test a value against a Boolean value.The syntax of IS operator can be as follows −Val IS Boolean_valHere Val is the value that we want to test against Boolean value.Boolean_val is the Boolean value against which the value would be tested and it can be TRUE, FALSE or UNKNOWN.The syntax of IS NOT operator can be as follows −Val IS NOT Boolean_valHere Val is the value that we want to test against Boolean value.Boolean_val is the Boolean value against which the val would be tested and it can be TRUE, FALSE or UNKNOWN.Following MySQL statements will demonstrate ... Read More
MySQL provides us a LOOP statement that executes a block of code repeatedly along with an added flexibility of using a loop label. We have the following two statements that allow us to control the loop −LEAVE statementIt allows us to exit the loop immediately without waiting for checking the condition.Iterate statementIt allows us to skip the entire code under it and start a new iteration.To demonstrate the use of LOOP statement with stored procedures, the following is a stored procedure which constructs a string with even numbers like 2, 4, 6, 8 etc. −mysql> Delimiter // mysql> CREATE PROCEDURE LOOP_loop() ... Read More
In this scenario, we need to use the name of the column as ‘Expression’ which will then be compared with the values in the list. If a column has value/s matched within the list, the output would be produced. For understanding it, consider the example from employee table having the following data −mysql> Select * from Employee; +----+--------+--------+ | ID | Name | Salary | +----+--------+--------+ | 1 | Gaurav | 50000 | | 2 | Rahul | 20000 | | 3 | Advik | 25000 | | 4 | Aarav | 65000 | | 5 | Ram | 20000 | ... Read More
We can also use IN() function to compare row constructors. Consider the following example to make it clearer −mysql> Select (10,2) IN ((5,10),(10,2),(2,10),(100,100)); +--------------------------------------------+ | (10,2) IN ((5,10),(10,2),(2,10),(100,100)) | +--------------------------------------------+ | 1 | +--------------------------------------------+ 1 row in set (0.00 sec) mysql> Select (10,2) IN ((5,10),(2,10),(100,100)); +-------------------------------------+ | (10,2) IN ((5,10),(2,10),(100,100)) | +-------------------------------------+ | 0 | +-------------------------------------+ 1 row in set (0.00 sec)
We can use a cursor to handle a result set inside a stored procedure. Basically a cursor allows us to iterate a set of rows returned by a query and process each row accordingly.To demonstrate the use of CURSOR 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 | Literature ... Read More
Following are the two cases when MySQL IN() function returns NULL as result −Case-1 − When expression on left side is NULL IN() function will return NULL if the expression on the left side is NULL. Following example will demonstrate it −mysql> Select NULL IN (1, 2, 3, 4, 10); +----------------------+ | NULL IN (1, 2, 3, 4, 10) | +----------------------+ | NULL | +----------------------+ 1 row in set (0.00 sec)Case-2 − When one of expression in the list is NULL and no match is foundIN() function will return NULL if it does ... Read More