How to write a MySQL stored function that inserts values in a table?

As we know that function is best used when we want to return a result. Hence, when we will create stored functions for manipulating tables like to Insert or Update values then it would be more or less like stored procedures.

Example

In the following example we are creating a stored function named ‘tbl_insert’ which will insert the values in a table named ‘student_marks’.

mysql> Create Function tbl_insert(S_name Varchar(50),M1 INT,M2 INT,M3 INT,M4 INT)
    -> RETURNS INT
    -> DETERMINISTIC
    -> BEGIN
    -> INSERT INTO student_marks values(S_name,M1,M2,M3,M4);
    -> RETURN 1;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> Select tbl_insert('Saurabh',85,79,65,71);
+------------------------------+
| tbl_insert('RR',58,25,65,32) |
+------------------------------+
|                            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 |    NULL |      86 |      81 |
| Saurabh |   85 |      79 |      65 |      71 |
+---------+------+---------+---------+---------+
4 rows in set (0.00 sec)

Giri Raju

Published on 21-Feb-2018 12:01:05

• Related Questions & Answers
• How to write a MySQL stored function that updates the values in a table?
• How can I create a MySQL stored procedure that returns multiple values from a MySQL table?
• How can I write a MySQL stored function that calculates the factorial of a given number?
• How can we create a MySQL stored function that uses the dynamic data from a table?
• How can I create a stored procedure to insert values in a MySQL table?
• How can I create a stored procedure to update values in a MySQL table?
• How to repeat the values stored in a data column of MySQL table?
• How can I create a stored procedure to delete values from a MySQL table?
• MySQL Stored Procedure to create a table?
• How can we write MySQL stored procedure to select all the data from a table?
• How to fasten MySQL inserts?
• How can we write MySQL handler in a stored procedure?
• How MySQL stored function evaluates if it got NULL value while using the dynamic values from a table?
• How can we alter a MySQL stored function?
• How can we handle NULL values stored in a MySQL table by using PHP script?