Creating and using stored functions

Creating and using stored functions

Online by JSC0d3
July 1, 2014 | | 1432 recognitions

This MySQL article explains how to create and drop functions in MySQL with syntax and examples. Stored functions are defined in a similar manner to stored procedures, except that the command to use is the CREATE FUNCTION command.

And while it isn’t mandatory for a stored procedure to return output to the caller, stored functions must necessarily produce a return value.

DELIMITER //
CREATE FUNCTION today()
RETURNS VARCHAR(255)
BEGIN
RETURN DATE_FORMAT(NOW(), '%D %M %Y');
END//

As with the CREATE PROCEDURE command, the CREATE FUNCTION command must be followed by the name of the stored function. The same rules that govern procedure names also apply to function names. Input parameters to the function, if any, appear within parentheses following the function name, together with their data type. The function’s return value (only a single return value is possible) is represented by a mandatory RETURNS clause that follows the parentheses; this RETURNS clause specifies the data type of the return value.
The main body of the function can contain SQL statements, variable definitions, conditional tests, loops, and error handlers. It must also include a RETURN statement, which specifies the value to return to the caller. However, because stored functions cannot return result sets, take care to ensure that your RETURN statement does not return the output of a SELECT (or any other command that returns a result set).

When MySQL encounters a RETURN statement inside a stored function, it halts processing at that point and exits the function with the specified return value.

To invoke a stored function, you don’t need to use the CALL command; instead, use the function name within a SQL statement, as you would for any other built-in function.

SELECT today();

To remove a stored function, use the DROP FUNCTION command with the function name as argument:

DROP FUNCTION today();

Create stored functions

To view the body of a specific stored function, use the SHOW CREATE FUNCTION command with the function name as argument.

Because stored functions use a separate RETURNS clause to define their output, all the parameters that appear within the parentheses in the function definition are assumed to be input parameters, and the OUT and INOUT keywords are not required (or supported) for the input argument list. These input parameters can then be manipulated or used for calculations within the function body.

DELIMITER //
CREATE FUNCTION get_circle_area(radius INT)
RETURNS FLOAT
BEGIN
RETURN PI() * radius * radius;
END//

You can now pass this function the length of a circle’s radius and receive the corresponding circle area.

SELECT get_circle_area(10);

Stored functions can also manipulate tables, just like stored procedures.

DELIMITER //
CREATE FUNCTION add_flight_dep(fid INT, depday INT, deptime
TIME)
RETURNS INT
BEGIN
INSERT INTO flightdep (FlightID, DepDay, DepTime)
VALUES (fid, depday, deptime);
RETURN 1;
END//

 

JSC0d3's Logo
About JSC0d3

JSC0d3 is an entrepreneur, online marketer, and an employee of an IT company. When not building websites, creating content, or helping customers improve their online business, spend time with their wife and two beautiful children. Although he still feels new in WordPress, he enjoys sharing what he has learned with all of you! If you want to get in touch with him, you can do this through this website.

On the same idea

Posted by | July 8, 2014

MySQL also supports loops in stored routines, thus enabling routines that repeat a series of actions until a prespecified condition is fulfilled
...

Posted by | July 4, 2014

In addition to storing and retrieving values in variables, MySQL lets programmers evaluate different conditions that occur during routine execution...

Posted by | July 4, 2014

A variable is a named data object whose value can change during the stored procedure execution We typically use the variables in stored procedures to...

Previous PostBackNext Post

Leave here an impression