Importance of Mysql Functions
Mysql which has already lot of built in function that provides flexibility during writing of SQL Queries.Apart from that mysql allows us to create our own custom functions as well. These custom functions are known as mysql function.
Mysql functions are different than mysql procedures in broadly following sense:
>> Stored function return single value
>> Procedures return value using OUT and INOUT whereas function uses RETURN keyword for that.
>> Unlike stored procedures stored functions can be used directly inside the SQL statements like SELECT, UPDATE, DELETE and INSERT.
Its such a great feature that can encapsulate commonly used business rules and formula. Even you can control the full program within a single mysql function and return your intended result after execution of program.
Here are an example of creating a stored function
CREATE FUNCTION cust_status (in_status CHAR(1))
RETURNS VARCHAR(20)
BEGIN
IF in_status =’N’ THEN
RETURN (‘NEW’);
ELSEIF in_status =’R’ THEN
RETURN (‘ReTURNING’);
ELSEIF in_status =’E’ THEN
RETURN (‘EXPIRED’);
END IF;
END;
Make sure you are using Delimiter $ .
We can use this function directly in our SQL Queries:
SELECT cust_status(‘N’) AS STATUS
To Delete this function from mysql
DROP FUNCTION IF EXISTS cust_status;
Chandra Shekhar
Latest posts by Chandra Shekhar (see all)
- Best practices for micro service design - January 23, 2022
- Spring Boot - January 23, 2022
- Java - January 23, 2022
Recent Comments