![Trending Articles on Technical and Non Technical topics](/images/trending_categories.jpeg)
Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Found 4219 Articles for MySQLi
![Prabhas](https://www.tutorialspoint.com/assets/profiles/13554/profile/60_90506-1512543588.jpg)
631 Views
If we have ALTER ROUTINE privileges for the procedure then with the help of ALTER PROCEDURE statement we can alter a MySQL stored procedure. To demonstrate it we are taking an example of a stored procedure named ‘delete_studentinfo’ which have the following create a statement −mysql> SHOW CREATE PROCEDURE Delete_studentinfo\G *************************** 1. row *************************** Procedure: Delete_studentinfo sql_mode: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `Delete_studentinfo`( IN p_id INT) BEGIN DELETE FROM student_info WHERE ID=p_id; END character_set_client: cp850 collation_connection: cp850_general_ci Database Collation: ... Read More
![Priya Pallavi](https://www.tutorialspoint.com/assets/profiles/13562/profile/60_91320-1512544134.jpg)
870 Views
To demonstrate it we are creating a procedure named ‘selectdetails()’ which will fetch all the records from table ‘student_detail’.mysql> Delimiter // mysql> Create Procedure selectdetails() -> BEGIN -> Select * from student_detail; -> END// Query OK, 0 rows affected (0.00 sec)Now, after invoking this procedure, we will get all the records from ‘student_detail’ table.mysql> Delimiter ; mysql> CALL selectdetails(); +-----------+-------------+------------+ | Studentid | StudentName | address | +-----------+-------------+------------+ | 100 | Gaurav | Delhi | | 101 | Raman | Shimla | | 103 | Rahul | Jaipur | | 104 | Ram | Chandigarh | | 105 | Mohan | Chandigarh | +-----------+-------------+------------+ 5 rows in set (0.00 sec) Query OK, 0 rows affected (0.01 sec)
![seetha](https://www.tutorialspoint.com/assets/profiles/13549/profile/60_42938-1512640859.jpg)
413 Views
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 uses SQLSTATE for default MySQL error 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_Studentdetails4(S_Studentid INT, S_StudentName Varchar(20), S_Address Varchar(20), OUT got_error ... Read More
![Nikitha N](https://www.tutorialspoint.com/assets/profiles/13558/profile/60_76068-1512713229.jpg)
458 Views
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
![Krantik Chavan](https://www.tutorialspoint.com/assets/profiles/13545/profile/60_126883-1512724834.jpg)
160 Views
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
![vanithasree](https://www.tutorialspoint.com/assets/profiles/13547/profile/60_85216-1512542720.jpg)
544 Views
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
![George John](https://www.tutorialspoint.com/assets/profiles/13511/profile/60_58410-1512537824.png)
51 Views
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’)
![Monica Mona](https://www.tutorialspoint.com/assets/profiles/13520/profile/60_87298-1512650151.jpg)
75 Views
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
![Anjana](https://www.tutorialspoint.com/assets/profiles/13774/profile/60_78029-1512648681.jpg)
75 Views
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)
![Srinivas Gorla](https://www.tutorialspoint.com/assets/profiles/13556/profile/60_78057-1512712331.jpg)
274 Views
Whenever an exception occurred in MySQL stored procedure, it is very important to handle it by throwing a proper error message. Suppose, 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 to handle the exception in the stored procedure. Followings are the four kinds of MySQL handlers which can be used in a stored procedure −DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT 'got an error';The above handler will throw an error message and continues the execution.DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET got_error=1;The above ... Read More