MySQLi Articles - Page 339 of 388

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 sets the particular value of a variable and continues 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.mysql> DELIMITER // mysql> Create Procedure Insert_Studentdetails2(S_Studentid INT, S_StudentName Varchar(20), S_Address Varchar(20), OUT ... Read More

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 continues 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_Studentdetails(S_Studentid INT, S_StudentName Varchar(20), S_Address Varchar(20))    -> BEGIN   ... Read More

Actually, MySQL has different comparison rules for quoted values such as strings and unquoted values such as numbers. On mixing the quoted and unquoted values in IN() function list may lead to the inconsistent result set. For example, we must not write the query with IN() function like below − Select Salary from employee where ID IN(1,’a’,2,3,’c’) Instead of this the better way to write the above query is as follows − Select Salary from employee where ID IN(‘1’,’a’,’2’,’3’,’c’)

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)

It is very important to handle the errors by throwing a proper error message. MySQL provides a handler to handle the error. We can declare a handler with the help of the following syntax −Syntax of handlerDECLARE handler_action FOR condition_value statement;The above syntax shows that we need to use DECLARE HANDLER statement to declare a handler. If a condition whose value matches the condition_value then MySQL will execute the statement and continue or exit the current code block based on the action. Followings are the three major things in the above syntax −Handler_action is of two types and can accept one ... Read More

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

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

When we use NULL-safe operator with row comparisons like (A, B) (C, D) then its performance is equivalent to (A C) AND (B D). Following example will demonstrate it −mysql> Select (100,50) (50,100); +-----------------------+ | (100,50) (50,100) | +-----------------------+ |                     0 | +-----------------------+ 1 row in set (0.00 sec) mysql> Select (100,50) (100,50); +-----------------------+ | (100,50) (100,50) | +-----------------------+ |                     1 | +-----------------------+ 1 row in set (0.00 sec)The above result sets show how NULL-safe operators can be used with row comparison.