As we all know, when we want to return a result, it is best to use a function. So when we create a stored function to operate on a table such as inserting or updating values, it is more or less similar to a stored procedure. In the following example, we will create a stored function named "tbl_update" which will update the values in the table named "student_marks".
mysql> Select * from student_marks// +---------+------+---------+---------+---------+ | Name | Math | English | Science | History | +---------+------+---------+---------+---------+ | Raman | 95 | 89 | 85 | 81 | | Rahul | 90 | 87 | 86 | 81 | | Mohit | 90 | 85 | 86 | 81 | | Saurabh | NULL | NULL | NULL | NULL | +---------+------+---------+---------+---------+ 4 rows in set (0.00 sec) mysql> Create Function tbl_Update(S_name Varchar(50),M1 INT,M2 INT,M3 INT,M4 INT) -> RETURNS INT -> DETERMINISTIC -> BEGIN -> UPDATE student_marks SET Math = M1,English = M2, Science = M3, History =M4 WHERE Name = S_name; -> RETURN 1; -> END // Query OK, 0 rows affected (0.03 sec) mysql> Select tbl_update('Saurabh',85,69,75,82); +------------------------------------+ | tbl_update('Saurabh',85,69,75,82) | +------------------------------------+ | 1 | +------------------------------------+ 1 row in set (0.07 sec) mysql> Select * from Student_marks; +---------+------+---------+---------+---------+ | Name | Math | English | Science | History | +---------+------+---------+---------+---------+ | Raman | 95 | 89 | 85 | 81 | | Rahul | 90 | 87 | 86 | 81 | | Mohit | 90 | 85 | 86 | 81 | | Saurabh | 85 | 69 | 75 | 82 | +---------+------+---------+---------+---------+ 4 rows in set (0.00 sec)
The above is the detailed content of How to write a MySQL stored function to update a value in a table?. For more information, please follow other related articles on the PHP Chinese website!