MySQL Functions and Custom Logic

Introduction

In this lab, you will learn how to create and use custom functions in MySQL. The lab focuses on encapsulating reusable logic within your database by creating a function to calculate the area of a rectangle.

The steps involve connecting to the MySQL server, creating a database (if needed), defining the calculate_rectangle_area function with input parameters for length and width, and specifying the return data type. The function body calculates the area and returns the result. Subsequent steps will cover using this function in a SELECT query, testing its output with sample data, and finally, dropping the function.

Connect to MySQL and Create a Database

In this step, you will connect to the MySQL server and create a database where you will create and use your custom function.

First, open your terminal and connect to the MySQL server as the root user. Run the following command once at the beginning of the lab:

sudo mysql -u root

This command uses sudo to run the mysql client as the root user. Since the root user in this environment does not have a password set, you will be connected directly to the MySQL shell. You will see the mysql> prompt.

Remain in the MySQL shell for all subsequent steps. Do not exit until the end of the lab.

Now, let's create a database named my_functions_db. This database will be used to store our table and function. Execute the following SQL command at the mysql> prompt:

CREATE DATABASE IF NOT EXISTS my_functions_db;

After creating the database, select it to make it the default database for subsequent operations:

USE my_functions_db;

You should see a message indicating that the database has been changed.

Now you are connected to the my_functions_db database and are ready to create your custom function in the next step.

Create a Function to Calculate Rectangle Area

In this step, you will create a custom function in MySQL to calculate the area of a rectangle. This function will take the length and width as input and return the calculated area.

Make sure you are still in the MySQL shell and have already selected the my_functions_db database as described in the previous step.

Now, let's create the function named calculate_rectangle_area. This function will accept two integer parameters, length and width, and return an integer representing the area.

Execute the following SQL command in the MySQL shell:

DELIMITER //

CREATE FUNCTION calculate_rectangle_area (length INT, width INT)
RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE area INT;
  SET area = length * width;
  RETURN area;
END //

DELIMITER ;

Let's break down this command:

DELIMITER //: This changes the statement delimiter from the default semicolon (;) to //. This is necessary because the function definition contains semicolons within its body.
CREATE FUNCTION calculate_rectangle_area (length INT, width INT): This defines the function name (calculate_rectangle_area) and its input parameters (length and width, both of type INT).
RETURNS INT: This specifies that the function will return an integer value.
DETERMINISTIC: This keyword indicates that the function will always produce the same output for the same input values. This helps MySQL optimize queries that use the function.
BEGIN ... END: This block contains the function's logic.
DECLARE area INT;: This declares a local variable named area of type INT within the function.
SET area = length * width;: This calculates the area by multiplying the length and width parameters and assigns the result to the area variable.
RETURN area;: This returns the value stored in the area variable as the function's output.
END //: This marks the end of the function definition, using the temporary delimiter //.
DELIMITER ;: This changes the delimiter back to the default semicolon (;).

After executing this command, the calculate_rectangle_area function will be created in the my_functions_db database.

To verify that the function was created successfully, you can list the functions in the current database:

SHOW FUNCTION STATUS LIKE 'calculate_rectangle_area';

This command will display information about the function, confirming its existence.

Use the Function in a SELECT Query

Now that you have created the calculate_rectangle_area function, you can use it in a SELECT query to calculate the area of rectangles based on data stored in a table.

Continue working in the same MySQL shell session and ensure you are still using the my_functions_db database.

First, let's create a table named rectangles to store the dimensions of different rectangles. Execute the following SQL command:

CREATE TABLE IF NOT EXISTS rectangles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  length INT,
  width INT
);

This command creates a table with an auto-incrementing id as the primary key, and columns for length and width.

Next, insert some sample data into the rectangles table:

INSERT INTO rectangles (length, width) VALUES (5, 10);
INSERT INTO rectangles (length, width) VALUES (7, 8);
INSERT INTO rectangles (length, width) VALUES (12, 3);

These commands add three rows to the rectangles table with different length and width values.

Now, you can use the calculate_rectangle_area function in a SELECT query to retrieve the rectangle dimensions and their calculated areas. Execute the following SQL command:

SELECT id, length, width, calculate_rectangle_area(length, width) AS area FROM rectangles;

In this query:

SELECT id, length, width: This selects the id, length, and width columns from the rectangles table.
calculate_rectangle_area(length, width): This calls the calculate_rectangle_area function, passing the values from the length and width columns of each row as arguments.
AS area: This assigns an alias area to the result returned by the function, making the output column more readable.
FROM rectangles: This specifies that the data should be retrieved from the rectangles table.

The output of this query will show the id, length, width, and the calculated area for each rectangle in the table. You should see output similar to this:

+----+--------+-------+------+
| id | length | width | area |
+----+--------+-------+------+
|  1 |      5 |    10 |   50 |
|  2 |      7 |     8 |   56 |
|  3 |     12 |     3 |   36 |
+----+--------+-------+------+

This demonstrates how to effectively use your custom function within a standard SQL query to perform calculations on your data.

Test Function Output with Sample Data

In this step, you will further test the calculate_rectangle_area function by inserting more sample data into the rectangles table and verifying the calculated areas. This helps ensure the function works correctly with different input values.

Continue working in the same MySQL shell session and ensure you are still using the my_functions_db database.

Now, insert additional rows into the rectangles table with different length and width values:

INSERT INTO rectangles (length, width) VALUES (10, 10);
INSERT INTO rectangles (length, width) VALUES (2, 5);
INSERT INTO rectangles (length, width) VALUES (15, 4);

These commands add three more rows to the rectangles table.

Now, execute the SELECT query again to see the calculated areas for all the rectangles, including the newly added ones:

SELECT id, length, width, calculate_rectangle_area(length, width) AS area FROM rectangles;

The output will now include the areas for the new rectangles:

+----+--------+-------+------+
| id | length | width | area |
+----+--------+-------+------+
|  1 |      5 |    10 |   50 |
|  2 |      7 |     8 |   56 |
|  3 |     12 |     3 |   36 |
|  4 |     10 |    10 |  100 |
|  5 |      2 |     5 |   10 |
|  6 |     15 |     4 |   60 |
+----+--------+-------+------+

Review the output and confirm that the calculated areas for the new rows are correct:

For length 10 and width 10, the area is 100 (10 * 10).
For length 2 and width 5, the area is 10 (2 * 5).
For length 15 and width 4, the area is 60 (15 * 4).

This step reinforces your understanding of how to use the function with varying data and confirms its accuracy.

Drop the Function and Table

In this final step, you will clean up the database by dropping the calculate_rectangle_area function and the rectangles table. This is a good practice to remove objects you no longer need.

Continue working in the same MySQL shell session and ensure you are still using the my_functions_db database.

First, let's drop the calculate_rectangle_area function. Execute the following SQL command:

DROP FUNCTION IF EXISTS calculate_rectangle_area;

DROP FUNCTION: This command is used to remove a function.
IF EXISTS: This clause prevents an error if the function does not exist.

Next, let's drop the rectangles table. Execute the following SQL command:

DROP TABLE IF EXISTS rectangles;

DROP TABLE: This command is used to remove a table.
IF EXISTS: This clause prevents an error if the table does not exist.

After executing these commands, both the function and the table will be removed from the my_functions_db database.

To verify that the function has been dropped, you can try to list the functions again:

SHOW FUNCTION STATUS LIKE 'calculate_rectangle_area';

This command should now return an empty result set, indicating that the function is gone.

To verify that the table has been dropped, you can try to select from it:

SELECT * FROM rectangles;

This command should return an error similar to ERROR 1146 (42S02): Table 'my_functions_db.rectangles' doesn't exist, indicating that the table is gone.

Now you can exit the MySQL shell by typing exit and pressing Enter.

You have successfully created, used, tested, and dropped a custom function and a table in MySQL.

Summary

In this lab, you learned how to create a function in MySQL to encapsulate reusable logic. The process involved connecting to the MySQL server, creating a database (if needed), and then defining a function with input parameters, a return data type, and the DETERMINISTIC keyword.

Specifically, you created the calculate_rectangle_area function, which takes the length and width of a rectangle as input and returns its area. The function body included declaring a local variable to store the calculated area and using the RETURN statement to return the result.

You then learned how to use this function in a SELECT query to perform calculations on data stored in a table. You also tested the function with additional sample data to ensure its accuracy. Finally, you learned how to drop the function and the table to clean up your database environment.

This lab provided a practical introduction to creating and using custom functions in MySQL, a valuable skill for encapsulating logic and improving the efficiency of your database operations.