Let’s dig further into the scenario we have with the logistics business. The business owners reached out and asked if all of their invoices could be stored accordingly in the database. This obviously sounds like a great idea. They further asked if the invoice creation could be done automatically.

First we will have to think through how we will design the invoice table. We will need an id to use for the invoices as a primary id. We will also want to record the account_id the invoice was for. Additionally some other important points of information that will be necessary for the invoice will be the distance the run was and the date that the run was made. Two new fields that are crucial for the invoice is the date that the invoice will be issued and the price that the invoice will be.

Through discussion with the business owners, let’s say that you’ve created a table with the details below.

You decide to first tackle the invoice price. You find out that the price is determined solely by the distance as this point. If the distance is less than a 100 miles, it’s $15 per mile. If the distance is between 100-200 miles the price is $12 per mile. If the distance is larger than 200, then it’s $10 per mile. Once we have that price we will add a 8% tax. To do this calculation, we will create a MySQL function.

Let’s take a close look at the code below and read through it.

We are already familiar with temporarily changing the delimiter to something other than “;”, so that we could delimit a code block. You could see the syntax of creating a function in defining the datatype that will provided as a parameter, in this case it’s an INT and we will name the parameter as “input_distance.” We could also see that similar to other coding languages that you might already be familiar with, we define the return datatype. In this case, we will be returning the price, which will have decimal points (cents), so we are using FLOAT.

One interesting point, that you don’t always see in other coding languages is “DETERMINISTIC.” In MySQL it is required to state if the function is “DETETMINISTIC” or “NOT DETERMINISTIC.” If a function is deterministic, it means that the same result will always be produced with the same input. A non-deterministic function will be a function that even with the same input, a different output (return) could be given. A straightforward example of a non-deterministic function could be a function that uses a random number (for instance using the RAND() function) within it’s process.

This is important in the perspective of database management as if there are fields that are using non-deterministic functions this will imply different measures required for keeping the data consistent and maintaining the level of data integrity necessary. You could imagine a situation of a field being random, and the number changing during some updates or unintentionally and impacting all the other data connected to it. But for this case, we will always get the same price if the distance is the same, so we go with DETERMINISTIC.

Now that we’ve covered the over structure of the function, let’s now move on to the actual process of the function. We start out by using DECLARE to define a variable “return_value” as a FLOAT. You could also see that at the end of the function, we use RETURN to return this value. There are slight differences in syntax with other commonly used coding languages, but you could see that the IF, THEN, ELSEIF and END IF structure resembles other languages and decently readable.

To do some calculations we use the SET statement. One helpful function here is the TRUNCATE() function. If you provide TRUNCATE() with a float and specify the number of decimal points you want the result to be, it will truncate the float after the specified number of decimal points. In our scenario, the run_distance is defined as an INT. So you won’t get more than two decimal points in the result of after adding the tax. But as the return value of the function will be used as a price, it’s a good measure to make sure the output is cleaned up.

Now, let’s try it out!

Once you’ve created a function you could now run the function to get the results.

If you “Refresh All” by right clicking on the “Navigator” pane in MySQL Workbench, you could now see that “distance_to_price” is showing up as a function for this database.

If you click on the small wrench icon, it will open up an editor so you could view and/or modify the function directly. This is quite helpful as if we didn’t have such features, we would have to DROP the whole function and recreate it whenever we need a slight modification.

The lighting icon next to the function in the navigation pane is also helpful as it brings up a window to conveniently execute the function.

We now have the table for the invoices to go into set up, and we have a function that will calculate the price to be invoiced based on the distance. So it’s time to turn our focus to creating the invoice rows.

The business owners told us that whenever there is a run, they would like an invoice row to be automatically created and the invoice date should be 14 days from the day of the run.

Thinking through what we’ve covered in creating triggers, we could create a TRIGGER that creates an invoice every time a new run is being inserted. As this will be an INSERT action, we could use the NEW notation, and we could use our nice new function to capture the price.

On this thread, is some code similar to below coming to mind?

With the knowledge and experience we’ve gained so far, hopefully most of the TRIGGER above makes good sense. One new notable function above is the DATE_ADD function. It takes two parameters and does exactly what the name implies and adds in an interval to the date, so we get the desired effect of storing the date the invoice needs to list.

Alright! We are now ready to test out our automation!

We will insert a new run entry to the run table.

After you’ve inserted the new run, try checking the invoice table.

You could see that by inserting a run to the run table, it trigger the process to create an invoice automatically by utilizing a some functions, one of them being a function that has been custom created.

Congratulations! You have now automated MySQL!

There are two videos for this topic, here and then here.