How To: Stored Function In MySQL (3 Min) | Learn Create, Drop Custom Functions With Example

In this tutorial, you'll learn how to create and drop stored functions in the MySQL database using Create Function & Drop Function syntax.

—
—
Video Transcript:
—
—
Hi guys, this is Abhi from Gokcedb. In this video, you're going to learn how to Create a Stored Function in the MySQL database. Let's start by looking at the entity relationship diagram.

I have seven tables in my database right now but in this tutorial, we'll mostly be working on the property table. Let's run a select query where we select the property name, county, size in acres, and cost basis from the property table for five records. The cost basis column, tells us how much total we paid for the property but say you wanted to calculate the cost basis price per acre.

To do that, we're going to use a stored function. Create a SQL file and start with setting the delimited to dollar dollar. This will prevent us from interfering with the semicolon which is used as a delimiter for regular SQL statements. next, use the create function function name syntax followed by the parameters in parentheses.

The first parameter will be the cost basis and the second parameter will be the size in acres. The returns keyword specifies the data type of what this function will return followed by the characteristic of this function. Deterministic means that this function will always return the same result given the same input parameters.

The meat of the function is defined between the beginning and the end markers. Ude the declare keyword to declare a variable followed by the data type. The set keyword is used to set a value to the variable.

Here we're calculating the price by dividing cost spaces by the size in acres. Next, we're specifying that this function will return the value of price followed by the end keyword specifying the end of the function and we're resetting the delimiter back to the semicolon. Now, I'll use MySQL Alias where I'm passing the authentication details using a config file.

Let's input the create function SQL file to the MySQL Alias. Use the show function status command to verify the status of the function. And to check the definition of the function, use the show create function function name syntax to call this function, we have to use it within the select statement.

Here, I'm reusing the select statement we looked at earlier and I'm going to append the name of the function at the end of the select column name part. As you may recall, the first parameter is the cost basis and the second parameter is the size in acres. I also use the as a keyword to rename the column to cost basis per acre to verify if the cost basis was calculated correctly, multiply the output with size in acres to see if you get the cost spaces back.

Finally, if you wanted to delete the function, use the drop function function name syntax. There you have it. Make sure you like, subscribe, and turn on the notification bell.

Until next time.